困扰许久的dataguard问题：error 12514 received logging on to the standby

搭建dataguard环境，主库的归档日志就是传送不到备库上

用select dest_name,error from v$archive_dest;

总是报错：

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

并且在主库的alert日志中有如下错误信息：

error 12514 received logging on to the standby

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

由于最近几个月搭建dataguard环境特别多，总会遇到这个问题，但我仔细检查过配置，tnsnames.ora，log_archive_dest_2等信息都是正确的。

但是，归档日志就是传送不过去。之前遇到这种类似情况（太多了，记不清了），有使用过alter system set log_archive_dest_state_2=defer;

然后switch logfile，接着再改成enable:alter system set log_archive_dest_state_2=enable;解决过相关问题，但今天就是不行。

以前遇到这样的情况，只好重启数据库，立马就可以将归档日志传输过去。但今天由于主库已经上生产，不能随便重启，导致我也无计可施了。

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

试着在metalink上搜了下（最近好几个月都很懒，不思进取，就想着天上掉馅饼），发现了如下一篇文章，算是对这个问题有深入的了解了：

归档进程在数据库启动后，只会在初始化过程中读取一次tnsnames.ora的信息，以后tnsnames.ora发生变化等情况，arch进程并不知晓。

这也就会导致当实例中log_archive_dest_2参数发生变化，arch识别不出相关tns别名，进而导致报错ora-12154

文章中提供的解决办法有：

1、kill掉归档进程（10g版本后不适用）

2、重启数据库（这招万能）

3、log_archive_dest_2参数不要用别名，写成连接字符串

比如：

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

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

我还是想想，怎么找机会重启数据库吧

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

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

---

In this Document

Symptoms Changes Cause Solution

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.1 [Release 9.0.1 to 11.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_<n> 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.

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.

SOLUTION

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:

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_<n> 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 <= 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: