Monitor Standby Database with Lag Time Report

by 
vi /home/oracle/scripts/lag_time_report_and_diskgroup_space.ksh
#!/bin/ksh
export ORACLE_SID=<Target_ORACLE_SID>
export ORACLE_HOME=/u01/oracle/product/11.2.0.4/db
/u01/oracle/product/11.2.0.4/db/bin/sqlplus -s / as sysdba << NACHO > /tmp/standbylag.log
set feedback off
select 'Standby thread '|| thread# || ' is '|| round((sysdate - max(first_time)) * 24,1) || ' hours behind Production.' from v\$log_history group by thread#;
select name,value,time_computed from v\$dataguard_stats where name='apply lag';
set linesize 132
col name for a35
select name, free_mb, total_mb, TRUNC(free_mb*100/total_mb,0) "% free" from v\$asm_diskgroup;
exit
NACHO
mail -s "<DBNAME> Lag time Report" "<ENTER YOUR EMAIL HERE>" < /tmp/standbylag.log
rm /tmp/standbylag.log
 

To save the script and change its permissions, follow these steps:

1. Save the script by typing `:wq` in the editor.
2. Change the file permissions using the `chmod` command.
chmod 755 /home/oracle/scripts/lag_time_report_and_diskgroup_space.ksh

Add this entry to the crontab for the oracle user:

###lag time report###
0 0,6,12,18 * * * /home/oracle/scripts/lag_time_report_and_diskgroup_space.ksh 2>&1