Linux 2.6.18-308.el5 i386 / Linux 2 .6.18-238.el5 x86_64
Code: Select all
#!/bin/sh
#
# Script To Automate Data Guard Database Status
# Created: drake 03.13.2013
#
# ---------------------------------------------------------------------------
# Begin - Environment Variables
# ---------------------------------------------------------------------------
# Set DB Environment
db_environment=DEV
# Oracle Home Path
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
# Oracle SID of the target standby database
ORACLE_SID=stan
export ORACLE_SID
# Set path to sqlplus executable
SQLPLUS=$ORACLE_HOME/bin/sqlplus
# Oracle DBA OS user ID
ORACLE_USER=oracle
# Set output file.
DG_LOG_FILE=/tmp/dgmon_$ORACLE_SID.out
# Binary path to awk utility
AWK=/bin/awk
# Set Oracle Grid Infrastructure Home
CRS_HOME=/u01/app/grid/11.2.0/grid
# Add e-mail recipient here, separated by a comma WITHOUT SPACE
DBAEMAIL=dba@email.com
# Set the Standby DB connect string
STANDBY_CONNECT_STR='/ as sysdba'
# Set Primary DB connect string
# Must be created on Primary as standard non-expiring account with GRANT SELECT ANY DICTIONARY Sys Priv
PRIMARY_CONNECT_STR=user/pass@prim
# ---------------------------------------------------------------------------
# End - Environment Variables
# ---------------------------------------------------------------------------
# ---------------------------------------------------------------------------
# Begin - Script
# ---------------------------------------------------------------------------
# NO NEED TO EDIT BEYOND THIS POINT
# ---------------------------------------------------------------------------
# Determine the user which is executing this script.
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
# check for existing output file and remove if found.
if [ -s $DG_LOG_FILE ] ; then
/bin/rm -f $DG_LOG_FILE
fi
# Log the start of this script.
echo Report Generated: \<br\> >> $DG_LOG_FILE
echo `date` \<br\> >> $DG_LOG_FILE
# ---------------------------------------------------------------------------
# Begin - Gather Standby & Primary Info
# ---------------------------------------------------------------------------
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
echo \<br\> >> $DG_LOG_FILE
## Active Data Guard Health Check
$SQLPLUS -s $STANDBY_CONNECT_STR <<EOF >> $DG_LOG_FILE
set feedback off
prompt <h2>Data Guard Information</h2>
set markup html on spool on TABLE "WIDTH='80%' BORDER='1'"
--Role Verification
set lines 1000
col STARTUP_TIME for a30
select DATABASE_ROLE, db_unique_name, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, TO_CHAR(startup_time, 'DD/MM/YYYY HH24:MI:SS') as startup_time from v\$instance i, v\$database;
--Last Seq# Received/Applied To Standby Site
SELECT a.thread#,
a.Last_Seq_Received-b.Last_Seq_Applied "Difference",
a.Last_Seq_Received,
b.Last_Seq_Applied
FROM
(SELECT thread#,
MAX(sequence#) Last_Seq_Received
FROM v\$archived_log
GROUP BY thread#
ORDER BY 1
) a ,
(SELECT thread#,
MAX(sequence#) Last_Seq_Applied
FROM v\$archived_log
WHERE applied in ('YES','IN-MEMORY')
GROUP BY thread#
ORDER BY 1
) b
WHERE a.thread#=b.thread# ;
--Redo Data Information
set lines 1000
COLUMN NAME FORMAT A30
COLUMN VALUE FORMAT A16
COLUMN TIME_COMPUTED FORMAT A24
select * from v\$dataguard_stats where NAME in ('transport lag', 'apply lag');
--verify if managed recovery process is real time?
set pages 999
set lines 999
col DEST_NAME for a30
col DESTINATION for a20
col DB_UNIQUE_NAME for a14
col STATUS for a10
SELECT dest_name,
recovery_mode,
status,
type,
destination,
db_unique_name
FROM v\$archive_dest_status
WHERE STATUS='VALID' and DEST_NAME <> 'STANDBY_ARCHIVE_DEST';
--List of Currently Active Instance
select INSTANCE_NAME,STATUS,HOST_NAME,TO_CHAR (STARTUP_TIME, 'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME",THREAD# from gv\$instance order by INSTANCE_NAME;
--Determine any gap that is currently blocking redo apply from continuing
--SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM v\$ARCHIVE_GAP;
SELECT THREAD#, LOW_SEQUENCE# as "Low Seq Gap", HIGH_SEQUENCE# as "High Seq Gap" FROM V\$ARCHIVE_GAP;
--ASM Disk Group Utilization
SET lines 999
col name for a20
col total_mb for 999,999,999.99
col group_number for 99 heading "Group|Num"
col free_mb for 999,999,999.99 heading "Free|Size|(MB)"
col required_mirror_free_mb for 999,999,999.99 heading "Required|Mirror|Free (MB)"
col usable_file_mb for 999,999,999.99 heading "Usable|File|(MB)"
col "% Free" for 999.99
col total_mb format 999,999,999,999.9 heading "Total|Size|(MB)"
SELECT group_number,name,
free_mb/total_mb*100 "% Free",
free_mb,
total_mb,
--required_mirror_free_mb,
usable_file_mb
FROM V\$ASM_DISKGROUP
;
set markup html off
EOF
## Primary Database Health Check
$SQLPLUS -s $PRIMARY_CONNECT_STR <<EOF >> $DG_LOG_FILE
prompt <h2>Primary Site Information</h2>
set markup html on spool on TABLE "WIDTH='80%' BORDER='1'"
set feedback off
--Role Verification
set lines 1000
col STARTUP_TIME for a30
select DATABASE_ROLE, db_unique_name, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, TO_CHAR(startup_time, 'DD/MM/YYYY HH24:MI:SS') as startup_time
from v\$instance i, v\$database;
--Last Seq# Generated on Primary
SELECT thread#,
MAX(sequence#) "Last Primary Seq Generated"
FROM v\$archived_log val,
v\$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY thread#
ORDER BY 1;
--Query any errors that occured the last time an attempt to archive to the destination was attempted.
--Local/Remote Archiving Status
SET lines 999
col DEST_NAME FOR a20
col ERROR FOR a40
col destination FOR a30
SELECT dest_name,
status,
NVL(error, 'No Issues Found') "Error",
target, destination,
DB_UNIQUE_NAME
FROM v\$archive_dest
WHERE DESTINATION IS NOT NULL;
--List of Currently Active Instance
select INSTANCE_NAME,STATUS,HOST_NAME,TO_CHAR (STARTUP_TIME, 'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME",THREAD# from gv\$instance order by INSTANCE_NAME;
--ASM Disk Group Utilization
SET lines 999
col name for a20
col total_mb for 999,999,999.99
col group_number for 99 heading "Group|Num"
col free_mb for 999,999,999.99 heading "Free|Size|(MB)"
col required_mirror_free_mb for 999,999,999.99 heading "Required|Mirror|Free (MB)"
col usable_file_mb for 999,999,999.99 heading "Usable|File|(MB)"
col "% Free" for 999.99
col total_mb format 999,999,999,999.9 heading "Total|Size|(MB)"
SELECT group_number,name,
free_mb/total_mb*100 "% Free",
free_mb,
total_mb,
--required_mirror_free_mb,
usable_file_mb
FROM V\$ASM_DISKGROUP
;
set markup html off
EOF
# ---------------------------------------------------------------------------
# End - Gather Standby & Primary Info
# ---------------------------------------------------------------------------
# ---------------------------------------------------------------------------
# Begin - Local & Cluster Resources - Data Guard
# ---------------------------------------------------------------------------
RSC_KEY=$1
QSTAT=-u
echo "<u>Additional Information</u> <h2>Local & Cluster Resources - Data Guard</h2> <b><i>$CRS_HOME/bin/crsctl stat res -t</b></i></p>" >> $DG_LOG_FILE
# Table header:
echo "<table> <tr>" >> $DG_LOG_FILE
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "<th>HA Resource</th>", "<th>State</th>", "<th>Target</th>"; }' >> $DG_LOG_FILE
echo "</tr>" >> $DG_LOG_FILE
# Table body:
$CRS_HOME/bin/crsctl status resource | $AWK \
'
function ltrim(s) { sub(/^[ \t]+/, "", s); return s }
function rtrim(s) { sub(/[ \t]+$/, "", s); return s }
function trim(s) { return rtrim(ltrim(s)); }
BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; split(apptarget, atarget, ","); state=2;}
$1~/STATE/ && state == 2 {appstate = $2; split(appstate, astate, ","); state=3;}
state == 3 { split(appname, a, ",");
for (i = 1; i <= length(atarget); i++) {
printf "%-45s %-10s %-18s\n",echo "<tr>" echo "<td>" appname echo "</td>", echo "<td>" trim(astate[i]) echo "</td>", echo "<td>" trim(atarget[i]) echo "</td>" echo "</tr>"
};
state=0;}' >> $DG_LOG_FILE
echo "</table>" >> $DG_LOG_FILE
# ---------------------------------------------------------------------------
# End - Local & Cluster Resources - Data Guard
# ---------------------------------------------------------------------------
# ---------------------------------------------------------------------------
# Begin - Add Additional Formatting To E-mail Body
# ---------------------------------------------------------------------------
sed -i "1iTo: $DBAEMAIL" $DG_LOG_FILE
sed -i '2iContent-Type: text/html; charset="us-ascii"' $DG_LOG_FILE
sed -i '3i<html>' $DG_LOG_FILE
sed -i '4i<head>' $DG_LOG_FILE
sed -i '5i<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">' $DG_LOG_FILE
sed -i '6i<meta name="generator" content="SQL*Plus 11.2.0">' $DG_LOG_FILE
sed -i '7i<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; text-align: left;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style>' $DG_LOG_FILE
sed -i "8i<title>$db_environment: $ORACLE_SID : Data Guard Status Report on `hostname -s`</title>" $DG_LOG_FILE
sed -i '9i</head>' $DG_LOG_FILE
sed -i '10i<body>' $DG_LOG_FILE
sed -i '11i<left>' $DG_LOG_FILE
# ---------------------------------------------------------------------------
# End - Add Additional Formatting To E-mail Body
# ---------------------------------------------------------------------------
# ---------------------------------------------------------------------------
# Script Details
# ---------------------------------------------------------------------------
echo \<br\> >> $DG_LOG_FILE
echo "*********************************" \<br\> >> $DG_LOG_FILE
echo Script Info... \<br\> >> $DG_LOG_FILE
echo \<br\> >> $DG_LOG_FILE
echo Script Name: `basename $0` \<br\> >> $DG_LOG_FILE
# ---------------------------------------------------------------------------
# Print out the value of the variables set by this script.
# ---------------------------------------------------------------------------
echo "SQL*Plus: $SQLPLUS" \<br\> >> $DG_LOG_FILE
echo "ORACLE_SID: $ORACLE_SID" \<br\> >> $DG_LOG_FILE
echo "Running on Host: `hostname -s`" \<br\> >> $DG_LOG_FILE
echo "ORACLE_USER: $ORACLE_USER" \<br\> >> $DG_LOG_FILE
echo "ORACLE_HOME: $ORACLE_HOME" \<br\> >> $DG_LOG_FILE
# ---------------------------------------------------------------------------
# Log the completion of this script.
# ---------------------------------------------------------------------------
echo \<br\> >> $DG_LOG_FILE
echo Report ended on `date` \<br\> >> $DG_LOG_FILE
echo \</left\> >> $DG_LOG_FILE
echo \</body\> >> $DG_LOG_FILE
echo \</html\> >> $DG_LOG_FILE
# ---------------------------------------------------------------------------
# Send e-mail
# ---------------------------------------------------------------------------
echo "Subject: $db_environment: $ORACLE_SID : Data Guard Status Report on `hostname -s`" | cat - $DG_LOG_FILE | sendmail -t
exit
# ---------------------------------------------------------------------------
# End - Script
# ---------------------------------------------------------------------------
See Sample E-mail Report Here:
http://www.oracle-forums.com/docs/dgmon.html
The output html file would be:
http://www.oracle-forums.com/docs/dgmon_stan.out