I got ” ORA-16664: unable to receive the result from a database ” error in Oracle database.
ORA-16664: unable to receive the result from a database
Details of error are as follows.
ORA-16664: unable to receive the result from a database Cause: During execution of a command, a database in the Data Guard broker configuration failed to return a result. Action: Check the Data Guard broker logs for the details of the failure. Ensure network communication is working properly amongst the members of the configuration. Fix any possible network problems and reissue the command.
unable to receive the result from a database
This ORA-16664 error is related to the execution of a command, a database in the Data Guard broker configuration
failed to return a result.
Check the Data Guard broker logs for the details of the failure. Ensure network communication is working properly amongst the members of the configuration. Fix any possible network problems and reissue the command.
<< 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 <strong>CAUSE</strong>
DGMGRL>show database 'standby' InconsistentProperties;
Check the non default port used in connect string of both primary and standby(tnsnames.ora).
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
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)))';
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
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.
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.
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 passwordfile 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 18.104.22.168, 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
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”.
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"
631 views last month, 4 views today