Note: Always where possible use the CDC API’s (dbms_cdc_publish) and not the streams API’s to stop/start/trouble shoot Oracle CDC (Change Data Capture)
Stop & Start CDC from stage database:
sqlplus cdc_stg_pub/cdc_stg_pub
begin
dbms_cdc_publish.alter_change_set(
change_set_name => ‘WFWH_PRD_TO_PRD_SET1′,
enable_capture => ‘Y’) ;
end;
begin
dbms_cdc_publish.alter_hotlog_change_source(
change_source_name => ‘WFWH_PRD_TO_PRD_SRC1’,
enable_source => ‘Y’) ;
end;
Switch archive logs on source database to start propagation.
Check Apply:
Run this from source and stage:
select * from dba_apply_error;
Increase Apply Parallelism:
DBMS_APPLY_ADM.Set_parameter(‘applyName’,’parallelism’,’4’);
Check source for archive log needed to restart CDC / Streams:
set serveroutput on
DECLARE lScn number := 0;
alog varchar2(1000);
begin select min(required_checkpoint_scn)into lScn from dba_capture ; DBMS_OUTPUT.ENABLE(2000);
dbms_output.put_line(’Capture will restart from SCN ‘ || lScn ||’ in the following file:’);
for cr in (select name, first_time from DBA_REGISTERED_ARCHIVED_LOG where lScn between first_scn and next_scn order by thread#) loop dbms_output.put_line(cr.name||’ (’||cr.first_time||’)');
end loop;
end;
Stop / Start propagation (source):
exec DBMS_PROPAGATION_ADM.stop_propagation(’CDC$P_WFWH_PRD_UAT_SET1′);
exec DBMS_PROPAGATION_ADM.start_propagation(’CDC$P_WFWH_PRD_TO_PRD_SET1′);
Stop / Start Capture:
EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => ‘CDC$C_WFWH_PRD_TO_PRD_SRC1′);
EXEC DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => ‘CDC$C_WFWH_PRD_TO_PRD_SRC1′);
How to Enable Capture tracing on SOURCE site:
1. Stop the capture
2. alter system set events ‘26700 trace name context forever, level 6′;
exec dbms_capture_adm.set_parameter(’yourcapturename’,'trace_level’,'127′);
Start capture
— set trace off after 30 minutes:
3. To turn off capture tracing:
exec dbms_capture_adm.set_parameter(’yourcapturename’,'trace_level’,null);
alter system set events ‘26700 trace name context off’;
Restart capture
How to Enable Propagation tracing on SOURCE site:
1. Stop/Disable Propagation
2. alter system set job_queue_processes=0;
alter system set events ‘ 24040 trace name context forever,level 10′;
alter system set job_queue_processes=10;
3. Start propagation
4. To disable the Propagation tracing
alter system set events ‘ 24040 trace name context off’;
Restart propagation
Run a healthcheck on both source and target:
Metalink: Note.273674.1 Streams Configuration Report and Health Check Script
SELECT * FROM v$bgprocess WHERE PADDR <> '00';