Solved

Script for Monitoring Data Guard

Posted on 2010-11-30
3
984 Views
Last Modified: 2012-05-10
Hi Experts,

I would like a Unix shell script that would run the SQL below to run via a cron job every hour.  

SELECT TYPE, STATUS, HIGH_SCN    FROM V$LOGSTDBY;

no rows selected

If the query returns, "no rows selected" then I know for sure that Data Gurad has stopped. If the query returns, "no rows selected" , then I would like an e-mail trigerred to inform the DBAs that Data Guard has stopped.


0
Comment
Question by:KamalAgnihotri
3 Comments
 
LVL 6

Accepted Solution

by:
sridharv9 earned 250 total points
ID: 34240699
#!/bin/ksh
C_DATE=`date +"%a %b %e "`
C_HOUR=`date +"%H"`
C_TIME=`date +"%p"`
RECEIVER1='<receiver@email.com'
LOC=/export/home/apps/scripts/
ORACLE_HOME=/opt/oracle/product/10.2.0
export ORACLE_HOME
OUTPUT_FILE=/export/home/apps/scripts/dg_status_output.txt

$ORACLE_HOME/bin/sqlplus /nolog << !
connect username/password@sid
set termout off
set arraysize 5
set echo off
set verify off
set pagesize 1000
set linesize 200

spool /export/home/apps/scripts/dg_status_output.txt

SELECT TYPE, STATUS, HIGH_SCN    FROM V$LOGSTDBY;

spool off;
!
# Extract if no rows found
grep -i "no rows" $OUTPUT_FILE > $LOC/norow_output
# Variable LN_CNT initialized to the no. of lines in the ora_errorsfile.
#
LN_CNT=`wc -l < $LOC/norow_output`

echo $LN_CNT
#
#
if [[ $LN_CNT -eq 1  ]]
then
  echo "Check Dataguard status"
mailx -s "DG status query returned NO ROWS FOUND" $RECEIVER1<$LOC/norow_output
fi
#
# end-of-script
0
 
LVL 5

Expert Comment

by:anand_20703
ID: 34290348
perfect script solution
0
 

Author Closing Comment

by:KamalAgnihotri
ID: 34291343
Hi  sridharv9 ,
Excellent. And it worked.
Thanks a lot sridharv9

Sincerely,

Kamal Agnihotri
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL 6 59
clob to char in oracle 3 40
MULTIPLE DATE QUERY 15 79
Import and exporting Oracle Data with encrypted columns 4 29
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question