Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 169,955
  • Archives

  • Categories

  • Subscribe

  • Advertisements

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: