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
ON CALL DBA SUPPORT 