12 Replies Latest reply: Nov 20, 2012 4:20 PM by mseberg RSS

    Dataguard - Primary not applying logs to Standby

    975078
      Having an issue applying logs to the standby, seemingly it's not setup correctly. I am sure I'm missing something simple here, but would love any input or help. Thanks in advance.

      Background:

      Primary: CDPMTSB (Single Stand alone)
      Standby: CDPMT (RAC)

      Error Message on Primary (Alert Log):

      Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
      ORA-16014: log 3 sequence# 4071 not archived, no available destinations
      ORA-00312: online log 3 thread 2: '+FRA_DG_01/cdpmtsb/onlinelog/group_3.291.799379949'
      ARCH: Archival error occurred on a closed thread. Archiver continuing
      ORACLE Instance CDPMTSB - Archival Error. Archiver continuing.
      Mon Nov 19 19:54:24 2012
      Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2
      Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2
      ARC6: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
      ARC6: Archive log rejected (thread 2 sequence 4071) at host 'CDPMT'
      Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
      ORA-16401: archivelog rejected by RFS
      Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
      ORA-16014: log 3 sequence# 4071 not archived, no available destinations
      ORA-00312: online log 3 thread 2: '+FRA_DG_01/cdpmtsb/onlinelog/group_3.291.799379949'
      ARCH: Archival error occurred on a closed thread. Archiver continuing
      ORACLE Instance CDPMTSB - Archival Error. Archiver continuing.
      Mon Nov 19 19:59:24 2012
      Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2
      Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2
      ARC6: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
      ARC6: Archive log rejected (thread 2 sequence 4071) at host 'CDPMT'
      Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
      ORA-16401: archivelog rejected by RFS
      Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
      ORA-16014: log 3 sequence# 4071 not archived, no available destinations
      ORA-00312: online log 3 thread 2: '+FRA_DG_01/cdpmtsb/onlinelog/group_3.291.799379949'
      ARCH: Archival error occurred on a closed thread. Archiver continuing
      ORACLE Instance CDPMTSB - Archival Error. Archiver continuing.
      Mon Nov 19 20:00:00 2012
      Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_j001_17473.trc:
      ORA-12012: error on auto execute of job 72620
      ORA-06502: PL/SQL: numeric or value error: character to number conversion error
      ORA-06512: at "CD_ADMIN.UTDCD_SURVEY_PKG", line 4926

      Standby Alert Log:

      ORA-16401: archivelog rejected by RFS
      Mon Nov 19 19:32:15 2012
      RFS[6]: Assigned to RFS process 4248
      RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 9561
      Mon Nov 19 19:32:22 2012
      RFS[1]: Selected log 6 for thread 1 sequence 4073 dbid 1629723947 branch 769881773
      Mon Nov 19 19:32:22 2012
      Archived Log entry 1097 added for thread 1 sequence 4072 ID 0x62e7f5cf dest 1:
      Archived Log entry 1098 added for thread 1 sequence 4072 ID 0x62e7f5cf dest 3:
      Mon Nov 19 19:34:23 2012
      Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_rfs_24994.trc:
      ORA-16401: archivelog rejected by RFS
      Mon Nov 19 19:38:12 2012
      RFS[1]: Selected log 5 for thread 1 sequence 4074 dbid 1629723947 branch 769881773
      Mon Nov 19 19:38:12 2012
      Archived Log entry 1099 added for thread 1 sequence 4073 ID 0x62e7f5cf dest 1:
      Archived Log entry 1100 added for thread 1 sequence 4073 ID 0x62e7f5cf dest 3:
      Mon Nov 19 19:39:23 2012
      Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_rfs_24994.trc:
      ORA-16401: archivelog rejected by RFS
      Mon Nov 19 19:44:24 2012
      Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_rfs_24994.trc:
      ORA-16401: archivelog rejected by RFS
      Mon Nov 19 19:49:24 2012
      Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_rfs_24994.trc:
      ORA-16401: archivelog rejected by RFS
      Mon Nov 19 19:54:24 2012
      Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_rfs_24994.trc:
      ORA-16401: archivelog rejected by RFS
      Mon Nov 19 19:59:24 2012
      Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_rfs_24994.trc:
      ORA-16401: archivelog rejected by RFS


      Primary Parameters:

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_config string DG_CONFIG=(CDPMT,CDPMTSB)
      log_archive_dest string
      log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
      DEST VALID_FOR=(ONLINE_LOGFIL
      ES,ALL_ROLES) DB_UNIQUE_NAME=C
      DPMTSB
      log_archive_dest_10 string
      log_archive_dest_2 string SERVICE=CDPMT VALID_FOR=(ONLIN
      E_LOGFILES,PRIMARY_ROLE) DB_UN
      IQUE_NAME=CDPMT
      log_archive_dest_3 string location="+FRA_DG_01/cdpmtsb/s
      tandbylog", valid_for=(STANDB
      Y_LOGFILE,STANDBY_ROLE)
      log_archive_dest_4 string
      log_archive_dest_5 string
      log_archive_dest_6 string
      log_archive_dest_7 string
      log_archive_dest_8 string
      log_archive_dest_9 string
      log_archive_dest_state_1 string enable
      log_archive_dest_state_10 string enable
      log_archive_dest_state_2 string ENABLE
      log_archive_dest_state_3 string ENABLE
      log_archive_dest_state_4 string defer
      log_archive_dest_state_5 string enable
      log_archive_dest_state_6 string enable
      log_archive_dest_state_7 string enable
      log_archive_dest_state_8 string enable
      log_archive_dest_state_9 string enable
      log_archive_duplex_dest string
      log_archive_format string %t_%s_%r.dbf
      log_archive_local_first boolean TRUE
      log_archive_max_processes integer 7
      log_archive_min_succeed_dest integer 2
      log_archive_start boolean FALSE
      log_archive_trace integer 0



      Standby Parameters:
      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------- -----------
      log_archive_config string dg_config=(CDPMT,CD PMTSB)
      log_archive_dest string
      log_archive_dest_1 string location="USE_DB_RE COVERY_FILE
      _DEST", valid_for= (ALL_LOGFIL
      ES,ALL_ROLES)
      log_archive_dest_10 string
      log_archive_dest_2 string SERVICE=cdpmtsb LGW R ASYNC VAL
      ID_FOR=(ONLINE_LOGF ILES,PRIMAR
      Y_ROLE) DB_UNIQUE_N AME=cdpmtsb
      log_archive_dest_3 string LOCATION=+FRA_DG_01 /CDPMT/STAN
      DBYLOG VALID_FOR=( STANDBY_LOG

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------- -----------
      FILES,STANDBY_ROLE) DB_UNIQUE_
      NAME=CDPMT
      log_archive_dest_4 string
      log_archive_dest_5 string
      log_archive_dest_6 string
      log_archive_dest_7 string
      log_archive_dest_8 string
      log_archive_dest_9 string
      log_archive_dest_state_1 string ENABLE
      log_archive_dest_state_10 string enable
      log_archive_dest_state_2 string ENABLE

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------- -----------
      log_archive_dest_state_3 string enable
      log_archive_dest_state_4 string enable
      log_archive_dest_state_5 string enable
      log_archive_dest_state_6 string enable
      log_archive_dest_state_7 string enable
      log_archive_dest_state_8 string enable
      log_archive_dest_state_9 string enable
      log_archive_duplex_dest string
      log_archive_format string %t_%s_%r.dbf
      log_archive_local_first boolean TRUE
      log_archive_max_processes integer 30

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------- -----------
      log_archive_min_succeed_dest integer 1
      log_archive_start boolean FALSE
      log_archive_trace integer 0
      SQL> show parameter log_ar

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_config string dg_config=(CDPMT,CDPMTSB)
      log_archive_dest string
      log_archive_dest_1 string location="USE_DB_RECOVERY_FILE
      _DEST", valid_for=(ALL_LOGFIL
      ES,ALL_ROLES)
      log_archive_dest_10 string
      log_archive_dest_2 string SERVICE=cdpmtsb LGWR ASYNC VAL
      ID_FOR=(ONLINE_LOGFILES,PRIMAR
      Y_ROLE) DB_UNIQUE_NAME=cdpmtsb
      log_archive_dest_3 string LOCATION=+FRA_DG_01/CDPMT/STAN
      DBYLOG VALID_FOR=(STANDBY_LOG

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      FILES,STANDBY_ROLE) DB_UNIQUE_
      NAME=CDPMT
      log_archive_dest_4 string
      log_archive_dest_5 string
      log_archive_dest_6 string
      log_archive_dest_7 string
      log_archive_dest_8 string
      log_archive_dest_9 string
      log_archive_dest_state_1 string ENABLE
      log_archive_dest_state_10 string enable
      log_archive_dest_state_2 string ENABLE

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_state_3 string enable
      log_archive_dest_state_4 string enable
      log_archive_dest_state_5 string enable
      log_archive_dest_state_6 string enable
      log_archive_dest_state_7 string enable
      log_archive_dest_state_8 string enable
      log_archive_dest_state_9 string enable
      log_archive_duplex_dest string
      log_archive_format string %t_%s_%r.dbf
      log_archive_local_first boolean TRUE
      log_archive_max_processes integer 30

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_min_succeed_dest integer 1
      log_archive_start boolean FALSE
      log_archive_trace integer 0
      SQL>


      DGMGRL> show configuration verbose;

      Configuration
      Name: cdpmtqa
      Enabled: YES
      Protection Mode: MaxPerformance
      Databases:
      cdpmtsb - Primary database
      cdpmt - Physical standby database

      Fast-Start Failover: DISABLED

      Current status for "cdpmtqa":
      Warning: ORA-16608: one or more databases have warnings


      DGMGRL> show database verbose CDPMT

      Database
      Name: cdpmt
      Role: PHYSICAL STANDBY
      Enabled: YES
      Intended State: APPLY-ON
      Instance(s):
      CDPMT1
      CDPMT2 (apply instance)

      Properties:
      DGConnectIdentifier = 'cdpmt'
      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 = ''
      LogFileNameConvert = ''
      FastStartFailoverTarget = ''
      StatusReport = '(monitor)'
      InconsistentProperties = '(monitor)'
      InconsistentLogXptProps = '(monitor)'
      SendQEntries = '(monitor)'
      LogXptStatus = '(monitor)'
      RecvQEntries = '(monitor)'
      HostName(*)
      SidName(*)
      StaticConnectIdentifier(*)
      StandbyArchiveLocation(*)
      AlternateLocation(*)
      LogArchiveTrace(*)
      LogArchiveFormat(*)
      LatestLog(*)
      TopWaitEvents(*)
      (*) - Please check specific instance for the property value

      Current status for "cdpmt":
      Warning: ORA-16809: multiple warnings detected for the database


      Any help would be really appreciated. Thanks!

      Edited by: 972075 on Nov 19, 2012 3:09 PM
        • 1. Re: Dataguard - Primary not applying logs to Standby
          Osama_Mustafa
          Check MOS Notes :
          Ora-16401: Archivelog Rejected By Rfs [ID 1183143.1]
          • 2. Re: Dataguard - Primary not applying logs to Standby
            mseberg
            Hello;

            Given your combination of errors I might look at this :

            Archive Log Creation Fails With Errors ORA-16014, ORA-00312 [ID 1373314.1]

            Assuming you are using FRA I would check its location :

            SQL> show parameter db_recovery

            If I remember correctly by default DGMGRL is taking archive destination as FRA.


            Use commands below from each location to check if the parameters are changing.





            DGMGRL> show database CDPMTSB logxptstatus;
            DGMGRL> SHOW DATABASE CDPMTSB InconsistentProperties;
            DGMGRL> show database CDPMTSB InconsistentLogXptProps;



            DGMGRL> show database CDPMT logxptstatus;
            DGMGRL> SHOW DATABASE CDPMT InconsistentProperties;
            DGMGRL> show database CDPMT InconsistentLogXptProps;



            Best Regards

            mseberg
            • 3. Re: Dataguard - Primary not applying logs to Standby
              975078
              Thanks MSEBERG,

              Here's what I found. FRA seems to have enough space on ASM and there are other logs there, not sure what the issue is:


              14:31:58 SYS: CDPMTSB> show parameter db_recovery

              NAME TYPE VALUE
              ------------------------------------ ----------- ------------------------------
              db_recovery_file_dest string +FRA_DG_01
              db_recovery_file_dest_size big integer 60G


              DGMGRL> show database CDPMTSB logxptstatus;
              LOG TRANSPORT STATUS
              PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
              CDPMTSB cdpmt

              DGMGRL> SHOW DATABASE CDPMTSB InconsistentProperties;
              INCONSISTENT PROPERTIES
              INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

              DGMGRL> show database CDPMTSB InconsistentLogXptProps;
              INCONSISTENT LOG TRANSPORT PROPERTIES
              INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE

              DGMGRL> show database CDPMT logxptstatus;
              Error: ORA-16757: unable to get this property's value

              DGMGRL> SHOW DATABASE CDPMT InconsistentProperties;
              INCONSISTENT PROPERTIES
              INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
              CDPMT2 DbFileNameConvert DG_01/cdpmtsb, DG_01/cdpmt
              CDPMT2 LogFileNameConvert FRA_DG_01/cdpmtsb, FRA_DG_01/cdpmt, DG_01/cdpmtsb, DG_01/cdpmt
              CDPMT1 LogArchiveMaxProcesses 4 30 4
              CDPMT1 DbFileNameConvert DG_01/cdpmtsb, DG_01/cdpmt DG_01/cdpmtsb,DG_01/cdpmt
              CDPMT1 LogFileNameConvert FRA_DG_01/cdpmtsb, FRA_DG_01/cdpmt, DG_01/cdpmtsb, DG_01/cdpmt FRA_DG_01/cdpmtsb,FRA_DG_01/cdpmt,+DG_01/cdpmtsb,+DG_01/cdpmt

              DGMGRL> show database CDPMT InconsistentLogXptProps;

              Error: ORA-16757: unable to get this property's value


              Errors in the Alert (from Primary):

              ARCH: Archival error occurred on a closed thread. Archiver continuing
              ORACLE Instance CDPMTSB - Archival Error. Archiver continuing.
              Tue Nov 20 14:34:43 2012
              Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2
              Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2
              ARC6: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
              ARC6: Archive log rejected (thread 2 sequence 4071) at host 'cdpmt'
              Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
              ORA-16401: archivelog rejected by RFS
              Errors in file /data/oracle/app/oracle/diag/rdbms/cdpmtsb/CDPMTSB/trace/CDPMTSB_arc6_9571.trc:
              ORA-16014: log 3 sequence# 4071 not archived, no available destinations
              ORA-00312: online log 3 thread 2: '+FRA_DG_01/cdpmtsb/onlinelog/group_3.291.799379949'
              ARCH: Archival error occurred on a closed thread. Archiver continuing
              ORACLE Instance CDPMTSB - Archival Error. Archiver continuing.

              DGMGRL> DGMGRL> show database verbose CDPMT

              Database
              Name: cdpmt
              Role: PHYSICAL STANDBY
              Enabled: YES
              Intended State: APPLY-ON
              Instance(s):
              CDPMT1
              CDPMT2 (apply instance)

              Properties:
              DGConnectIdentifier = 'cdpmt'
              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 = ''
              LogFileNameConvert = ''
              FastStartFailoverTarget = ''
              StatusReport = '(monitor)'
              InconsistentProperties = '(monitor)'
              InconsistentLogXptProps = '(monitor)'
              SendQEntries = '(monitor)'
              LogXptStatus = '(monitor)'
              RecvQEntries = '(monitor)'
              HostName(*)
              SidName(*)
              StaticConnectIdentifier(*)
              StandbyArchiveLocation(*)
              AlternateLocation(*)
              LogArchiveTrace(*)
              LogArchiveFormat(*)
              LatestLog(*)
              TopWaitEvents(*)
              (*) - Please check specific instance for the property value

              Current status for "cdpmt":
              Warning: ORA-16809: multiple warnings detected for the database

              DGMGRL> show database verbose CDPMTSB

              Database
              Name: cdpmtsb
              OEM Name: CDPMTSB_devdb40.utd.com
              Role: PRIMARY
              Enabled: YES
              Intended State: TRANSPORT-ON
              Instance(s):
              CDPMTSB

              Properties:
              DGConnectIdentifier = 'cdpmtsb'
              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 = '7'
              LogArchiveMinSucceedDest = '2'
              DbFileNameConvert = '+DG_01/cdpmt, +DG_01/cdpmtsb'
              LogFileNameConvert = '+FRA_DG_01/cdpmt, FRA_DG_01/cdpmtsb, DG_01/cdpmt, +DG_01/cdpmtsb'
              FastStartFailoverTarget = ''
              StatusReport = '(monitor)'
              InconsistentProperties = '(monitor)'
              InconsistentLogXptProps = '(monitor)'
              SendQEntries = '(monitor)'
              LogXptStatus = '(monitor)'
              RecvQEntries = '(monitor)'
              HostName = 'devdb40.utd.com'
              SidName = 'CDPMTSB'
              StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devdb40.utd.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDPMTSB_DGMGRL)(INSTANCE_NAME=CDPMTSB)(SERVER=DEDICATED)))'
              StandbyArchiveLocation = '+FRA_DG_01/cdpmtsb/standbylog'
              AlternateLocation = ''
              LogArchiveTrace = '0'
              LogArchiveFormat = '%t_%s_%r.dbf'
              LatestLog = '(monitor)'
              TopWaitEvents = '(monitor)'

              Current status for "cdpmtsb":
              SUCCESS


              Thanks for your help btw, I'm really at a loss here as to what is going on with this.
              • 4. Re: Dataguard - Primary not applying logs to Standby
                mseberg
                OK

                It must be time for coffee. My bad, should be :

                DGMGRL> SHOW DATABASE 'CDPMTSB' 'logxptstatus';

                Can you try it like this?

                The others need the quotes too.

                Best Regards

                mseberg
                • 5. Re: Dataguard - Primary not applying logs to Standby
                  975078
                  Thanks MSEBERG,

                  Coffee is critical :)

                  The quotes didn't help actually. Worked without them however:



                  DGMGRL> show database 'CDPMTSB' 'logxptstatus';
                  Object "CDPMTSB" was not found
                  DGMGRL> show database CDPMTSB 'logxptstatus';
                  LOG TRANSPORT STATUS
                  PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
                  CDPMTSB cdpmt




                  DGMGRL> SHOW DATABASE CDPMTSB 'InconsistentProperties';
                  INCONSISTENT PROPERTIES
                  INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE


                  DGMGRL> show database CDPMTSB 'InconsistentLogXptProps';
                  INCONSISTENT LOG TRANSPORT PROPERTIES
                  INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE

                  DGMGRL> show database CDPMT 'logxptstatus';
                  Error: ORA-16757: unable to get this property's value

                  DGMGRL> SHOW DATABASE CDPMT InconsistentProperties;
                  INCONSISTENT PROPERTIES
                  INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
                  CDPMT2 DbFileNameConvert DG_01/cdpmtsb, DG_01/cdpmt
                  CDPMT2 LogFileNameConvert FRA_DG_01/cdpmtsb, FRA_DG_01/cdpmt, DG_01/cdpmtsb, DG_01/cdpmt
                  CDPMT1 LogArchiveMaxProcesses 4 30 4
                  CDPMT1 DbFileNameConvert DG_01/cdpmtsb, DG_01/cdpmt DG_01/cdpmtsb,DG_01/cdpmt
                  CDPMT1 LogFileNameConvert FRA_DG_01/cdpmtsb, FRA_DG_01/cdpmt, DG_01/cdpmtsb, DG_01/cdpmt FRA_DG_01/cdpmtsb,FRA_DG_01/cdpmt,+DG_01/cdpmtsb,+DG_01/cdpmt

                  DGMGRL> show database CDPMT 'InconsistentLogXptProps';

                  Error: ORA-16757: unable to get this property's value

                  DGMGRL>


                  Checked the DG Broker log as well to see if the request was coming through:

                  DG 2012-11-20-14:33:27 0 2 0 RSM 0 received GETPROP request: rid=0x02010000, pid=57
                  DG 2012-11-20-14:33:27 0 2 0 Database Resource: Get Property LogXptStatus
                  DG 2012-11-20-14:33:27 0 2 0 Database Resource GetProperty succeeded
                  DG 2012-11-20-14:33:27 2010000 4 813859846 DMON: MON_PROPERTY operation completed
                  DG 2012-11-20-14:33:47 0 2 813859848 DMON: ENUM_DRC: success. (len = 809)
                  DG 2012-11-20-14:33:47 0 2 813859848 DMON: ENUM_DRC operation completed
                  DG 2012-11-20-14:33:47 0 2 0 RSM 0 received GETPROP request: rid=0x02010000, pid=57
                  DG 2012-11-20-14:33:47 0 2 0 Database Resource: Get Property LogXptStatus
                  DG 2012-11-20-14:33:47 0 2 0 Database Resource GetProperty succeeded
                  DG 2012-11-20-14:33:47 2010000 4 813859849 DMON: MON_PROPERTY operation completed
                  DG 2012-11-20-14:34:01 0 2 813859850 DMON: MON_PROPERTY operation completed
                  • 6. Re: Dataguard - Primary not applying logs to Standby
                    mseberg
                    Hello again;

                    Sorry for the delay.

                    So for broker you need to have an SPFILE configuration as you probably know.

                    Is it possible that there's an spfile file on ASM and another that's not?

                    You may be using the spfile located in the $ORACLE_HOME/dbs directory.

                    If yes I believe you can use the SRVCTL utility so the database starts using the right SPFILE.

                    srvctl modify database -d atdb -p <asm_location_and_filename>

                    Inconsistent Properties is generally cause by changing a value SQL Plus instead broker after broker is setup.

                    Tough error. I see why you are at a loss.

                    If the spfile is correct you can always try the do over :

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

                    Best Regards

                    mseberg
                    • 7. Re: Dataguard - Primary not applying logs to Standby
                      975078
                      Thanks MSEBERG,

                      I've removed the DG Broker Config and I think the root cause is something to do with the Archive Logs and how it's either setup.

                      In the Primary DB Alert I see:

                      Tue Nov 20 19:45:25 2012
                      Thread 1 cannot allocate new log, sequence 4085
                      Checkpoint not complete
                      Current log# 2 seq# 4084 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_2.290.799379949
                      ORACLE Instance CDPMTSB - Can not allocate log, archival required
                      Thread 1 cannot allocate new log, sequence 4085
                      All online logs needed archiving
                      Current log# 2 seq# 4084 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_2.290.799379949
                      Tue Nov 20 19:49:09 2012
                      Changing destination 4 from remote to local during archival of log#: 3 sequence#: 4071 thread#: 2

                      So on the Primary:

                      19:46:58 SYS: CDPMT> archive log list

                      Database log mode Archive Mode
                      Automatic archival Enabled
                      Archive destination USE_DB_RECOVERY_FILE_DEST
                      Oldest online log sequence 4083
                      Next log sequence to archive 4083
                      Current log sequence 4084

                      19:47:03 SYS: CDPMT> show parameter DB_RECOVER

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      db_recovery_file_dest string +FRA_DG_01
                      db_recovery_file_dest_size big integer 60G
                      19:47:17 SYS: CDPMT> ALTER SYSTEM ARCHIVE LOG ALL;

                      ALTER SYSTEM ARCHIVE LOG ALL
                      *
                      ERROR at line 1:
                      ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST

                      19:49:09 SYS: CDPMT> 19:49:09 SYS: CDPMT> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      log_archive_min_succeed_dest integer 2

                      So I was thinking maybe it's not archiving for some reason, but on ASM:

                      ASMCMD> pwd
                      +FRA_DG_01/CDPMTSB/ARCHIVELOG
                      ASMCMD> ls
                      2012_11_13/
                      2012_11_15/
                      2012_11_16/
                      2012_11_17/
                      2012_11_18/
                      2012_11_19/
                      2012_11_20/
                      ASMCMD> cd 2012_11_16/
                      ASMCMD> ls
                      thread_1_seq_4037.310.799459825
                      thread_1_seq_4038.312.799513553
                      thread_1_seq_4039.315.799515053
                      thread_1_seq_4040.317.799520243
                      thread_1_seq_4041.320.799525681
                      thread_1_seq_4042.321.799528227
                      thread_1_seq_4043.322.799529173
                      thread_1_seq_4044.324.799529175
                      thread_1_seq_4045.328.799529695
                      thread_1_seq_4046.329.799538481
                      thread_2_seq_4062.309.799459823
                      thread_2_seq_4063.311.799462815
                      thread_2_seq_4064.313.799513553
                      thread_2_seq_4065.316.799515093
                      thread_2_seq_4066.318.799520245
                      thread_2_seq_4067.319.799525681
                      thread_2_seq_4068.323.799529175
                      thread_2_seq_4069.325.799529199
                      thread_2_seq_4070.326.799529255
                      thread_2_seq_4071.327.799529267
                      ASMCMD> cd ..
                      ASMCMD> ls
                      2012_11_13/
                      2012_11_15/
                      2012_11_16/
                      2012_11_17/
                      2012_11_18/
                      2012_11_19/
                      2012_11_20/

                      BTW for SPFILE:

                      19:49:35 SYS: CDPMT> show parameter spfile

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      spfile string /data/oracle/app/oracle/produc
                      t/11.1.0/db_1/dbs/spfileCDPMTS
                      B.ora
                      Does this have something to do with Archive Log? On the Standby I'm seeing the logs moving over but with rejections:

                      RFS[8]: Possible network disconnect with primary database
                      Tue Nov 20 19:40:58 2012
                      Archived Log entry 1117 added for thread 1 sequence 4082 ID 0x62e7f5cf dest 1:
                      Archived Log entry 1118 added for thread 1 sequence 4082 ID 0x62e7f5cf dest 3:

                      Thanks again for all your help!



                      Actually just noticed this:

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      audit_syslog_level string
                      commit_logging string
                      db_create_online_log_dest_1 string
                      db_create_online_log_dest_2 string
                      db_create_online_log_dest_3 string
                      db_create_online_log_dest_4 string
                      db_create_online_log_dest_5 string
                      enable_ddl_logging boolean FALSE
                      log_archive_config string DG_CONFIG=(CDPMT,CDPMTSB)
                      log_archive_dest string
                      log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
                      DEST VALID_FOR=(ONLINE_LOGFIL
                      ES,ALL_ROLES) DB_UNIQUE_NAME=C
                      DPMTSB
                      log_archive_dest_10 string
                      log_archive_dest_2 string
                      log_archive_dest_3 string location="+FRA_DG_01/cdpmtsb/s
                      tandbylog", valid_for=(STANDB
                      Y_LOGFILE,STANDBY_ROLE)
                      log_archive_dest_4 string


                      And in ASM:

                      ASMCMD> pwd
                      +FRA_DG_01/CDPMTSB
                      ASMCMD> cd stand*
                      ASMCMD> ls
                      1_4029_769881773.dbf
                      1_4030_769881773.dbf
                      1_4031_769881773.dbf
                      1_4032_769881773.dbf
                      1_4033_769881773.dbf
                      1_4034_769881773.dbf
                      1_4035_769881773.dbf
                      1_4036_769881773.dbf
                      1_4037_769881773.dbf
                      1_4038_769881773.dbf
                      1_4039_769881773.dbf
                      1_4040_769881773.dbf
                      1_4041_769881773.dbf
                      1_4042_769881773.dbf
                      1_4043_769881773.dbf
                      1_4044_769881773.dbf
                      2_4054_769881773.dbf
                      2_4055_769881773.dbf
                      2_4056_769881773.dbf
                      2_4057_769881773.dbf
                      2_4058_769881773.dbf
                      2_4059_769881773.dbf
                      2_4060_769881773.dbf
                      2_4061_769881773.dbf
                      2_4062_769881773.dbf
                      2_4063_769881773.dbf
                      2_4064_769881773.dbf
                      2_4065_769881773.dbf
                      2_4066_769881773.dbf
                      2_4067_769881773.dbf
                      2_4068_769881773.dbf
                      2_4069_769881773.dbf
                      2_4070_769881773.dbf


                      It's stopping at 4070 and can't create 4071 (which is the one it keeps complaining about). Shouldn't these logs be moving over to the standby? Sorry, thinking out loud.

                      Edited by: BostonDBA on Nov 20, 2012 3:05 PM
                      • 8. Re: Dataguard - Primary not applying logs to Standby
                        mseberg
                        OK;

                        So you have log_archive_min_succeed_dest set to *2*

                        A value of 2 specifies that the destinations specified in both parameters must succeed.

                        I might change this back to the default of 1 because I would rather have a gap then a Primary issue.

                        Then you avoid :
                        Thread 1 cannot allocate new log, sequence 4085
                        This :
                        Possible network disconnect with primary database
                        Is generally an informative message and for the time being I would not worry about it.


                        Can you run this query on the primary and post the results?

                        http://www.visi.com/~mseberg/monitor_data_guard_transport.html


                        Best Regards

                        mseberg
                        • 9. Re: Dataguard - Primary not applying logs to Standby
                          975078
                          Here's the result of that query:

                          DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
                          ---------- -------------- ------------ ----------- -------------- -------
                          CDPMT DEVDB40 4085 4070 16-NOV/19:27 15

                          Elapsed: 00:00:00.03


                          I changed the parameter as you specified:

                          20:54:19 SYS: CDPMT> show parameter log_archive_min_succeed_dest

                          NAME TYPE VALUE
                          ------------------------------------ ----------- ------------------------------
                          log_archive_min_succeed_dest integer 1


                          Thanks,

                          P
                          • 10. Re: Dataguard - Primary not applying logs to Standby
                            mseberg
                            OK

                            So you have a gap of 15. Not good.

                            So are we back to the issue that the logs are not making to the standby? ( I'm assuming yes )

                            or are they making it and not being applied?


                            What's odd is I don't see an 'ASYNC' in your log_archive_dest_2

                            If I read the "LOG_ARCHIVE_DEST_n Parameter Attributes" section of E10700-02 correctly I don't think this defaults but
                            the section reads "This is the default behavior if neither SYNC or ASYNC is specified." So I don't think this is it.


                            I'm thinking Primary Alert log should show an error(s). Standby probably shows waiting for log right?
                                 
                            cdpmtsb - Primary database
                            cdpmt - Physical standby database
                            
                            
                            
                            Primary Parameters
                            
                            log_archive_dest_2  SERVICE=CDPMT VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDPMT
                            log_archive_dest_3  location="+FRA_DG_01/cdpmtsb/standbylog", valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
                            
                            
                            log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
                            
                            
                            Standby Parameters
                            
                            log_archive_dest_2 SERVICE=cdpmtsb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdpmtsb
                            log_archive_dest_3 LOCATION=+FRA_DG_01 /CDPMT/STANDBYLOG VALID_FOR=( STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDPMT
                            Best Regards

                            mseberg
                            • 11. Re: Dataguard - Primary not applying logs to Standby
                              975078
                              To be honest, I cannot tell if logs are being applied or not because the following returns nothing on standby and primary:

                              21:44:51 SYS: CDPMT> select * from V$ARCHIVE_GAP;

                              no rows selected

                              However:

                              Primary:

                              21:46:06 SYS: CDPMT> select SWITCHOVER_STATUS, OPEN_MODE from v$database;

                              SWITCHOVER_STATUS OPEN_MODE
                              -------------------- ----------
                              SESSIONS ACTIVE READ WRITE


                              Standby:

                              SQL> select SWITCHOVER_STATUS, OPEN_MODE from v$database;

                              SWITCHOVER_STATUS OPEN_MODE
                              -------------------- ----------
                              RECOVERY NEEDED MOUNTED


                              On Standby when I do:

                              SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

                              Database altered.

                              SQL> archive log list
                              Database log mode Archive Mode
                              Automatic archival Enabled
                              Archive destination USE_DB_RECOVERY_FILE_DEST
                              Oldest online log sequence 4090
                              Next log sequence to archive 0
                              Current log sequence 4091

                              In Alert log I see:

                              Tue Nov 20 21:47:42 2012
                              ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
                              Attempt to start background Managed Standby Recovery process (CDPMT1)
                              Tue Nov 20 21:47:42 2012
                              MRP0 started with pid=45, OS id=15967
                              MRP0: Background Managed Standby Recovery process started (CDPMT1)
                              Fast Parallel Media Recovery enabled
                              Tue Nov 20 21:47:47 2012
                              Managed Standby Recovery not using Real Time Apply
                              MRP0: Background Media Recovery terminated with error 1153
                              Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_mrp0_15967.trc:
                              ORA-01153: an incompatible media recovery is active
                              Errors in file /opt/app/oracle/diag/rdbms/cdpmt/CDPMT1/trace/CDPMT1_mrp0_15967.trc:
                              ORA-01153: an incompatible media recovery is active
                              MRP0: Background Media Recovery process shutdown (CDPMT1)
                              Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION


                              Primary Alert log is not giving errors like before but is still unable to allocate:


                              hread 1 cannot allocate new log, sequence 4089
                              Private strand flush not complete
                              Current log# 2 seq# 4088 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_2.290.799379949
                              Thread 1 advanced to log sequence 4089 (LGWR switch)
                              Current log# 1 seq# 4089 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_1.289.799379949
                              Tue Nov 20 21:29:12 2012
                              Changing destination 4 from remote to local during archival of log#: 2 sequence#: 4088 thread#: 1
                              Archived Log entry 124 added for thread 1 sequence 4088 ID 0x62e7f5cf dest 1:
                              Tue Nov 20 21:29:12 2012
                              LNS: Standby redo logfile selected for thread 1 sequence 4089 for destination LOG_ARCHIVE_DEST_2
                              Tue Nov 20 21:42:55 2012
                              Thread 1 cannot allocate new log, sequence 4090
                              Private strand flush not complete
                              Current log# 1 seq# 4089 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_1.289.799379949
                              Thread 1 advanced to log sequence 4090 (LGWR switch)
                              Current log# 2 seq# 4090 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_2.290.799379949
                              Tue Nov 20 21:42:55 2012
                              Archived Log entry 125 added for thread 1 sequence 4089 ID 0x62e7f5cf dest 1:
                              Tue Nov 20 21:42:56 2012
                              LNS: Standby redo logfile selected for thread 1 sequence 4090 for destination LOG_ARCHIVE_DEST_2
                              Tue Nov 20 21:44:50 2012
                              Thread 1 cannot allocate new log, sequence 4091
                              Checkpoint not complete
                              Current log# 2 seq# 4090 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_2.290.799379949
                              Thread 1 advanced to log sequence 4091 (LGWR switch)
                              Current log# 1 seq# 4091 mem# 0: +FRA_DG_01/cdpmtsb/onlinelog/group_1.289.799379949
                              Tue Nov 20 21:44:51 2012
                              Changing destination 4 from remote to local during archival of log#: 2 sequence#: 4090 thread#: 1
                              Archived Log entry 127 added for thread 1 sequence 4090 ID 0x62e7f5cf dest 1:
                              Tue Nov 20 21:44:52 2012
                              LNS: Standby redo logfile selected for thread 1 sequence 4091 for destination LOG_ARCHIVE_DEST_2


                              I changed the parameters to as follows:


                              CDPMT (Standby) :

                              alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDPMT' scope=both
                              alter system set LOG_ARCHIVE_DEST_2='SERVICE='SERVICE=CDPMTSB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDPMTSB' scope=both;
                              alter system set LOG_ARCHIVE_DEST_3='LOCATION=+FRA_DG_01/CDPMT/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDPMT' scope=both;

                              CDPMTSB (Primary)

                              alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDPMTSB' scope=both;
                              alter system set LOG_ARCHIVE_DEST_2='SERVICE=CDPMT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDPMT' scope=both;
                              alter system set LOG_ARCHIVE_DEST_3='LOCATION=+FRA_DG_01/CDPMTSB/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDPMTSB' scope=both;


                              This is throughly confusing, LOL.


                              Thanks,

                              P
                              • 12. Re: Dataguard - Primary not applying logs to Standby
                                mseberg
                                Hello;

                                Sorry I'm on call tonight and its busy.

                                1. V$ARCHIVE_GAP cannot be trusted. Period.

                                2. alter system when using broker is bad news. Once you start using broker you must always use Broker or the database and broker will have different settings and you will have errors.

                                Plan

                                Remove broker and make sure Data Guard is working correctly ( maybe a few days ) before putting broker back.

                                Use the query in the link I gave you to monitor.

                                Confirm what spfile you are using on all databases.

                                Best Regards

                                mseberg