ON CALL DBA SUPPORT

— Database blog

STEPS TO IMPLEMENT TABLE LEVEL ORACLE STREAMS

Posted by ssgottik on 14/04/2011

   STEPS TO SETUP TABLE LEVEL STREAMS
   ————————————————————-

Here i am replicating four table from source database to target database. Details of the source database,target database and tables

which are involved in replication is mentioned below:

SOURCE DATABASE : DBSOURCE

TARGET DATABASE : DBTARGET

SOURCE TABLE OWNER NAME :  SCOTT

TARGET TABLE OWNER NAME :  SCOTT

TABLES WHICH ARE MEMEBER OF REPLICATION : EMP,DEPT,EMPLOYEES
Fallow the steps in the same sequence.

STEP 0 : ADD SUPPLEMENT LOGIN TO ALL THE TABLES WHICH ARE PART OF STREAMS REPLICATION

@STEP0_SYS_SOURCE_SUPPLEMENTAL_LOG_DATA.SQL

— CONTENTS OF .SQL FILES

spool c:\STREAMS_LOG\STEP0_SYS_SOURCE_SUPPLEMENT_LOG_DATA.log

CONN SYS@DBSOURCE AS SYSDBA

set echo on

SHOW USER

alter database force logging;

alter database add supplemental log data;
alter table SCOTT.EMP  ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;                                       
alter table SCOTT.DEPT  ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;                            
alter table SCOTT.EMPLOYEES  ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;

SPOOL OFF;
STEP 1 : SETTING THE ENV VARIABLES AT SOURCE – DBSOURCE

— The database must run in archive log mode

@STEP1_SYS_SOURCE_GLOBALNAME.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step1_sys_source_globalname.log

CONN SYS@DBSOURCE AS SYSDBA

SHOW USER

select * from global_name; –to see current global_name

alter system set global_names=true scope=both;

— Restart DB & do the same changes on Target DB also

spool off

STEP 2 : SETTING THE ENV VARIABLES AT TARGET – DBTARGET

— the database must run in archive log mode

@STEP2_SYS_TARGET_GLOBALNAME.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step2_sys_target_globalname.log

CONN SYS@DBTARGET AS SYSDBA

SHOW USER

select * from global_name; –to see current global_name

alter system set global_names=false scope=both;

— Restart DB & do the same changes on Source DB also

spool off

STEP 3 : CREATING STREAMS ADMINISTRATOR USER AT SOURCE – DBSOURCE

—at the SOURCE:

SQL> create tablespace strepadm datafile ‘/oradata/DBSOURCE/strepadm01.dbf’ size 1000m;

@STEP3_SYS_SOURCE_CREATE_USER.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step3_sys_source_create_user.log

CONN SYS@DBSOURCE AS SYSDBA

SHOW USER

PROMPT CREATING USERS

create user STRMADMIN identified by STRMADMIN default tablespace strepadm temporary tablespace temp;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(‘STRMADMIN’);

spool off
STEP 3a: CREATING DB LINK AT THE SOURCE -DBSOURCE

@STEP3a_STRMADMIN_SOURCE_DBLINK.SQL

— CONTENTS OF .SQL FILES

/* Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at DBSOURCE*/
/* Add the TNS ENTRY details in the tnsnames.ora file */
set echo on

spool c:\STREAMS_LOG\STEP3a_strmadmin_source_dblink.log

CONN STRMADMIN@DBSOURCE AS SYSDBA

show user

create database link DBTARGET connect to STRMADMIN identified by STRMADMIN using ‘DBTARGET’;

spool off

STEP 4 : CREATING STREAMS ADMINISTRATOR USER AT TARGET – DBTARGET

—at the TARGET:

SQL> create tablespace strepadm datafile ‘/oradata/DBTARGET/strepadm01.dbf’ size 1000m;

@STEP4_SYS_TARGET_CREATE_USER.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step4_sys_TARGET_create_user.log

CONN SYS@DBTARGET AS SYSDBA

show user

PROMPT CREATING USERS

create user STRMADMIN identified by STRMADMIN default tablespace strepadm temporary tablespace temp;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(‘STRMADMIN’);

spool off
— IF SCOTT schema is not present in the target please create the same.

STEP 5 : CREATE QUEUE AND QUEUE TABLE AT THE SOURCE – DBSOURCE

@STEP5_STRMADMIN_SOURCE_QUEUE.SQL

— CONTENTS OF .SQL FILES

/* Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at DBSOURCE */
set echo on
spool c:\STREAMS_LOG\step5_strmadmin_source_queue.log

connect STRMADMIN@DBSOURCE

show user

BEGIN
   DBMS_STREAMS_ADM.SET_UP_QUEUE(
     queue_table => ‘STREAMS_QUEUE_TABLE’,
     queue_name  => ‘STREAMS_QUEUE_Q’,
     queue_user  => ‘STRMADMIN’);
END;
/

spool off
STEP 6 : CREATE QUEUE AND QUEUE TABLE AT THE TARGET – DBTARGET

@STEP6_STRMADMIN_TARGET_QUEUE.SQL

— CONTENTS OF .SQL FILES

/* Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at DBTARGET */
set echo on
spool c:\STREAMS_LOG\step6_strmadmin_target_queue.log

conn STRMADMIN@DBTARGET

show user

BEGIN
   DBMS_STREAMS_ADM.SET_UP_QUEUE(
     queue_table => ‘STREAMS_QUEUE_TABLE’,
     queue_name  => ‘STREAMS_QUEUE_Q’,
     queue_user  => ‘STRMADMIN’);
