rman_exp_dguard_summary.ksh


#!/bin/ksh -x
#
# Script:    rman_exp_dg_sum.ksh
# Purpose:   daily mailer for RMAN backups, this script calls an HTML SQL
#            wrapper
# Created:   CWMack, 4 May 2012
# Modified:
#            removed IGNITE resync in favor of doing the resync from the
#            client side
#            CWMack, 8 Nov 2012, added the DGuard info and some CASE
#            statements to color code runtimes and arch gap differences.
#
############################################################################
#
#=-=-=-=-=-=-=-=-=-=
# Variables
#=-=-=-=-=-=-=-=-=-=
#
export ORACLE_HOME=/u01/oracle/11.2/emrep
export ORACLE_SID=emrep
export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export RMAN=$ORACLE_HOME/bin/rman
SD=$ORACLE_HOME/dba_scripts
LOGDIR=$SD/log
LD2=$ORACLE_HOME/dba_logs
SQLPLUS=$ORACLE_HOME/bin/sqlplus
MAIL=/bin/mailx
DATE=`date +%m%d%y`
MAILTO=”joe-bob@abc.com,jane-bob@abc.com”
#MAILTO=”jim-bob@abc.com”
#
#
#=-=-=-=-=-=-=-=-=-=
# resync some windows boxes
#=-=-=-=-=-=-=-=-=-=
#
$RMAN <<EOF
connect target sys/PASSWD@tstemr95
connect catalog repo/PASSWD#1@emrep
resync catalog;
exit;
EOF
#
$RMAN <<EOF
connect target sys/PASSWD@emr95
connect catalog repo/PASSWD#1@emrep
resync catalog;
exit;
EOF
#
#=-=-=-=-=-=-=-=-=
# get to work already……
#=-=-=-=-=-=-=-=-=
#
$SQLPLUS -S /nolog <<EOF
connect repo/PASSWD#1@emrep
#
set echo off feedback off heading on pages 99 trims on lines 122
set headsep !
set define off
break on db_name
column start_time format a16
column “Missing Backups” format a144
column end_time format a16
column status format a50
column “Input GB” format 999.99
column “Output GB” format 999.99
column “Minutes” format 999.99
column “Dump in MB” format 999,999.99
column compression_ratio ON HEADING ‘Compression!Ratio’ headsep ‘!’ format 99.999
column “B/Up Duration in mins” ON HEADING ‘B/Up Duration!in mins’ format 999.99
column input_type ON HEADING ‘RMAN!Input Type’ format a11
alter session set nls_date_format = ‘DD-MON-YY HH24:MI:SS’;
#
@$SD/set_markup.sql
#
#
spool $LOGDIR/missing_rman.htm
#
SELECT ‘Database ‘||'<span class=”threshold-critical”>’||db_name||'</span>’||’ missing backup(s) with the –
        last backup date being: ‘||max(start_time) as “Missing Backups”
FROM rc_rman_backup_job_details
WHERE db_name not in
   (SELECT db_name
    FROM rc_rman_backup_job_details
    WHERE start_time > (sysdate – 1))
