Archive Gap Check on Standby Database

select database_role,open_mode from v$database;

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

select to_char(sysdate,'DD.MM.RR HH24:MI: SS') time, a.thread#, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived, max (a.sequence#) applied, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max (a.sequence#) gap from v$archived_log a where a.applied='YES' group by a.thread#;

PROMPT Oracle Log Apply Gap
SELECT ROWNUM, DEST_ID, Thread, Last_Sequence_Received, Last_Sequence_Applied, Difference FROM (SELECT distinct ARCH.DEST_ID,ARCH.THREAD# Thread, ARCH.SEQUENCE# Last_Sequence_Received, APPL.SEQUENCE# Last_Sequence_Applied, (ARCH.SEQUENCE# - APPL.SEQUENCE#) Difference FROM (SELECT THREAD# ,DEST_ID,SEQUENCE# FROM GV$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM GV$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#) ORDER BY 2;

Oracle Log Apply Gap

PROMPT Oracle Log Apply Gap

SELECT DISTINCT ROWNUM,appl.dest_id, cu.thread#, SUM (la.lastarchived) Last_Sequence_Archived,SUM (appl.lastapplied) Last_Sequence_Applied, decode(SUM (appl.lastapplied),0,0,SUM (la.lastarchived) - SUM (appl.lastapplied)) Difference FROM (SELECT gvi.thread#, gvd.destination, gvd.dest_id, gvd.target, MAX (gvd.log_sequence) currentsequence FROM gv$archive_dest gvd, gv$instance gvi WHERE gvd.status in ('VALID','ERROR') AND gvi.inst_id = gvd.inst_id GROUP BY thread#, dest_id, destination, target) cu, (SELECT thread#, dest_id, MAX (sequence#) lastarchived FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND archived = 'YES' and dest_id=1 GROUP BY thread#, dest_id) la, (SELECT thread#, dest_id, MAX (sequence#) lastapplied FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND applied = 'YES' GROUP BY thread#, dest_id) appl WHERE cu.thread# = la.thread# AND cu.thread# = appl.thread# and cu.dest_id=appl.dest_id and cu.target='STANDBY' GROUP BY ROWNUM, appl.dest_id, cu.thread# ORDER BY 2;

PROMPT Oracle Archive Destination Details

select V$ARCHIVE_DEST.DEST_ID,LOG_SEQUENCE,TRANSMIT_MODE,NET_TIMEOUT,RECOVERY_MODE,AFFIRM,V$ARCHIVE_DEST.DEST_NAME,V$ARCHIVE_DEST_STATUS.STATUS,V$ARCHIVE_DEST_STATUS.TYPE,V$ARCHIVE_DEST_STATUS.DESTINATION,PROTECTION_MODE,FAIL_SEQUENCE,ARCHIVED_SEQ#,APPLIED_SEQ#,DELAY_MINS,V$ARCHIVE_DEST.ERROR from V$ARCHIVE_DEST,V$ARCHIVE_DEST_STATUS where V$ARCHIVE_DEST.STATUS!='INACTIVE' and V$ARCHIVE_DEST.DEST_ID=V$ARCHIVE_DEST_STATUS.DEST_ID;

Archive logs generated per day

SELECT
    TRUNC(FIRST_TIME) AS "DATE",
    COUNT(*) AS "NUMBER_OF_ARCHIVE_LOGS",
    ROUND(SUM(BLOCKS * BLOCK_SIZE) / (1024 * 1024), 2) AS "TOTAL_SIZE_MB"
FROM
    V$ARCHIVED_LOG
GROUP BY
    TRUNC(FIRST_TIME)
ORDER BY
    TRUNC(FIRST_TIME);

Daily Archive Log Generation :

select trunc(COMPLETION_TIME,'DD') Day, thread#, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log 

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Hourly Archive Log Generation :

set pages 1000

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives from v$archived_log 

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;