Search This Blog

Total Pageviews

Sunday 17 June 2012


Oracle 10g physical standby log gap check via shell script 


#!/usr/bin/ksh 
. /u01/app/ora/.profile 
export ORAENV_ASK=NO 
export PATH=${PATH}:/usr/local/bin 
export ORACLE_HOME=/u01/app/ora/10g 
export ORACLE_BASE=/u01/app/ora 
export ORACLE_SID=spscfm1 
export ORACLE_HOSTNAME=SMI 
export TNS_ADMIN=${ORACLE_HOME}/network/admin 
export PATH=$PATH:$ORACLE_HOME/bin 
sqlplus -S "/ as sysdba" <<! 
set heading off 
set feedb off 
spool /u01/app/ora/admin/spscfm/logs/diff.log 
select (select name from V\$DATABASE),(select max(sequence#) from v\$archived_logwhere dest_id=1) Current_primary_seq,( select max(sequence#) from v\$archived_logwhere to_date(next_time,'dd-mm-yyyy') > sysdate-1 
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) fromv\$archived_log 
where to_date(next_time,'dd-mm-yyyy') > sysdate-1 
and dest_id=2 and applied='NO'),0) from dual) "To be applied",((select max(sequence#) from v\$archived_log 
where dest_id=1) - (select max(sequence#) from v\$archived_log 
where dest_id=2)) "To be Shipped" 
from dual 
/ 
spool off 
! 
diff=`awk '{print $4}' /u01/app/ora/admin/spscfm/logs/diff.log` 
#echo $diff 
if [[ $diff -gt 0 ]]; 
then 
echo "
The figures show whether the logs have been successfully applied to the Standby. If the number in the subject is > 0 then this many logs haven't been applied to the Standby
Primary:***0001*** 
Standby:***0001***">>/u01/app/ora/admin/spscfm/logs/diff.log 
cat /u01/app/ora/admin/spscfm/logs/diff.log | /usr/bin/mailx -s " ###SY01POY - Standby Log difference `printf $diff` " "support.@.com"
 fi

Oracle DBA

anuj blog Archive