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
SHAR(1) BSD General Commands Manual SHAR(1)
NAME
shar -- create a shell archive of files
SYNOPSIS
shar file ...
DESCRIPTION
shar writes an sh(1) shell script to the standard output which will recreate the file hierarchy specified by the command line operands.
Directories will be recreated and must be specified before the files they contain (the find(1) utility does this correctly).
shar is normally used for distributing files by ftp(1) or mail(1).
SEE ALSO
compress(1), mail(1), tar(1), uuencode(1)
BUGS
shar makes no provisions for special types of files or files containing magic characters.
EXAMPLES
To create a shell archive of the program ls(1) and mail it to Rick:
cd ls
shar `find . -print` | mail -s "ls source" rick
To recreate the program directory:
mkdir ls
cd ls
...
<delete header lines and examine mailed archive>
...
sh archive
HISTORY
The shar command appears in 4.4BSD.
SECURITY CONSIDERATIONS
It is easy to insert trojan horses into shar files. It is strongly recommended that all shell archive files be examined before running them
through sh(1). Archives produced using this implementation of shar may be easily examined with the command:
egrep -v '^[X#]' shar.file
4.4BSD June 6, 1993 4.4BSD