Identify Issues and Fix Streams Capture Process

My Capture, Propagate and apply processes were working fine until the server fial over. After a restart even though capture processes are enabled, they are stalled at status “INITIALIZING”. The Propagate and Apply were working fine.

The alrtlog didn’t show any streams error message except process details when Capture processes were enabled.

Streams CAPTURE C004 started with pid=74, OS id=23523
Thu OCt 21 13:07:18 2010
Streams CAPTURE C019 started with pid=84, OS id=27340
Thu OCt 21 13:27:37 2010
Streams CAPTURE C016 started with pid=81, OS id=5580
Thu OCt 21 13:27:50 2010
Streams CAPTURE C014 started with pid=56, OS id=5653
Thu OCt 21 13:27:59 2010
Streams CAPTURE C013 started with pid=68, OS id=5759
Thu OCt 21 13:28:11 2010
Streams CAPTURE C011 started with pid=86, OS id=5881
Thu OCt 21 13:28:35 2010
Streams CAPTURE C007 started with pid=87, OS id=6068
Thu OCt 21 13:28:48 2010
Streams CAPTURE C003 started with pid=88, OS id=6149
Thu OCt 21 13:41:41 2010

Find the Captured SCNs number and then locate the log files needed

select min(start_scn), min(applied_scn)     from dba_capture;

Find the Archivelog names by using the SCN

SELECT   name,
thread#,
sequence#,
status,
first_time,
next_time,
first_change#,
next_change#
FROM   v$archived_log
WHERE   111694632468 BETWEEN first_change# AND next_change#;

If the status column from above Query returned D then please restore the archivelog.

The following query can be used to determine the oldest archive log that will need to be read, during the next restart of a capture process.

SELECT   a.sequence#, b.name
FROM   v$log_history a, v$archived_log b
WHERE       a.first_change# <= (SELECT   required_checkpoint_scn
FROM   dba_capture
WHERE   capture_name = ‘SCOTT_C’)
AND a.next_change# > (SELECT   required_checkpoint_scn
FROM   dba_capture
WHERE   capture_name = ‘SCOTT_C’)
AND a.sequence# = b.sequence#(+);

If needed restore archivelog files using

$RMAN target / ${CATALOG} log=$LOG_FILE append << EOF
RUN {
### Backup Archived Logs
allocate channel t1 type disk
set archivelog destination to ‘/mnt/logs/oradata/TEST/arch’;
# restore archivelog from logseq=9970 until logseq=9984 thread=1;    — Use if you know the SEQ number needed (select * from V$ARCHIVED_LOG )
restore ARCHIVELOG FROM TIME ‘SYSDATE-3’ UNTIL TIME ‘SYSDATE-1’;  — Use if you want to restore daywise
release channel t1;
}
}
exit;
EOF

After log restore, I  stopped/started capture processes and it all worked fine.