Solved

Script for Monitoring Data Guard

Posted on 2010-11-30
3
976 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.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now