This discussion is archived
3 Replies Latest reply: Jun 5, 2013 1:17 AM by 1012736 RSS

Data Guard Broker connecting to standby database fails

1012736 Newbie
Currently Being Moderated
Hello everybody

I checked lots of pages but I'm not able to find a solution für my problem. I already set up a primary and a standby database (prim = ALPHA1 / standby = ALPHA2).
After enabling my dgmgrl configuration I got two errors:

DGM-17016: failed to retrieve status for database "alpha2"
ORA-16664: unable to receive the result from a database


The dg log from ALPHA1 says:

06/04/2013 16:06:57
Site alpha2 returned ORA-16664.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration alphadgb Warning ORA-16607
Primary Database alpha1 Success ORA-00000
Physical Standby Database alpha2 Error ORA-16664



While the dg log from ALPHA2 (standby) says:

06/04/2013 16:43:28
SPFILE is missing value for property 'LogArchiveFormat' with sid='ALPHA2'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='arch_ALPHA2_%S_%t_%r.arc', SPFILE='(missing)', DATABASE='arch_ALPHA2_%S_%t_%r.arc'
Failed to connect to remote database alpha1. Error is ORA-12514
Failed to send message to site alpha1. Error code is ORA-12514.



How can I solve this issue? Every type of tnsping is successfull. The sqlplus login from the primary to the standby database works, the other way round works too! Therefore the tnsnames and listener data seems to be correct.

My configuration for ALPHA1 (primary db):

---------------------------------------------------------------
Listener
---------------------------------------------------------------

LISTENER_ALPHA1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.3.13)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
)

SID_LIST_LISTENER_ALPHA1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ALPHA1_DGMGRL)
(ORACLE_HOME = /oracle/ALPHA1/orahome)
(SID_NAME = ALPHA1)
)
)

---------------------------------------------------------------
tnsnames.ora
---------------------------------------------------------------

ALPHA1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.3.13)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ALPHA1)
)
)

ALPHA2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.3.13)(PORT = 1522))
)
(CONNECT_DATA =
(SID = ALPHA2)
)
)

DG_ALPHA1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.3.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ALPHA1_DGMGRL)
)
)

DG_ALPHA2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.3.13)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = ALPHA2_DGMGRL)
)
)


---------------------------------------------------------------
Parameters
---------------------------------------------------------------


archive_lag_target integer 0
log_archive_config string DG_CONFIG=(ALPHA2,ALPHA1)
log_archive_dest string
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) DB_UNIQUE_NAME=ALPHA2
...
log_archive_dest_2 string SERVICE=ALPHA1 SYNC valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=ALPHA1
...
log_archive_format string arch_ALPHA2_%S_%t_%r.arc
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
standby_archive_dest string ?/dbs/arch


---------------------------------------------------------------
For the DG Broker configuration
---------------------------------------------------------------

DGMGRL>     connect sys/dgalpha42@DG_ALPHA1
DGMGRL>     create configuration ALPHADGB
DGMGRL>     primary database is ALPHA1
DGMGRL>     connect identifier is DG_ALPHA1
DGMGRL>     ;
DGMGRL>     add database ALPHA2
DGMGRL>     connect identifier is DG_ALPHA2
DGMGRL>     maintained as physical
DGMGRL>     ;

There were no errors.


---------------------------------------------------------------
DGMGRL> show database verbose ALPHA1
---------------------------------------------------------------
Database - alpha1

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ALPHA1

Properties:
DGConnectIdentifier = 'dg_alpha1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'ALPHA2, ALPHA1'
LogFileNameConvert = 'ALPHA2, ALPHA1'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'ALPHA1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprakt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ALPHA1_DGMGRL)(INSTANCE_NAME=ALPHA1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_ALPHA1_%S_%t_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS


---------------------------------------------------------------
DGMGRL> show database verbose ALPHA2
---------------------------------------------------------------

Database - alpha2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
ALPHA2

Properties:
DGConnectIdentifier = 'dg_alpha2'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'ALPHA1, ALPHA2'
LogFileNameConvert = 'ALPHA1, ALPHA2'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'ALPHA2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprakt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ALPHA2_DGMGRL)(INSTANCE_NAME=ALPHA2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_ALPHA2_%S_%t_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
DGM-17016: failed to retrieve status for database "alpha2"
ORA-16664: unable to receive the result from a database





