DataGuard切換報ora-16009錯誤的解決辦法

一. 問題描述

在做oracle data-guard的切換測試時,將生產環境切換到備庫伺服器上後,進行了日誌的切換,這時發現,日誌檔案是複製到了主庫伺服器(此時資料庫的角色為standby database)的相關目錄,當沒有得到正常的應用,在主庫伺服器的alert日誌中報"ORA-16009: remote archive log destination must be a STANDBY database"錯誤;

ORACLE 對錯誤的描述為:

$ oerr ora 16009

16009, 00000, "remote archive log destination must be a STANDBY database"

// *Cause: The database associated with the archive log destination service

// name is other than the required STANDBY type database.

// Remote archival of redo log files is not allowed to non-STANDBY

// database instances.

// *Action: Take the necessary steps to create the required compatible STANDBY

// database before retrying the ARCHIVE LOG processing.

[@more@]

二. 問題分析

主庫伺服器的hostname為:primarydb,備庫伺服器的hostname為:standbydb

資料庫生產環境原來是執行在primarydb上,現在已經透過切換命令,完成了生產環境從主庫伺服器切換到備庫伺服器。在備庫伺服器（資料庫角色為primary），進行日誌切換時，發現日誌檔案已經複製到主庫伺服器的相關目錄，但在應用日誌時報了ora-16009的錯誤,具體的日誌描述如下:

[oracle@primarydb bdump]$ tail -f alert_gridctl.log

。。。。。。

Media Recovery Log /oradata/archivelog/standby_arc/1_219_724504451.dbf

Media Recovery Waiting for thread 1 sequence 220

Mon Sep 6 11:33:16 2010

Errors in file /oracle/admin/gridctl/bdump/gridctl_arc1_15207.trc:

ORA-16009: remote archive log destination must be a STANDBY database

Mon Sep 6 11:33:16 2010

PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16009.

。。。。。。

[oracle@standbydb bdump]$ tail -f alert_gridctl.log

Errors in file /oracle/admin/gridctl/udump/gridctl_rfs_24014.trc:

ORA-16009: remote archive log destination must be a STANDBY database

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[9]: Assigned to RFS process 24049

RFS[9]: Database mount ID mismatch [0xc95dd6eb:0xc95e148e]

RFS[9]: Client instance is standby database instead of primary

RFS[9]: Not using real application clusters

Mon Sep 6 11:36:16 2010

[oracle@primarydb archivelog]$ ls -lt *

standby_arc:

-rw-r----- 1 oracle oinstall 119808 Sep 6 11:32 1_219_724504451.dbf

-rw-r----- 1 oracle oinstall 1249792 Sep 6 11:30 1_218_724504451.dbf

primary_arc:

total 484140

-rw-r----- 1 oracle oinstall 15872 Sep 6 11:14 1_217_724504451.dbf

-rw-r----- 1 oracle oinstall 49836032 Sep 6 11:14 1_216_724504451.dbf

-rw-r----- 1 oracle oinstall 98818048 Sep 5 23:32 1_215_724504451.dbf

-rw-r----- 1 oracle oinstall 99174912 Sep 5 02:15 1_214_724504451.dbf

[oracle@standbydb archivelog]$ ls -lt *

primary_arc:

-rw-r----- 1 oracle oinstall 119808 Sep 6 11:32 1_219_724504451.dbf

-rw-r----- 1 oracle oinstall 1249792 Sep 6 11:28 1_218_724504451.dbf

standby_arc:

total 387116

-rw-r----- 1 oracle oinstall 15872 Sep 6 11:14 1_217_724504451.dbf

-rw-r----- 1 oracle oinstall 49836032 Sep 6 11:14 1_216_724504451.dbf

-rw-r----- 1 oracle oinstall 98818048 Sep 5 23:32 1_215_724504451.dbf

-rw-r----- 1 oracle oinstall 99174912 Sep 5 02:15 1_214_724504451.dbf

從alert日誌檔案中,發現:"PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16009",於是考慮是不是log_archive_dest_2的設定有問題,目前主庫伺服器的資料庫角色已經轉換為standby database,不需要設定歸檔日誌的遠端路徑,所以考慮將這個引數置空。

主庫,備庫伺服器的ORACLE 相關配置檔案內容如下:

[oracle@primarydb /]$ more /etc/hosts

168.0.3.92 primarydb

168.0.3.93 standbydb

[oracle@primarydb /]$ more $ORACLE_HOME/network/admin/tnsnames.ora

PRIMARY=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primarydb )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = gridctl)

)

)

STANDBY=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = standbydb )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = gridctl)

)

)

三. 問題解決

修改備庫伺服器的log_archive_dest_2,將歸檔日誌指向主庫伺服器,同時修改主庫伺服器的log_archive_dest_2引數值為空.

[oracle@standbydb /]$ sqlplus / as sysdba

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string

SQL> alter system set log_archive_dest_2='service=primary mandatory reopen=60' scope=both;

System altered.

[oracle@primarydb /]$ sqlplus / as sysdba

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service=standby mandatory reop

en=60

SQL>ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;

System altered.

[oracle@standbydb /]$ sqlplus / as sysdba

SQL>alter system switch logfile;

該引數修改完成後,在備庫伺服器上進行日誌的切換,發現主庫伺服器的日誌檔案立即得到了應用,具體的過程可以透過alert日誌查詢到.

[oracle@primarydb /]$ tail -f alert_gridctl.log

ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;

Mon Sep 6 11:38:35 2010

RFS[1]: Archived Log: '/oradata/archivelog/standby_arc/1_220_724504451.dbf'

Mon Sep 6 11:38:39 2010

Media Recovery Log /oradata/archivelog/standby_arc/1_220_724504451.dbf

Media Recovery Waiting for thread 1 sequence 221

Mon Sep 6 11:39:17 2010

RFS[2]: Archived Log: '/oradata/archivelog/standby_arc/1_221_724504451.dbf'

Mon Sep 6 11:39:19 2010

Media Recovery Log /oradata/archivelog/standby_arc/1_221_724504451.dbf

Media Recovery Waiting for thread 1 sequence 222

[oracle@primarydb /]$ tail -f alert_gridctl.log

Thread 1 advanced to log sequence 221 (LGWR switch)

Current log# 1 seq# 221 mem# 0: /oradata/gridctl/redo01.log

Mon Sep 6 11:39:17 2010

Thread 1 advanced to log sequence 222 (LGWR switch)

Current log# 2 seq# 222 mem# 0: /oradata/gridctl/redo02.log