create rman logs when using oem

Have you ever thought, in oem, where are the backup logs? Interested? Read on.
 
When using rman from oem all your logs will be preserved inside the rman catalog database. And oem will remove old logs according to it's policy. This is just an example how to pull out the logs from oem and keep them on the filesystem. Also grepping for errors and missing backups.

Normally you would add some extra stuff to cron and send off a daily mail to sys admin and possibly  only preserve a number of days with logs on your main directory. If you have many databases it will add up quite a bit. I can add this to a later blog post.

The trick with adding a constant to your column list, and later grep for the constant can be used in many scripts.

First we need a place to store the logs.
mkdir /var/log/rman_logs

Please modify according to your own environment.

 #/bin/bash
# by kp, to be run as the ora owner on the ora server
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
export ORACLE_SID=RMAN
export HOST=`hostname`
export OUTPUT=/var/log/rman_logs
export TODAY=`date +"%Y%m%d"`
export MISSING=$OUTPUT/missing-$TODAY.lst
export ERRORS=$OUTPUT/errors-$TODAY.lst
export STATUS=0

-- add a where clause to the select if you want to omit any of the dbs
$ORACLE_HOME/bin/sqlplus /nolog <<eol|grep db:|cut -d ':' -f2 > $OUTPUT/rman_db.list
 connect / as sysdba
 select distinct 'db:'||name from rman.rc_database;
 exit
eol

for i in `cat $OUTPUT/rman_db.list`;do
  $ORACLE_HOME/bin/sqlplus /nolog <<eol > /dev/null
    connect / as sysdba
    column tm new_value file_time noprint;
    select to_char(sysdate,'YYYYMMDD') tm from dual;
    spool $OUTPUT/$i-&file_time
    select output from rman.rc_rman_output where session_key in
        (select session_key from rman.rc_rman_backup_job_details
            where db_name='$i' and
                       start_time > sysdate-1 and
                       input_type <> 'ARCHIVELOG')
             order by recid;
    exit
eol
done

echo "---------------------------------------------"
echo "this status is sent from cron on $HOST, ora user"
echo ""
echo "output under $OUTPUT"
echo ""
echo "check rman_status_$TODAY.lst"

grep -l "no rows" $OUTPUT/*-$TODAY.lst > $MISSING
STATUS=$?

if [ "$STATUS" -eq 0 ]
then
  echo ""
  echo "rman backup missing for some databases"
  echo ""
  cat   $MISSING
  echo ""
  echo "consider a manual backup for those missing"
else
  rm -f $MISSING
fi

egrep -i 'error|rman-' $OUTPUT/*-$TODAY.lst > $ERRORS
STATUS=$?

if [ "$STATUS" -eq 0 ]
then
  echo ""
  echo "rman errors detected"
  echo ""
  echo "check $ERRORS"
  STATUS=1
else
  rm -f $ERRORS
  STATUS=0
fi
echo ""
echo "---------------------------------------------"
exit $STATUS