主庫到standby報錯解決：Error 12154 received logging on to the standby ORA-12154

** checked for relevance '23-Nov-2015' **

When adding or changing the parameter log_archive_dest_ to point to a newly created standby database, the archiver process for the new destination reports the following error in the alert log

Corresponding archiver trace file may show:

Query on V$ARCHIVE_DEST shows the following:

 CHANGES

Added a new standby database and updated the tnsnames.ora with a new TNS alias for the new standby.

CAUSE 引起原因

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.

1. Shutdown and restart the primary database instance.  方法1. 關閉重啟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.  方法2. log_archive_dest_2 使用具體指明的serivice引數，而不使用tns別名方式

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:

REMOTE_DEST_NEW = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY) ) ) 

The corresponding 'alter system' command would then be:

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.  方法3. kill 主庫arc程式(重啟arc程式)

With RDBMS releases <= 9.2 it was possible to stop and restart the archiver processes by issuing 'archive log stop' followed by 'archive log start'.
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:

ps -ef|egrep "ora_arc.*_${ORACLE_SID}"|grep -v grep |while read user pid junk
do
 echo "kill -9 $pid"
done