Can anybody help me to find a solution for this?
  • 1. Re: Data Guard Broker connecting to standby database fails
    mseberg Guru
    Currently Being Moderated
    Hello;

    What my notes say :
    ORA-16607 on SHOW CONFIGURATION
     
    Problem: After creating your configuration and adding the standby database, you issued a SHOW CONFIGURATION as suggested. 
    Instead of the expected SUCCESS, the report ends up with
     
    Warning: ORA-16607: one or more databases have failed
     
    Checking with err ora 16607 was not very helpful (see above), and you neither can find anything in your alert.log nor any trace files.
     
    Cause: Probably at least one of your databases is not using an SPFILE.
     
    Solution: Check whether your databases have an SPFILE associated. 
    It is usually located in $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora. 
    If it does not exist, create it: Login to your database as SYSDBA, and issue the command CREATE SPFILE FROM PFILE;. 
    Even if it exists, to make the database using it you need to restart the instance - it must be used already at startup.
    I would consider a redo.

    How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

    To be safe I would consider recreating my standby spfile from a current pfile from the Primary and all the edits I need to make it standby.

    Broker is great when it works.

    The thing is once you start using broker you need to always use it to make any changes to your DG configuration. If you use SQL after this Broker will either put things back or it will lead to inconsistencies between the broker configuration and the database (What I believe is the issue here). This is why I say create a pfile and a new spfile for the standby because it should clear these up after you remove Broker and set it up again.

    Best Regards

    mseberg
  • 2. Re: Data Guard Broker connecting to standby database fails
    1012736 Newbie
    Currently Being Moderated
    Hey

    thanks for the answer. I followed you recommendations but I got the same error again. I restored/recovered the old status and looked deeper into the dgmgrl configuration before enabling. I found an interesting point. (show database verbose ALPHAx)

    Database - alpha1

    Role: PRIMARY
    Intended State: OFFLINE
    Instance(s):
    ALPHA1

    Properties:
    DGConnectIdentifier = 'dg_alpha1'
    ObserverConnectIdentifier = ''
    LogXptMode = 'ASYNC'
    DelayMins = '0'
    Binding = 'optional'
    MaxFailure = '0'
    MaxConnections = '1'
    ReopenSecs = '300'
    NetTimeout = '30'
    RedoCompression = 'DISABLE'
    LogShipping = 'ON'
    PreferredApplyInstance = ''
    ApplyInstanceTimeout = '0'
    ApplyParallel = 'AUTO'
    StandbyFileManagement = 'AUTO'
    ArchiveLagTarget = '0'
    LogArchiveMaxProcesses = '4'
    LogArchiveMinSucceedDest = '1'
    DbFileNameConvert = 'ALPHA2, ALPHA1'
    LogFileNameConvert = 'ALPHA2, ALPHA1'
    FastStartFailoverTarget = ''
    InconsistentProperties = '(monitor)'
    InconsistentLogXptProps = '(monitor)'
    SendQEntries = '(monitor)'
    LogXptStatus = '(monitor)'
    RecvQEntries = '(monitor)'
    SidName = 'ALPHA1'
    StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprakt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ALPHA1_DGMGRL)(INSTANCE_NAME=ALPHA1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation = ''
    LogArchiveTrace = '0'
    LogArchiveFormat = 'arch_ALPHA1_%S_%t_%r.arc'
    TopWaitEvents = '(monitor)'

    Database Status:
    DISABLED




    Database - alpha2

    Role: PHYSICAL STANDBY
    Intended State: OFFLINE
    Transport Lag: (unknown)
    Apply Lag: (unknown)
    Real Time Query: OFF
    Instance(s):
    ALPHA2

    Properties:
    DGConnectIdentifier = 'dg_alpha2'
    ObserverConnectIdentifier = ''
    LogXptMode = 'ASYNC'
    DelayMins = '0'
    Binding = 'OPTIONAL'
    MaxFailure = '0'
    MaxConnections = '1'
    ReopenSecs = '300'
    NetTimeout = '30'
    RedoCompression = 'DISABLE'
    LogShipping = 'ON'
    PreferredApplyInstance = ''
    ApplyInstanceTimeout = '0'
    ApplyParallel = 'AUTO'
    StandbyFileManagement = 'AUTO'
    ArchiveLagTarget = '0'
    LogArchiveMaxProcesses = '4'
    LogArchiveMinSucceedDest = '1'
    DbFileNameConvert = 'ALPHA1, ALPHA2'
    LogFileNameConvert = 'ALPHA1, ALPHA2'
    FastStartFailoverTarget = ''
    InconsistentProperties = '(monitor)'
    InconsistentLogXptProps = '(monitor)'
    SendQEntries = '(monitor)'
    LogXptStatus = '(monitor)'
    RecvQEntries = '(monitor)'
    SidName = 'ALPHA2'
    StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprakt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ALPHA2_DGMGRL)(INSTANCE_NAME=ALPHA2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation = ''
    LogArchiveTrace = '0'
    LogArchiveFormat = 'arch_ALPHA2_%S_%t_%r.arc'
    TopWaitEvents = '(monitor)'

    Database Status:
    DISABLED


    As the listener are configured ALPHA1 (prim) should be on port 1521 while ALPHA2 (stby) should work on 1522. In the configuration of DGMGRL only appears port 1521 (look at StaticConnectIdentifier). Is this maybe the reason of the networking problems with DG Broker? How can I fix this?


    Regards Mirko

    Edited by: 1009733 on 04.06.2013 09:22
  • 3. Re: Data Guard Broker connecting to standby database fails
    1012736 Newbie
    Currently Being Moderated
    Hey

    I solved the problem after editing the port for ALPHA2 (1521 => 1522) in the dgmgrl config. Now system is running perfectly. Thanks for you recommendations!

    Regards Mirko

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points