##  Shell script to perform HOT Database Backups to Disk 


#! /bin/ksh 

HOT=/CML/oracle/HOT/${ORACLE_SID}
LOG=/dsk5/${ORACLE_SID}/dump/alert_${ORACLE_SID}.log

if [ -f $LOG ] ; then
     Bgn_Bkp=‘egrep -ci '^alter tablespace .* begin backup' $LOG‘
     End_Bkp=‘egrep -ci '^alter tablespace .* end backup' $LOG‘
     Diff_Bkp=‘expr $Bgn_Bkp - $End_Bkp‘
else
     mail oracle <<EOF
     ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
     Could not locate Alert Log - Please Investigate !
EOF
     exit 1
fi

sqlplus / <<EOF

    column tablespace_name noprint
    column seqn		 noprint
    set    pagesize        0
    set    linesize        132
    set    feedback        off
    set    sqlprompt       ""

    Whenever SQLERROR exit FAILURE

    select chr(1) tablespace_name, -9999 seqn,
           'alter system switch logfile;'
    from   dual
    UNION
    select chr(1) tablespace_name, -9998 seqn,
           'alter database backup controlfile to ''' ||
           '${HOT}/controlfile_${ORACLE_SID}.HOT.full.before'' reuse;'
    from   dual
    UNION
    select tablespace_name, 0, 
           'alter tablespace '||tablespace_name||' begin backup;'
    from   sys.dba_tablespaces
    where  status = 'ONLINE'
    UNION
    select tablespace_name, file_id,
           '!compress < '||file_name||'> ${HOT}/' || 
           substr(file_name,instr(file_name,'/',-1) + 1) || '.Z'
    from   sys.dba_data_files
    where  status = 'AVAILABLE'
    UNION
    select tablespace_name, 9999,
           'alter tablespace '||tablespace_name||' end backup;'
    from   sys.dba_tablespaces
    where  status = 'ONLINE'
    UNION
    select chr(255) tablespace_name, 9998 seqn,
           'alter database backup controlfile to ''' ||
           '${HOT}/controlfile_${ORACLE_SID}.HOT.full.after'' reuse;'
    from   dual
    UNION
    select chr(255) tablespace_name, 9999 seqn,
           'alter system switch logfile;'
    from   dual
    ORDER
       BY  1, 2

    spool  /tmp/ora_HOT_backup.sql
    /
    spool  off
    start /tmp/ora_HOT_backup.sql
    exit
EOF
   
Bgn_Bkp=‘egrep -ci '^alter tablespace .* begin backup' $LOG‘
End_Bkp=‘egrep -ci '^alter tablespace .* end backup' $LOG‘

if [ $Bgn_Bkp != ‘expr $End_Bkp + $Diff_Bkp‘ ] ; then
     mail oracle <<EOF
     ERROR : HOT BACKUP For "${ORACLE_SID}" Has Failed 
 
     Number of "Begin Backup" stmts does not equal "End Backup" 

     Please Investigate Immediately !
EOF
     exit 1
 if