Required Steps to Recreate a Capture Process. Doc ID 471695.1
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 184.108.40.206 [Release 10.1 to 11.2] Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2] Information in this document applies to any platform.
This note aims to outline the necessary steps to recreate a Capture process.
This note can be used to move forward past a missing logfile.
This should not be used to re-create the CDC capture process.
A new Capture process can start from an existing Streams Dictionary build or from a new build. If no explicit build is performed, creating a Capture process will perform this operation implicitly. In order to see the Steams Dictionary builds which exists, issue:
column first_change# heading 'First_SCN' format 9999999999999 column next_change format 9999999999999 column name heading 'Log File Name' format A50 select distinct first_change#,next_change#, name from v$archived_log where dictionary_begin = 'YES' order by first_change#;
If a Dictionary build is available, the first_change# of the related log can be used as the first_scn value (step 2 is therefore not necessary) as detailed in step 4 below.
The Steps to recreate the Capture process are as follows : 1. Drop the current capture process: First of all record relevant information which should be considered in step 4 below.
select queue_name, capture_name, rule_set_name, rule_set_owner, source_database,negative_rule_set_name, negative_rule_set_owner, checkpoint_retention_time from dba_capture where queue_name = '<queue name>' and queue_owner = '<owner>';
(Note: column checkpoint_retention_time is not present in 10.1). then drop the capture process : exec dbms_capture_adm.stop_capture('<capture name>'); exec dbms_capture_adm.drop_capture('<capture name>');
2. Generate a new dictionary dump in the current log:
set serveroutput on declare scn number; begin dbms_capture_adm.build( first_scn => scn); dbms_output.put_line('First SCN Value = ' || scn); end; /
Note: please record the first SCN Value.
3. Ideally, database objects should be prepared for instantiation after a build is performed. Run one or more of the following procedures in the dbms_capture_adm package to prepare database objects for instantiation:
begin dbms_capture_adm.create_capture( queue_name => '<queue owner>.<queue name>', capture_name => '<capture process name>', rule_set_name => '<exiting ruleset used by capture process>', first_scn => &no); -- <enter the value for the first scn> end; /
The above is an example. You should also consider whether details from Step 1 are also relevant : negative ruleset , etc. 5. If required, reinstantiate the replicated tables at the destination, either manually using exp/imp or expdp/impdp and then setting the correct instantiation scn using : dbms_streams_adm.set_<table|schem>_instantiation_scn(). Ensure that the flashback_scn on export / import is the same as that used on the relevant dbms_streams_adm.set_<table|schem>_instantiation_scn() call. This will ensure that the destination database will only apply changes after the instantiation scn. The sequence is therefore as follows : # on the source database
column inst_scn format 9999999999999 select dbms_flashback.get_system_change_number() inst_scn from dual;
# move the data across ; use the inst_scn as the flashback_scn on export .
export / import data
# Against the target database; use the inst_scn as the instantiation_scn :
use dbms_apply_adm.set_schema_instantiation_scn / dbms_apply_adm.set_table_instantiation_scn
6. Restart the Apply process first then start the new Capture process.