一次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)
-------------------------------------------------------------------------------------