缺少log_archive_config導致歸檔路徑被禁用
10g的DATA GUARD的一個主要特點就是引入了log_archive_config引數，如果缺少這個引數，可能會導致歸檔路徑被禁用。
看別人建立DATA GUARD時碰到了這個問題，當時覺得比較有意思，於是特意重現一下。
當前是一個已經配置好的DATA GUARD，為了模擬錯誤，先將這個引數設定為空：
SQL> alter system set log_archive_config = '';
System altered.
SQL> alter system switch logfile;
System altered.
SQL> show parameter archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=standby LGWR SYNC VALI
D_FOR=(ONLINE_LOGFILES,PRIMARY
_ROLE) DB_UNIQUE_NAME=standby
SQL> alter system set log_archive_dest_2 = 'SERVICE=standby ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
System altered.
SQL> alter system switch logfile;
System altered.
日誌切換後，alert檔案中並沒有任何的錯誤，包含重新設定log_archive_dest_2引數後再次執行切換日誌，alert檔案中仍然看不到預期的錯誤。
導致問題無法重現的原因可能是由於系統中已經設定過log_archive_config引數了，雖然現在置為空，但是這個引數的生效可能會一直保留，於是嘗試重啟資料庫：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> alter system set log_archive_dest_2 = 'SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
System altered.
SQL> alter system switch logfile;
System altered.
這是從alert檔案中已經可以看到預期的錯誤了：
Thu Dec 23 21:42:48 2010
Completed: ALTER DATABASE OPEN
Thu Dec 23 21:42:51 2010
Thread 1 advanced to log sequence 11
Current log# 2 seq# 11 mem# 0: /data/oradata/primary/redo02.log
Thu Dec 23 21:42:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:42:51 2010
FAL[server, ARC1]: Error 16057 creating remote archivelog file 'standby'
FAL[server, ARC1]: FAL archive failed, see trace file.
Thu Dec 23 21:42:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Thu Dec 23 21:42:51 2010
ORACLE Instance primary - Archival Error. Archiver continuing.
Thu Dec 23 21:43:46 2010
Shutting down archive processes
Thu Dec 23 21:43:51 2010
ARCH shutting down
ARC2: Archival stopped
Thu Dec 23 21:45:10 2010
ALTER SYSTEM SET log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' SCOPE=BOTH;
LNS1 started with pid=18, OS id=18425
Thu Dec 23 21:45:29 2010
Thread 1 advanced to log sequence 12
Current log# 3 seq# 12 mem# 0: /data/oradata/primary/redo03.log
Thu Dec 23 21:45:29 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:45:29 2010
FAL[server, ARC1]: Error 16057 creating remote archivelog file 'standby'
FAL[server, ARC1]: FAL archive failed, see trace file.
Thu Dec 23 21:45:29 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Thu Dec 23 21:45:29 2010
ORACLE Instance primary - Archival Error. Archiver continuing.
Thu Dec 23 21:50:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:50:51 2010
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
Thu Dec 23 21:55:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:55:51 2010
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
Thu Dec 23 22:00:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 22:00:51 2010
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
錯誤資訊很明確ORA-16057，說明當前的SERVICE歸檔設定的主機沒有包括在DATA GUARD配置中。
下面將log_archive_config引數新增回來：
SQL> alter system set log_archive_config = 'DG_CONFIG=(primary,standby)';
System altered.
SQL> alter system switch logfile;
System altered.
檢查alert檔案：
Thu Dec 23 22:05:29 2010
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(primary,standby)' SCOPE=BOTH;
Thu Dec 23 22:05:40 2010
Thread 1 advanced to log sequence 13
Current log# 1 seq# 13 mem# 0: /data/oradata/primary/redo01.log
Thu Dec 23 22:05:40 2010
ARC0: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: destination Data Guard configuration error
這時，第二個預期的錯誤也出現了對於LOG_ARCHIVE_DEST_2引數設定的路徑被禁止掉。
SQL> show parameter log_archive_dest_%2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=standby LGWR ASYNC VAL
ID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=standby
log_archive_dest_state_2 string enable
僅從引數上看，log_archive_dest_state_2的值仍然是enable，但是這個歸檔路徑已經不會完成歸檔操作了：
SQL> select name, sequence#
2 from v$archived_log
3 order by 2, 1;
NAME SEQUENCE#
------------------------------------------------------------ ----------
3
/data/oradata/primary/archivelog/1_4_737020478.dbf 4
standby 4
4
/data/oradata/primary/archivelog/1_5_737020478.dbf 5
standby 5
/data/oradata/primary/archivelog/1_6_737020478.dbf 6
standby 6
/data/oradata/primary/archivelog/1_7_737020478.dbf 7
standby 7
/data/oradata/primary/archivelog/1_8_737020478.dbf 8
standby 8
/data/oradata/primary/archivelog/1_9_737020478.dbf 9
standby 9
/data/oradata/primary/archivelog/1_10_737020478.dbf 10
/data/oradata/primary/archivelog/1_11_737020478.dbf 11
/data/oradata/primary/archivelog/1_12_737020478.dbf 12
/data/oradata/primary/archivelog/1_13_737020478.dbf 13
18 rows selected.
最近4個歸檔都沒有想到遠端，檢查V$ARCHIVE_DEST檢視：
SQL> select dest_name, status, error
2 from v$archive_dest
3 where dest_id = 2;
DEST_NAME STATUS ERROR
-------------------- --------- ------------------------------------------------------------
LOG_ARCHIVE_DEST_2 DISABLED ORA-16057: DGID from server not in Data Guard configuration
可以看到，路徑2對應的狀態是DISABLED，透過設定log_archive_dest_state_2為ENABLE，可以解決這個問題：
SQL> alter system set log_archive_dest_state_2 = enable;
System altered.
SQL> alter system switch logfile;
System altered.
檢查檢視狀態：
SQL> select name, sequence#
2 from v$archived_log
3 order by 2, 1;
NAME SEQUENCE#
------------------------------------------------------------ ----------
3
/data/oradata/primary/archivelog/1_4_737020478.dbf 4
standby 4
4
/data/oradata/primary/archivelog/1_5_737020478.dbf 5
standby 5
/data/oradata/primary/archivelog/1_6_737020478.dbf 6
standby 6
/data/oradata/primary/archivelog/1_7_737020478.dbf 7
standby 7
/data/oradata/primary/archivelog/1_8_737020478.dbf 8
standby 8
/data/oradata/primary/archivelog/1_9_737020478.dbf 9
standby 9
/data/oradata/primary/archivelog/1_10_737020478.dbf 10
STANDBY 10
/data/oradata/primary/archivelog/1_11_737020478.dbf 11
STANDBY 11
/data/oradata/primary/archivelog/1_12_737020478.dbf 12
STANDBY 12
/data/oradata/primary/archivelog/1_13_737020478.dbf 13
STANDBY 13
/data/oradata/primary/archivelog/1_14_737020478.dbf 14
standby 14
24 rows selected.
SQL> select dest_name, status, failure_count, error
2 from v$archive_dest
3 where dest_id = 2;
DEST_NAME STATUS ERROR
-------------------- --------- ------------------------------------------------------------
LOG_ARCHIVE_DEST_2 VALID
有的時候並不要過分相信Oracle返回的狀態，比如這個例子中路徑已經被禁止，但是查詢引數log_archive_dest_state_2的值卻是ENABLE。而解決這個問題的辦法，又恰好就是將log_archive_dest_state_2引數的值改為ENABLE。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-683461/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 缺少歸檔導致不完整恢復
- 歸檔日誌多歸檔路徑 duplex
- 更改ORACLE歸檔路徑及歸檔模式Oracle模式
- 改變歸檔檔案路徑
- 修改歸檔日誌路徑
- 更改archive log 歸檔路徑和歸檔檔名稱Hive
- 更換歸檔日誌引數路徑導致RMAN備份時報ORA-19625錯誤
- dataguard歸檔路徑的問題
- 歸檔日誌無法歸檔導致資料庫hang住資料庫
- 更改oracle10g的歸檔模式和歸檔路徑Oracle模式
- oracle rac歸檔使用nfs 導致oracle hungOracleNFS
- oracle單機改變歸檔路徑Oracle
- oracle資料庫更改歸檔路徑Oracle資料庫
- [Archive]更改ORACLE預設歸檔路徑HiveOracle
- 修改db2的歸檔路徑DB2
- 【檔案上傳繞過】路徑拼接問題導致上傳漏洞
- 誤刪歸檔日誌除導致備份歸檔日誌失敗
- 歸檔空間不足導致例項死鎖
- standby庫歸檔日誌路徑小節
- 歸檔路徑與FRA實驗過程
- 【archive_dest】歸檔的路徑問題Hive
- ORA-00257 (線上更改歸檔路徑,刪除歸檔日誌)
- mysql主鍵的缺少導致備庫hangMySql
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- Oracle歸檔檔案丟失導致OGG不用啟動Oracle
- 非歸檔模式下的資料檔案路徑修改模式
- 【Mysql】mysql主鍵的缺少導致備庫hangMySql
- dg中備庫歸檔目錄滿了，導致不能接收主庫歸檔檔案
- 【實驗】【Archived Log】歸檔日誌格式和歸檔路徑之change趣談Hive
- Production DB expdp 導致standby歸檔恢復時混亂
- oracle歸檔日誌儲存路徑的設定Oracle
- Oracle 10G RAC歸檔引數格式問題導致歸檔至ASM時出錯Oracle 10gASM
- 歸檔問題導致的資料庫無法啟動資料庫
- 獲取當前js檔案被引用的路徑JS
- 【基本操作】快速恢復區存在時修改歸檔路徑
- 10g修改歸檔日誌路徑的問題
- 多路徑重用歸檔日誌
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