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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1037949/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1037949/