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 = '' and queue_owner = '';

(Note: column checkpoint_retention_time is not present in 10.1).

            then drop the capture process :

            exec dbms_capture_adm.stop_capture('');

            exec dbms_capture_adm.drop_capture(''); 

            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 => '.', 

            capture_name => '', 

            rule_set_name => '', 

            first_scn => &no); --

            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_

            Ensure that the flashback_scn on export / import is the same as that used on

            the relevant dbms_streams_adm.set_

            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