Check Whether Physical Standby Is In Sync With The Primary Or NOT

Check Whether Physical Standby Is In Sync With The Primary Or NOT

Method.1

set lines 555

select instance_name,name,open_mode,to_char(startup_time,'DD-MM-YY:HH24:MI:ss') startup_time from v$database,v$instance;

select database_role,db_unique_name instance,open_mode,protection_mode,protection_level,switchover_status from v$database;

+ From 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;

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

+ From Physical Standby:

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;

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 applied='YES' group by thread# order by 1;

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

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

select max(sequence#) from v$log_history;

select max(sequence#) from v$archived_log where applied='YES';

--Without realtime:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--With realtime apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Method.2

From Primary Side:

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;

select thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

select max(sequence#) from v$log_history;

v$archived_log

gv$archive_dest

v$database

gv$instance 

 

From Physical Standby Side:

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;

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

select max(sequence#) from v$log_history;

select process,status,thread#,sequence# from v$managed_standby;

 

v$archived_log

gv$archive_dest

v$database

v$managed_standby 

 

Primary Side:

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                       2915

 

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

   THREAD#    DEST_ID STATUS

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

ERROR                                                             FAIL_SEQUENCE

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

         1          1 VALID

         1          2 VALID 0

SQL>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

————–

2915

                                                                              

Standby Side:

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;

THREAD# Last Standby Seq Received

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

         1                      2915

 

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 applied='YES' group by thread# order by 1;

 THREAD# Last Standby Seq Applied

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

         1                     2915

SQL>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

————–

2915

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

PROCESS   STATUS          THREAD#  SEQUENCE#

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

DGRD      ALLOCATED             0          0

ARCH      CLOSING               1       2897

DGRD      ALLOCATED             0          0

ARCH      CLOSING               1       2914

ARCH      CLOSING               1       2893

ARCH      CLOSING               1       2915

MRP0      APPLYING_LOG          1       2916

RFS       IDLE                  1          0

RFS       IDLE                  1       2916

9 rows selected.