主库到standby报错解决：Error 12154 received logging on to the standby ORA-12154

背景：
笔者建库无数，但今天对Oracle 11.2.0.3库使用duplicate新建standby后，主库redo无法通过RFS传输至standby

主库alert.log报错：
Thu Jun 14 14:05:20 2018
Error 12154 received logging on to the standby

主库报错生成trc文件：
*** 2018-06-14 14:25:20.581
Redo shipping client performing standby login
OCIServerAttach failed -1
… Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
’
OCIServerAttach failed -1
… Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
’
OCIServerAttach failed -1
… Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
’
*** 2018-06-14 14:25:20.585 4320 krsh.c
Error 12154 received logging on to the standby
*** 2018-06-14 14:25:20.585 915 krsu.c
Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host ‘agile9dg’
Error 12154 attaching to destination LOG_ARCHIVE_DEST_2 standby host ‘agile9dg’
*** 2018-06-14 14:25:20.585 4320 krsh.c
PING[ARC2]: Heartbeat failed to connect to standby ‘agile9dg’. Error is 12154.
*** 2018-06-14 14:25:20.585 2932 krsi.c
krsi_dst_fail: dest:2 err:12154 force:0 blast:1

主库 v$archive_dest
error依旧是 ORA-12154

分析：
ORA-12154此类问题通常为主库通过log_archive_dest_2使用tnsnames.ora中standby TNS文件无法访问到备库导致，
但检查：tnsnames.ora，密码文件 ,各参数，防火墙，selinux等设置均正常
问题确实奇怪，查找官方真有相关问题说明： Adding an new Standby fails with error Ora-12154: TNS:could not resolve the connect identifier specified (ID 1240558.1)

具体内容：
APPLIES TO: 涉及版本还真广
Oracle Database - Enterprise Edition - Version 9.0.1.0 to 12.2.0.1 [Release 9.0.1 to 12.2]
Information in this document applies to any platform.
SYMPTOMS 症状和我遇到完全一样
** 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

Error 12514 received logging on to the standby
Errors in file /u01/diag/rdbms/prod/PROD/trace/PROD_arc0_2596.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Corresponding archiver trace file may show:

Redo shipping client performing standby login
OCIServerAttach failed -1
… Detailed OCI error val is 12514 and errmsg is 'ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
’
OCIServerAttach failed -1
… Detailed OCI error val is 12514 and errmsg is 'ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
’
OCIServerAttach failed -1
… Detailed OCI error val is 12514 and errmsg is 'ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
’
*** 2010-11-05 08:50:39.219 1117 krsh.c
Error 12514 received logging on to the standby
Error 12514 connecting to destination LOG_ARCHIVE_DEST_2 standby host ‘remote_dest_new’

Query on V$ARCHIVE_DEST shows the following:

SQL> select dest_id,status,error from v$archive_dest;

DEST_ID STATUS ERROR

---

     1 INACTIVE 
     2 ERROR     ORA-12514: TNS:listener does not currently know of service requested in connect descriptor 
     3 INACTIVE

…

Please note that this behavior seems to have changed in 11.2, the tnsnames.ora is now being read by the ARC processes when a new remote destination is added. It is unclear when exactly this was changed.
CHANGES

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

The same error can happen on a existing standby database when tns-alias/log_archive_dest_x is changed:

Example:
log_archive_dest_2=‘service=ORCL2 …’ and ORCL2 has been defined in
TNSNAMES.ORA

• edit TNSNAMES.ORA and copy or rename the ORCL2 entry to ORCL22
• run alter system set log_archive_dest_2=‘service=ORCL22 …’
• TNS-12154 will be written to the alert file of the primary
  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.

在新建的standby后，在主库中建立了相关TNS别名到tnsnames.ora，但instance和arch进程都没有重启

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.
arc进程只有在初始化时才读取一次tnsnames.ora文件，arc进程启动后再对tnsnames.ora文件的更新引发了ORA-12154

SOLUTION

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:

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. 方法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

解决：
研究了一下采用方法2“LOG_ARCHIVE_DEST_2使用具体指明的service参数 ” 不重启instance和arc进程的方法最保险，方法2修改：
原参数：log_archive_dest_2=‘SERVICE=agile9dg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=agile9dg’
新参数：alter system set log_archive_dest_2 =‘service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.17.47)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=agile9)))" LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=agile9dg’
修改后传输正常
alert.log：
Thu Jun 14 14:25:44 2018
ALTER SYSTEM SET log_archive_dest_2=‘service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.17.47)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=agile9)))" LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=agile9dg’ SCOPE=BOTH;
Thu Jun 14 14:26:07 2018

---

LGWR: Setting ‘active’ archival for destination LOG_ARCHIVE_DEST_2

---

LGWR: Standby redo logfile selected for thread 1 sequence 1490 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 1490 (LGWR switch)
Current log# 2 seq# 1490 mem# 0: /data/oradata/agile9/log2agile9.ora
trc文件：
*** 2018-06-14 14:26:10.214
Redo shipping client performing standby login
*** 2018-06-14 14:26:10.248 4645 krsu.c
Logged on to standby successfully
Client logon and security negotiation successful!
Redo shipping client performing standby login

至此，问题解决。
如有转载请注明原文出处，谢谢

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