Streams Capture CCA Mode

Capture CCA Mode



Summary

In Oracle 11g a new Streams replication feature has been added. The new feature is for the streams capture process to be able to run in combined capture & apply. When capture is running in this mode it directly captures the LCRS and propagates them to the remote apply process. Unfortunately there can be issues with CCA mode and streams will effectively hang and stop propagating changes.

This document details the symptoms of the issues and the known workarounds to resolve.

The current capture mode can be determined with

SQL> select CAPTURE_NAME, decode(OPTIMIZATION,0,'Non-CCA','CCA') from gv$streams_capture
  2  /
CAPTURE_TO_NYDB  CCA
CAPTURE_TO_LNDTB CCA


Symptoms/Observations

During a maintenance or release activity the capture process or database is restarted. The symptoms of the issue are :

1. first_scn is two to three days in the past whereas the required_checkpoint_scn is up to date.

SQL> set lines 132
SQL> column fcn format a35
SQL> column rcn format a35
SQL> column capture_name format a20
SQL> column first_scn format 99999999999999
SQL> column required_checkpoint_scn format 99999999999999

SQL> select capture_name, first_scn, scn_to_timestamp(first_scn) fcn,required_checkpoint_scn,scn_to_timestamp
(required_checkpoint_scn) rcn from dba_capture

CAPTURE_NAME               FIRST_SCN FCN                                 REQUIRED_CHECKPOINT_SCN RCN
-------------------- --------------- ----------------------------------- ----------------------- ----------------------------------
CAPTURE_TO_QNYDB    7420845030624 05-DEC-14 11.00.59.000000000 AM               
423420410445 08-DEC-14 01.43.57.000000000 PM
CAPTURE_TO_QSGDB    7420840301551 05-DEC-14 10.01.04.000000000 AM               7423420469291 08-DEC-14 01.51.35.000000000 PM

2. The capture state is showing as WAITING FOR INACTIVE DEQUEUERS

SQL> select capture_name, rtrim(state) from gv$streams_capture

 
Workarounds

The first step is to update the first_scn = required_checkpoint_scn

SQL> select capture_name, first_scn, required_checkpoint_scn from dba_capture ;

CAPTURE_NAME               FIRST_SCN REQUIRED_CHECKPOINT_SCN
-------------------- --------------- -----------------------
CAPTURE_TO_QNYDB    7420845030624           7423420410445
CAPTURE_TO_QSGDB   7420840301551           7423423914847

Login as strmadmin user

Take a note of the required_checkpoint_scn for the capture that is not working. Then

begin
dbms_capture_adm.alter_capture(capture_name=>’[capture]’, 
                                                  first_scn=> [required_checkpoint_scn]);
end;
/

exec dbms_capture_adm.stop_capture('[capture_name]')
exec dbms_capture_adm.start_capture('[capture_name]')

At this point try to restart capture and confirm it’s not stuck waiting for inactive dequers. If the issue is still persisting then a capture checkpoint should be forced.

execute dbms_capture_adm.set_parameter(‘[capture]’, '_CHECKPOINT_FORCE', 'Y'); 
stop/start capture


It has been observed that occasionally the above fails to remediate and capture is still stuck. In this case we should disable CCA mode for the capture that is not working. This is done by setting a capture parameter.

exec dbms_capture_adm.set_parameter('[capture_name]', '_DIRECT_APPLY', 'N');
stop/start only the effected capture

exec dbms_capture_adm.stop_capture('[capture_name]')
exec dbms_capture_adm.start_capture('[capture_name]')


Capture should then proceed to capture messages however this does not resolve our initial problem…

I believe the nature of the CCA bug is that once capture has minded past the time when the capture was restarted and has checkpointed so it does not need to re-read from a previous SCN then it will work. So if the checkpoint force and setting first_scn does not work then leave the capture running in non-CCA mode for many hours and repeat the above work around steps.