Yes, a question indeed, looking for help through the blog! I recently got into a streams issue, have not supported streams in a live environment until recently (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 the innocent, I have changed the database, host names…
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.
Wed Aug 21 01:32:59 2013 LOGMINER: End mining logfile for session 4 thread 1 sequence 6667, +DEV2/dev2/onlinelog/group_7.538.810543533 LOGMINER: Begin mining logfile for session 4 thread 1 sequence 6668, +DEV2/dev2/onlinelog/group_8.540.810543545 Wed Aug 21 01:33:14 2013
After initiating the normal database shutdown, I can also see messages of a clean streams shutdown as well…
Wed Aug 21 02:34:46 2013 LOGMINER: End mining logfile for session 4 thread 1 sequence 6670, +DEV2/dev2/onlinelog/group_10.544.81 0543567 LOGMINER: Begin mining logfile for session 4 thread 1 sequence 6671, +DEV2/dev2/onlinelog/group_11.546.81 0543579 Wed Aug 21 02:34:54 2013 Archived Log entry 6690 added for thread 1 sequence 6670 ID 0xffffffffacaf64cf dest 1: Wed Aug 21 02:39:48 2013 Completed checkpoint up to RBA [0x1a0f.2.10], SCN: 8657881886596 Wed Aug 21 02:42:45 2013 Shutting down instance (immediate) Stopping background process SMCO Shutting down instance: further logons disabled Stopping background process QMNC Wed Aug 21 02:42:52 2013 Stopping background process CJQ0 Wed Aug 21 02:42:52 2013 Streams Apply Server AS02 for CDC$A_RD_EBS with pid=397 OS id=26667 stopped Wed Aug 21 02:42:52 2013 Streams Apply Reader AS01 for CDC$A_RD_EBS with pid=396 OS id=26665 stopped Wed Aug 21 02:42:52 2013 Streams APPLY AP01 for CDC$A_RD_EBS with pid=329, OS id=26661 stopped Wed Aug 21 02:42:53 2013 LOGMINER: session#=4 (CDC$C_RD_EBS), preparer MS02 pid=460 OS id=28280 sid=1707 stopped Wed Aug 21 02:42:53 2013 LOGMINER: session#=4 (CDC$C_RD_EBS), builder MS01 pid=459 OS id=28278 sid=1336 stopped Stopping background process MMNL Wed Aug 21 02:42:53 2013 LOGMINER: session#=4 (CDC$C_RD_EBS), reader MS00 pid=406 OS id=28276 sid=1693 stopped Wed Aug 21 02:42:53 2013 Streams CAPTURE CP01 for CDC$C_RD_EBS with pid=404, OS id=26669 stopped Stopping background process MMON
And during the database startup, I can see streams coming up as well…
Wed Aug 21 03:03:24 2013 Streams CAPTURE CP01 for CDC$C_RD_EBS with pid=33, OS id=25421 is in combined capture and apply mode. Capture CDC$C_RD_EBS is handling 1 applies. Starting persistent Logminer Session with sid = 4 for Streams Capture CDC$C_RD_EBS LOGMINER: Parameters summary for session# = 4 LOGMINER: Number of processes = 3, Transaction Chunk Size = 1 LOGMINER: Memory Size = 30M, Checkpoint interval = 1000M LOGMINER: SpillScn 0, ResetLogScn 8637982978142 LOGMINER: summary for session# = 4 LOGMINER: StartScn: 8657881991134 (0x07df.d1fb07de) LOGMINER: EndScn: 0 LOGMINER: HighConsumedScn: 8657881991134 (0x07df.d1fb07de) LOGMINER: session_flag: 0x0 LOGMINER: Read buffers: 16 LOGMINER: LowCkptScn: 8653693507993 (0x07de.d853d999) LOGMINER: HighCkptScn: 8653694790356 (0x07de.d8676ad4) LOGMINER: SkipScn: 8653693507993 (0x07de.d853d999) Wed Aug 21 03:05:27 2013
But 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
ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE '+ORAARCH/dev2/archivelog/2013_08_22/thread_1_seq_6728.1179.824120475' FOR 'CDC$C_RD_EBS'
They showed up in dba_registered_archived_log properly. Stopped and started the capture, but nothing happening. Still the message is:
SQL> select capture_name, state from gv$streams_capture; CAPTURE_NAME ------------------------------ STATE -------------------------------------------------------------------------------- CDC$C_RD_EBS WAITING FOR DICTIONARY REDO: FIRST SCN 8653644859909
Increased memory from 30M to 50M, no change. Traced (based on metalink note 313279.1), and found in the trace file:
*** 2013-08-22 17:46:11.522 krvxptl: Priming thread list with scn: 0x07de.d853d999 krvxptl: 6758 archived record(s) searched, 12 online record(s) searched. krvxpsr: Missing logfile - No logfiles registered within given SCN range
And the log file it is looking for is from July 31st!
++ Minimum Archive Log Necessary to Restart Capture ++ Note: This query is valid for databases where the capture processes exist for the same source database. Capture will restart from SCN 8653675791039 in the following file: +ORAARCH/dev2/archivelog/2013_07_31/thread_1_seq_5284.926.822208349 (31-JUL-13)
And these are the SCN numbers found in DBA_CAPTURE (date when they are generated in brackets for clarity).
FIRST_SCN – 8653644859909 (July 30)
APPLIED_SCN – 8657881991134 (Aug 21)
CAPTURED_SCN – 8657881991134 (Aug 21)
REQUIRED_CHECKPOINT_SCN (July 31)
STATUS_CHANGE_TIME is Aug 21
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.
Changed the retention time to 2 days using:
exec dbms_capture_adm.alter_capture(‘CDC$C_RD_EBS’, checkpoint_retention_time => 2);
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.
So, there is something I do not understand is happening here in streams.
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?
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.
Thanks to you in advance for all help!