Script to confirm last SCN and Standby Latest sync status

There are two options to check current sync status of standby database.

1. check current scn and then using this scn value determine last sync time.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1301571

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(1447102) from dual;

=================================================

2. Second option is to run below script it will automatically take last scn and use it to determine last sync time of standby database.


set serveroutput on
declare
v_scn varchar2(38);
v_time varchar2(38);
begin
select current_scn into v_scn from v$database;
select scn_to_timestamp(v_scn) into v_time from dual;
DBMS_OUTPUT.PUT(chr(10));
DBMS_OUTPUT.PUT_LINE('Last SCN is ' ||v_scn||'');
DBMS_OUTPUT.PUT(chr(10));
DBMS_OUTPUT.PUT_LINE('Standby is updated up to ' ||v_time||'');
end;
/
=================================================
Some other useful scripts for monitoring

select * from v$archive_gap;

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

select sequence#, first_time, next_time, applied from v$archived_log;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select thread#, max (sequence#) from v$log_history group by thread#;


select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;