GROUP BY db_name;
spool off
#
TTITLE LEFT _DATE CENTER ‘<h1> Daily RMAN Backup Report, previous 2 days</h1>’ –
RIGHT ‘Page:’ FORMAT 999 SQL.PNO SKIP CENTER ‘<h2><span class=threshold-critical>Red</span> = ERROR, <span class=threshold-warning>Orange</span> = Warning</h2>’
#
#
spool $LOGDIR/rman_report.htm
#
SELECT ‘<a href=”https://paladin2.hsc.net.ou.edu:4444/em/console/database/instance/sitemap?ctxType=Databases&type=oracle_database&target=&#8217;||db_name||’&event=doLoad”>’||db_name||'</a>’ db_name,
start_time as “Start Time”,
input_bytes/1024/1024/1024 as “Input GB”,
output_bytes/1024/1024/1024 as “Output GB”,
compression_ratio,
CASE
WHEN status = ‘COMPLETED’ then
‘<span class=”threshold-ok”>’||status||'</span>’
WHEN status = ‘COMPLETED WITH WARNINGS’ then
‘<span class=”threshold-warning”>’||status||'</span>’
ELSE
‘<span class=”threshold-critical”>’||status||'</span>’
END as “Status”,
input_type,
CASE
WHEN elapsed_seconds/60 < 100 then
‘<span class=”threshold-ok”>’||rpad(trunc(elapsed_seconds/60,2),4,0)||'</span>’
WHEN elapsed_seconds/60 between 100 and 300 then
‘<span class=”threshold-warning”>’||rpad(trunc(elapsed_seconds/60,2),5,0)||'</span>’
ELSE
‘<span class=”threshold-critical”>’||rpad(trunc(elapsed_seconds/60,2),5,0)||'</span>’
END as “B/Up Duration in mins”
FROM RC_RMAN_BACKUP_JOB_DETAILS
WHERE start_time > (sysdate – 2)
ORDER BY db_name, start_time;
spool off
#
#elapsed_seconds/60 as “B/Up Duration in mins”
#
TTITLE LEFT _DATE CENTER ‘<h1> Daily Export Report</h1>’ –
RIGHT ‘Page:’ FORMAT 999 SQL.PNO SKIP CENTER ‘<h2><span class=threshold-critical>Red</span> = ERROR</h2>’
spool $LOGDIR/export_report.htm
#
SELECT db_name,
hostname,
CASE
WHEN status = ‘Successful’ then
‘<span class=”threshold-ok”>’||status||'</span>’
ELSE
‘<span class=”threshold-critical”>’||status||'</span>’
END status,
start_time,
end_time,
dmp_size/1024/1024 “Dump in MB”,
file_name
FROM exp_data
WHERE start_time > (sysdate -2)
order by db_name, start_time;
spool off
exit;
EOF
#
rm $LOGDIR/dguard_report.htm
#
#
check_dg_sync()
{
#
$SQLPLUS -S /nolog <<EOF
#
connect sys/$1@$3 as sysdba
alter session set nls_date_format=’HH24:MI:SS DD/MON/YY’;
set feedback off pages 55 trims on lines 133
set feedback off heading on pages 55 trims on lines 133
#break on “DataGuard Status” skip 1 on report
column dest_name format a19
column destination format a40
column group# format a7
column client_dbid format a11
column client_pid format a10
column error format a20
column arch format a4
column log_seq# format 9999999
column reopen format 99999
column delay format 9999
column timeout format 999999
column arch_seq format 9999999
column app_seq format 999999
column recovery_mode format a24
column sequence# format 999999999
#
@$SD/set_markup.sql
#
spool $LOGDIR/dguard_report.htm APPEND
#
SELECT ‘Database = ‘
||'<span class=”threshold-critical”>’
||’$3′
||'</span>’ AS “DGuard Status”
FROM dual;
#
SELECT
   DISTINCT arch.thread# “Thread”,
   CASE
      WHEN (arch.sequence# – appl.sequence#) = 0 then
         ‘<span class=”threshold-ok”>’||(arch.sequence# – appl.sequence#)||'</span>’
      WHEN (arch.sequence# – appl.sequence#) between 1 and 3 then
         ‘<span class=”threshold-warning”>’||(arch.sequence# – appl.sequence#)||'</span>’
      ELSE
         ‘<span class=”threshold-critical”>’||(arch.sequence# – appl.sequence#)||'</span>’
      END as “Difference”,
   arch.sequence# “Last Sequence Received”,
   appl.sequence# “Last Sequence Applied”
FROM
   (SELECT thread#,
     sequence#
   FROM v\$archived_log
   WHERE (thread#,first_time ) IN
     (SELECT thread#,MAX(first_time) FROM v\$archived_log GROUP BY thread#
     )
   ) arch,
   (SELECT thread#,
     sequence#
   FROM v\$log_history
   WHERE (thread#,first_time ) IN
     (SELECT thread#,MAX(first_time) FROM v\$log_history GROUP BY thread#
     )
   ) appl
WHERE arch.thread# = appl.thread#
ORDER BY 1;
spool off;
exit;
EOF
}
#
#=-=-=-=-=-=-=-=-=-=-=-=-=
#
# add databases by following the syntax below….
#
#=-=-=-=-=-=-=-=-=-=-=-=-=
#
check_dg_sync PASSWDft1   PRDFT90 PRDFTDR
check_dg_sync PASSWDct1   PRDCT90 PRDCTDR
check_dg_sync PASSWDht1   PRDHT90 PRDHTDR
check_dg_sync PASSWD1      OSSRAC OSSRACDR
check_dg_sync PASSWDafm1 AFMPRD AFMDR
check_dg_sync “PASSWD”    ERES    ERESDR
check_dg_sync PASSWDfmx21 PRDFMX2 FMX2DR
check_dg_sync PASSWD_08   PRDPIN PINDR
#
#
#
#Mail the LOG
mail_log()
{
#
CONTENTa=”$LOGDIR/missing_rman.htm”
CONTENT=”$LOGDIR/rman_report.htm”
CONTENT2=”$LOGDIR/export_report.htm”
CONTENTDG=”$LOGDIR/dguard_report.htm”
SUBJECT=”RMAN/Export/DGuard Summary for `date` “
(
echo “Subject: $SUBJECT”
echo “MIME-Version: 1.0”
echo “Content-Type: text/html”
echo “Content-Disposition: inline”
cat $CONTENTa $CONTENT $CONTENT2 $CONTENTDG
) | /usr/sbin/sendmail $MAILTO cat $CONTENTa
}
#
mail_log
exit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s