13 Replies Latest reply: Jan 6, 2014 4:17 AM by petra-K RSS

    Script to check log Gaps

      Hi all,

       

      11.2.0.1

       

      DataGuard - Physical Standby.

       

       

      Can you share me your script to check gaps please.

       

      For manual checking, At PRIMARY I run :

       

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

       

      PROCESS   STATUS        SEQUENCE#

      --------- ------------ ----------

      ARCH      CLOSING            4069

      ARCH      CLOSING            4054

      ARCH      CLOSING            4067

      ARCH      CLOSING            4068

      LNS       WRITING            4070

       

       

      Then at STANDBY , I also run the same script:

       

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

       

      PROCESS   STATUS        SEQUENCE#

      --------- ------------ ----------

      ARCH      CLOSING            4067

      ARCH      CONNECTED             0

      ARCH      CLOSING            4065

      ARCH      CLOSING            4069

      RFS       IDLE               4070

      RFS       IDLE                  0

      RFS       IDLE                  0

      RFS       IDLE                  0

      MRP0      APPLYING_LOG       4070

       

      9 rows selected.



      Based on the two(2) outputs, Is my applied logs have gaps? or delayed? or is it good?



      Thanks,

      pK

        • 1. Re: Script to check log Gaps
          saratpvv

          select * from v$archive_gap; - run in standby - will know gap between your primary /standby

           

          To Know the last sequence number in primary database generated

           

          Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"

          from v$archived_log val, v$database vdb

          where val.resetlogs_change# = vdb.resetlogs_change#

          group by thread# order by 1;

           

          Run in standby - To know last sequence recevied in Standby

           

          PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"
          from v$archived_log val, v$database vdb
          where val.resetlogs_change# = vdb.resetlogs_change#
          group by thread# order by 1;

           

          Run in standby - To know last sequence applied in standby

           

          PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
          from v$archived_log val, v$database vdb
          where val.resetlogs_change# = vdb.resetlogs_change#
          and val.applied='YES'
          group by thread# order by 1;

           

          And comming to scripts - You need to put some effor to build your own as per your host

          • 2. Re: Script to check log Gaps
            mseberg

            Hello;

             

            Here's a simple one you can run from the Primary side:

             

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

             

            Best Regards

             

            mseberg

            • 3. Re: Script to check log Gaps
              Aman - Oracle

              select * from v$archive_gap;

               

              or following script will show archives not created on standby database in last day:

              Select THREAD#,sequence# from v$archived_log where COMPLETION_TIME>sysdate-1 and DEST_ID=1 and (THREAD#,sequence#) not in (select THREAD#,sequence# from v$archived_log where DEST_ID=2);

               

              Here "Dest_id 1" is destination for archive log on your machine and "2" is standby database service.

               

              If you want to check if archive is applied or not and use APPLIED='NO' condition in second query. Hope it will help.

              • 4. Re: Script to check log Gaps
                Anar Godjaev

                HI,

                 

                The script below will show the log sequences involved and even how many hours wide is the gap, for each thread of each standby database:


                select dest_id,thread#,max(primary) primary, max(transf) maxtransf,

                       max(standby) standby, MAX(primary)-MAX(transf) mintransf_gap, MAX(primary)-MAX(standby) apply_gap,

                       max(timegap) hoursgap

                from (

                SELECT dest_id,thread#,max(sequence#) primary, 0 transf, 0 standby, 0 timegap

                     FROM v$archived_log

                    WHERE STANDBY_DEST='YES'

                      and archived = 'YES'

                      AND resetlogs_change# = ( select d.resetlogs_change# from v$database d )

                GROUP BY dest_id,thread#

                union all

                SELECT dest_id,thread#,0 primary, max(sequence#) transf, 0 standby, 0 timegap

                     FROM v$archived_log

                    WHERE STANDBY_DEST='YES'

                      and archived = 'YES'

                      AND resetlogs_change# = ( select d.resetlogs_change# from v$database d )

                GROUP BY dest_id,thread#

                union all

                SELECT dest_id,thread#,0 primary, 0 transf, max(sequence#) standby, trunc((sysdate-max(FIRST_TIME))*24) timegap

                     FROM v$archived_log

                    WHERE STANDBY_DEST='YES'

                      and applied = 'YES'

                      AND resetlogs_change# = ( select d.resetlogs_change# from v$database d )

                GROUP BY dest_id,thread#

                ) asd

                group by dest_id,thread#

                  order by thread#,dest_id;


                Update:


                OR please check following link:

                Script to Monitor Primary and Standby database(s) | www.ORACLE-CKPT.com


                Thank you

                • 5. Re: Script to check log Gaps

                  I thank you all,

                   

                  But based on my two(2) outputs above, is my standby applying logs or not? or The script does not make sense and can not give the needed info?

                  • 6. Re: Script to check log Gaps
                    mseberg

                    Hello;

                     

                    Based on your output above Managed Recovery is running. But that one view ( v$managed_standby ) won't tell you everything. Compare it output to the query in the link I posted before.

                     

                    Then perform a log switch on the primary and run them again.

                     

                    If the numbers advance you can be pretty sure apply is working.

                     

                    Best Regards

                     

                    mseberg

                    • 7. Re: Script to check log Gaps

                      I thank you Sarat,Mse, & Anar.

                       

                      Actually, I have made a script to check if my standby is working good.

                      My script checks or its gauge or sign that my standby db is working good is that if I see MRP0 > WAITING_FOR_LOG since it is currently WRITING in the PRIMARY.

                      For almost 6 months now, I always see MPR0 > WAITING FOR LOG, but now when I select this:

                       

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

                       

                      PROCESS   STATUS        SEQUENCE#

                      --------- ------------ ----------

                      ARCH      CLOSING            4077

                      ARCH      CONNECTED             0

                      ARCH      CLOSING            4076

                      ARCH      CLOSING            4078

                      RFS       IDLE               4079

                      RFS       IDLE                  0

                      RFS       IDLE                  0

                      RFS       IDLE                  0

                      MRP0      APPLYING_LOG       4079


                      9 rows selected.


                      It has been 2 days that is it always in APPLY_LOG status, and does not turn to WAIT_FOR_LOG status?


                      Please help.



                      I also run this:


                      At PRIMARY:


                      SQL> select thread#, max(sequence#) "Last Primary Seq Generated"

                      from v$archived_log val, v$database vdb

                      where val.resetlogs_change# = vdb.resetlogs_change#

                      group by thread# order by 1;

                       

                       

                         THREAD# Last Primary Seq Generated

                      ---------- --------------------------

                               1                       4078

                       

                       

                      At STANDBY:

                       

                      SQL> select * from v$archive_gap;

                       

                       

                      no rows selected

                       

                       

                      SQL> select thread#, max(sequence#) "Last Standby Seq Received"

                      from v$archived_log val, v$database vdb

                      where val.resetlogs_change# = vdb.resetlogs_change#

                      group by thread# order by 1;  2    3    4

                       

                       

                         THREAD# Last Standby Seq Received

                      ---------- -------------------------

                               1                      4078

                       

                       

                      SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

                      from v$archived_log val, v$database vdb

                      where val.resetlogs_change# = vdb.resetlogs_change#

                      and val.applied='YES'

                      group by thread# order by 1;

                       

                       

                       

                         THREAD# Last Standby Seq Applied

                      ---------- ------------------------

                               1                     4078




                      At PRIMARY:

                       

                      SQL> SET PAGESIZE 140

                      SQL> COL DB_NAME FORMAT A10

                      SQL> COL HOSTNAME FORMAT A14

                      SQL> COL LOG_ARCHIVED FORMAT 999999

                      SQL> COL LOG_APPLIED FORMAT 999999

                      SQL> COL LOG_GAP FORMAT 9999

                      SQL> COL APPLIED_TIME FORMAT A14

                      SQL> SELECT

                        2     DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP

                        3  FROM

                        4  ( SELECT

                        5     NAME DB_NAME

                        6  FROM

                        7     V$DATABASE

                        8  ),

                      (

                        9   10  SELECT

                      11     UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME

                      12  FROM

                      13     V$INSTANCE

                      14  ),

                      15  (

                      16  SELECT

                      17     MAX(SEQUENCE#) LOG_ARCHIVED

                      18  FROM

                      19     V$ARCHIVED_LOG

                      20  WHERE

                      21     DEST_ID=1

                      22  AND

                      23     ARCHIVED='YES'

                      24  ),

                      25  (

                      26  SELECT

                      27     MAX(SEQUENCE#) LOG_APPLIED

                      28  FROM

                      29     V$ARCHIVED_LOG

                      30  WHERE

                      31     DEST_ID=2

                      32  AND

                      33     APPLIED='YES'

                      34  ),

                      35  (

                      36  SELECT

                      37     TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME

                      38  FROM

                      39     V$ARCHIVED_LOG

                      40  WHERE

                      41     DEST_ID=2

                      42  AND

                      43     APPLIED='YES'

                      44  );

                       

                       

                      DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP

                      ---------- -------------- ------------ ----------- -------------- -------

                      BNCT       DEBBIE                 4078        4078 03-JAN/10:00         0

                       

                       

                      SQL> select dest_id,thread#,max(primary) primary, max(transf) maxtransf,

                        2         max(standby) standby, MAX(primary)-MAX(transf) mintransf_gap, MAX(primary)-MAX(standby) apply_gap,

                        3         max(timegap) hoursgap

                        4  from (

                        5  SELECT dest_id,thread#,max(sequence#) primary, 0 transf, 0 standby, 0 timegap

                        6       FROM v$archived_log

                        7      WHERE STANDBY_DEST='YES'

                        8        and archived = 'YES'

                        9        AND resetlogs_change# = ( select d.resetlogs_change# from v$database d )

                      10  GROUP BY dest_id,thread#

                      11  union all

                      12  SELECT dest_id,thread#,0 primary, max(sequence#) transf, 0 standby, 0 timegap

                      13       FROM v$archived_log

                      14      WHERE STANDBY_DEST='YES'

                      15        and archived = 'YES'

                      16        AND resetlogs_change# = ( select d.resetlogs_change# from v$database d )

                      17  GROUP BY dest_id,thread#

                      18  union all

                      19  SELECT dest_id,thread#,0 primary, 0 transf, max(sequence#) standby, trunc((sysdate-max(FIRST_TIME))*24) timegap

                      20       FROM v$archived_log

                      21      WHERE STANDBY_DEST='YES'

                      22        and applied = 'YES'

                      23        AND resetlogs_change# = ( select d.resetlogs_change# from v$database d )

                      24  GROUP BY dest_id,thread#

                      25  ) asd

                      26  group by dest_id,thread#

                      27    order by thread#,dest_id;

                       

                       

                         DEST_ID    THREAD#    PRIMARY  MAXTRANSF    STANDBY MINTRANSF_GAP  APPLY_GAP   HOURSGAP

                      ---------- ---------- ---------- ---------- ---------- ------------- ---------- ----------

                               2          1       4078       4078       4078             0          0          2

                       

                       

                      What is an HOURGAP? Is is the lag time it took the log to be applied from primary to standby?


                      Thanks

                      • 8. Re: Script to check log Gaps
                        Anar Godjaev

                        HI,

                         

                         

                         

                        In my opinion, there is no any problem here. But in order to more accurately please send result in Secondary location :

                         

                         

                         

                        select name, value from v$dataguard_Stats;

                         

                         

                         

                        And please run this command in primary location:

                         

                         

                         

                        column applied_time for a30

                        set linesize 140

                        select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;

                        SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,

                        (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or

                                    (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or

                                    ((LOG_ARCHIVED-LOG_APPLIED) > 5))

                              then 'Error! Log Gap is '

                              else 'OK!'

                        end) Status

                        FROM

                        (

                        SELECT INSTANCE_NAME DB_NAME

                        FROM GV$INSTANCE

                        where INST_ID = 1

                        ),

                        (

                        SELECT MAX(SEQUENCE#) LOG_ARCHIVED

                        FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1

                        ),

                        (

                        SELECT MAX(SEQUENCE#) LOG_APPLIED

                        FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1

                        ),

                        (

                        SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME

                        FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1

                        )

                        UNION

                        SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,

                        (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or

                                    (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or

                                    ((LOG_ARCHIVED-LOG_APPLIED) > 5))

                              then 'Error! Log Gap is '

                              else 'OK!'

                        end) Status

                        from (

                        SELECT INSTANCE_NAME DB_NAME

                        FROM GV$INSTANCE

                        where INST_ID = 2

                        ),

                        (

                        SELECT MAX(SEQUENCE#) LOG_ARCHIVED

                        FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2

                        ),

                        (

                        SELECT MAX(SEQUENCE#) LOG_APPLIED

                        FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2

                        ),

                        (

                        SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME

                        FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2

                        )

                        /

                         

                        Thank you

                        • 9. Re: Script to check log Gaps

                          Hi Anar,

                           

                          SQL>

                          SQL> column applied_time for a30

                          SQL> set linesize 140

                          SQL> select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;

                           

                           

                          Current Time

                          -------------------

                          01-04-2014 19:12:47

                           

                           

                          SQL> SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,

                            2  (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or

                            3              (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or

                            4              ((LOG_ARCHIVED-LOG_APPLIED) > 5))

                            5        then 'Error! Log Gap is '

                            6        else 'OK!'

                          end) Status

                            7    8  FROM

                            9  (

                          10  SELECT INSTANCE_NAME DB_NAME

                          FROM GV$INSTANCE

                          11   12  where INST_ID = 1

                          13  ),

                          14  (

                          15  SELECT MAX(SEQUENCE#) LOG_ARCHIVED

                          FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1

                          ),

                          (

                          SELECT MAX(SEQUENCE#) LOG_APPLIED

                          FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1

                          ),

                          (

                          SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME

                          FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1

                          )

                          UNION

                          SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,

                          (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or

                                      (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or

                                      ((LOG_ARCHIVED-LOG_APPLIED) > 5))

                                then 'Error! Log Gap is '

                                else 'OK!'

                          end) Status

                          from (

                          SELECT INSTANCE_NAME DB_NAME

                          FROM GV$INSTANCE

                          where INST_ID = 2

                          ),

                          (

                          SELECT MAX(SEQUENCE#) LOG_ARCHIVED

                          FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2

                          ),

                          (

                          SELECT MAX(SEQUENCE#) LOG_APPLIED

                          FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2

                          ),

                          (

                          SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME

                          FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2

                          )

                          / 16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51

                           

                           

                          DB_NAME          APPLIED_TIME                      LOG_GAP STATUS

                          ---------------- ------------------------------ ---------- ------------------

                          PROD1            04-JAN/18:28                            0 OK!

                          • 10. Re: Script to check log Gaps
                            Anar Godjaev

                            HI,

                             

                            All is well, I do not see any problems.

                             

                               LOG_GAP            STATUS

                            ---------------- ---------------

                                0                  OK!


                            Thank you

                            • 11. Re: Script to check log Gaps

                              Thanks  Anar,

                               

                              But my boss is setting the standard or service level that logs should be current or applied at standby to the latest 30 minute?

                               

                              How do I configure this such  that If ever we have disaster, we only lost 30 mins of archive transaction?

                              • 12. Re: Script to check log Gaps
                                Baris Yildirim

                                Hi,

                                I couldn't see which process you use to send redo logs.

                                Anyway if you use arch processes, you can use archive_lag_target parameter.

                                 

                                Regards

                                • 13. Re: Script to check log Gaps

                                  Thanks, I will try to study that parameter.