119 lines
4.1 KiB
Bash
119 lines
4.1 KiB
Bash
#!/bin/bash
|
|
######################################################################
|
|
# Author : Vishal Gupta
|
|
# Date Created : 23-Jun-2008
|
|
# Purpose : Check RMAN backup status
|
|
#
|
|
# Modification History
|
|
# Date Ver Author Remarks
|
|
# -------- --- -------------- ---------------------------------------
|
|
# 23/06/08 0.1 Vishal Gupta First Draft
|
|
#
|
|
#
|
|
######################################################################
|
|
|
|
################################################################################
|
|
# RMAN Repository database variables
|
|
################################################################################
|
|
RMANCAT_USER=rmanrep
|
|
RMANCAT_PASSWORD_ENCRYPTED=HP0aEW
|
|
RMANCAT_SERVICE=RMAN01P
|
|
EMAIL_ADDRESSES="fm.eu.DBAAlerts@rabobank.com"
|
|
UNENCRY=/appl/oracle/admin/scripts/unencry
|
|
LOGDIR=/appl/oracle/admin/logs
|
|
LOGFILE=${LOGDIR}/`basename $0`_`date '+%Y%m%d_%H%M%S'`.log
|
|
LOG_KEEPDAY=14
|
|
|
|
export ORACLE_HOME=/appl/oracle/product/10.2.0
|
|
export PATH=$ORACLE_HOME/bin:$PATH
|
|
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
|
|
|
|
######################################
|
|
# M A I N E X E C U T I O N
|
|
######################################
|
|
|
|
find ${LOGDIR} -name `basename $0`*log -mtime +${LOG_KEEPDAY} -exec rm {} \;
|
|
|
|
sqlplus -s /nolog >>${LOGFILE} <<EOF
|
|
WHENEVER SQLERROR EXIT SQLCODE
|
|
set trimspool on
|
|
set feed off
|
|
set lines 250
|
|
set pages 100
|
|
connect ${RMANCAT_USER}/`${UNENCRY} ${RMANCAT_PASSWORD_ENCRYPTED} 20`@${RMANCAT_SERVICE}
|
|
|
|
PROMPT
|
|
PROMPT
|
|
PROMPT Backups with errors in last 24 hours
|
|
PROMPT ####################################
|
|
COLUMN session_key FORMAT 9999999
|
|
COLUMN start_Time FORMAT a18
|
|
COLUMN status FORMAT a25
|
|
COLUMN object_type FORMAT a15
|
|
COLUMN row_type FORMAT a20
|
|
COLUMN operation FORMAT a30
|
|
COLUMN output_device_Type HEADING "Media" FORMAT a8
|
|
COLUMN input_mb HEADING "Input|(MB)" FORMAT 999,990.99
|
|
COLUMN output_mb HEADING "Output|(MB)" FORMAT 999,990.99
|
|
COLUMN duration HEADING "Dur|(Min)" FORMAT 990.99
|
|
SELECT s.session_key
|
|
,to_char(s.start_Time,'dd-MON-YY hh24:mi:SS') start_time
|
|
,s.db_name
|
|
,s.status
|
|
-- ,s.row_type
|
|
,s.operation
|
|
,s.object_type
|
|
,s.output_device_Type
|
|
,ROUND(s.input_bytes/1024/1024,2) input_mb
|
|
,ROUND(s.output_bytes/1024/1024,2) output_mb
|
|
,ROUND( (s.end_time - s.start_time) * 24 * 60,2) duration
|
|
FROM rc_rman_status s
|
|
WHERE s.start_time > sysdate - 1 + (1/24)
|
|
-- exclude manual list command errors and overall session status.
|
|
-- individual backup/restore/delete step errors will still be reported
|
|
AND s.operation NOT IN ('RMAN'
|
|
,'LIST'
|
|
,'REPORT'
|
|
,'DUPLICATE DB' -- used to clone db on non-prod
|
|
,'RESTORE'
|
|
,'RESTORING AND APPLYING LOGS'
|
|
,'RECOVER AND APPLYING LOGS'
|
|
)
|
|
AND s.status NOT IN ('COMPLETED'
|
|
,'RUNNING'
|
|
,'RUNNING WITH WARNINGS'
|
|
,'COMPLETED WITH WARNINGS'
|
|
)
|
|
ORDER BY start_time asc;
|
|
|
|
PROMPT
|
|
PROMPT
|
|
PROMPT Last successful backup
|
|
PROMPT #######################
|
|
COLUMN max_end_time FORMAT a30 HEADING "Last Successfull DB Backup"
|
|
COLUMN backedup_yesterday FORMAT a30 HEADING "Backed up in last 24hrs ?"
|
|
SELECT d.name
|
|
, to_char(max(s.end_time),'dd-MON-YY hh24:mi:SS') max_end_time
|
|
, CASE WHEN max(s.end_time) > trunc(sysdate -1)
|
|
THEN 'Yes'
|
|
ELSE 'No'
|
|
END backedup_yesterday
|
|
FROM rc_database d
|
|
, rc_rman_status s
|
|
WHERE d.DB_KEY = s.db_key
|
|
AND d.DBINC_KEY = s.DBINC_KEY
|
|
AND d.NAME = s.db_name
|
|
AND s.row_type = 'COMMAND'
|
|
AND s.operation = 'BACKUP'
|
|
AND s.object_type in ( 'DB FULL','DB INCR')
|
|
AND s.status = 'COMPLETED'
|
|
GROUP BY d.name
|
|
ORDER by d.name
|
|
;
|
|
|
|
EOF
|
|
|
|
cat $LOGFILE |mailx -s "Oracle RMAN Backup status ** MORNING CHECK **" "${EMAIL_ADDRESSES}"
|
|
|
|
|