Causes and Solutions for DGMGRL ORA-16664 Data Guard Broker Doc ID 2494260.1
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later Information in this document applies to any platform. The Article explains the various causes and solution for ORA-16664. ORA-16664 is a generic error. For ORA-16664 on DGMGRL prompt, check primary and standby DRC log. For ORA-16664, generic error recorded in primary DRC log but check the standby DRC log to know the accompanying error. The ORA-16664 happen for many reason. Article collate most of the causes and solution.
GOAL
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s),Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner. For the purposes of this document, the following fictitious environment is used as an example to describe the procedure: Database Names: MYDB_021,CHICACO,HRPROD Standby Db Unique Name: BOSTON Host Name: NORTH_SALES Instance Name: north_sales1
The Article explains the various causes and solution for ORA-16664. ORA-16664 (ORA-16664: unable to receive the result from a database) is a generic error. For ORA-16664 on DGMGRL prompt, check primary and standby DRC log. For ORA-16664, generic error recorded in primary DRC log but check the standby DRC log to know the accompanying error. The ORA-16664 happen for many reason. Article collate most of the causes and solution.
SOLUTION
I. ORA-16664 with ORA-12514
<< Primary DRC log >>
DG 2010-09-28-13:44:56 0 2 809851344 for opcode = CTL_GET_STATUS, phase = BEGIN, req_id = 1.1.809851344 DG 2010-09-28-13:46:11 0 2 0 NSV1: Received error ORA-16664 from target remote site hrstdby. <<<<<<<<<<<<<<< ORA-16664 DG 2010-09-28-13:46:11 0 2 809851345 DMON: Database hrstdby returned ORA-16664
<< Standby DRC log >>
DG 2010-09-15-19:44:49 0 2 809835872 Operation CTL_GET_STATUS continuing with warning, status = ORA-16792 DG 2010-09-15-19:44:49 0 2 809835872 Operation CTL_GET_STATUS continuing with warning, status = ORA-16792 DG 2010-09-15-19:44:49 0 2 0 NSV0: Failed to connect to remote database hrprod. Error is ORA-12514 <<<<<<<<<<<<<<< ORA-12514 DG 2010-09-15-19:44:49 0 2 0 NSV0: Failed to send message to site hrprod. Error code is ORA-12514. DG 2010-09-15-19:44:49 0 2 0 DMON: Database hrprod returned ORA-12514
Check the non default port used in connect string of both primary and standby(tnsnames.ora).
<strong>Solution</strong>
<strong>CASE : 1</strong>
1. Changes on database level,
If non default port is used for DG transport then make sure we have a local_listener or LISTENER_NETWORKS setting to reflect the correct listener.
SQL> alter system set local_listener='<value which is LocalListenerAddress>'; Ex, SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=North_Sales)(PORT=<port#>)))';
2. Changes on DG Broker Configuration,
Check the listener services mentioned in the DG broker configuration exisit on the lsnrctl status. If service mismatch either modify the listener.ora or the DG broker configuration as expplained below
In 10G,
DGMGRL> edit database <standby> set property LocalListenerAddress='<Listener address>';
In 11G,
DGMGRL> edit database <standby> set property StaticConnectIdentifier='<Static Address>';
For example, DGMGRL> edit database <standby> set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=North_Sales)(PORT=<port#>))(CONNECT_DATA=(SERVICE_NAME=North_Sales_DGMGRL.example.com)(INSTANCE_NAME=north_sales1)(SERVER=DEDICATED)))';
<strong> CASE :2</strong>
1. Issue explained on DG Admin guide,
Refer to http://docs.oracle.com/cd/B28359_01/server.111/b28294/create_ps.htm#i66205 , it contains the complete steps to manually create the standby when the Primary is using database encryption wallet
2. If wallet is used then Copy the primary database encryption wallet to the standby database system.If the primary database has a database encryption wallet, copy it to the standby database system and configure the standby database to use this wallet.
Note: The database encryption wallet must be copied from the primary database system to each standby database system whenever the master encryption key is updated.
Encrypted data in a standby database cannot be accessed unless the standby database is configured to point to a database encryption wallet or hardware security module that contains the current master encryption key from the primary database.
II. ORA-16664 along with ORA-00604
Data Guard Broker switchover fails with an ORA-16664 and the standby DRC log shows an ORA-604.
DGMGRL> switchover to '<stdby>'; Performing switchover NOW, please wait... Error: ORA-16664: unable to receive the result from a database Failed. Unable to switchover, primary database is still "MYDB_021
DRC log : Data Guard Broker log:
2013-02-12 19:55:23.107 SQL [ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN] Executed successfully 2013-02-12 19:55:23.107 Database Resource SetState succeeded 2013-02-12 19:55:23.108 INSV: Reply received for message with 2013-02-12 19:55:23.108 req ID 1.1.1457631131, opcode CTL_SWITCH, phase TEARDOWN 2013-02-12 19:55:23.108 02001000 1457631131 DMON: Entered rfm_release_chief_lock() for CTL_SWITCH 2013-02-12 19:55:26.591 NSV0: Failed to connect to remote database MFXPRD_xstmc021. Error is ORA-00604 2013-02-12 19:55:26.592 NSV0: Failed to send message to site MYDB_021. Error code is ORA-00604. 2013-02-12 19:55:26.592 02001000 1457631131 DMON: Database MYDB_021 returned ORA-00604 2013-02-12 19:55:26.592 02001000 1457631131 for opcode = CTL_SWITCH, phase = TEARDOWN, req_id = 1.1.1457631131
CAUSE Check for the logon trigger
SQL> select owner, trigger_name, trigger_type, triggering_event from all_triggers where triggering_event like '%LOGON%' or triggering_event like '%STARTUP%' or triggering_event like '%SHUTDOWN%';
A logon trigger was created in the primary database.
<strong>Solution</strong>
Disable the logon trigger and then perform a switchover.
III. ORA-16664 with ORA-01031 passwordfile issue
ORA-16664 will report, even for passwordfile issue on standby. DGMGRL> show configuration;
Configuration - DG
Protection Mode: MaxPerformance Databases: BOSTON - Primary database stdby - Physical standby database logstdby - Logical standby database Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status: ERROR
-- ---Primary DRC log----
2012-01-02 13:46:06.850 req ID 1.1.973032160, opcode DEL_RES, phase NULL 2012-01-02 13:46:15.798 NSV2: Initialization of connection failed. 2012-01-02 13:46:15.801 NSV2: expected destination db_unique_name is chicago 2012-01-02 13:46:15.802 NSV2: database actually reached LOGSTDBY 2012-01-02 13:46:15.803 NSV2: Failed to send message to site chicago. Error code is ORA-16642. 2012-01-02 13:46:15.804 03000000 973032160 DMON: Database chicago returned ORA-16642 2012-01-02 13:46:15.805 03000000 973032160 for opcode = DEL_RES, phase = NULL, req_id = 1.1.973032160 2012-01-02 13:46:23.670 NSV2: Initialization of connection failed. 2012-01-02 13:46:23.673 NSV2: expected destination db_unique_name is chicago 2012-01-02 13:46:23.675 NSV2: database actually reached LOGSTDBY 2012-01-02 13:46:23.677 NSV2: Failed to send message to site chicago. Error code is ORA-16642. 2012-01-02 13:46:23.678 03000000 973032160 DMON: Database chicago returned ORA-16642
-------Standby DRC log ----------------
2012-01-03 13:51:24.902 Connection to database BOSTON returns ORA-01031. 2012-01-03 13:51:24.903 Please check database BOSTON is using a remote password file, 2012-01-03 13:51:24.904 its remote_login_passwordfile is set to SHARED or EXCLUSIVE, 2012-01-03 13:51:24.905 and the SYS password is the same as this database. 2012-01-03 13:51:24.905 NSV0: Failed to connect to remote database BOSTON. Error is ORA-01031 2012-01-03 13:51:24.906 NSV0: Failed to send message to site BOSTON. Error code is ORA-01031.
<strong>Cause</strong>
Passwordfile mismatch
Solution
1. Verify primary tnsnames.ora whether the connect identifier for standby database is correct and you are able to tnsping and connect using sqlplus as sysdba. 2. Make sure asswordfile is present on all nodes of rimary and all nodes of standby database.
ref : Troubleshooting ORA-16191 and ORA-1017/ORA-1031 in Data Guard Log Transport Services or Data Guard Broker (Doc ID 1368170.1) 3. Verify configuration using DGMGRL>show configuration
IV. ORA-16664 and ORA-16501
In 12.1.0.2, Broker commands are failing with the Error ORA-16664.
Broker logs reports the below Errors:
Primary Broker logs: NSV1: Unable to obtain a connect descriptor to member <standby db_unique_name>, ORA-16501) NSV1: Failed to connect to member <standby db_unique_name> NSV1: Failed to send message to site <standby db_unique_name>. Error code is ORA-16501. DMON: Database <standby db_unique_name> returned ORA-16501
Standby Broker logs: NSV0: Unable to obtain a connect descriptor to member <Primary db_unique_name>, ORA-16501) NSV0: Failed to connect to member <Primary db_unique_name> NSV0: Failed to send message to site <Primary db_unique_name>. Error code is ORA-16501. DMON: Database <Primary db_unique_name> returned ORA-16501
SQLPLUS connectivity from Primary to Standby and Standby to Primary using the 'DGConnectIdentifier' does Not report Any Errors
<strong>Cause</strong>
Unpublished Bug 21756401 - BROKER DOES NOT HANDLE DESCRIPTION_LIST and it is fixed in 12.2
Broker cannot connect to other databases when a connect description to which theDGConnectIdentifier property points contains "DESCRIPTION_LIST".
<strong>Solution</strong>
Workaround: Change the connect identifier that DGConnectIdentifier points to to a connect descriptor that contains only 1 DESCRIPTION without DESCRIPTION_LIST.
If the Workaround is Not feasible get the Patch for the Bug 21756401
NOTE : If the above mentioned scenarios didn't help, please enable trace on standby (restart standby) and check with Support,
event = "16664 trace name errorstack forever, level 3"