10-27-2008
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,
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
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
2. Shell Programming and Scripting
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
3. Shell Programming and Scripting
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
4. UNIX for Dummies Questions & Answers
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
5. Shell Programming and Scripting
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
6. Shell Programming and Scripting
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
7. UNIX for Advanced & Expert Users
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
8. Shell Programming and Scripting
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
9. UNIX for Beginners Questions & Answers
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
10. UNIX for Advanced & Expert Users
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
exit(1) User Commands exit(1)
NAME
exit, return, goto - shell built-in functions to enable the execution of the shell to advance beyond its sequence of steps
SYNOPSIS
sh
exit [n]
return [n]
csh
exit [ ( expr )]
goto label
ksh
*exit [n]
*return [n]
DESCRIPTION
sh
exit will cause the calling shell or shell script to exit with the exit status specified by n. If n is omitted the exit status is that of
the last command executed (an EOF will also cause the shell to exit.)
return causes a function to exit with the return value specified by n. If n is omitted, the return status is that of the last command exe-
cuted.
csh
exit will cause the calling shell or shell script to exit, either with the value of the status variable or with the value specified by the
expression expr.
The goto built-in uses a specified label as a search string amongst commands. The shell rewinds its input as much as possible and searches
for a line of the form label: possibly preceded by space or tab characters. Execution continues after the indicated line. It is an error to
jump to a label that occurs between a while or for built-in command and its corresponding end.
ksh
exit will cause the calling shell or shell script to exit with the exit status specified by n. The value will be the least significant 8
bits of the specified status. If n is omitted then the exit status is that of the last command executed. When exit occurs when executing
a trap, the last command refers to the command that executed before the trap was invoked. An end-of-file will also cause the shell to exit
except for a shell which has the ignoreeof option (See set below) turned on.
return causes a shell function or '.' script to return to the invoking script with the return status specified by n. The value will be the
least significant 8 bits of the specified status. If n is omitted then the return status is that of the last command executed. If return
is invoked while not in a function or a '.' script, then it is the same as an exit.
On this man page, ksh(1) commands that are preceded by one or two * (asterisks) are treated specially in the following ways:
1. Variable assignment lists preceding the command remain in effect when the command completes.
2. I/O redirections are processed after variable assignments.
3. Errors cause a script that contains them to abort.
4. Words, following a command preceded by ** that are in the format of a variable assignment, are expanded with the same rules as a vari-
able assignment. This means that tilde substitution is performed after the = sign and word splitting and file name generation are not
performed.
ATTRIBUTES
See attributes(5) for descriptions of the following attributes:
+-----------------------------+-----------------------------+
| ATTRIBUTE TYPE | ATTRIBUTE VALUE |
|Availability |SUNWcsu |
+-----------------------------+-----------------------------+
SEE ALSO
break(1), csh(1), ksh(1), sh(1), attributes(5)
SunOS 5.10 15 Apr 1994 exit(1)