Yes, a question indeed, looking for help through the blog! \u00a0I recently got into a streams issue, have not supported streams in a live environment \u00a0until recently\u00a0(when you work on your virtual desktop streams and golden gate and everything works perfect, isn’t!!)…<\/p>\n
Ok, here is the question… need to give you some background… To protect the innocent, I have changed the database, host names…<\/p>\n
Apparently Streams was set up on the database for SAP Data Services BI application on this database, and was working fine until Aug 21, until the database cycled. This is visible in the alert log, and can see messages such as this until just seconds before the database shutdown. BI also confirmed they were receiving changes from Oracle fine.<\/p>\n
<\/p>\n
Wed Aug 21 01:32:59 2013\nLOGMINER: End mining logfile for session 4 thread 1 sequence 6667, +DEV2\/dev2\/onlinelog\/group_7.538.810543533\nLOGMINER: Begin mining logfile for session 4 thread 1 sequence 6668, +DEV2\/dev2\/onlinelog\/group_8.540.810543545\nWed Aug 21 01:33:14 2013<\/pre>\nAfter initiating the normal database shutdown, I can also see messages of a clean streams shutdown as well…<\/p>\n
<\/p>\n
Wed Aug 21 02:34:46 2013\nLOGMINER: End mining logfile for session 4 thread 1 sequence 6670, +DEV2\/dev2\/onlinelog\/group_10.544.81\n0543567\nLOGMINER: Begin mining logfile for session 4 thread 1 sequence 6671, +DEV2\/dev2\/onlinelog\/group_11.546.81\n0543579\nWed Aug 21 02:34:54 2013\nArchived Log entry 6690 added for thread 1 sequence 6670 ID 0xffffffffacaf64cf dest 1:\nWed Aug 21 02:39:48 2013\nCompleted checkpoint up to RBA [0x1a0f.2.10], SCN: 8657881886596\nWed Aug 21 02:42:45 2013\nShutting down instance (immediate)\nStopping background process SMCO\nShutting down instance: further logons disabled\nStopping background process QMNC\nWed Aug 21 02:42:52 2013\nStopping background process CJQ0\nWed Aug 21 02:42:52 2013\nStreams Apply Server AS02 for CDC$A_RD_EBS with pid=397 OS id=26667 stopped\nWed Aug 21 02:42:52 2013\nStreams Apply Reader AS01 for CDC$A_RD_EBS with pid=396 OS id=26665 stopped\nWed Aug 21 02:42:52 2013\nStreams APPLY AP01 for CDC$A_RD_EBS with pid=329, OS id=26661 stopped\nWed Aug 21 02:42:53 2013\nLOGMINER: session#=4 (CDC$C_RD_EBS), preparer MS02 pid=460 OS id=28280 sid=1707 stopped\nWed Aug 21 02:42:53 2013\nLOGMINER: session#=4 (CDC$C_RD_EBS), builder MS01 pid=459 OS id=28278 sid=1336 stopped\nStopping background process MMNL\nWed Aug 21 02:42:53 2013\nLOGMINER: session#=4 (CDC$C_RD_EBS), reader MS00 pid=406 OS id=28276 sid=1693 stopped\nWed Aug 21 02:42:53 2013\nStreams CAPTURE CP01 for CDC$C_RD_EBS with pid=404, OS id=26669 stopped\nStopping background process MMON<\/pre>\nAnd during the database startup, I can see streams coming up as well…<\/p>\n
Wed Aug 21 03:03:24 2013\nStreams CAPTURE CP01 for CDC$C_RD_EBS with pid=33, OS id=25421 is in combined capture and apply mode.\nCapture CDC$C_RD_EBS is handling 1 applies.\nStarting persistent Logminer Session with sid = 4 for Streams Capture CDC$C_RD_EBS\nLOGMINER: Parameters summary for session# = 4\nLOGMINER: Number of processes = 3, Transaction Chunk Size = 1\nLOGMINER: Memory Size = 30M, Checkpoint interval = 1000M\nLOGMINER: SpillScn 0, ResetLogScn 8637982978142\nLOGMINER: summary for session# = 4\nLOGMINER: StartScn: 8657881991134 (0x07df.d1fb07de)\nLOGMINER: EndScn: 0\nLOGMINER: HighConsumedScn: 8657881991134 (0x07df.d1fb07de)\nLOGMINER: session_flag: 0x0\nLOGMINER: Read buffers: 16\nLOGMINER: LowCkptScn: 8653693507993 (0x07de.d853d999)\nLOGMINER: HighCkptScn: 8653694790356 (0x07de.d8676ad4)\nLOGMINER: SkipScn: 8653693507993 (0x07de.d853d999)\nWed Aug 21 03:05:27 2013<\/pre>\nBut no capture happening. Ran streams healthcheck, and it showed not having the archived log files registered with logminer, so manually registered the log files using command<\/p>\n
ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE '+ORAARCH\/dev2\/archivelog\/2013_08_22\/thread_1_seq_6728.1179.824120475' FOR 'CDC$C_RD_EBS'<\/pre>\nThey showed up in dba_registered_archived_log properly. Stopped and started the capture, but nothing happening. Still the message is:<\/p>\n
SQL> select capture_name, state from gv$streams_capture;\n\nCAPTURE_NAME\n------------------------------\nSTATE\n--------------------------------------------------------------------------------\nCDC$C_RD_EBS\nWAITING FOR DICTIONARY REDO: FIRST SCN 8653644859909<\/pre>\nIncreased memory from 30M to 50M, no change. Traced (based on metalink note\u00a0313279.1), and found in the trace file:<\/p>\n
*** 2013-08-22 17:46:11.522\nkrvxptl: Priming thread list with scn: 0x07de.d853d999\nkrvxptl: 6758 archived record(s) searched, 12 online record(s) searched.\nkrvxpsr: Missing logfile - No logfiles registered within given SCN range<\/pre>\nAnd the log file it is looking for is from July 31st!<\/p>\n
++ Minimum Archive Log Necessary to Restart Capture ++\nNote: This query is valid for databases where the capture processes exist for the same source database.\n\nCapture will restart from SCN 8653675791039 in the following file:\n+ORAARCH\/dev2\/archivelog\/2013_07_31\/thread_1_seq_5284.926.822208349\n(31-JUL-13)<\/pre>\nAnd these are the SCN numbers found in DBA_CAPTURE (date when they are generated in brackets for clarity).<\/p>\n
FIRST_SCN –\u00a08653644859909 (July 30)<\/p>\n
APPLIED_SCN –\u00a08657881991134 (Aug 21)<\/p>\n
CAPTURED_SCN –\u00a08657881991134 (Aug 21)<\/p>\n
REQUIRED_CHECKPOINT_SCN (July 31)<\/p>\n
STATUS_CHANGE_TIME is Aug 21<\/p>\n
Found that the streams parameter for capture, CHECKPOINT_RETENTION_TIME was the default 60… so I can understand why FIRST_SCN did not move from where it was.<\/p>\n
Changed the retention time to 2 days using:<\/p>\n
exec dbms_capture_adm.alter_capture(‘CDC$C_RD_EBS’, checkpoint_retention_time => 2);<\/p>\n
Stopped and started CDC capture several times, no change in status, still waiting for dictionary redo with required SCN, l an SCN generated on July 31.<\/p>\n
So, there is something I do not understand is happening here in streams.<\/p>\n
Since the captured_scn and applied_scn are both from Aug 21, why is required_checkpoint_scn from July 31? What changed during the database cycle? Is there any manual checkpoint advance we need to do before database shutdown?<\/strong><\/p>\n
Also, RMAN has not deleted any file from archivelog location yet (after backup) since Aug 21 as it is thinking that the CDC process requires them. We will have to force delete the archivelogs soon, as we might run out of room soon.<\/p>\n
Thanks to you in advance for all help!<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Yes, a question indeed, looking for help through the blog! \u00a0I recently got into a streams issue, have not supported streams in a live environment \u00a0until recently\u00a0(when you work on your virtual desktop streams and golden gate and everything works perfect, isn’t!!)… Ok, here is the question… need to give you some background… To protect … 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":[34,68],"class_list":["post-199","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-change-data-capture","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\/199","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=199"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/199\/revisions"}],"predecessor-version":[{"id":2957,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/199\/revisions\/2957"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}