ON CALL DBA SUPPORT

— Database blog

STEPS TO IMPLEMENT SCHEMA LEVEL ORACLE STREAMS

Posted by ssgottik on 20/04/2011

 STEPS TO IMPLEMENT SCHEMA LEVEL ORACLE STREAMS

Here i am replicating all the objects of SCOTT schema from DBSOURCE database to SCOTT schema in DBTARGET database.

 SOURCE DATABASE : DBSOURCE

TARGET DATABASE : DBTARGET

SOURCE SCHEMA NAME : SCOTT

TARGET SCHEMA NAME : SCOTT

Fallow the steps in the same sequence.

STEP 0: Check streams unsupported objects present with the schema

Qurey DBA_STREAMS_UNSUPPORTED to get the list of Tables and the reason why streams wont support those  tables  in replication.

SQL > SELECT TABLE_NAME,REASON FROM DBA_STREAMS_UNSUPPORTED WHERE OWNER=’SCOTT’;

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

@STEP1_SYS_SOURCE_SUPPLEMENTAL_LOG_DATA.SQL

 Add the supplement login for all the tables present in SCOTT schema at the source side

—    CONTENTS OF .SQL FILES

spool c:\STREAMS_LOG\step1_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 2 : SETTING THE ENV VARIABLES AT SOURCE – DBSOURCE

— The database must run in archive log mode

@STEP2_SYS_SOURCE_GLOBALNAME.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step2_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 3 : SETTING THE ENV VARIABLES AT TARGET – DBTARGET

— the database must run in archive log mode

@STEP3_SYS_TARGET_GLOBALNAME.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step3_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 4 : CREATING STREAMS ADMINISTRATOR USER AT SOURCE – DBSOURCE

—at the SOURCE:

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

@STEP4_SYS_SOURCE_CREATE_USER.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step4_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 5: CREATING DB LINK AT THE SOURCE -DBSOURCE

@STEP5_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\STEP5_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 6 : CREATING STREAMS ADMINISTRATOR USER  AT TARGET – DBTARGET

—at the TARGET:

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

@STEP6_SYS_TARGET_CREATE_USER.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step6_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 7 : CREATE QUEUE AND QUEUE TABLE AT THE SOURCE – DBSOURCE

@STEP7_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\step7_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 8: CREATE QUEUE AND QUEUE TABLE AT THE TARGET – DBTARGET

@STEP8_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\step8_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 9: CREATE PROPAGATION PROCESS AT SOURCE – DBSOURCE

@STEP9_STRMADMIN_SOURCE_PROPOGATION.SQL

— CONTENTS OF .SQL FILES

set echo on

spool C:\STREAMS_LOG\step9_strmadmin_source_propogation.log

conn strmadmin@DBSOURCE

SHOW USER

BEGIN

   DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

     schema_name                        => ‘SCOTT’,

     streams_name                        => ‘STREAM_PROPAGATE_P1’,

     source_queue_name              => ‘STRMADMIN.STREAMS_QUEUE_Q’,

     destination_queue_name       => ‘STRMADMIN.STREAMS_QUEUE@DBTARGET’,

     include_dml                           => true,

     include_ddl                            => true,

     source_database                   => ‘DBSOURCE’);

END;

/

spool off

STEP 10 : CREATE CAPTURE PROCESS AT SOURCE – DBSOURCE

@STEP10_STRMADMIN_SOURCE_CAPTURE.SQL

— CONTENTS OF .SQL FILES

set echo on

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

spool C:\STREAMS_LOG\step10_strmadmin_source_capture.log

CONNstrmadmin@DBSOURCE

show user

BEGIN

  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

    schema_name              => ‘SCOTT’,

    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 11 : CREATE APPLY PROCESS AT TARGET – DBTARGET

@STEP11_STRMADMIN_TARGET_APPLY.SQL

 — CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step11_strmadmin_target_apply_start.log

CONN STRMADMIN/STRMADMIN@DBTAGET

show user

BEGIN

   DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

     schema_name     => ‘SCOTT’,

     streams_type    => ‘APPLY ‘,

     streams_name    => ‘STREAM_APPLY_A1’,

     queue_name      => ‘STRMADMIN.STREAMS_QUEUE_Q’,

     include_dml     => true,

     include_ddl     => true,

     source_database => ‘DBTARGET’);

END;

/

SPOOL OFF

STEP 12: CREATE NEGATIVE RULE AT SOURCE FOR UNSUPPORTED TABLES – DBSOURCE

 Set negative rule for all the tables which are unsupported by streams ( List you got from  querying DBA_STREAMS_UNSUPPORTED)

— CONTENTS OF .SQL FILES

set echo on

spool c:\streams_source\step12_strmadmin_source_negative_rule.log

conn strmadmin@DBSOURCE

show user

