【歸檔日誌路徑】之引數 log_archive_dest、LOG_ARCHIVE_DEST_n及快速恢復區路徑關係
更改歸檔日誌路徑
1. 先檢視歸檔路徑
SYS@ORA11GR2>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
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_28 string
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string enable
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string enable
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
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SYS@ORA11GR2>
2. 修改歸檔路徑：
SYS@ORA11GR2>alter system set log_archive_dest_1='LOCATION=/home/oracle';
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_10='LOCATION=/u01/app/oracle';
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_2='LOCATION=/u01/app';
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_3='LOCATION=/u01/app';
alter system set log_archive_dest_3='LOCATION=/u01/app'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16033: parameter LOG_ARCHIVE_DEST_3 destination cannot be the same as parameter
LOG_ARCHIVE_DEST_2 destination
SYS@ORA11GR2>alter system set log_archive_dest_3='LOCATION=/u01/app/FRA';
System altered.
SYS@ORA11GR2>alter system set log_archive_min_succeed_dest=2;
System altered.
SYS@ORA11GR2>
3.再次檢視歸檔路徑：
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/home/oracle
log_archive_dest_10 string LOCATION=/u01/app/oracle
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string LOCATION=/u01/app
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string LOCATION=/u01/app/FRA
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string enable
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string enable
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
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 2
log_archive_start boolean FALSE
log_archive_trace integer 0
SYS@ORA11GR2>
4.更改log_archive_dest值
SYS@ORA11GR2>alter system set log_archive_dest='/u01/app/oracle/ORA11GR2';
alter system set log_archive_dest='/u01/app/oracle/ORA11GR2'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST
log_archive_dest和log_archive_dest_n這個引數不能同時設定。
SYS@ORA11GR2>
5.解決：
SYS@ORA11GR2>alter system set log_archive_dest_3='';
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_2='';
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_1='';
alter system set log_archive_dest_1=''
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST
requires
SYS@ORA11GR2>alter system set log_archive_min_succeed_dest=1;
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_10='';
System altered.
SYS@ORA11GR2>alter system set log_archive_dest_1='';
System altered.
SYS@ORA11GR2>
6.再次檢視歸檔路徑及快速恢復區：
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/FRA
db_recovery_file_dest_size big integer 3G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
SYS@ORA11GR2>alter system set log_archive_dest='/u01/app/FRA';
alter system set log_archive_dest='/u01/app/FRA'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST
7.解決：
SYS@ORA11GR2>show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/FRA/
db_recovery_file_dest_size big integer 3G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@ORA11GR2>alter system set db_recovery_file_dest='';
System altered.
SYS@ORA11GR2>show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 3G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@ORA11GR2>
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SYS@ORA11GR2>
再次設定快速恢復區路徑：
SYS@ORA11GR2>alter system set db_recovery_file_dest='/u01/app/FRA';
System altered.
SYS@ORA11GR2>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
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/FRA
db_recovery_file_dest_size big integer 3G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@ORA11GR2>
SYS@ORA11GR2>alter system set log_archive_dest='/u01/app/FRA';
alter system set log_archive_dest='/u01/app/FRA'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST
結論：
1. 指定了db_recovery_file_dest快速恢復區作為歸檔路徑，則不能使用LOG_ARCHIVE_DEST，不相容；
而db_recovery_file_dest和log_archive_dest_n相容，且未指定log_archive_dest_n引數時歸檔預設指向db_recovery_file_dest快速恢復區，而當log_archive_dest_n引數設定值時則歸檔目錄指定為log_archive_dest_n引數所指向的目錄。
2. log_archive_dest引數：使用log_archive_dest引數最多可設定2個歸檔路徑，透過log_archive_dest設定一個主歸檔路徑，透過LOG_ARCHIVE_DUPLEX_DEST 引數設定一個從歸檔路徑。所有的路徑必須是本地的
3.LOG_ARCHIVE_DEST_n, LOG_ARCHIVE_DEST_n 引數可以設定最多10個不同的歸檔路徑，透過設定關鍵詞location或service，該引數指向的路徑可以是本地或遠端的。
4.可見log_archive_dest和LOG_ARCHIVE_DEST_n這兩個引數都可以設定歸檔路徑，不同的是後者可以設定遠端歸檔到standby端，而前者只能歸檔到本地，且最多同時歸檔到2個路徑下
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126503/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 歸檔日誌路徑三個引數DB_RECOVERY_FILE_DEST和LOG_ARCHIVE_DEST和LOG_ARCHIVE_DEST_nHive
- 【基本操作】快速恢復區存在時修改歸檔路徑
- 修改歸檔日誌路徑
- 歸檔日誌多歸檔路徑 duplex
- standby庫歸檔日誌路徑小節
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- Oracle的閃回恢復區和歸檔日誌多路徑設定Oracle
- 【實驗】【Archived Log】歸檔日誌格式和歸檔路徑之change趣談Hive
- oracle歸檔日誌儲存路徑的設定Oracle
- 更改ORACLE歸檔路徑及歸檔模式Oracle模式
- Oracle資料庫的閃回恢復區及多歸檔路徑的設定Oracle資料庫
- ORA-00257 (線上更改歸檔路徑,刪除歸檔日誌)
- 10g修改歸檔日誌路徑的問題
- 多路徑重用歸檔日誌
- 12c日誌檔案路徑
- Rman在run命令塊裡臨時設定歸檔目錄，歸檔日誌能否恢復主要看預設的歸檔路徑
- 日誌路徑彙總
- 更換歸檔日誌引數路徑導致RMAN備份時報ORA-19625錯誤
- hadoop之 hadoop日誌存放路徑Hadoop
- 改變歸檔檔案路徑
- 歸檔路徑更改後，如何對資料庫進行恢復(轉)資料庫
- 歸檔日誌命令及引數總結
- Tomcat日誌路徑修改方法Tomcat
- DG歸檔日誌缺失恢復
- dataguard歸檔路徑的問題
- office for mac的自動恢復檔案的路徑Mac
- 更改archive log 歸檔路徑和歸檔檔名稱Hive
- 11g的alert日誌路徑
- Oracle 11g的日誌路徑Oracle
- oracle單機改變歸檔路徑Oracle
- oracle資料庫更改歸檔路徑Oracle資料庫
- [Archive]更改ORACLE預設歸檔路徑HiveOracle
- 修改db2的歸檔路徑DB2
- RMAN備份及恢復歸檔日誌的語法
- Oracle設定多個歸檔路徑生成多份歸檔日誌，Rman備份時也只備份其中的一份歸檔日誌Oracle
- Go基礎：路徑、檔名和包名的關係Go
- 恢復歸檔日誌檔案的常用方法
- 【Oracle】如何修改資料檔案和日誌檔案的路徑Oracle