3 Replies Latest reply: Mar 10, 2013 7:42 AM by mseberg RSS

    DataGuard Applied Log Issue

    Meenakshy singh
      Hi Gurus,

      Oracle Version 9i
      OS :- AIX

      My query when i am checking the log on Primary database it shows APPLIED=NO' whereas on DR it shows APPLIED=YES for same log sequence.Can you let me know what could be the possible reason and how should i fix it.
      I have database is datagurad mode and DG_BROKER=true.


      PRIMARY DATABAS_E
      On Primary database process running are
      SQL> select process,status,sequence# from v$managed_standby;

      PROCESS STATUS SEQUENCE#
      --------- ------------ ----------
      ARCH CLOSING 596798
      ARCH CLOSING 596797


      SQL> select sequence#,applied,first_time,next_time,dest_id from v$archived_log where sequence#=596715;

      SEQUENCE# APP FIRST_TIM NEXT_TIME DEST_ID
      ---------- --- --------- --------- ----------
      *596715 NO 09-MAR-13 09-MAR-13 1*
      *596715 NO 09-MAR-13 09-MAR-13 2*

      ON SECONDARY DATABASE_

      SQL> select process,status,sequence# from v$managed_standby;

      PROCESS STATUS SEQUENCE#
      --------- ------------ ----------
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      MRP0 WAIT_FOR_LOG 596800
      RFS RECEIVING 596798
      RFS ATTACHED 596797

      SQL> show parameter dg_broker_start

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      dg_broker_start boolean TRUE



      SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
      FROM
      (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
      (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
      WHERE
      ARCH.THREAD# = APPL.THREAD#
      ORDER BY 1;

      Thread Last Sequence Received Last Sequence Applied Difference
      ---------- ---------------------- --------------------- ----------
      1 596799 596799 0


      SQL> select sequence#,applied,first_time,next_time,dest_id from v$archived_log where sequence#=596715;

      SEQUENCE# APP FIRST_TIM NEXT_TIME DEST_ID
      ---------- --- --------- --------- ----------
      *596715 YES 09-MAR-13 09-MAR-13 0*


      Thanks
      Meena
        • 1. Re: DataGuard Applied Log Issue
          mseberg
          Hello Meena;

          For Oracle 9 you should query from the Standby side.

          First verify that archive was received.
          SELECT 
            SEQUENCE#, 
            FIRST_TIME, 
            NEXT_TIME
          FROM 
            V$ARCHIVED_LOG 
          ORDER BY 
            SEQUENCE#;
          Then verify archive was applied.
          SELECT 
            SEQUENCE#,
             APPLIED
          FROM 
            V$ARCHIVED_LOG
          ORDER BY 
            SEQUENCE#;
          A primary database does not apply archive, it generates it. So using v$archived_log on a Primary not valid. The answer to your question is expected results. It should show applied on the DR or Standby and should not show applied on the Primary.

          See Oracle document A96653-02 - 3.3 Verifying the Physical Standby Database

          OR

          http://docs.oracle.com/cd/A97630_01/server.920/a96653/standbyreco.htm#1006412

          Best Regards

          mseberg
          • 2. Re: DataGuard Applied Log Issue
            Meenakshy singh
            Hi Mseberg,

            When I am quering on primary database on v$archive_dest_status it shows applied sequence as 596714 . Where as on secondary on v$archived_log i can see all the other log uptil 596799 to be applied

            PRIMARY DB_

            SQL > select ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;

            ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
            ---------------- ------------- --------------- ------------
            1 596799 0 0
            1 596799 1 596714
            0 0 0 0
            0 0 0 0
            0 0 0 0
            0 0 0 0
            0 0 0 0
            0 0 0 0
            0 0 0 0
            0 0 0 0

            SECONDARY/DR DB_

            SQL> select sequence#,applied,first_time,next_time,dest_id from v$archived_log where sequence#=596714;

            SEQUENCE# APP FIRST_TIM NEXT_TIME DEST_ID
            ---------- --- --------- --------- ----------
            596714 YES 09-MAR-13 09-MAR-13 0

            SQL> select sequence#,applied,first_time,next_time,dest_id from v$archived_log where sequence#=596715;

            SEQUENCE# APP FIRST_TIM NEXT_TIME DEST_ID
            ---------- --- --------- --------- ----------
            596715 YES 09-MAR-13 09-MAR-13 0

            SQL> select sequence#,applied,first_time,next_time,dest_id from v$archived_log where sequence#=596716;

            SEQUENCE# APP FIRST_TIM NEXT_TIME DEST_ID
            ---------- --- --------- --------- ----------
            596716 YES 09-MAR-13 09-MAR-13 0

            Thanks
            Meena
            • 3. Re: DataGuard Applied Log Issue
              mseberg
              OK

              So according to this query

              SELECT 
                 ARCHIVED_THREAD#, 
                 ARCHIVED_SEQ#, 
                 APPLIED_THREAD#, 
                 APPLIED_SEQ#
              FROM 
                V$ARCHIVE_DEST_STATUS;
              ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
              ---------------- ------------- --------------- ------------
                        1      596799                1       596714
                        
              Gap is 85. Yes, you have an issue.

              Do you have all these Archive logs on either the Standby or Primary server? So starting at 596714 up to 596799.

              If yes use ALTER DATABASE REGISTER LOGFILE statement to apply them.


              Best Regards

              mseberg