#!/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