Wednesday, December 12, 2018

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

We have received below error when trying to enabling Data guard Broker configuration

Error: ORA-16664: unable to receive the result from a database
Dataguard broker log has below errors

Failed to connect to remote database sat. Error is ORA-12514
Failed to send message to member sat. Error code is ORA-12514.


To identify the issue review the confirmation

DGMGRL> SHOW CONFIGURATION;
Configuration - orcl_dgbroker_fsfconf
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stdby - Physical standby database
Error: ORA-16664: unable to receive the result from a member

Fast-Start Failover: DISABLED

DGMGRL> show database verbose 'ORCL';
Database - ORCL
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL
Properties:
DGConnectIdentifier = 'ORCL'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = 'dummy, dummy'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.oracleracexpert.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_DGMGRL)(INSTANCE_NAME=ORCL)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/oraarch/ORCL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_ORCL_%r_%s_%t.arc'
TopWaitEvents = '(monitor)'

Log file locations:
Alert log : /oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log
Data Guard Broker log : /oracle/diag/rdbms/ORCL/ORCL/trace/drcORCL.log

Database Status:
SUCCESS

DGMGRL> show database verbose 'ORCL_stdby';
Database - ORCL_stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ORCL
Properties:
DGConnectIdentifier = 'ORCL_stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = 'dummy, dummy'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)HOST=node2.oracleracexpert.com)(PORT=1521))(CONNECT_DATA=SERVICE_NAME=ORCL_STDBY_DGMGRL)(INSTANCE_NAME=ORCL)SERVER=DEDICATED)))'
StandbyArchiveLocation = '/oraarch/ORCL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_ORCL_%r_%s_%t.arc'
TopWaitEvents = '(monitor)'

Log file locations:
(Failed to retrieve log file locations.)
Database Status:
DGM-17016: failed to retrieve status for database "ORCL_stdby"
ORA-16664: unable to receive the result from a member


Solution:

1. Makes sure you have Static listener entry has SID_DGMGRL

cat $ORACLE_HOME/network/admin/listener.ora

(SID_DESC =
(GLOBAL_DBNAME = ORCL_DGMGRL) <----------------add _DGMGRL
(ORACLE_HOME = /<oracle_home>)
(SID_NAME = ORCL)
)

2. Same way change the static entry of current primary also for feature role transition. For non-default port set the local listener on the respective database where ORA-12514 was thrown.

SQL>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostIP>)(PORT=port)))';

Refer Below oracle notes for more scenarios
1. Causes and Solutions for DGMGRL ORA-16664 (Data Guard Broker) (Doc ID 2494260.1) 

Regards
Satishbabu Gunukula

2 comments:

  1. Hire Best Packers And Movers Mumbai for hassle-free Household Shifting, ***Office Relocation, ###Car Transporation, Loading Unloading, packing Unpacking at affordable ✔✔✔ Price Quotation. Top Rated, Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate
    Packers And Movers Mumbai

    ReplyDelete