{"id":204,"date":"2013-08-29T21:47:01","date_gmt":"2013-08-30T02:47:01","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=204"},"modified":"2026-02-07T15:01:09","modified_gmt":"2026-02-07T21:01:09","slug":"streams-answer","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2013\/204\/","title":{"rendered":"Streams Answer&#8230;"},"content":{"rendered":"<p>I posted the question few days back on a streams issue I have been working on&#8230;<\/p>\n<p><strong><a href=\"https:\/\/bijoos.com\/oraclenotes\/oraclenotes\/2013\/199\">https:\/\/bijoos.com\/oraclenotes\/oraclenotes\/2013\/199<\/a><\/strong><\/p>\n<p>It looks like there was a open or long transaction, and before that transaction was committed or rollback, the archivelog file got deleted by the RMAN backup and cleanup job&#8230; \u00a0or we just hit a bug (patch <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/ui\/patch\/PatchDetail.jspx?patchId=13853654\" target=\"_blank\">13853654<\/a>)&#8230;\u00a0so, here is what I learned&#8230;<\/p>\n<p>Let me know if anything should be different.<\/p>\n<h1>Reference Documents<\/h1>\n<ul>\n<li>Streams Recommended Patches (<a href=\"https:\/\/support.oracle.com\/CSP\/main\/article?cmd=show&amp;type=NOT&amp;id=437838.1\">Doc ID 437838.1<\/a>)<\/li>\n<li>Streams Configuration Report and Health Check Script (<a href=\"https:\/\/support.oracle.com\/CSP\/main\/article?cmd=show&amp;type=NOT&amp;id=273674.1\">Doc ID 273674.1<\/a>)<\/li>\n<li>Master Note for Streams Recommended Configuration (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/ui\/km\/SearchDocDisplay.jspx?_afrLoop=162535929566991&amp;recommended=true&amp;type=DOCUMENT&amp;id=418755.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=sdkfwsx86_70\">Doc ID 418755.1<\/a>)<\/li>\n<li>Troubleshooting Long-Running Transactions in Oracle Streams (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=168062269525119&amp;id=783927.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mf8joeuzb_17\">Doc ID 783927.1<\/a>)<\/li>\n<li>Master Note for AQ Queue Monitor Process (QMON) (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=305662.1\">Doc ID 305662.1<\/a>)<\/li>\n<li>Streams Complete Reference FAQ (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/ui\/km\/SearchDocDisplay.jspx?returnToSrId=&amp;srnum=&amp;type=DOCUMENT&amp;id=335516.1&amp;displayIndex=2&amp;_adf.ctrl-state=mf8joeuzb_193&amp;_afrLoop=168930510291378\">Doc ID 752871.1<\/a>)<\/li>\n<li>Different States of Capture &amp; Apply Process (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=169202418184635&amp;id=471713.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mf8joeuzb_222\">Doc ID 471713.1<\/a>)<\/li>\n<li>Minimum Archived Log Necessary to Restart 10g and 11g Streams Capture Process (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=169379596419366&amp;id=290143.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mf8joeuzb_248\">Doc ID 290143.1<\/a>)<\/li>\n<\/ul>\n<h1>Recommended Changes<\/h1>\n<p>Following may be considered at a minimum for better performance and maintenance. Other best practice recommendations can be considered from the above documents.<\/p>\n<ul>\n<li>Apply database patches based on <a href=\"https:\/\/support.oracle.com\/CSP\/main\/article?cmd=show&amp;type=NOT&amp;id=437838.1\">Doc ID 437838.1<\/a>: Analysis need to be done which ones are applicable other than the first one. The first one needs to be applied to avoid the issue we saw on our database.\n<ul>\n<li><a href=\"https:\/\/support.oracle.com\/epmos\/faces\/ui\/patch\/PatchDetail.jspx?patchId=13853654\" target=\"_blank\">Patch 13853654<\/a>\u00a0 Required checkpoint scn not moving forward<\/li>\n<li>Patch 13878246\u00a0 Streams rule AND_CONDITION=:dml.get_command_type()=&#8221;DELETE&#8221; doesn&#8217;t work<\/li>\n<li>Patch 14055108\u00a0 Apply parallel processing impacted by constraints<\/li>\n<li><span style=\"text-decoration: line-through;\">o\u00a0\u00a0 <\/span><span style=\"text-decoration: line-through;\">Patch 14138823\u00a0 Propagation receiver slow between 10.2 and 11.2 databases<\/span><\/li>\n<li>If you plan to use MAINTAIN_CHANGE_TABLE or statement handlers, they should apply the following two additional patches:\n<ul>\n<li>Patch 13882311\u00a0 MAINTAIN_CHANGE_TABLE failing with ORA_29285, ora-6502<\/li>\n<li>Patch 13932732\u00a0 Apply ORA-1 insert into source table if stmt handler name is 30 char<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Since we will never be able to pull in 60 days\u2019 worth of archive logs and restart capture process, the default value of 60 days retention must be changed to a realistic value. Based on the thresholds and BI expectations of seeing the data on the other side, the value must be set to less than 7 days. Set <strong>exec dbms_capture_adm.alter_capture(&#8216;CDC$C_RD_EBS&#8217;, checkpoint_retention_time =&gt; 2);<\/strong><\/li>\n<\/ul>\n<ul>\n<li>AQ_TM_PROCESS database parameter must be set to auto-tune itself (if not already). Do <strong>alter system reset aq_tm_processes scope=spfile sid=&#8217;*&#8217;;<\/strong><\/li>\n<\/ul>\n<h1>DB Monitoring<\/h1>\n<p>The following additional DB monitoring may need to be setup from the DBA standpoint to make sure CDC\/Streams working properly.<\/p>\n<ul>\n<li>Archived log files generated after REQUIRED_CHECKPOINT_SCN are on disk, under their original location.<\/li>\n<li>Alert Action: DBA Restore the archive logs required; force a checkpoint; if issue not cleared stop and start capture process.<\/li>\n<li>REQUIRED_CHECKPOINT_SCN time is not more than 6 hours behind current time.<\/li>\n<li>Alert Action: Force a checkpoint; if issue not cleared stop and start capture process.<\/li>\n<li>Look for STATUS in DBA_CAPTURE. If not ENABLED, raise an alert.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I posted the question few days back on a streams issue I have been working on&#8230; https:\/\/bijoos.com\/oraclenotes\/oraclenotes\/2013\/199 It looks like there was a open or long transaction, and before that transaction was committed or rollback, the archivelog file got deleted by the RMAN backup and cleanup job&#8230; \u00a0or we just hit a bug (patch 13853654)&#8230;\u00a0so, &#8230; <a title=\"Streams Answer&#8230;\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2013\/204\/\" aria-label=\"Read more about Streams Answer&#8230;\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[68],"class_list":["post-204","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-streams"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=204"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/204\/revisions"}],"predecessor-version":[{"id":2956,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/204\/revisions\/2956"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}