{"id":1076,"date":"2009-01-28T09:40:10","date_gmt":"2009-01-28T13:40:10","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/"},"modified":"2009-01-28T10:15:13","modified_gmt":"2009-01-28T14:15:13","slug":"how-to-check-oracle-replication-status-health-check","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/","title":{"rendered":"How to check Oracle Replication status \/ health check"},"content":{"rendered":"

Introduction:<\/strong>\u00a0In the first post Here<\/a>, 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.<\/p>\n

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.<\/p>\n

In the next article we will see, how to make things right, if there is some problem with the Replication.<\/p>\n

Replication health check script:<\/strong><\/p>\n

set feed on
\nset echo off
\nset trimspoo on
\nset verify off
\nset lines 1000
\ncolumn dblink format a20<\/p>\n

prompt
\nprompt ————- job queue related parameters ———-<\/p>\n

show parameter job<\/p>\n

prompt
\nprompt ————– database name ————————<\/p>\n

select name
\nfrom v$database
\n\/
\nprompt
\nprompt ————– Master Definition Site (master defintion, master) ————–<\/p>\n

select gname,dblink,masterdef,master,group_owner
\nfrom dba_repsites
\n\/
\nprompt
\nprompt ————– defcall results —————-<\/p>\n

select *
\nfrom defcall
\n\/<\/p>\n

prompt
\nprompt ————– deftran ——————–
\nselect *
\nfrom deftran<\/p>\n

\/<\/p>\n

prompt
\nprompt ————– deftrandest ——————–
\nselect *
\nfrom deftrandest<\/p>\n

\/<\/p>\n

prompt
\nprompt ————– The number of Deferred Transaction w.r.t Destination —–<\/p>\n

SELECT DISTINCT(dblink), COUNT(deferred_tran_id)
\nFROM deftrandest GROUP BY dblink<\/p>\n

\/<\/p>\n

prompt
\nprompt ————– No. of transactions associated with replicated transaction —-<\/p>\n

select deferred_tran_id, count(*) from defcall where deferred_tran_id in (
\nselect deferred_tran_id from (
\nselect deferred_tran_id, to_char(start_time,’HH:MI:SS’) from deftran where sysdate – start_time < 1
\n))group by deferred_tran_id<\/p>\n

\/<\/p>\n

prompt
\nprompt ————— repgroup status —————<\/p>\n

select sname,master,status,gname,owner
\nfrom dba_repgroup
\n\/<\/p>\n

prompt
\nprompt ————— repobject status —————<\/p>\n

select sname,oname,type,status,gname,group_owner
\nfrom dba_repobject
\n\/<\/p>\n

prompt
\nprompt ————— repcatlog ———————<\/p>\n

select gname,request,status,errnum
\nfrom dba_repcatlog
\norder by id,gname
\n\/<\/p>\n

prompt
\nprompt ————— db jobs ———————–<\/p>\n

select job,schema_user,broken,interval,failures,what,instance
\nfrom dba_jobs
\n\/<\/p>\n

prompt
\nprompt —————\u00a0 failure jobs —————-<\/p>\n

select job,failures,what from dba_jobs
\nwhere upper(what) like ‘%DBMS_DEFER_SYS%’
\n\/<\/p>\n

prompt
\nprompt ————— push jobs ——————–<\/p>\n

select job push_job,dblink,
\nsubstr(to_char(last_date,’mm\/dd\/yy hh24:mi:ss’),1,20) last_date,
\nsubstr(to_char(next_date,’mm\/dd\/yy hh24:mi:ss’),1,20) next_date,interval
\nfrom defschedule
\nwhere job in (select job from dba_jobs
\n\u00a0\u00a0where upper(what) like ‘%DBMS_DEFER_SYS.PUSH%’)
\n\/<\/p>\n

prompt
\nprompt ————– purge jobs ——————–<\/p>\n

select job purge_job,dblink,
\nsubstr(to_char(last_date,’mm\/dd\/yy hh24:mi:ss’),1,20) last_date,
\nsubstr(to_char(next_date,’mm\/dd\/yy hh24:mi:ss’),1,20) next_date,interval
\nfrom defschedule
\nwhere job in (select job from dba_jobs
\n\u00a0\u00a0where upper(what) like ‘%DBMS_DEFER_SYS.PURGE%’)
\n\/<\/p>\n

prompt
\nprompt ————- broken jobs ——————-<\/p>\n

select job,broken,failures
\nfrom dba_jobs
\nwhere upper(what) like ‘%DBMS_DEFER_SYS%’
\n\/<\/p>\n

prompt
\nprompt ————- defpropagator —————-<\/p>\n

select *
\nfrom defpropagator
\n\/<\/p>\n

prompt
\nprompt ————- propagator links —————<\/p>\n

select db_link
\nfrom dba_db_links
\nwhere owner = (select username from defpropagator)
\n\/<\/p>\n

prompt
\nprompt ————- pushed sites by propagator ——<\/p>\n

select job,dblink pushed_site_by_propagator
\nfrom defschedule
\nwhere job in (select job from dba_jobs
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where log_user in (select username from defpropagator)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and upper(what) like ‘%DBMS_DEFER_SYS.PUSH%’)
\n\/<\/p>\n

prompt
\nprompt ———— current running jobs ————<\/p>\n

select \/*+ ORDERED *\/ j.job, j.sid, d.dblink,
\nsubstr(to_char(j.this_date,’mm\/dd\/rrrr hh24:mi:ss’),1,20) start_date
\nfrom defschedule d, dba_jobs_running j
\nwhere j.job in (select job from dba_jobs
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where upper(what) like ‘%DBMS_DEFER_SYS.PUSH%’)
\nand j.job = d.job<\/p>\n

\/<\/p>\n

set feed on
\nset echo on
\nset verify on
\nspool off;<\/p>\n","protected":false},"excerpt":{"rendered":"

Introduction:\u00a0In the first post Here, we have seen all the procedure to make the Oracle 9i Replication between 2 sites. […]<\/p>\n","protected":false},"author":115,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[424],"tags":[],"class_list":["post-1076","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nHow to check Oracle Replication status \/ health check -<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to check Oracle Replication status \/ health check -\" \/>\n<meta property=\"og:description\" content=\"Introduction:\u00a0In the first post Here, we have seen all the procedure to make the Oracle 9i Replication between 2 sites. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/\" \/>\n<meta property=\"article:published_time\" content=\"2009-01-28T13:40:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2009-01-28T14:15:13+00:00\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/\",\"name\":\"How to check Oracle Replication status \/ health check -\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"datePublished\":\"2009-01-28T13:40:10+00:00\",\"dateModified\":\"2009-01-28T14:15:13+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to check Oracle Replication status \/ health check\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation & Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to check Oracle Replication status \/ health check -","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/","og_locale":"en_US","og_type":"article","og_title":"How to check Oracle Replication status \/ health check -","og_description":"Introduction:\u00a0In the first post Here, we have seen all the procedure to make the Oracle 9i Replication between 2 sites. […]","og_url":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/","article_published_time":"2009-01-28T13:40:10+00:00","article_modified_time":"2009-01-28T14:15:13+00:00","author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/","url":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/","name":"How to check Oracle Replication status \/ health check -","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"datePublished":"2009-01-28T13:40:10+00:00","dateModified":"2009-01-28T14:15:13+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/28\/how-to-check-oracle-replication-status-health-check\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"How to check Oracle Replication status \/ health check"}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation & Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/1076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=1076"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/1076\/revisions"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=1076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=1076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=1076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}