Troubleshooting Oracle Streams / CDC

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

Leave a Reply

You must be logged in to post a comment.


Adipex Buy Acyclovir Nexium Bontril Buy Darvocet Levitra Buy Lorazepam Zyban Buy Adderall Buy Flexeril Buy Viagra Buy Vicodin Buy Zyprexa Buy Viagra Carisoprodol Bontril Buy Acyclovir Buy Cialis Carisoprodol Buy Diflucan Buy Cialis Online Biaxin Codeine Glucophage Nexium Online Xanax Buy Soma Xanax On Line Buy Ativan Buy Zyban Buy Nexium Biaxin Buy Paxil Buy Celexa Buy Valium Buy Bupropion Buy Percocet Buy Norvasc Buy Zyprexa Buy Fioricet Buy Cheap Phentermine Buy Ephedrine Buy Phentermine Online Buy Ativan Buy Carisoprodol Buy Adipex Meridia Soma Buy Hydrocodone Online Ambien Buy Diazepam Zocor Tenuate Buy Tramadol Clonazepam Buy Butalbital Buy Lortab Buy Vicodin Online Buy Adipex Buy Flexeril Buy Diflucan Zyprexa Buy Codeine Phentermine Online Generic Viagra Clonazepam Buy Darvocet Viagra Online Buy Tramadol Buy Bupropion Prozac Buy Xanax On Line Buy Online Xanax Buy Cialis Online Buy Celexa Darvocet Phentermine Buy Seroquel Vicodin Buy Tramadol Online Buy Clonazepam