find last applied log for lisprod

最新推荐文章于 2024-09-05 18:26:59 发布

转载最新推荐文章于 2024-09-05 18:26:59 发布 · 79 阅读

本文介绍了一个用于检查Oracle主备数据库归档日志同步状态的Shell脚本。通过对比主数据库和备用数据库上最后应用的日志文件，该脚本能够识别尚未同步到备用数据库的归档日志，并进行清理。

#!/bin/ksh
#made by xxxx 26-may-08
NLS_LANG=american_america.ZHT16BIG5
ORACLE_BASE=/apps/oracle
ORACLE_SID=lisprod
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG
PATH=$ORACLE_HOME/bin:$PATH
JOB_HOME=/apps/oracle/jobs
export JOB_HOME PATH
MAIL_GROUP=/apps/oracle/jobs/dba_mail.addr1

#find last applied log for primary database
sqlplus -s xxxx/xxxx@prod_lisprod << !
@$JOB_HOME/pri_lis_last_applied_log
exit
!

#find last applied log file for standby databse
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba"< @$JOB_HOME/stby_lis_last_applied_log
exit
!

PRO_LIS_LAST_APPLIED_LOG=`sed -n '4p' /tmp/pri_lis_last_applied_log.txt`
STBY_LIS_LAST_APPLIED_LOG=`sed -n '4p' /tmp/stby_lis_last_applied_log.txt`
if [[ "$PRO_LIS_LAST_APPLIED_LOG" == "$STBY_LIS_LAST_APPLIED_LOG" ]]
then
    find /archlog/lisprod_arch/ -type f ! -newer /archlog/lisprod_arch/$STBY_LIS_LAST_APPLIED_LOG -exec rm {} \;
else
   echo "Primary Database:"$PRO_LIS_LAST_APPLIED_LOG>lis_last_applied_log.txt
   echo "Standby Database:"$STBY_LIS_LAST_APPLIED_LOG>>lis_last_applied_log.txt
   cat lis_last_applied_log.txt| mailx -s "$ORACLE_SID Last Applied Archive log file does not match" `cat $MAIL_GROUP`
fi

/*
    Program: lisprod_last_applied_log.sql
    Purpose: find last applied log for lisprod
    History:
        Rev     Date        Author           Description
        -----   ---------- -------------    -----------------
        1.0     26-May-08   xxxxxx        Create the script.
*/
SET echo OFF
SET verify OFF
SET feedback OFF
SET termout OFF
SET line 110

COLUMN LAST_APPLIED_LOG format a16 heading "LAST_APPLIED_LOG"

spool /tmp/pri_lis_last_applied_log.txt
SELECT thread#||'_'||max(sequence#)||'.log' AS "LAST_APPLIED_LOG"
FROM v$log_history
GROUP BY thread#;

spool OFF
exit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8203880/viewspace-312611/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8203880/viewspace-312611/