Monitoring Oracle standby log transport Lag and Errors

Checking Archive Generation

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"

FROM V$LOG_HISTORY

GROUP BY TRUNC(FIRST_TIME)

ORDER BY TRUNC(FIRST_TIME) DESC

SELECT to_date(first_time) DAY,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99999') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99999') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99999') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"

from

v$log_history

GROUP by

to_char(first_time,'YYYY-MON-DD'), to_date(first_time)

order by to_date(first_time)

set line 400;

set pages 999;

select

to_char(first_time,'YY-MM-DD') day,

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",

COUNT(*) TOT

from v$log_history

group by to_char(first_time,'YY-MM-DD')

order by day

;

SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),

dbms_lob.substr(sql_text,4000,1),

dhss.instance_number,

dhss.sql_id,executions_delta,rows_processed_delta

FROM dba_hist_sqlstat dhss,

dba_hist_snapshot dhs,

dba_hist_sqltext dhst

WHERE upper(dhst.sql_text) LIKE ‘%segmentname%’

AND dhss.snap_id=dhs.snap_id

AND dhss.instance_Number=dhs.instance_number

AND begin_interval_time BETWEEN to_date('2014_10_20 16','YYYY_MM_DD HH24')

AND to_date('2014_10_20 18','YYYY_MM_DD HH24')

AND dhss.sql_id = dhst.sql_id;

To check errors related to Log transport:

select message, timestamp from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;

select dest_id,status,error from v$archive_dest;

select severity, error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status where dest_id=2;

select DEST_ID,ERROR from v$archive_dest_status;

Monitor Mrp and its Speed on Standby :

select process,status,client_process,sequence#,block#,active_agents,known_agents from gv$managed_standby;

select * from v$archive_gap;

set pagesize 2000

set lines 2000

col MESSAGE for a90

select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate - 1/6;

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;

Note: this command can only run when the database is open

set linesize 80

select b.lhseq sequence#, b.lhtsm fromtime, a.lhtsm totime,

((to_date(a.lhtsm,'MM/DD/RR HH24:MI:SS') -

to_date(b.lhtsm,'MM/DD/RR HH24:MI:SS')) * 1440) minutes_to_apply

from sys.x$kcclh a, sys.x$kcclh b

where a.lhrid = b.lhrid + 1

order by a.lhrid

;

select min(lhlot) min_log_date, max(lhlot) max_log_date,

numtodsinterval(max(to_number(a_lhtsm – b_lhtsm)),’DAY’) max_log_apply_duration,

numtodsinterval(min(to_number(a_lhtsm – b_lhtsm)),’DAY’) min_log_apply_duration,

numtodsinterval(avg(to_number(a_lhtsm – b_lhtsm)),’DAY’) avg_log_apply_duration

from ( select lhlot lhlot,

to_date(lhtsm,’MM/DD/RR HH24:MI:SS’) a_lhtsm,

to_date(lag(lhtsm) over (order by lhrid),’MM/DD/RR HH24:MI:SS’) b_lhtsm,

lhrid

from sys.x$kcclh )

order by lhrid;

Recovery operations
select to_char(start_time, 'dd-mon-rr hh24:mi:ss') start_time,
item, round(sofar/1024,2) "MB/Sec"
from v$recovery_progress
where (item='Active Apply Rate' or item='Average Apply Rate');

set echo on feed on term on

set linesize 120

col PRIMARY_TIME format a20

col STANDBY_COMPLETION_TIME format a23

spool dg_lag_minutes_&1..sql.log.txt

SELECT

prim.thread# thread,

prim.seq primary_seq,

to_char(prim.tm, ‘DD-MON-YYYY HH24:MI:SS’) primary_time,

tgt.thread# standby_thread,

tgt.seq standby_seq,

to_char(tgt.tm, ‘DD-MON-YYYY HH24:MI:SS’) standby_completion_time,

prim.seq – tgt.seq seq_gap,

( prim.tm – tgt.tm ) * 24 * 60 lag_minutes

FROM

(

SELECT

thread#,

MAX(sequence#) seq,

MAX(completion_time) tm

FROM

v$archived_log

GROUP BY

thread#

) prim,

(

SELECT

thread#,

MAX(sequence#) seq,

MAX(completion_time) tm

FROM

v$archived_log

WHERE

dest_id IN (

SELECT

dest_id

FROM

v$archive_dest

WHERE

target = ‘STANDBY’

)

AND applied = ‘YES’

GROUP BY

thread#

) tgt

WHERE

prim.thread# = tgt.thread#;

spool off

Checking Adaptive Log File Sync issue or io issue on primary :

I see prashant has written Good blog on it . So will add his blog reference :

https://fatdba.com/2022/04/11/adaptive-log-file-sync-is-not-always-good-for-you/

select name,value from v$sysstat where name in ('redo synch poll writes','redo synch polls');

Recovery information redo_info.html file.

set pages 20 echo on time on feed off ttitle on

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

set markup html on spool on

HEAD <TITLE>Redo Logs information</TITLE></HEAD

spool redo_info.html

select INSTANCE_NUMBER,sysdate as "Run_time",instance_name,startup_time,HOST_NAME,VERSION,STATUS,logins from v$instance;

select NAME,CREATED,PLATFORM_NAME,LOG_MODE,CONTROLFILE_TYPE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_FK from v$database;

show parameter archive;

show parameter checkpoint;

show parameter fast_start_mttr_target

show parameter archive_lag_target

show parameter db_writer_processes

show parameter CONTROL_FILE_RECORD_KEEP_TIME

archive log list;

select sum(bytes)/1024/1024/1024 DB_Size_GB from dba_data_files;

select * from v$controlfile;

select * from V$CONTROLFILE_RECORD_SECTION order by 1;

select GROUP#,THREAD#,to_char(SEQUENCE#) SEQUENCE#,BYTES/1024/1024 MB,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,to_char(FIRST_CHANGE#) FIRST_CHANGE#,FIRST_TIME,to_char(NEXT_CHANGE#) NEXT_CHANGE#,NEXT_TIME from v$log order by 1,2,3;

-- for 11g below -- select GROUP#,THREAD#,to_char(SEQUENCE#) SEQUENCE#,BYTES/1024/1024 MB,MEMBERS,ARCHIVED,STATUS,to_char(FIRST_CHANGE#) FIRST_CHANGE#,FIRST_TIME from v$log

select * from v$logfile order by 1;

select l.group#,f.member,l.archived,l.bytes/1048576 MB,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#;

select FILE#,status,to_char(CHANGE#) CHANGE#,TIME from v$backup where status!='NOT ACTIVE' order by 2;

set echo off

col day for a8;