Wednesday, February 17, 2021

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.


No comments:

Post a Comment