Different Methods To Check Physical Standby is in Sync with the Primary or Not

Different Methods To Check Physical Standby is in Sync with the Primary or Not

Method.1

The following tips will help you in monitoring sync between primary and standby databases.

1. Check for GAP on standby
2. Check redo received on standby
3. Check redo applied on standby

Solution:

Execute following queries:

A. On Primary

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;

Thread Last Sequence Generated
---------- -----------------------
1                    4498
1                    4498

B. On Physical Standby

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                   4498                  4498          0

C. On Physical Standby

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Now perform following checks:

1. Check for GAP

If query “C” returns any row then this means there are some archive log missing on standby.
Example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Thread     Low Sequence High Sequence
---------- ------------ -------------
1          8            9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.
If query “C” does not returns any row and output is “no row selected” than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.
If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.


Method.2

On Primary database:

1)
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
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)
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;

On Standby database:

1)
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#;

2)
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;

3)
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 in ('YES','IN-MEMORY')
group by thread# order by 1;


Method.3

Step 1 : Check the status of database on both server.

On Primary Server.
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY

On Standby Server.
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

Step 2 : Check for GAP on Standby

PRIMARY@QDB>
select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
          4505

STANDBY@QDB>
select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
          4505

STANDBY@QDB>
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;

Thread Last Sequence Generated
---------- -----------------------
1                    4506
1                    4506


Step 4: Check redo received and applied on standby.

STANDBY@QDB>
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#;

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


Step 5: Identify the missing archive log file.

STANDBY@QDB>

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

—-If found gap

Step 6: Copy missing archive log file

After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:

PRIMARY@QDB> 
SELECT name FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN 20931 and 76922;

Output:
/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc
56027 rows selected.

Step 7: Register archive logfile with standby.
Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE statement on the physical standby database.

For example:
STANDBY@QDB> ALTER DATABASE REGISTER LOGFILE ‘/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc’;

Step 8: Restart the managed recovery operations.

— After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations.

For example, to put the physical standby database into automatic recovery managed mode:
STANDBY@QDB> alter database recover managed standby database disconnect from session;