Required Steps to Recreate a Capture Process. Doc ID 471695.1

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

GOAL

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. 

Note:

This should not be used to re-create the CDC capture process.

SOLUTION

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:

prepare_global_instantiation 
prepare_schema_instantiation 
prepare_table_instantiation 

4. Create the capture process. eg :

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.

REFERENCES

NOTE:273839.1 - FAQ on Streams
NOTE:437838.1 - Streams Recommended Patches
NOTE:471713.1 - Different States of Streams Capture & Apply Processes
NOTE:273674.1 - Streams Configuration Report and Health Check Script