3 Replies Latest reply: Jun 5, 2013 3:17 AM by 1012736 RSS

    Data Guard Broker connecting to standby database fails

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