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.
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.
