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,