END;
/

spool off
STEP 7 : CREATE PROPAGATION PROCESS AT SOURCE – DBSOURCE

@STEP7_STRMADMIN_SOURCE_PROPOGATION.SQL

— CONTENTS OF .SQL FILES

set echo on

/*Step 7 -Connected to DBSOURCE, create PROPAGATION */

spool C:\STREAMS_LOG\step7_strmadmin_source_propogation.log

conn strmadmin@DBSOURCE

SHOW USER

–EMP

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘SCOTT.EMP’,
streams_name => ‘STREAM_PROPAGATE_P1’,
source_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q@DBTARGET’,
include_dml => true,
include_ddl => true,
source_database => DBSOURCE);
END;
/
–DEPT

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘SCOTT.DEPT’,
streams_name => ‘STREAM_PROPAGATE_P1’,
source_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q@DBTARGET’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/
–EMPLOYEES

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘SCOTT.EMPLOYEES’,
streams_name => ‘STREAM_PROPAGATE_P1’,
source_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q@DBTARGET’,
include_dml => true,
include_ddl => true,
source_database => ‘DB0SOURCE’);
END;
/

SPOOL OFF

STEP 8 : CREATE CAPTURE PROCESS AT SOURCE – DBSOURCE

@STEP8_STRMADMIN_SOURCE_CAPTURE.SQL

— CONTENTS OF .SQL FILES

set echo on

/*Step 8 -Connected to DBSOURCE , create CAPTURE */

spool C:\STREAMS_LOG\step8_strmadmin_source_capture.log

CONN strmadmin@DBSOURCE

show user

–EMP

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.EMP’,
streams_type => ‘CAPTURE’,
streams_name => ‘STREAM_CAPTURE_C1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/
–DEPT

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.DEPT’,
streams_type => ‘CAPTURE’,
streams_name => ‘STREAM_CAPTURE_C1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/
–EMPLOYEES

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.EMPLOYEES’,
streams_type => ‘CAPTURE’,
streams_name => ‘STREAM_CAPTURE_C1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/

SPOOL OFF
STEP 9 : CREATE APPLY PROCESS AT TARGET – DBTARGET

@STEP9_STRMADMIN_TARGET_APPLY.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step9_strmadmin_target_apply_start.log
/* STEP 9.- Specify an ‘APPLY USER’ at the destination database.
This is the user who would apply all statements and DDL statements.
The user specified in the APPLY_USER parameter must have the necessary privileges to perform DML and DDL changes on the apply objects*/ */

CONN STRMADMIN/STRMADMIN@DB001042

show user
–EMP

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.EMP’,
streams_type => ‘APPLY’,
streams_name => ‘STREAM_APPLY_A1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/
–DEPT

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.DEPT’,
streams_type => ‘APPLY’,
streams_name => ‘STREAM_APPLY_A1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/
–EMPLOYEES

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.EMPLOYEES’,
streams_type => ‘APPLY’,
streams_name => ‘STREAM_APPLY_A1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘DBSOURCE’);
END;
/
—change the user and Set stop_on_error to false so apply does not abort for every error; */

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => ‘STREAM_APPLY_A1’,
apply_user => ‘SCOTT’);
END;
/
BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => ‘STREAM_APPLY_A1’,
    parameter  => ‘disable_on_error’,
    value      => ‘n’);
END;
/
spool off
STEP 10: STREAMS OBJECT INSTANTATION

@ STEP10_EXP_IMP — Details are present in this text.

SOURCE :

$exp USERNAME/PASSWORD parfile=exp_streams.par

vi exp_streams.par

file=exp_streams.dmp
log=exp_streams.log
object_consistent=y
tables=’EMP’,’DEPT’,’EMPLOYEES’
STATISTICS=NONE

SCP THE .DMP FILE TO TARGET AND IMPORT IT:

TARGET:

imp FROMUSER=SCOTT TOUSER=SCOTT FILE=exp_streams.dmp  log=exp_streams.log STREAMS_INSTANTIATION=Y IGNORE=Y COMMIT=Y

NOTE1: Remove all the trigger which got imported and revoke the create trigger privilage.of the schema which is involved in streams.

STEP 11: START THE APPLY PROCESS AT TARGET – DBTARGET

@STEP11_STRMADMIN_TARGET_START_APPLY.SQL

— CONTENTS OF .SQL FILES

SET ECHO ON

spool c:\STREAMS_LOG\step11_STRMADMIN_TARGET_APPLY_START.log

connect STRMADMIN@DBTARGET

show user

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => ‘STREAM_APPLY_A1’);
END;
/

spool off

STEP 12 : START THE CAPTURE PROCESS AT SOURCE – DBSOURCE

@STEP12_STRMADMIN_SOURCE_START_CAPTURE.SQL

— CONTENTS OF .SQL FILES

SET ECHO ON

spool c:\STREAMS_LOG\step12_STRMADMIN_SOURCE_CAPTURE_START.log

connect STRMADMIN@DBTARGET

show user

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => ‘STREAM_CAPTURE_C1’);
END;
/
spool off

                                                                                                                                                   Thanks and Regards,

                                                                                                                                                   Satish.G.S

Advertisements

One Response to “STEPS TO IMPLEMENT TABLE LEVEL ORACLE STREAMS”

  1. Mohanad Awad said

    Amazing

    Thanks,
    Mohanad Awad

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: