Standby or dataguard sync checking

Prod/Primary:
---------->
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set lin 500
select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time,t.DEST_ID from
gv$archived_log t where first_time >sysdate -8 and dest_id=2 order by t.sequence# asc;
set lin 80
select process, client_process, thread#, sequence#, status from v$managed_standby;



alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set lin 500
select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time,t.DEST_ID from
gv$archived_log t where first_time >sysdate -1/16 and dest_id=2 order by t.sequence# asc;
set lin 80
select process, client_process, thread#, sequence#, status from v$managed_standby;


Archive Sequence Lag
select a.dest_id,a.thread#, applied "Last Applied Seq", b.notappld "Current Seq", b.notappld-applied "Difference" from
(select dest_id,thread#, max(sequence#) applied from gv$archived_log where applied='YES' and RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) group by dest_id,thread#) A, (select thread#,max(sequence#) notappld from gv$archived_log where RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) and dest_id=1 group by thread#) B where a.thread# = b.thread# order by 1,2;



Stdby
------------>>
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set lin 500
select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time,t.DEST_ID from
gv$archived_log t where first_time >sysdate -1/18 order by t.sequence# asc;
set lin 80


Standby Database Time Lag
set lin 500
col NAME for a25
col VALUE for a20
col UNIT for a30
col TIME_COMPUTED for a25
select NAME,VALUE,UNIT,TIME_COMPUTED from v$dataguard_stats;
set lin 80

select process, status, sequence# from v$managed_standby;
SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MRP Process Status
select inst_id,process, client_process, thread#, sequence#, status from gv$managed_standby;


select inst_id,process, client_process, thread#, sequence#, status from gv$managed_standby where process like 'MRP%';

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



Redo Apply Rate:
-----------------
select to_char(snapshot_time, 'dd-mon-rr hh24:mi:ss') snapshot_time,
thread#, sequence#, applied_scn, apply_rate from v$standby_apply_snapshot;


alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;


alter system set log_archive_dest_2='service=splp-el1-d001:1521/POELO03_OOR.WORLD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=POELO03_OOR';