Introduction: In the first post Here, we have seen all the procedure to make the Oracle 9i Replication between 2 sites. It is always easy to build the things, but it is more difficult to maintain the things.
For Oracle Replication, there might be different problems, especially because of the connectivity between the replicated sites. We need to check / monitor the replication status, for this we can use the information related to replication, and off course, we can get this from the views which are related to Replication. Following script can be used to check the health of Replication.
In the next article we will see, how to make things right, if there is some problem with the Replication.
Replication health check script:
set feed on
set echo off
set trimspoo on
set verify off
set lines 1000
column dblink format a20
prompt
prompt ————- job queue related parameters ———-
show parameter job
prompt
prompt ————– database name ————————
select name
from v$database
/
prompt
prompt ————– Master Definition Site (master defintion, master) ————–
select gname,dblink,masterdef,master,group_owner
from dba_repsites
/
prompt
prompt ————– defcall results —————-
select *
from defcall
/
prompt
prompt ————– deftran ——————–
select *
from deftran
/
prompt
prompt ————– deftrandest ——————–
select *
from deftrandest
/
prompt
prompt ————– The number of Deferred Transaction w.r.t Destination —–
SELECT DISTINCT(dblink), COUNT(deferred_tran_id)
FROM deftrandest GROUP BY dblink
/
prompt
prompt ————– No. of transactions associated with replicated transaction —-
select deferred_tran_id, count(*) from defcall where deferred_tran_id in (
select deferred_tran_id from (
select deferred_tran_id, to_char(start_time,’HH:MI:SS’) from deftran where sysdate – start_time < 1
))group by deferred_tran_id
/
prompt
prompt ————— repgroup status —————
select sname,master,status,gname,owner
from dba_repgroup
/
prompt
prompt ————— repobject status —————
select sname,oname,type,status,gname,group_owner
from dba_repobject
/
prompt
prompt ————— repcatlog ———————
select gname,request,status,errnum
from dba_repcatlog
order by id,gname
/
prompt
prompt ————— db jobs ———————–
select job,schema_user,broken,interval,failures,what,instance
from dba_jobs
/
prompt
prompt ————— failure jobs —————-
select job,failures,what from dba_jobs
where upper(what) like ‘%DBMS_DEFER_SYS%’
/
prompt
prompt ————— push jobs ——————–
select job push_job,dblink,
substr(to_char(last_date,’mm/dd/yy hh24:mi:ss’),1,20) last_date,
substr(to_char(next_date,’mm/dd/yy hh24:mi:ss’),1,20) next_date,interval
from defschedule
where job in (select job from dba_jobs
where upper(what) like ‘%DBMS_DEFER_SYS.PUSH%’)
/
prompt
prompt ————– purge jobs ——————–
select job purge_job,dblink,
substr(to_char(last_date,’mm/dd/yy hh24:mi:ss’),1,20) last_date,
substr(to_char(next_date,’mm/dd/yy hh24:mi:ss’),1,20) next_date,interval
from defschedule
where job in (select job from dba_jobs
where upper(what) like ‘%DBMS_DEFER_SYS.PURGE%’)
/
prompt
prompt ————- broken jobs ——————-
select job,broken,failures
from dba_jobs
where upper(what) like ‘%DBMS_DEFER_SYS%’
/
prompt
prompt ————- defpropagator —————-
select *
from defpropagator
/
prompt
prompt ————- propagator links —————
select db_link
from dba_db_links
where owner = (select username from defpropagator)
/
prompt
prompt ————- pushed sites by propagator ——
select job,dblink pushed_site_by_propagator
from defschedule
where job in (select job from dba_jobs
where log_user in (select username from defpropagator)
and upper(what) like ‘%DBMS_DEFER_SYS.PUSH%’)
/
prompt
prompt ———— current running jobs ————
select /*+ ORDERED */ j.job, j.sid, d.dblink,
substr(to_char(j.this_date,’mm/dd/rrrr hh24:mi:ss’),1,20) start_date
from defschedule d, dba_jobs_running j
where j.job in (select job from dba_jobs
where upper(what) like ‘%DBMS_DEFER_SYS.PUSH%’)
and j.job = d.job
/
set feed on
set echo on
set verify on
spool off;
