DR Log Apply Gap Detection

Hi Everyone,

I was working on physical standy recently to get synched up with Primary database.
I have used scripts to find the apply gap between PRD and Standby.
very handy script. This is working in my 10.2.0.3 DB nicely. This script needs to be run in standby DB.

SQL> @apply_gap

THREAD# LAST_APPLIED NEWEST_LOG GAP
---------- ------------ ---------- ----------
1 9185 9201 16 (Thready one is lagging 16 sequence behind primary)
2 9967 9981 14
3 1364 1384 20
4 6401 6427 26
----------
sum 76 ( total 76 is the gap)


NAME VALUE
---------- ------------------------------
apply lag +00 05:24:52 (Standby is 5 hrs 24 minutes and 52 seconds behind)


PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 9201 2045953 145
ARCH CLOSING 3 1384 2037761 8
MRP0 APPLYING_LOG 1 9186 132452 488756 (MRP process is currently applying the sequence 9186)
RFS IDLE 3 1385 707612 2031
RFS IDLE 2 9982 5896 138
RFS IDLE 4 6428 8704 4535
RFS IDLE 1 9202 186784 20054

7 rows selected.

SQL>

Script for the output is here

SQL> !cat apply_gap.sql

-- this script finds the gap

compute sum of gap on report;
break on report
SELECT applied.thread#
,last_applied
,newest_log
,newest_log - last_applied gap
FROM (
SELECT thread#
,MAX( sequence# ) last_applied
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#
) applied,
(
SELECT thread#
,MAX( sequence# ) newest_log
FROM v$archived_log
WHERE applied='NO'
GROUP BY thread#
) newest
WHERE applied.thread# = newest.thread#;

-- this script finds the time it delays from primary

column name format a10
column value format a30

select NAME,value from V$DATAGUARD_STATS WHERE NAME='apply lag';

-- this finds the status of recovery, arch,rfs processes

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY
WHERE THREAD# > 0;

SQL>

Let me know your thoughts on this.

3 comments:

Suresh Lakshmanan said...

Metalink Note 275416.1 gives some insight on 9i DB.

Arun said...

Hi Suresh,

Thanks. Script works like charm.

Thanks
Arun

Suresh Lakshmanan said...

datagaurd metalink index note for 10g R2 db: 739396.1

datagaurd metalink index note for 11g R1 db: 798974.1