11.2.0.1配置DATAGUARD遭遇ORA-12514

今天同僚配置了一套DATAGUARD但是配置好後備庫沒有RFS程序，檢視日志報錯

Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'bossdbd'

Error 12154 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'bossdbd'

ORA-12154: TNS:could not resolve the connect identifier specified 

既然是報12154那麼一般說明是TNSNAMES.ORA裡面的配置不對的。但是奇怪的是，我使用

sqlplus sys/***@bossdbd 連接配接備庫是沒有問題。這就說明TNSNAMES.ORA配置沒有問題。

考慮到11.2.0.1是比較低的版本，以前配置11.2.0.3并沒有這個問題，可能是BUG。随機查詢文檔，在MOS上查到如下：

Adding an new Standby fails with error Ora-12154: TNS:could not resolve the connect identifier specified (文檔 ID 1240558.1)

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.1 [Release 9.0.1 to 11.2]

After adding a new standby database, a corresponding new TNS alias entry was added to the tnsnames.ora on the primary node, but neither the instance nor the archiver processes were restarted.

The ARC processes read the tnsnames.ora only once during process initialization, any updates to the tnsnames.ora after startup will not be known to the ARC process and hence the error

ORA-12154: TNS:could not resolve the connect identifier specified

is reported when the ARC processes try to resolve the (new) value for the 'service' attribute.

解決方法如下：

1. Shutdown and restart the primary database instance.

This will cause a (short) outage of the primary database and may not be feasible for this reason.

2. Use a connect descriptor for the 'service' parameter.

Instead of using a TNS alias for the service parameter (which requires a lookup of the tnsnames.ora file) one can use the connect descriptor itself.

Assume the following (new) entry in the tnsnames.ora on the primary node:

The corresponding 'alter system' command would then be:

alter system set log_archive_dest_2 = 'service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standbynode)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STDBY)))"' ;

Please note that there's a length limit for the log_archive_dest_ parameter, so this will only work if the length of the connect string plus the length of other attributes specified does not exceed this limit.

3. Kill the ARC processes of the primary instance.

With RDBMS releases

However these commands are no longer valid with 10g and above, so to cause a respawn of the archiver processes they must be killed, they will be restarted immediately by the instance.

This solution requires due care to avoid accidentally killing other vital background processes.

The following script (ksh,bash) may assist in identifying the correct ARC processes that need to be killed:

記錄一下。