一次DG搭建过程中碰到的问题

原本这次去客户现场只是做一次简单的巡检，正巧碰到客户在搭建DG，顺便协助客户搭建。本来是一次很正常的搭建过程，而且对于DG的搭建也算是一名标准的熟练工。却碰到了一个比较奇怪的问题
在全部搭建完成后，开启了传输进程：
alter system set log_archive_dest_state_2=enable;
但是日志切换过程中，备库并没有接收到传输过来的日志
检查视图：
SQL> select error,destination from v$archive_dest;


ERROR
-----------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------

/arch ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
同时在后台的alert日志中看到如下错误：
Error 12154 received logging on to the standby
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance addr - Archival Error. Archiver continuing.

但是我们检测连接串，发现并没有发现问题：
tnsping没有问题
[oracle@addr ~]$ tnsping addrdg
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-APR-2015 20:25:56
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.166.209)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = addrdg)))
OK (0 msec)


直接连接备库也没有问题：
[oracle@addr ~]$ sqlplus "sys/oracle@addrdg as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 9 20:26:17 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


检查密码文件，系统防火墙，安全协议也没有问题
到这里，似乎走进了死胡同，于是乎，我们去检查系统产生的详细的arch进程的跟踪文件：
Redo shipping client performing standby login
*** 2015-04-09 14:30:05.701
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
'
*** 2015-04-09 14:30:05.702 4320 krsh.c
Error 12154 received logging on to the standby
Detailed OCI error val is 12154 and errmsg
这个报错似乎定位的更加精确
对以上的报错，我们检索了MOS，从这个报错的定位来看，我认为应该可以在MOS上发现一定的蛛丝马迹，果然，发现如下文档：
Adding an new Standby fails with error Ora-12154: TNS:could not resolve the connect identifier specified (文档 ID 1240558.1)
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) ) )

从以上信息看，似乎需要重启数据库才可以，但是作为生产数据库，并不是说重启就可以的。那我可以考虑新建一个传输进程
之前我们设置的是log_archive_dest_2。我们重置该参数，设置log_archive_dest_3参数
alter system set log_archive_dest_3=service="(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.166.209)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = addrdg)))" ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=addrdg' scope=both;
alter system set log_archive_dest_state_3 = enable;
重置后，比较幸运，并不需要重启数据库，归档就及时传送过去，且没有报错。

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

原博客地址：http://blog.itpub.net/23732248/
原作者：应以峰 (frank-ying)
-------------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/23732248/viewspace-1558544/