BEGIN

  DBMS_STREAMS_ADM.ADD_TABLE_RULES(

    table_name      =>  ‘SCOTT.<UNSUPPORTED TABLE NAME>’,

    streams_type    =>  ‘capture’,

    streams_name    =>  ‘STREAM_CAPTURE_C1’,

    queue_name      =>  ‘strmadmin.STREAMS_QUEUE_Q’,

    include_dml     =>  true,

    include_ddl     =>  true,

    inclusion_rule  =>  false);

END;

/

SPOOL OFF

STEP 13: 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
OWNER=SCOTT
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

STEP 14: START THE APPLY PROCESS AT TARGET – DBTARGET

@STEP14_STRMADMIN_TARGET_START_APPLY.SQL

— CONTENTS OF .SQL FILES

SET ECHO ON

spool c:\STREAMS_LOG\step14_STRMADMIN_TARGET_APPLY_START.log

connect STRMADMIN@DBTARGET

show user

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

—- Set stop_on_error to false so apply does not abort for every error; then, start the Apply process on the destination

BEGIN

  DBMS_APPLY_ADM.SET_PARAMETER(

    apply_name => ‘STREAM_APPLY_A1’,

    parameter  => ‘disable_on_error’,

    value      => ‘n’);

END;

/

 — Start Apply

BEGIN 

DBMS_APPLY_ADM.START_APPLY( 

apply_name => ‘STREAM_APPLY_A1’); 

END; 

spool off

STEP 15 : START THE CAPTURE PROCESS AT SOURCE – DBSOURCE

@STEP15_STRMADMIN_SOURCE_START_CAPTURE.SQL

— CONTENTS OF .SQL FILES

SET ECHO ON

spool c:\STREAMS_LOG\step15_STRMADMIN_SOURCE_CAPTURE_START.log

connect STRMADMIN@DBTARGET

show user

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

YOUR COMMENTS ARE MOST WELCOME

 Thanks and Regards,

Satish.G.S

Advertisements

19 Responses to “STEPS TO IMPLEMENT SCHEMA LEVEL ORACLE STREAMS”

  1. Saravanan said

    Hi,
    Nice post

    how can i include the tables that i excluded from schema level replication.

    Hi i have excluded tables from schema level replication. Now i need to include the excluded tables. Before including the table, i dropped the exluded tables in both db and i created the tables in the same name, here it wont get replicated why?

    ok now i included the tables using

    begin
    dbms_streams_adm.add_table_rules(
    table_name => ‘schema.table_to_be_skipped’,
    streams_type => ‘CAPTURE’,
    streams_name => ‘your_capture_name’,
    queue_name => ‘strmadmin.capture_queue_name’,
    include_dml => true,
    include_ddl => true,
    inclusion_rule => true
    );
    end;
    /

    Here it should work as usual for schema level replication. But it wont. i created tables in some other name , it gets replicated.

    But here the tables that i included is not replicating now .

    Do i need to do table level replication setup once again or any other way we have for including the excluded tables.

    Any help would be appreciated.

    Thanks

  2. Imran said

    Hi,

    Thanks for posting your knowledge here.
    I have been following your steps, all went ok but at the end i got this error messages in propagation process:
    “ORA-25215: user_data type and queue type do not match”

    Can you please tell me how to resolve this error and how to find more info about it from the streams views?

    Thanks, Imran

    • ssgottik said

      Hi Imran,

      Looks like there is an issue with your configuration.

      Try to query DBA_PROPAGATION.

      can you please provide me the detailed script which you used to setup streams, It will be much easy to identify the PROBLEM.

      Thanks and Regards,
      Satish.G.S

  3. good article. keep up the good work;

  4. what steps would differ if I did not want the enire schema but onl a few tables?

  5. cham said

    thank you for steps replication schema
    but i have problem in

    SQL> BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => ‘SCOTT.’,
    streams_type => ‘capture’,
    streams_name => ‘STREAM_CAPTURE_C1’,
    queue_name => ‘strmadmin.STREAMS_QUEUE_Q’,
    include_dml => true,
    include_ddl => true,
    inclusion_rule => false);
    END;
    /
    2 3 4 5 6 7 8 9 10 11 BEGIN
    *
    ERROR at line 1:
    ORA-23481: unexpected name string “SCOTT.”
    ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 372
    ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 312
    ORA-06512: at line 2

    Can you please tell me how to resolve this error 😀

  6. Teddy said

    I setup everything correctly as your steps. But when i insert data, i could not see the target DB apply the data.
    How can i check this error?

  7. Lucky said

    Hi,

    Thanks for the wonderful post. It is really helpful for the beginners .

    I have followed all the steps as said, but replication is not happening.
    From DBA_PROPAGATION view i can see the error: “ORA-25205: the QUEUE STREAMADMIN.STREAMS_QUEUE does not exist”.

    one observation is , from the step 9,
    destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE@DBTARGET’,

    But it is not created any where , am i correct?

    Please advice what to do.

  8. Nagaraju said

    Good article having clear procedure on step by step schema level streams implementation

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: