Visit The New, Modern Unix Linux Community


How to use SSH to connect to Primary DB and send alert mail


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use SSH to connect to Primary DB and send alert mail
# 1  
How to use SSH to connect to Primary DB and send alert mail

Hi All,

AIX 5.3 64 bit:

I am using the below shell script the objective is:

Objective:
========

Use SQL*Plus to query the MAX(SEQUENCE#) from both databases V$LOG_HISTORY view. If the STANDBY appears to be falling behind,then send alert mail through the below shell script:

How could I connect to the primary database in the below shell script using SSH?

#-----------------------------------------------------------------------------
# Use SQL*Plus to query the MAX(SEQUENCE#) from both databases V$LOG_HISTORY view. If the STANDBY appears to be falling behind, send alert mail..
#-----------------------------------------------------------------------------
_OutFile=/tmp/stdby_chk_$$.out
sqlplus -s /nolog << __EOF__ > ${_OutFile} 2>&1
whenever oserror exit 99
connect / as sysdba
set verify off

whenever sqlerror exit 6
col logseq_on_standby new_value V_STDBY_LOGSEQ
select /*+ rule */ max(h.sequence#) logseq_on_standby
from v\$log_history h,
v\$parameter p
where h.thread# = to_number(decode(p.value,'0',1,p.value))
and p.name = 'thread';

col filecnt new_value V_STDBY_FILECNT
select count(*) filecnt
from v\$datafile;

whenever sqlerror exit 7
connect ${_PriUnPwd}

whenever sqlerror exit 8
col logseq_on_primary new_value V_PRIMARY_LOGSEQ
select /*+ rule */ max(h.sequence#) logseq_on_primary
from sys.v_\$log_history h,
sys.v_\$parameter p
where h.thread# = to_number(decode(p.value,'0',1,p.value))
and p.name = 'thread';

col filecnt new_value V_PRIMARY_FILECNT
select count(*) filecnt
from v\$datafile;

whenever sqlerror exit 9
begin
if &&V_STDBY_LOGSEQ < &&V_PRIMARY_LOGSEQ - 2 then
--
if &&V_PRIMARY_FILECNT > &&V_STDBY_FILECNT then
--
raise_application_error(-20001,
'${_StdbyOraSid} is falling behind; datafile(s) were added to PRIMARY');
--
elsif &&V_PRIMARY_FILECNT < &&V_STDBY_FILECNT then
--
raise_application_error(-20002,
'${_StdbyOraSid} is falling behind; datafile(s) were dropped from PRIMARY');
--
else
--
raise_application_error(-20000,
'${_StdbyOraSid} is falling behind.');
--
end if;
--
end if;
end;
/
exit success
__EOF__


Anyone could help would be appreciated!!

Thanks for your time!

Regards,

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #700
Difficulty: Medium
In the theory of Vernadsky, the noosphere is the fifth in a succession of phases of related to the development of the Earth.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Client was not authenticated to send anonymous mail during MAIL FROM (in reply to MAIL FROM comm

I am having trouble getting mail to work on a red hat server. At first I was getting this message. Diagnostic-Code: X-Postfix; delivery temporarily suspended: connect to :25: Connection refused Then added the port to my firewall. Then I temporarily turned off selinux. I then copied this file... (1 Reply)
Discussion started by: cokedude
1 Replies

2. UNIX for Beginners Questions & Answers

Shell script to send mail alert

Hi I have below shell script to send mail alert , however I want to add more functionality in this script and that is , script should only check that file between 9 am to 5pm , and if there is no activity in this time 9 am to 5 pm for 2hours then it should give me mail alert, please help... (2 Replies)
Discussion started by: scazed
2 Replies

3. Shell Programming and Scripting

Shell script to send mail alert

HI Guys, I am writing one shell script to send the mail alert to some email id's if the file not modified in last 10 mins but its not working, I believe MTIME is null string is wrong . can you please assist me on this. script :- filename="abc.txt" echo "Filename is $filename"... (1 Reply)
Discussion started by: abhigrkist
1 Replies

4. UNIX for Advanced & Expert Users

need to configure mail setting to send mail to outlook mail server

i have sun machines having solaris 9 & 10 OS . Now i need to send mail from the machines to my outlook account . I have the ip adress of OUTLOOK mail server. Now what are the setting i need to do in solaris machines so that i can use mailx or sendmail. actually i am trying to automate the high... (2 Replies)
Discussion started by: amitranjansahu
2 Replies

5. Shell Programming and Scripting

how to write a shellscript to send a mail alert to the website user based on license expiration time

hi, i am very much new to shell scripting i have a requirement that i have to develop a License Renewal Alert system that has to give a alert mail to the users before 30days of user account expiration, by checking expiration date of the user with the data base, this system will... (0 Replies)
Discussion started by: deepu_Shscripts
0 Replies

6. Shell Programming and Scripting

Poll for a file. If not present...Send a alert mail

Dear Experts, I have a requirement where a 3rd party system places a file in my folder.I am running on HP UNIX. I would like to have a unix script which will check for the existence of the file. If yes OK. if the file is not placed then it has to send a mail to couple of emails ids saying that... (3 Replies)
Discussion started by: phani333
3 Replies

7. UNIX for Dummies Questions & Answers

How to send html file in a mail not as an attachment but it should display in the mail in table for

Hi The below script working when we are sending the html as attachment can u please guide how to send thesmae data in table form direct in the mail and not in mail attachment . cat Employee.sql SET VERIFY OFF SET PAGESIZE 200 SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON - HEAD... (0 Replies)
Discussion started by: mani_isha
0 Replies

8. Shell Programming and Scripting

How to automatically send a mail alert if the script errors out

I have a script like this, which calls other scripts from that script: #!/usr/ksh moveFiles.sh extract.sh readfile=/home/sample.txt cat $readfile | while read line do file= `echo $line|awk '{print $4}'` if ; then mv $file /home/temp_stage fi (4 Replies)
Discussion started by: ss3944
4 Replies

9. Shell Programming and Scripting

Shell script using ssh remains connected at primary site

Hi All, OS:AIX 5.3 L My shell script using ssh remains connected at primary site and ssh doesn't get disconnected.What should be typed and where in the shell script below to do it. Shell script ======== #!/usr/bin/ksh v_standby=`sqlplus -s /nolog <<END @standby.sql END` echo... (5 Replies)
Discussion started by: a1_win
5 Replies

10. Shell Programming and Scripting

need to send alert mail

hi, i need to monitor a cron job that runs every 5 mins. if this cron job does not process the request for more than 6 hrs, an alert mail should be sent. how do i achieve this?? (1 Reply)
Discussion started by: smurala
1 Replies

Featured Tech Videos