How to check/Identify archival gaps in Standby Environment.
Posted by FatDBA on March 2, 2015
ON PRIMARY DATABASE
=====================
SQL> SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1; 2 3 4
Thread Last Sequence Generated
———- ———————–
1 105334
1 105334
1 105334
SQL> set time on
06:26:03 SQL>
06:28:04 SQL> alter system switch logfile;
System altered.
06:28:15 SQL> /.
System altered.
06:28:17 SQL> /
System altered.
06:28:17 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol5/oracle/dixit_db_arc/dixitdb/arch
Oldest online log sequence 105334
Next log sequence to archive 105338
Current log sequence 105338
ON STANDBY DATABASE
======================
SQL> select process, status, THREAD#,SEQUENCE#,DELAY_MINS from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH CLOSING 1 105298 0
ARCH CLOSING 1 105299 0
ARCH CLOSING 1 105326 0
ARCH CLOSING 1 105327 0
ARCH CLOSING 1 105328 0
ARCH CLOSING 1 105329 0
ARCH CLOSING 1 105330 0
ARCH CLOSING 1 105334 0
ARCH CLOSING 1 105306 0
ARCH CLOSING 1 105307 0
ARCH CLOSING 1 105308 0
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH CLOSING 1 105309 0
ARCH CLOSING 1 105310 0
ARCH CLOSING 1 105311 0
ARCH CLOSING 1 105312 0
ARCH CLOSING 1 105285 0
ARCH CLOSING 1 105313 0
ARCH CLOSING 1 105314 0
ARCH CLOSING 1 105315 0
ARCH CLOSING 1 105316 0
ARCH CLOSING 1 105317 0
ARCH CLOSING 1 105318 0
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH CLOSING 1 105319 0
ARCH CLOSING 1 105320 0
ARCH CLOSING 1 105321 0
ARCH CLOSING 1 105322 0
ARCH CLOSING 1 105323 0
ARCH CLOSING 1 105324 0
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 105325 0
RFS IDLE 1 105335 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
MRP0 WAIT_FOR_LOG 1 105335 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
40 rows selected.
SQL> 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 105334 105334 0
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> set time on
06:26:06 SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
dixitdb READ ONLY WITH APPLY PHYSICAL STANDBY
06:28:20 SQL> 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 105337 105337 0
06:28:32 SQL>
06:29:35 SQL> select process, status, THREAD#,SEQUENCE#,DELAY_MINS from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH CLOSING 1 105298 0
ARCH CLOSING 1 105299 0
ARCH CLOSING 1 105326 0
ARCH CLOSING 1 105327 0
ARCH CLOSING 1 105328 0
ARCH CLOSING 1 105329 0
ARCH CLOSING 1 105330 0
ARCH CLOSING 1 105334 0
ARCH CLOSING 1 105306 0
ARCH CLOSING 1 105335 0
ARCH CLOSING 1 105336 0
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH CLOSING 1 105337 0
ARCH CLOSING 1 105310 0
ARCH CLOSING 1 105311 0
ARCH CLOSING 1 105312 0
ARCH CLOSING 1 105285 0
ARCH CLOSING 1 105313 0
ARCH CLOSING 1 105314 0
ARCH CLOSING 1 105315 0
ARCH CLOSING 1 105316 0
ARCH CLOSING 1 105317 0
ARCH CLOSING 1 105318 0
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH CLOSING 1 105319 0
ARCH CLOSING 1 105320 0
ARCH CLOSING 1 105321 0
ARCH CLOSING 1 105322 0
ARCH CLOSING 1 105323 0
ARCH CLOSING 1 105324 0
ARCH CONNECTED 0 0 0
ARCH CLOSING 1 105325 0
RFS IDLE 1 105338 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
PROCESS STATUS THREAD# SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
MRP0 WAIT_FOR_LOG 1 105338 0
RFS IDLE 0 0 0
RFS IDLE 0 0 0
Thanks
Prashant Dixit
Tales From A Lazy Fat DBA 
Leave a Reply