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.
