Friday, June 6, 2014

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


We have configured a new dataguard database. So we want to verify whether the dataguard configuration is set up good.

So we have used 12c Cloud control to verify the Data Guard Configuration and it has ended with the above error.

How to Verify Dataguard Configuration using 12c Cloud Control?

http://stepintooracledba.blogspot.com/2014/06/verify-data-guard-configuration-using.html

##########################
## Error
##########################

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

### Full Error

DGMGRL> show configuration;
Configuration - oralin_dg
  Protection Mode: MaxPerformance
  Databases:
    oralin   - Primary database
    oralin_stdby - Physical standby database
      Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

##########################
#  Error Occurred
##########################

Error occured while running a "show configuration" command in DGMGRL in 11.2.0.3.0 version

##########################
## Command Executed
##########################

In DGMGRL,

show configuration;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Verify Data Guard Configuration in 12c Cloud Control
#########################################

Initializing
Connected to instance host01:oralin
Starting alert log monitor...
Updating Data Guard link on database homepage...
WARNING: Broker name (oralin) and target name (oralin_host01) do not match.
WARNING: The broker name will be renamed to match the target name.
WARNING: Broker name (oralin_stdby) and target name (oralin_stdby_host02) do not match.
WARNING: The broker name will be renamed to match the target name.
Skipping verification of fast-start failover static services check.
Data Protection Settings:
  Protection mode : Maximum Performance
  Redo Transport Mode settings:
    oralin: ASYNC
    oralin_stdby: ASYNC
  Checking standby redo log files.....not checked due to broker name mismatch. Run verify again.
Checking Data Guard status
  oralin : Normal
  oralin_stdby : ORA-16664: unable to receive the result from a database
Checking inconsistent properties
Checking agent status
WARNING: No credentials available for target. host01
Attempting agent ping ... Down
WARNING: No credentials available for target. host02
Attempting agent ping ... Down
Processing completed.

=====================================================================================================================
Verification of Data Guard in 12c oem gives Below Error for standby Database
ORA-16664: unable to receive the result from a database
=====================================================================================================================

#########################################
# 2) Reproduce the Error
#########################################

Show configuration in DGMGRL in standby site gives the error.

[oracle@host02 trace]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - oralin_dg
  Protection Mode: MaxPerformance
  Databases:
    oralin   - Primary database
    oralin_stdby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-16501: the Data Guard broker operation failed
ORA-16625: cannot reach database "oralin"
DGM-17017: unable to determine configuration status
DGMGRL>

=====================================================================================================================

#########################################
# 3) Check DRC logs
#########################################

#### Primary Database Log

/u01/app/oracle/diag/rdbms/oralin/oralin/trace/drcoralin.log

[oracle@host01 trace]$ vi drcoralin.log
05/31/2014 18:18:37
Site oralin_stdby returned ORA-16664.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               oralin_dg                        Warning  ORA-16607
  Primary Database            oralin                             Success  ORA-00000
  Physical Standby Database   oralin_stdby                             Error  ORA-16664

#### Standby Database Log

/u01/app/oracle/diag/rdbms/oralin_stdby/oralin_stdby/trace/drcoralin_stdby.log

[oracle@host02 trace]$ vi drcoralin_stdby.log
05/31/2014 18:34:38
Property 'DbFileNameConvert' has inconsistent values:METADATA='+DATA, +DATA', SPFILE='+DATA/oralin,+DATA/oralin_stdby', DATABASE='+DATA, +DATA'
Property 'LogFileNameConvert' has inconsistent values:METADATA='+RECO, +RECO, +DATA, +DATA', SPFILE='+DATA/oralin,+DATA/oralin_stdby,+RECO/oralin,+RECO/oralin_stdby', DATABASE='+RECO, +RECO, +DATA, +DATA'
Failed to send message to site oralin. Error code is ORA-16501.
[oracle@host02 trace]$

=====================================================================================================================
From Standby Database drcoralin_stdby.log we can see that there are inconsistent Values set
=====================================================================================================================

#########################################
# 4) Check Inconsistent Properties
#########################################

show database 'oralin_stdby' InconsistentProperties;

[oracle@host02 trace]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database 'oralin_stdby' InconsistentProperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
         oralin_stdby    DbFileNameConvert         +DATA, +DATA +DATA/oralin,+DATA/oralin_stdby         +DATA, +DATA
         oralin_stdby   LogFileNameConvert +RECO, +RECO, +DATA, +DATA +DATA/oralin,+DATA/oralin_stdby,+RECO/oralin,+RECO/oralin_stdby +RECO, +RECO, +DATA, +DATA
DGMGRL>

=====================================================================================================================

#########################################
# 5) Reason for Failure
#########################################

From the above output, we can find out the reason for failure is because of the Inconsistent Properties of db_file_name_convert and log_file_name_convert set at the database level in standby.

=====================================================================================================================

##########################
## Solution
##########################

Alter both the parameter in standby Database.

ALTER SYSTEM SET log_file_name_convert='+DATA/oralin_stdby, +DATA/oralin, +RECO/oralin_stdby, +RECO/oralin' SCOPE=SPFILE;

ALTER SYSTEM SET db_file_name_convert='+DATA/oralin_stdby','+DATA/oralin' SCOPE=SPFILE;

=====================================================================================================================
Check Inconsistent Properties
=====================================================================================================================

[oracle@host02 ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database 'oralin_stdby' inconsistentproperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
DGMGRL>

=====================================================================================================================
Verification using 12c Is also Looks good.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================



2 comments:

Anonymous said...

when checking the logfiles I saw the following msgs can you guide me how to resolve that

/u01/app/oracle/diag/rdbms/insdwp/INSDWP1/trace ==> PROD

09/16/2014 11:59:55
Site INSDWPDR returned ORA-16664.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration INSDWP Warning ORA-16607
Primary Database INSDWP Warning ORA-16629
Physical Standby Database INSDWPDR Error ORA-16664
09/16/2014 12:00:55
Error: The actual protection level 'RESYNCHRONIZATION' is different from the configured protection mode 'MAXIMUM AVAILABILITY'.



/u01/app/oracle/diag/rdbms/insdwpdr/INSDWPDR1/trace ==> INSDWPDR1

09/16/2014 12:04:30
Connection to database INSDWP returns ORA-01031.
Please check database INSDWP is using a remote password file,
its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and the SYS password is the same as this database.
Failed to connect to remote database INSDWP. Error is ORA-01031
Failed to send message to site INSDWP. Error code is ORA-01031.
09/16/2014 12:05:45

SID said...


Hi,

From our 1st trace file, the error states like your database protection_level is in RE-SYNCHRONIZATION mode.

A database PROTECTION_LEVEL goes to RE-SYNCHRONIZATION mode when there is a network disconnect between Primary and Standby. Once the network connection establishes the connectivity it will remain in RE-SYNCHRONIZATION mode until Primary and Standby is in SYNC.

Run the below query and post the protection_level you database is in.

select protection_mode, protection_level from v$database;

2nd Trace :

From the 2nd trace output, it shows like Username/Password is INVALID.

Ship the password file from Primary to Standby location and rename with the same name as of Standby instance.

This should resolve your issue.

Please let me know the outcome.