section 1:

#! /bin/sh

PATH=/usr/local/bin:$PATH
ORACLE_SID=statdb
ORAENV_ASK=NO
. oraenv > /dev/null 2>&1

cd /home/oracle/utility/OGG_delay
sqlplus "/ as sysdba" <<_EOF
@q_delay_testRO.sql
_EOF


section 2:
alter session set nls_date_format=‘DD-MON-YYYY HH24:MI:SS‘
/
set pages 20000

col txn_date head ‘Date‘
col txn_time head ‘Time‘
col no_of_times head ‘Occurance‘
col delay_in_seconds head ‘Delay (seconds)‘
col total_delay      head ‘Delay (seconds)‘ for a15

spool q_delay.lst

prompt OGG testRO delay list (last 7 hours)
prompt running at 7:00,13:00 everyday in temporary.
prompt ========================

--select ‘< ‘||ceil((LAST_UPDATED_TIME-last_transaction_datetime)*3600*24/10)||‘0‘ total_delay, count(*) no_of_times
--from gguser.gguser_delay@testRO
--where last_transaction_datetime between (sysdate - 420/1440) and trunc(sysdate + 1)
--group by ‘< ‘||ceil((LAST_UPDATED_TIME-last_transaction_datetime)*3600*24/10)||‘0‘
--order by 1 desc
--/

select ‘< ‘||ceil_delay "YICT_OGG_DELAY (in seconds)", count(*) from
(
select to_char(LAST_UPDATED_TIME,‘dd-mon-yy hh24:mi:ss‘),
round((LAST_UPDATED_TIME-last_transaction_datetime)*3600*24) absdelay,
floor(round((LAST_UPDATED_TIME-last_transaction_datetime)*3600*24)/10)*10+10 ceil_delay
from gguser.gguser_delay@testRO
--where LAST_UPDATED_TIME between trunc(sysdate) and trunc(sysdate + 1)
where last_transaction_datetime between (sysdate - 420/1440) and trunc(sysdate + 1)
) group by ceil_delay
order by ceil_delay Desc
/

select to_char(last_transaction_datetime,‘YYYYMMDD HH24:MI:SS‘) txn_date_time, (LAST_UPDATED_TIME-last_transaction_datetime)*3600*24 delay_in_seconds
from gguser.gguser_delay@testRO
where last_transaction_datetime between (sysdate - 420/1440) and trunc(sysdate + 1)
order by txn_date_time
/

spool off

ogg 监控脚本