spool C:\Databases\replication\reports\replication_status_output.log
rem This script gathers the Replication related information
rem this can be used to invistigate the problem 
rem this can be used as well to see the health of replication



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 ---------- (job parameter value should be > 0)

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 --------------- (The status should be Normal)

select sname,master,status,gname,owner
from dba_repgroup
/

prompt 
prompt --------------- repobject status --------------- (The status should be Valid)

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 ----------------------- (The Broekn shoud be 'N')

select job,schema_user,broken,interval,failures,what,instance
from dba_jobs
/

prompt 
prompt --------------- failure jobs ---------------- (The ideal value for the Failures shoud be 0)

select job,failures,what from dba_jobs
where upper(what) like '%DBMS_DEFER_SYS%'
/

prompt 
prompt --------------- push jobs -------------------- (The next_date should not be less then the sysdate) 

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 ------------ (The ideal output of this query would be no rows )

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;