Solved

Script for Monitoring Data Guard

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Identify records which do NOT qualify for a view 9 36
oracle query help 18 99
null value 15 92
sql query 9 37
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

943 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

16 Experts available now in Live!

Get 1:1 Help Now