ORA-16664: unable to receive the result from a database

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).

<strong>SOLUTION</strong>

CASE: 1

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)))';

CASE: 1

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.

SOLUTION

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.

CAUSE

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 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 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

CAUSE

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”.

SOLUTION

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

event = "16664 trace name errorstack forever, level 3"

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )