Archive Log的基本应用和启用
归档日志即重做日志的备份，使用归档日志的目的是为了实现介质恢复。
日志操作模式
1． Noarchivelog(非归档模式)
不保存重做日志。
不能在open状态下进行物理备份；要定期执行完全数据库备份；只能将数据库恢复到上次的完全备份点。
2． Archivelog
当进行日志切换时，ARCH进程会将重做日志的内容复制到归档日志中。
在归档重做日志前，新事务变化不能覆盖旧事务变化。
可以在open 状态下进行物理备份。
可以将数据库恢复到失败前的状态。
Oracle Redo Log模式
sys@newtestCDB> set linesize 180
sys@newtestCDB> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID 1 1 97 209715200 512 1 YES INACTIVE 5349625 2018-01-16 22:00:41 5379203 2018-01-17 07:00:02 0 2 1 98 209715200 512 1 NO CURRENT 5379203 2018-01-17 07:00:02 1.8447E+19 0 3 1 96 209715200 512 1 YES INACTIVE 5332295 2018-01-16 15:52:10 5349625 2018-01-16 22:00:41 0Elapsed: 00:00:00.03
sys@newtestCDB> col member format A80
sys@newtestCDB> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID 3 ONLINE C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO03.LOG NO 0 2 ONLINE C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO02.LOG NO 0 1 ONLINE C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO01.LOG NO 0Elapsed: 00:00:00.02
sys@newtestCDB> select * from v$log_history;--这个视图查询日志切换的频率
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 1 962645365 1 1 1490582 2017-12-13 17:22:26 1519075 1490582 2017-12-13 17:22:26 0 2 962665319 1 2 1519075 2017-12-13 17:29:25 1558070 1490582 2017-12-13 17:22:26 0 3 962726438 1 3 1558070 2017-12-13 23:01:59 1616879 1490582 2017-12-13 17:22:26 0 4 962756343 1 4 1616879 2017-12-14 16:00:38 1653100 1490582 2017-12-13 17:22:26 0 5 962802274 1 5 1653100 2017-12-15 00:19:03 1707202 1490582 2017-12-13 17:22:26 0 6 962844291 1 6 1707202 2017-12-15 13:04:34 1749462 1490582 2017-12-13 17:22:26 0 7 962877913 1 7 1749462 2017-12-16 00:44:51 1787727 1490582 2017-12-13 17:22:26 0 8 962886319 1 8 1787727 2017-12-16 10:05:13 1824534 1490582 2017-12-13 17:22:26 0 9 962903735 1 9 1824534 2017-12-16 12:25:19 1856224 1490582 2017-12-13 17:22:26 0 10 962923551 1 10 1856224 2017-12-16 17:15:35 1889226 1490582 2017-12-13 17:22:26 0 11 962945761 1 11 1889226 2017-12-16 22:45:51 1923796 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 12 962963178 1 12 1923796 2017-12-17 04:56:01 1954876 1490582 2017-12-13 17:22:26 0 13 962978793 1 13 1954876 2017-12-17 09:46:18 1988335 1490582 2017-12-13 17:22:26 0 14 963000038 1 14 1988335 2017-12-17 14:06:33 2024153 1490582 2017-12-13 17:22:26 0 15 963017817 1 15 2024153 2017-12-17 20:00:38 2060196 1490582 2017-12-13 17:22:26 0 16 963049651 1 16 2060196 2017-12-18 00:56:57 2103855 1490582 2017-12-13 17:22:26 0 17 963093693 1 17 2103855 2017-12-18 09:47:31 2148622 1490582 2017-12-13 17:22:26 0 18 963150514 1 18 2148622 2017-12-18 22:01:33 2203580 1490582 2017-12-13 17:22:26 0 19 963191355 1 19 2203580 2017-12-19 13:48:34 2246693 1490582 2017-12-13 17:22:26 0 20 963266479 1 20 2246693 2017-12-20 01:09:15 2309929 1490582 2017-12-13 17:22:26 0 21 963321052 1 21 2309929 2017-12-20 22:01:19 2362544 1490582 2017-12-13 17:22:26 0 22 963360680 1 22 2362544 2017-12-21 13:10:52 2405528 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 23 963412315 1 23 2405528 2017-12-22 00:11:20 2457100 1490582 2017-12-13 17:22:26 0 24 963449002 1 24 2457100 2017-12-22 14:31:55 2496526 1490582 2017-12-13 17:22:26 0 25 963477230 1 25 2496526 2017-12-23 00:43:22 2526834 1490582 2017-12-13 17:22:26 0 26 963496444 1 26 2526834 2017-12-23 08:33:50 2566864 1490582 2017-12-13 17:22:26 0 27 963512655 1 27 2566864 2017-12-23 13:54:04 2599710 1490582 2017-12-13 17:22:26 0 28 963531273 1 28 2599710 2017-12-23 18:24:15 2637076 1490582 2017-12-13 17:22:26 0 29 963546277 1 29 2637076 2017-12-23 23:34:32 2670552 1490582 2017-12-13 17:22:26 0 30 963561301 1 30 2670552 2017-12-24 03:44:37 2703386 1490582 2017-12-13 17:22:26 0 31 963567919 1 31 2703386 2017-12-24 07:55:01 2725147 1490582 2017-12-13 17:22:26 0 32 963579916 1 32 2725147 2017-12-24 09:45:19 2762899 1490582 2017-12-13 17:22:26 0 33 963594923 1 33 2762899 2017-12-24 13:05:16 2796793 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 34 963614748 1 34 2796793 2017-12-24 17:15:23 2838171 1490582 2017-12-13 17:22:26 0 35 963630957 1 35 2838171 2017-12-24 22:45:48 2875254 1490582 2017-12-13 17:22:26 0 36 963649511 1 36 2875254 2017-12-25 03:15:57 2908383 1490582 2017-12-13 17:22:26 0 37 963673598 1 37 2908383 2017-12-25 08:25:11 2952805 1490582 2017-12-13 17:22:26 0 38 963718444 1 38 2952805 2017-12-25 15:06:38 2995857 1490582 2017-12-13 17:22:26 0 39 963761872 1 39 2995857 2017-12-26 03:34:04 3043423 1490582 2017-12-13 17:22:26 0 40 963810038 1 40 3043423 2017-12-26 15:37:52 3087208 1490582 2017-12-13 17:22:26 0 41 963864058 1 41 3087208 2017-12-27 05:00:38 3140628 1490582 2017-12-13 17:22:26 0 42 963908903 1 42 3140628 2017-12-27 20:00:57 3181503 1490582 2017-12-13 17:22:26 0 43 963957609 1 43 3181503 2017-12-28 08:28:23 3232744 1490582 2017-12-13 17:22:26 0 44 963995376 1 44 3232744 2017-12-28 22:00:09 3269718 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 45 964044092 1 45 3269718 2017-12-29 08:29:36 3325229 1490582 2017-12-13 17:22:26 0 46 964080421 1 46 3325229 2017-12-29 22:01:32 3361924 1490582 2017-12-13 17:22:26 0 47 964095420 1 47 3361924 2017-12-30 08:07:01 3394656 1490582 2017-12-13 17:22:26 0 48 964110437 1 48 3394656 2017-12-30 12:17:00 3430490 1490582 2017-12-13 17:22:26 0 49 964130866 1 49 3430490 2017-12-30 16:27:17 3465638 1490582 2017-12-13 17:22:26 0 50 964147681 1 50 3465638 2017-12-30 22:07:46 3503501 1490582 2017-12-13 17:22:26 0 51 964165698 1 51 3503501 2017-12-31 02:48:01 3539305 1490582 2017-12-13 17:22:26 0 52 964172914 1 52 3539305 2017-12-31 07:48:18 3562148 1490582 2017-12-13 17:22:26 0 53 964184921 1 53 3562148 2017-12-31 09:48:34 3598496 1490582 2017-12-13 17:22:26 0 54 964201141 1 54 3598496 2017-12-31 13:08:41 3633366 1490582 2017-12-13 17:22:26 0 55 964220364 1 55 3633366 2017-12-31 17:39:01 3673431 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 56 964234184 1 56 3673431 2017-12-31 22:59:24 3706844 1490582 2017-12-13 17:22:26 0 57 964253348 1 57 3706844 2018-01-01 02:49:44 3741114 1490582 2017-12-13 17:22:26 0 58 964279836 1 58 3741114 2018-01-01 08:09:08 3787898 1490582 2017-12-13 17:22:26 0 59 964327993 1 59 3787898 2018-01-01 15:30:36 3831135 1490582 2017-12-13 17:22:26 0 60 964364545 1 60 3831135 2018-01-02 04:53:13 3873387 1490582 2017-12-13 17:22:26 0 61 964407622 1 61 3873387 2018-01-02 15:02:25 3916292 1490582 2017-12-13 17:22:26 0 62 964451013 1 62 3916292 2018-01-03 03:00:22 3962378 1490582 2017-12-13 17:22:26 0 63 964497627 1 63 3962378 2018-01-03 15:03:33 4007609 1490582 2017-12-13 17:22:26 0 64 964694908 1 64 4007609 2018-01-04 04:00:27 4036768 1490582 2017-12-13 17:22:26 0 65 964764019 1 65 4036768 2018-01-06 10:48:28 4084827 1490582 2017-12-13 17:22:26 0 66 964789212 1 66 4084827 2018-01-07 06:00:19 4115396 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 67 964806676 1 67 4115396 2018-01-07 13:00:12 4141184 1490582 2017-12-13 17:22:26 0 68 964832506 1 68 4141184 2018-01-07 17:51:16 4174481 1490582 2017-12-13 17:22:26 0 69 964908046 1 69 4174481 2018-01-08 01:01:46 4241143 1490582 2017-12-13 17:22:26 0 70 964919252 1 70 4241143 2018-01-08 22:00:46 4261261 1490582 2017-12-13 17:22:26 0 71 964994472 1 71 4261261 2018-01-09 01:07:32 4328022 1490582 2017-12-13 17:22:26 0 72 965035264 1 72 4328022 2018-01-09 22:01:12 4361569 1490582 2017-12-13 17:22:26 0 73 965080835 1 73 4361569 2018-01-10 09:21:04 4414925 1490582 2017-12-13 17:22:26 0 74 965095238 1 74 4414925 2018-01-10 22:00:35 4438662 1490582 2017-12-13 17:22:26 0 75 965124524 1 75 4438662 2018-01-11 02:00:38 4457139 1490582 2017-12-13 17:22:26 0 76 965168557 1 76 4457139 2018-01-11 10:08:44 4508210 1490582 2017-12-13 17:22:26 0 77 965217636 1 77 4508210 2018-01-11 22:22:37 4552303 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 78 965255015 1 78 4552303 2018-01-12 12:00:36 4598294 1490582 2017-12-13 17:22:26 0 79 965286366 1 79 4598294 2018-01-12 22:23:35 4633267 1490582 2017-12-13 17:22:26 0 80 965308587 1 80 4633267 2018-01-13 07:06:06 4673785 1490582 2017-12-13 17:22:26 0 81 965325646 1 81 4673785 2018-01-13 13:16:27 4707598 1490582 2017-12-13 17:22:26 0 82 965341017 1 82 4707598 2018-01-13 18:00:46 4737114 1490582 2017-12-13 17:22:26 0 83 965348254 1 83 4737114 2018-01-13 22:16:57 4760771 1490582 2017-12-13 17:22:26 0 84 965366847 1 84 4760771 2018-01-14 00:17:34 4797971 1490582 2017-12-13 17:22:26 0 85 965378864 1 85 4797971 2018-01-14 05:27:27 4826958 1490582 2017-12-13 17:22:26 0 86 965392082 1 86 4826958 2018-01-14 08:47:44 4865442 1490582 2017-12-13 17:22:26 0 87 965405892 1 87 4865442 2018-01-14 12:28:02 4898783 1490582 2017-12-13 17:22:26 0 88 965426925 1 88 4898783 2018-01-14 16:18:12 4938985 1490582 2017-12-13 17:22:26 0 RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME CON_ID 89 965438330 1 89 4938985 2018-01-14 22:08:45 4967429 1490582 2017-12-13 17:22:26 0 90 965460555 1 90 4967429 2018-01-15 01:18:50 5007404 1490582 2017-12-13 17:22:26 0 91 965490801 1 91 5007404 2018-01-15 07:29:15 5142845 1490582 2017-12-13 17:22:26 0 92 965493451 1 92 5142845 2018-01-15 15:53:21 5249046 1490582 2017-12-13 17:22:26 0 93 965523632 1 93 5249046 2018-01-15 16:37:31 5278163 1490582 2017-12-13 17:22:26 0 94 965566821 1 94 5278163 2018-01-16 01:00:32 5311651 1490582 2017-12-13 17:22:26 0 95 965577130 1 95 5311651 2018-01-16 13:00:21 5332295 1490582 2017-12-13 17:22:26 0 96 965599241 1 96 5332295 2018-01-16 15:52:10 5349625 1490582 2017-12-13 17:22:26 0 97 965631602 1 97 5349625 2018-01-16 22:00:41 5379203 1490582 2017-12-13 17:22:26 097 rows selected.
Elapsed: 00:00:00.27
sys@newtestCDB> select name,FIRST_CHANGE#,NEXT_CHANGE#,FIRST_TIME,NEXT_TIME from v$archived_log order by FIRST_CHANGE#;
NAME FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME NEXT_TIME
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000070_0962644946.0001 4241143 4261261 2018-01-08 22:00:46 2018-01-09 01:07:32
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000071_0962644946.0001 4261261 4328022 2018-01-09 01:07:32 2018-01-09 22:01:12
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000072_0962644946.0001 4328022 4361569 2018-01-09 22:01:12 2018-01-10 09:21:04
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000073_0962644946.0001 4361569 4414925 2018-01-10 09:21:04 2018-01-10 22:00:35
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000074_0962644946.0001 4414925 4438662 2018-01-10 22:00:35 2018-01-11 02:00:38
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000075_0962644946.0001 4438662 4457139 2018-01-11 02:00:38 2018-01-11 10:08:44
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000076_0962644946.0001 4457139 4508210 2018-01-11 10:08:44 2018-01-11 22:22:37
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000077_0962644946.0001 4508210 4552303 2018-01-11 22:22:37 2018-01-12 12:00:36
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000078_0962644946.0001 4552303 4598294 2018-01-12 12:00:36 2018-01-12 22:23:35
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000079_0962644946.0001 4598294 4633267 2018-01-12 22:23:35 2018-01-13 07:06:06
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000080_0962644946.0001 4633267 4673785 2018-01-13 07:06:06 2018-01-13 13:16:27
NAME FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME NEXT_TIME
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000081_0962644946.0001 4673785 4707598 2018-01-13 13:16:27 2018-01-13 18:00:46
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000082_0962644946.0001 4707598 4737114 2018-01-13 18:00:46 2018-01-13 22:16:57
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000083_0962644946.0001 4737114 4760771 2018-01-13 22:16:57 2018-01-14 00:17:34
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000084_0962644946.0001 4760771 4797971 2018-01-14 00:17:34 2018-01-14 05:27:27
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000085_0962644946.0001 4797971 4826958 2018-01-14 05:27:27 2018-01-14 08:47:44
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000086_0962644946.0001 4826958 4865442 2018-01-14 08:47:44 2018-01-14 12:28:02
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000087_0962644946.0001 4865442 4898783 2018-01-14 12:28:02 2018-01-14 16:18:12
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000088_0962644946.0001 4898783 4938985 2018-01-14 16:18:12 2018-01-14 22:08:45
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000089_0962644946.0001 4938985 4967429 2018-01-14 22:08:45 2018-01-15 01:18:50
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000090_0962644946.0001 4967429 5007404 2018-01-15 01:18:50 2018-01-15 07:29:15
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000091_0962644946.0001 5007404 5142845 2018-01-15 07:29:15 2018-01-15 15:53:21
NAME FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME NEXT_TIME
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000092_0962644946.0001 5142845 5249046 2018-01-15 15:53:21 2018-01-15 16:37:31
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000093_0962644946.0001 5249046 5278163 2018-01-15 16:37:31 2018-01-16 01:00:32
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000094_0962644946.0001 5278163 5311651 2018-01-16 01:00:32 2018-01-16 13:00:21
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000095_0962644946.0001 5311651 5332295 2018-01-16 13:00:21 2018-01-16 15:52:10
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000096_0962644946.0001 5332295 5349625 2018-01-16 15:52:10 2018-01-16 22:00:41
C:\APP\ADMINISTRATOR\VIRTUAL\PRODUCT\12.2.0\DBHOME_2\RDBMS\ARC0000000097_0962644946.0001 5349625 5379203 2018-01-16 22:00:41 2018-01-17 07:00:02
28 rows selected.
Elapsed: 00:00:00.08
v$archived_log记录的数据库所有的归档日志信息，在删除归档日志的时候不能在操作系统下面直接删除以为就可以了，v$archived_log里面的记录还是不会变话的，要想删除归档日志必须使用rman来删除，这样oracle数据库才知道变化，或者使用操作系统命令来删除也是可以的，但还是要使用rman来更新一下expired的日志。
sys@newtestCDB> col name format A30
sys@newtestCDB> col value format A20
sys@newtestCDB> col description format A50
sys@newtestCDB> select num,
2 name,
3 value,
4 description
5 from v$parameter
6 where name like 'log_archive_dest%'
7 /
NUM NAME VALUE DESCRIPTION 1664 log_archive_dest_1 archival destination #1 text string 1665 log_archive_dest_2 archival destination #2 text string 1666 log_archive_dest_3 archival destination #3 text string 1667 log_archive_dest_4 archival destination #4 text string 1668 log_archive_dest_5 archival destination #5 text string 1669 log_archive_dest_6 archival destination #6 text string 1670 log_archive_dest_7 archival destination #7 text string 1671 log_archive_dest_8 archival destination #8 text string 1672 log_archive_dest_9 archival destination #9 text string 1673 log_archive_dest_10 archival destination #10 text string 1674 log_archive_dest_11 archival destination #11 text string NUM NAME VALUE DESCRIPTION 1675 log_archive_dest_12 archival destination #12 text string 1676 log_archive_dest_13 archival destination #13 text string 1677 log_archive_dest_14 archival destination #14 text string 1678 log_archive_dest_15 archival destination #15 text string 1679 log_archive_dest_16 archival destination #16 text string 1680 log_archive_dest_17 archival destination #17 text string 1681 log_archive_dest_18 archival destination #18 text string 1682 log_archive_dest_19 archival destination #19 text string 1683 log_archive_dest_20 archival destination #20 text string 1684 log_archive_dest_21 archival destination #21 text string 1685 log_archive_dest_22 archival destination #22 text string NUM NAME VALUE DESCRIPTION 1686 log_archive_dest_23 archival destination #23 text string 1687 log_archive_dest_24 archival destination #24 text string 1688 log_archive_dest_25 archival destination #25 text string 1689 log_archive_dest_26 archival destination #26 text string 1690 log_archive_dest_27 archival destination #27 text string 1691 log_archive_dest_28 archival destination #28 text string 1692 log_archive_dest_29 archival destination #29 text string 1693 log_archive_dest_30 archival destination #30 text string 1694 log_archive_dest_31 archival destination #31 text string 1695 log_archive_dest_state_1 enable archival destination #1 state text string 1696 log_archive_dest_state_2 enable archival destination #2 state text string NUM NAME VALUE DESCRIPTION 1697 log_archive_dest_state_3 enable archival destination #3 state text string 1698 log_archive_dest_state_4 enable archival destination #4 state text string 1699 log_archive_dest_state_5 enable archival destination #5 state text string 1700 log_archive_dest_state_6 enable archival destination #6 state text string 1701 log_archive_dest_state_7 enable archival destination #7 state text string 1702 log_archive_dest_state_8 enable archival destination #8 state text string 1703 log_archive_dest_state_9 enable archival destination #9 state text string 1704 log_archive_dest_state_10 enable archival destination #10 state text string 1705 log_archive_dest_state_11 enable archival destination #11 state text string 1706 log_archive_dest_state_12 enable archival destination #12 state text string 1707 log_archive_dest_state_13 enable archival destination #13 state text string NUM NAME VALUE DESCRIPTION 1708 log_archive_dest_state_14 enable archival destination #14 state text string 1709 log_archive_dest_state_15 enable archival destination #15 state text string 1710 log_archive_dest_state_16 enable archival destination #16 state text string 1711 log_archive_dest_state_17 enable archival destination #17 state text string 1712 log_archive_dest_state_18 enable archival destination #18 state text string 1713 log_archive_dest_state_19 enable archival destination #19 state text string 1714 log_archive_dest_state_20 enable archival destination #20 state text string 1715 log_archive_dest_state_21 enable archival destination #21 state text string 1716 log_archive_dest_state_22 enable archival destination #22 state text string 1717 log_archive_dest_state_23 enable archival destination #23 state text string 1718 log_archive_dest_state_24 enable archival destination #24 state text string NUM NAME VALUE DESCRIPTION 1719 log_archive_dest_state_25 enable archival destination #25 state text string 1720 log_archive_dest_state_26 enable archival destination #26 state text string 1721 log_archive_dest_state_27 enable archival destination #27 state text string 1722 log_archive_dest_state_28 enable archival destination #28 state text string 1723 log_archive_dest_state_29 enable archival destination #29 state text string 1724 log_archive_dest_state_30 enable archival destination #30 state text string 1725 log_archive_dest_state_31 enable archival destination #31 state text string 1727 log_archive_dest archival destination text string63 rows selected.
Elapsed: 00:00:00.16
确定参数
也可以用
sys@newtestCDB> show parameter log_archive_dest
NAME TYPE VALUE
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
NAME TYPE VALUE
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
NAME TYPE VALUE
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
NAME TYPE VALUE
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
NAME TYPE VALUE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\app\Administrator\virtual\product\12.2.0\dbhome_2\RDBMS
Oldest online log sequence 96
Next log sequence to archive 98
Current log sequence 98
创建Archive Log存放文件夹
sys@newtestCDB> alter system set log_archive_dest='C:\app\Administrator\virtual\archivelog';
System altered.
Elapsed: 00:00:00.05
sys@newtestCDB> show parameter log_archive_dest
NAME TYPE VALUE
log_archive_dest string C:\app\Administrator\virtual\a
rchivelog
存放地址改变
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\app\Administrator\virtual\archivelog
Oldest online log sequence 96
Next log sequence to archive 98
Current log sequence 98
切换日志
sys@newtestCDB> alter system switch logfile;
System altered.
Elapsed: 00:00:00.10
还原默认值只需要将
sys@newtestCDB> alter system set log_archive_dest='';
然后重启库
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\app\Administrator\virtual\product\12.2.0\dbhome_2\RDBMS
Oldest online log sequence 99
Next log sequence to archive 101
Current log sequence 101
归档格式
sys@newtestCDB> show parameter log_archive_format
NAME TYPE VALUE
log_archiveformat string ARC%S%R.%T
%s：日志序列号
l %S：日志序列号（带有前导）
l %t：重做线程编号
l %T：重做线程编号（带有前导）
l %a：活动ID号
l %d：数据库ID号
l %r：resetlogs的ID值
修改格式 alter system set log_archive_format=''
切换Redo Log
1：alter system checkpoint;
强迫oracle进行以次检查点，确保所有提交的事务的改变都被写到磁盘数据文件上。但此时数据库必须是打开的状态
2： alter system archive log all;
手工归档所有的日志文件组
3：alter system archive log current; --建议在mount状态
手工归档活动的日志文件组
4： alter system switch logfile;
开始写新的日志文件组。不管当前日志文件组是否满了
在mount状态修改启用手动归档
alter database archivelog manual;
恢复
alter database archivelog;
状态查询 select log_mode from v$database;
设置flashback 相关
与flashback分开
sys@newtestCDB> alter system set log_archive_dest_1='location=c:\app\Administrator\virtual\archivelog';
System altered.
Elapsed: 00:00:00.04
sys@newtestCDB> show parameter db_recovery_file_dest
NAME TYPE VALUE
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\app\Administrator\virtual\archivelog
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
sys@newtestCDB> alter system set db_recovery_file_dest='C:\app\Administrator\virtual\FlashRecovery'
2 ;
alter system set db_recovery_file_dest='C:\app\Administrator\virtual\FlashRecovery'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
Elapsed: 00:00:00.05
sys@newtestCDB> alter database flashback on
2 ;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
Elapsed: 00:00:00.08
sys@newtestCDB> alter system set DB_RECOVERY_FILE_DEST_SIZE=300M;
System altered.
Elapsed: 00:00:00.04
sys@newtestCDB> alter system set db_recovery_file_dest='C:\app\Administrator\virtual\FlashRecovery';
System altered.
Elapsed: 00:00:00.11
sys@newtestCDB> alter database flashback on
2 ;
Database altered.
Elapsed: 00:00:02.25
sys@newtestCDB> show parameter db_recovery_file_dest
NAME TYPE VALUE
db_recovery_file_dest string C:\app\Administrator\virtual\F
lashRecovery
db_recovery_file_dest_size big integer 300M
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\app\Administrator\virtual\archivelog
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
LOG_ARCHIVE_DEST：指定归档文件存放的路径，该路径只能是本地磁盘，默认为''。
LOG_ARCHIVE_DEST_n：默认值为''。Oracle最多支持把日志文件归档到10个地方，n从1到30。归档地址可以为本地磁盘，或者网络设备。
DB_RECOVERY_FILE_DEST：指定闪回恢复区路径。
三者关系：
1、 如果设置了DB_RECOVERY_FILE_DEST，就不能设置LOG_ARCHIVE_DEST，默认的归档日志存放于DB_RECOVERY_FILE_DEST指定的闪回恢复区中。可以设置LOG_ARCHIVE_DEST_n，如果这样，那么归档日志不再存放于DB_RECOVERY_FILE_DEST中，而是存放于LOG_ARCHIVE_DEST_n设置的目录中。如果想要归档日志继续存放在DB_RECOVERY_FILE_DEST中，可以通过如下命令：alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';
2、 如果设置了LOG_ARCHIVE_DEST，就不能设置LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST。如果设置了LOG_ARCHIVE_DEST_n，就不能设置LOG_ARCHIVE_DEST。也就是说，LOG_ARCHIVE_DEST参数和DB_RECOVERY_FILE_DEST、LOG_ARCHIVE_DEST_n都不共存。而DB_RECOVERY_FILE_DEST和LOG_ARCHIVE_DEST_n可以共存。
3、 LOG_ARCHIVE_DEST只能与LOG_ARCHIVE_DUPLEX_DEST共存。这样可以设置两个归档路径。LOG_ARCHIVE_DEST设置一个主归档路径，LOG_ARCHIVE_DUPLEX_DEST设置一个从归档路径。所有归档路径必须是本地的。
4、 如果LOG_ARCHIVE_DEST_n设置的路径不正确，那么Oracle会在设置的上一级目录归档。比如设置LOG_ARCHIVE_DEST_1='location=C:\archive1'，而OS中并没有archive1这个目录，那么Oracle会在C盘归档。
