10g修改歸檔日誌路徑的問題
總結了一下10g修改歸檔路徑的方法，跟自己想像中的還是有點差別的，凡事都要測試一遍才能保證是正確的。
[@more@]修改歸檔日誌的路徑有兩種方法：
1.用LOG_ARCHIVE_DEST 和LOG_ARCHIVE_DUPLEX_DEST引數。
2.用LOG_ARCHIVE_DEST_n 引數在10g中，LOG_ARCHIVE_FORMAT比以前多個了%r引數，記錄resetlogs的ID，以便可以容易的恢復過去的incarnation.如不指定此引數，預設為%t_%s_%r.dbf。
下面試試這兩種方法的異同
1.先看看b_recovery_file_dest和log_archive_dest並存會有什麼問題。
log_archive_dest='/u01/app/oracle/admin/orcl/archive'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
用此引數檔案啟動後
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
歸檔目錄依然顯示USE_DB_RECOVERY_FILE_DEST
SQL> alter system switch logfile;
System altered.
SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm516c_.arc 2009-01-
13:07:02:23
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm5185_.arc 2009-01-
13:07:03:04
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_14_4pqm54v0_.arc 2009-01-
13:07:03:15
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqk8lmn_.arc 2009-01-
13:06:32:31
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc 2009-01-
13:07:02:23
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-
13:06:32:31
7 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-
13:06:33:22
8 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc 2009-01-
13:06:42:19
9 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqk9l4t_.arc 2009-01-
13:06:32:50
10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm1mpq_.arc 2009-01-
13:07:03:04
11 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_7_4pqk7z7q_.arc 2009-01-
12:13:26:22
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
12 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-
13:06:32:50
13 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4poo3yk0_.arc 2009-01-
12:03:00:11
14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_7_4pok10bf_.arc 2009-01-
12:11:50:54
15 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_5_4poo2hrt_.arc 2009-01-
10:19:18:34
16 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4pohjz08_.arc 2009-01-
12:03:00:11
17 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_4_4poo2f69_.arc 2009-01-
09:22:05:28
18 /u01/app/oracle/admin/orcl/archive/1_15_675592897.dbf 2009-01-
13:07:05:08
19 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_15_4pqob6s1_.arc 2009-01-
13:07:05:08
19 rows selected.
switch一次發現，log_archive_dest和DB_RECOVERY_FILE_DEST目錄都產生的歸檔。
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm516c_.arc 2009-01-
13:07:02:23
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm5185_.arc 2009-01-
13:07:03:04
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_14_4pqm54v0_.arc 2009-01-
13:07:03:15
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqk8lmn_.arc 2009-01-
13:06:32:31
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc 2009-01-
13:07:02:23
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-
13:06:32:31
7 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-
13:06:33:22
8 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc 2009-01-
13:06:42:19
9 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqk9l4t_.arc 2009-01-
13:06:32:50
10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm1mpq_.arc 2009-01-
13:07:03:04
11 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_7_4pqk7z7q_.arc 2009-01-
12:13:26:22
12 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-
13:06:32:50
13 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4poo3yk0_.arc 2009-01-
12:03:00:11
14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_7_4pok10bf_.arc 2009-01-
12:11:50:54
15 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_5_4poo2hrt_.arc 2009-01-
10:19:18:34
16 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4pohjz08_.arc 2009-01-
12:03:00:11
17 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_4_4poo2f69_.arc 2009-01-
09:22:05:28
18 /u01/app/oracle/admin/orcl/archive/1_15_675592897.dbf 2009-01-
13:07:05:08
19 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_15_4pqob6s1_.arc 2009-01-
13:07:05:08
20 /u01/app/oracle/admin/orcl/archive/1_16_675592897.dbf 2009-01-
13:07:41:58
21 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_16_4pqodq87_.arc 2009-01-
13:07:41:58
22 /u01/app/oracle/admin/orcl/archive/1_17_675592897.dbf 2009-01-
13:07:43:19
23 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_17_4pqog4wq_.arc 2009-01-
13:07:43:19
23 rows selected.
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
而log_archive_format的值應用於log_archive_dest目錄中，DB_RECOVERY_FILE_DEST的檔案format不受log_archive_format參
數的影響。
2.取消db_recovery_file_dest的歸檔，只保留log_archive_dest的值
log_archive_dest='/u01/app/oracle/admin/orcl/archive'
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
用此引數檔案啟動後
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/orcl/archive
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm516c_.arc 2009-01-
13:07:02:23
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm5185_.arc 2009-01-
13:07:03:04
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_14_4pqm54v0_.arc 2009-01-
13:07:03:15
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqk8lmn_.arc 2009-01-
13:06:32:31
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc 2009-01-
13:07:02:23
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-
13:06:32:31
7 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-
13:06:33:22
8 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc 2009-01-
13:06:42:19
9 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqk9l4t_.arc 2009-01-
13:06:32:50
10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm1mpq_.arc 2009-01-
13:07:03:04
11 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_7_4pqk7z7q_.arc 2009-01-
12:13:26:22
12 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-
13:06:32:50
13 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4poo3yk0_.arc 2009-01-
12:03:00:11
14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_7_4pok10bf_.arc 2009-01-
12:11:50:54
15 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_5_4poo2hrt_.arc 2009-01-
10:19:18:34
16 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4pohjz08_.arc 2009-01-
12:03:00:11
17 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_4_4poo2f69_.arc 2009-01-
09:22:05:28
18 /u01/app/oracle/admin/orcl/archive/1_15_675592897.dbf 2009-01-
13:07:05:08
19 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_15_4pqob6s1_.arc 2009-01-
13:07:05:08
20 /u01/app/oracle/admin/orcl/archive/1_16_675592897.dbf 2009-01-
13:07:41:58
21 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_16_4pqodq87_.arc 2009-01-
13:07:41:58
22 /u01/app/oracle/admin/orcl/archive/1_17_675592897.dbf 2009-01-
13:07:43:19
23 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_17_4pqog4wq_.arc 2009-01-
13:07:43:19
24 /u01/app/oracle/admin/orcl/archive/1_18_675592897.dbf 2009-01-
13:07:44:04
25 /u01/app/oracle/admin/orcl/archive/1_19_675592897.dbf 2009-01-
13:08:02:24
25 rows selected.
3.同時設定log_archive_dest和log_archive_dest_1是不允許的，報錯如下：
SQL> startup pfile='/u01/app/oracle/admin/orcl/pfile/init.ora';
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
4.只設定log_archive_dest_1的值
#*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
log_archive_dest_1="location=/u01/app/oracle/admin/orcl/archive"
啟動後看看路徑沒有問題。
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/orcl/archive
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
需要注意的是log_archive_dest_n引數必須指定location或者service,否則會啟動時會報錯
5.再看看b_recovery_file_dest和log_archive_dest_n並存會有什麼問題。
log_archive_dest_1="location=/u01/app/oracle/admin/orcl/archive"
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
用此引數檔案啟動後
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/orcl/archive
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm516c_.arc 2009-01-
13:07:02:23
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm5185_.arc 2009-01-
13:07:03:04
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_14_4pqm54v0_.arc 2009-01-
13:07:03:15
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqk8lmn_.arc 2009-01-
13:06:32:31
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc 2009-01-
13:07:02:23
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-
13:06:32:31
7 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-
13:06:33:22
8 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc 2009-01-
13:06:42:19
9 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqk9l4t_.arc 2009-01-
13:06:32:50
10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm1mpq_.arc 2009-01-
13:07:03:04
11 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_7_4pqk7z7q_.arc 2009-01-
12:13:26:22
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
12 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-
13:06:32:50
13 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4poo3yk0_.arc 2009-01-
12:03:00:11
14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_7_4pok10bf_.arc 2009-01-
12:11:50:54
15 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_5_4poo2hrt_.arc 2009-01-
10:19:18:34
16 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4pohjz08_.arc 2009-01-
12:03:00:11
17 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_4_4poo2f69_.arc 2009-01-
09:22:05:28
18 /u01/app/oracle/admin/orcl/archive/1_15_675592897.dbf 2009-01-
13:07:05:08
19 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_15_4pqob6s1_.arc 2009-01-
13:07:05:08
20 /u01/app/oracle/admin/orcl/archive/1_16_675592897.dbf 2009-01-
13:07:41:58
21 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_16_4pqodq87_.arc 2009-01-
13:07:41:58
22 /u01/app/oracle/admin/orcl/archive/1_17_675592897.dbf 2009-01-
13:07:43:19
RECID NAME TO_CHAR
(FIRST_TIME,
---------- ------------------------------------------------------------------------------------------ --------
-----------
23 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_17_4pqog4wq_.arc 2009-01-
13:07:43:19
24 /u01/app/oracle/admin/orcl/archive/1_18_675592897.dbf 2009-01-
13:07:44:04
25 /u01/app/oracle/admin/orcl/archive/1_19_675592897.dbf 2009-01-
13:08:02:24
26 /u01/app/oracle/admin/orcl/archive/1_20_675592897.dbf 2009-01-
13:08:02:32
27 /u01/app/oracle/admin/orcl/archive/1_21_675592897.dbf 2009-01-
13:08:08:46
28 /u01/app/oracle/admin/orcl/archive/1_22_675592897.dbf 2009-01-
13:08:09:10
發現db_recovery_file_dest的設定並未起作用，只歸檔了log_archive_dest_1的路徑。這裡與同時設定
db_recovery_file_dest和log_archive_dest的值有明顯的區別。
總結：
1.db_recovery_file_dest和log_archive_dest同時設定時，會在這兩個目錄中同時產生歸檔日誌
2.db_recovery_file_dest和log_archive_dest_n同時設定時，只有log_archive_dest_n的目錄產生日誌。
3.db_recovery_file_dest和log_archive_dest_n不是同時設定
4.LOG_ARCHIVE_FORMAT的設定只對log_archive_dest和log_archive_dest_n生效，10G增加了%r引數，記錄resetlogs的ID。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1016130/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改歸檔日誌路徑
- 歸檔日誌多歸檔路徑 duplex
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- dataguard歸檔路徑的問題
- standby庫歸檔日誌路徑小節
- oracle歸檔日誌儲存路徑的設定Oracle
- 多路徑重用歸檔日誌
- oracle 10g 歸檔日誌Oracle 10g
- ORA-00257 (線上更改歸檔路徑,刪除歸檔日誌)
- 【archive_dest】歸檔的路徑問題Hive
- 修改db2的歸檔路徑DB2
- 【Oracle】如何修改資料檔案和日誌檔案的路徑Oracle
- oracle 10g 歸檔日誌清除Oracle 10g
- Tomcat日誌路徑修改方法Tomcat
- 使用歸檔日誌分析解決歸檔日誌迅速增長問題(logmnr)
- rman刪除舊的歸檔日誌問題
- 【實驗】【Archived Log】歸檔日誌格式和歸檔路徑之change趣談Hive
- oracle歸檔切換以及歸檔日誌滿報錯問題Oracle
- Oracle設定多個歸檔路徑生成多份歸檔日誌，Rman備份時也只備份其中的一份歸檔日誌Oracle
- 非歸檔模式下的資料檔案路徑修改模式
- standby無法使用歸檔日誌問題處理
- 歸檔日誌
- 修改10g的歸檔地址
- oracle 10g rac+asm 歸檔路徑磁碟組空間滿問題處理Oracle 10gASM
- 12c日誌檔案路徑
- ORA-15028歸檔日誌被鎖問題
- mysql的日誌引數修改的問題.MySql
- kvm日誌路徑
- oracle 10g rac叢集log日誌存放路徑Oracle 10g
- 日誌檔案問題
- oracle歸檔日誌Oracle
- Oracle 歸檔日誌Oracle
- 歸檔日誌挖掘
- PostgreSQL 歸檔日誌SQL
- Rman在run命令塊裡臨時設定歸檔目錄，歸檔日誌能否恢復主要看預設的歸檔路徑
- 歸檔日誌的刪除
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- 控制檔案/歸檔日誌