ON CALL DBA SUPPORT

— Database blog

STEPS FOR ADDING NEW OBJECTS TO THE EXISTING ORACLE TABLE LEVEL STREAMS CONFIGURATION

Posted by ssgottik on 09/05/2011

Here i am adding a table by name NEW_STRM_TBL to already up and running TABLE level streams.

SOURCE :

SERVER IP     : 192.168.0.1
DATABASE NAME : SOURCEDB
SCHEMA NAME   : SCOTT
TABLE NAME    : NEW_STRM_TBL
TARGET :

SERVER IP     : 192.168.1.1
DATABASE NAME : TARGETDB
SCHEMA NAME   : SCOTT
TABLE_NAME    : NEW_STRM_TBL

STEP 1: STOP STREAMS

Stop APPLY, PROPAGATION and CAPTURE process

STEP 2: DEFINE THE RULE FOR APPLY
CONN STRMADMIN/STRMADMIN@TARGETDB

show user
–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_type => ‘APPLY’,
streams_name => ‘STREAM_APPLY_A1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP 3: DEFINE THE RULE FOR PROPAGATION

CONN STRMADMIN/STRMADMIN@SOURCEDB

–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_name => ‘STREAM_PROPAGATE_P1’,
source_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q@TARGETDB’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP 4: DEFINE THE RULE FOR CAPTURE

CONN strmadmin/STRMADMIN@SOURCEDB

show user

–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_type => ‘CAPTURE’,
streams_name => ‘STREAM_CAPTURE_C1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP5: OBJECT INSTANTATION

Take the export of table from the source database:

exp SCOTT/TIGER file=exp_tbl.dmp log=exp_tbl.log object_consistancy=y tables=SCOTT.new_strm_tbl

Transfer the dump file to target database:
Import the dump into the target database:

imp SCOTT/TIGER fromuser=SCOTT touser=SCOTT file='<PATH>/exp_tbl.dmp’ log='<PATH>/exp_tbl.log’ STREAMS_INSTANTIATION=Y IGNORE=Y COMMIT=Y

STEP 6: START STREAMS

Start APPLY, PROPAGATION and APPLY

Thanks and Regards,

Satish.G.S

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: