溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
歸檔日志即重做日志的備份，使用歸檔日志的目的是為了實(shí)現(xiàn)介質(zhì)恢復(fù)。
日志操作模式
1． Noarchivelog(非歸檔模式)
不保存重做日志。
不能在open狀態(tài)下進(jìn)行物理備份；要定期執(zhí)行完全數(shù)據(jù)庫備份；只能將數(shù)據(jù)庫恢復(fù)到上次的完全備份點(diǎn)。
2． Archivelog
當(dāng)進(jìn)行日志切換時，ARCH進(jìn)程會將重做日志的內(nèi)容復(fù)制到歸檔日志中。
在歸檔重做日志前，新事務(wù)變化不能覆蓋舊事務(wù)變化。
可以在open 狀態(tài)下進(jìn)行物理備份。
可以將數(shù)據(jù)庫恢復(fù)到失敗前的狀態(tài)。
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;--這個視圖查詢?nèi)罩厩袚Q的頻率
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記錄的數(shù)據(jù)庫所有的歸檔日志信息，在刪除歸檔日志的時候不能在操作系統(tǒng)下面直接刪除以為就可以了，v$archived_log里面的記錄還是不會變話的，要想刪除歸檔日志必須使用rman來刪除，這樣oracle數(shù)據(jù)庫才知道變化，或者使用操作系統(tǒng)命令來刪除也是可以的，但還是要使用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
確定參數(shù)
也可以用
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
創(chuàng)建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
還原默認(rèn)值只需要將
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：日志序列號（帶有前導(dǎo)）
l %t：重做線程編號
l %T：重做線程編號（帶有前導(dǎo)）
l %a：活動ID號
l %d：數(shù)據(jù)庫ID號
l %r：resetlogs的ID值
修改格式 alter system set log_archive_format=''
切換Redo Log
1：alter system checkpoint;
強(qiáng)迫oracle進(jìn)行以次檢查點(diǎn)，確保所有提交的事務(wù)的改變都被寫到磁盤數(shù)據(jù)文件上。但此時數(shù)據(jù)庫必須是打開的狀態(tài)
2： alter system archive log all;
手工歸檔所有的日志文件組
3：alter system archive log current; --建議在mount狀態(tài)
手工歸檔活動的日志文件組
4： alter system switch logfile;
開始寫新的日志文件組。不管當(dāng)前日志文件組是否滿了
在mount狀態(tài)修改啟用手動歸檔
alter database archivelog manual;
恢復(fù)
alter database archivelog;
狀態(tài)查詢 select log_mode from v$database;
設(shè)置flashback 相關(guān)
與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：指定歸檔文件存放的路徑，該路徑只能是本地磁盤，默認(rèn)為’’。
LOG_ARCHIVE_DEST_n：默認(rèn)值為’’。Oracle最多支持把日志文件歸檔到10個地方，n從1到30。歸檔地址可以為本地磁盤，或者網(wǎng)絡(luò)設(shè)備。
DB_RECOVERY_FILE_DEST：指定閃回恢復(fù)區(qū)路徑。
三者關(guān)系：
1、 如果設(shè)置了DB_RECOVERY_FILE_DEST，就不能設(shè)置LOG_ARCHIVE_DEST，默認(rèn)的歸檔日志存放于DB_RECOVERY_FILE_DEST指定的閃回恢復(fù)區(qū)中。可以設(shè)置LOG_ARCHIVE_DEST_n，如果這樣，那么歸檔日志不再存放于DB_RECOVERY_FILE_DEST中，而是存放于LOG_ARCHIVE_DEST_n設(shè)置的目錄中。如果想要?dú)w檔日志繼續(xù)存放在DB_RECOVERY_FILE_DEST中，可以通過如下命令：alter system set log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST’;
2、 如果設(shè)置了LOG_ARCHIVE_DEST，就不能設(shè)置LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST。如果設(shè)置了LOG_ARCHIVE_DEST_n，就不能設(shè)置LOG_ARCHIVE_DEST。也就是說，LOG_ARCHIVE_DEST參數(shù)和DB_RECOVERY_FILE_DEST、LOG_ARCHIVE_DEST_n都不共存。而DB_RECOVERY_FILE_DEST和LOG_ARCHIVE_DEST_n可以共存。
3、 LOG_ARCHIVE_DEST只能與LOG_ARCHIVE_DUPLEX_DEST共存。這樣可以設(shè)置兩個歸檔路徑。LOG_ARCHIVE_DEST設(shè)置一個主歸檔路徑，LOG_ARCHIVE_DUPLEX_DEST設(shè)置一個從歸檔路徑。所有歸檔路徑必須是本地的。
4、 如果LOG_ARCHIVE_DEST_n設(shè)置的路徑不正確，那么Oracle會在設(shè)置的上一級目錄歸檔。比如設(shè)置LOG_ARCHIVE_DEST_1=’location=C:\archive1’，而OS中并沒有archive1這個目錄，那么Oracle會在C盤歸檔。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