Step by Step example on setting up Streams – Oracle 11g


Step 1 Create Users & Setup Privileges




SQL>CREATE USER ANAR IDENTIFIED BY ANAR;

SQL>GRANT CONNECT, RESOURCE, DBA TO ANAR;

SQL>GRANT SELECT_CATALOG_ROLE TO ANAR;

SQL>GRANT UNLIMITED TABLESPACE TO ANAR;

SQL>EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE =>

'ANAR');




Step 2 Create database links at source and target databases




connect ANAR/ANAR@ANARDB1

CREATE DATABASE LINK ANARDB2 CONNECT TO ANAR IDENTIFIED BY ANAR USING

'ANARDB2';




connect ANAR/ANAR@ANARDB2

CREATE DATABASE LINK ANARDB1 CONNECT TO ANAR IDENTIFIED BY ANAR USING

'ANARDB1';




Step 3 Create the queue at ANARDB1/ANARDB2 - Source Database







connect ANAR/ANAR@ANARDB1

BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE (

QUEUE_TABLE => 'C1_STREAM_Q1_QT',

QUEUE_NAME => 'C1_STREAM_Q1',

QUEUE_USER => 'ANAR');

END;

/




Create the queue at ANARDB2 (Target) ##




connect ANAR/ANAR@ANARDB2

BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE (

QUEUE_TABLE => 'A1_STREAM_Q1_QT',

QUEUE_NAME => 'A1_STREAM_Q1',

QUEUE_USER => 'ANAR');

END;

/




Step 4 Create capture at source




Connect to ANARDB1...




connect ANAR/ANAR@ANARDB1

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

schema_name =>'OC_UNW',

streams_type =>'CAPTURE',

streams_name =>'C1_STREAM',

queue_name =>'ANAR.C1_STREAM_Q1',

include_dml =>TRUE,

include_ddl =>TRUE,

source_database =>'ANARDB1');

END;

/




Step 5 Create apply process at target




connect ANAR/ANAR@ANARDB2

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (

SCHEMA_NAME => 'OC_UNW',

STREAMS_TYPE => 'APPLY',

STREAMS_NAME => 'A1_STREAM',

QUEUE_NAME => 'ANAR.A1_STREAM_Q1',

INCLUDE_DML => TRUE,

INCLUDE_DDL => TRUE,

SOURCE_DATABASE => 'ANARDB1');

END;

/




BEGIN

DBMS_APPLY_ADM.SET_PARAMETER(

apply_name => 'A1_STREAM',

parameter => 'disable_on_error',

value => 'n');

END;

/




Step 6 Create propagation at source

connect ANAR/ANAR@ANARDB1

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (

SCHEMA_NAME => 'OC_UNW',

STREAMS_NAME => 'P1_STREAM',

SOURCE_QUEUE_NAME => 'ANAR.C1_STREAM_Q1',

DESTINATION_QUEUE_NAME => 'ANAR.A1_STREAM_Q1@ANARDB2',

INCLUDE_DML => TRUE,

INCLUDE_DDL => TRUE);

END;

/




Step 7 Instantiation at ANARDB2

At source ANARDB1...




SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;




expdp ANAR/ANAR@ANARDB1 SCHEMAS=OC_UNW DIRECTORY=expadmin

DUMPFILE=OC_UNW.dmp logfile=OC_UNW.log PARALLEL=4

FLASHBACK_SCN=<scn>




At Target ANARDB2...




impdp ANAR/ANAR@ANARDB2 SCHEMAS=OC_UNW DIRECTORY=expadmin

DUMPFILE=OC_UNW.dmp logfile=OC_UNW.log PARALLEL=4




Check if schema instantiation is working fine..




select * from DBA_APPLY_INSTANTIATED_SCHEMAS;




connect ANAR/ANAR@ANARDB2

declare

v_scn number;

begin

v_scn := 943015;

dbms_output.put_line('Scn : ' || v_scn);

dbms_apply_adm.set_schema_instantiation_scn(

source_schema_name => 'OC_UNW',

source_database_name => 'ANARDB1',

instantiation_scn => v_scn,

recursive => true);

end;

/




Step 8 At target start apply




connect ANAR/ANAR@ANARDB2




exec dbms_apply_adm.start_apply('A1_STREAM');




Step 9 At source start capture




connect ANAR/ANAR@ANARDB1




exec DBMS_CAPTURE_ADM.START_CAPTURE('C1_STREAM');
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: