Streams/CDC Question? Help…

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!