Unidirectional oracle streams setup from Source database Sup.world to Replicated database Rup.world

Step 1)   Initialization parameter setup – Following init parameters need to be set.

1. global_names = true
2. job_queue_processes = 4
3. aq_tm_processes = 1
4. logmnr_max_persistent_sessions = 1
5. log_parallelism = 1
6. parallel_max_servers = 2
7. streams_pool_size = 200 min
8. open_links = 4
9. _job_queue_interval = 1

Step 2) Streams administrator setup  at source database (sup.world).

Create streams administrator

sql> connect ?/as sysdba?

sql> create user STREAMADM identified by <password>;

sql> CREATE TABLESPACE STRTBS DATAFILE

‘/? /streamstbs.dbf’

SIZE 250 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED SEGMENT SPACE    MANAGEMENT AUTO;

Sql> ALTER USER STREAMADM DEFAULT TABLESPACE streamstbs

QUOTA UNLIMITED ON streamsts;

Sql> ALTER USER STREAMADM TEMPORARY TABLESPACE temp;

Grant privileges to streams administrator

sql> GRANT DBA TO STREAMADM;

sql> BEGIN

DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(

grantee          => ‘STREAMADM’,

grant_privileges => true);

END;

/

Step 3) Create streams queue at source database (sup.world)

sql> connect STREAMADM/<password>

sql> BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(

queue_table => ‘STREAMS_QUEUE_TABLE’,

queue_name => ‘STREAMS_QUEUE’,

queue_user => ‘STREAMADM’);

END;

Step 4) Setup streams administrator at replicated database (rup.world)

Same steps to be followed as in step 2.

Step 5) Create streams queue at replicated database (rup.world)

sql> connect STREAMADM/<password>

sql> BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(

queue_name => ‘STREAMS_QUEUE’,

queue_table =>’STREAMS_QUEUE_TABLE’,

queue_user => ‘STREAMADM’);

END;

Step 6) Create database link to replicated database (rup.world) in source database.

sql> CREATE DATABASE LINK <rup.world> CONNECT TO STREAMADM IDENTIFIED   BY <password> USING <rup>;

Step 7) Turn on supplemental logging for entire source database tables

Sql > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

(PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Step 8) Add capture rules for the required schemas at the source database

sql> BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

schema_name => ‘<schema_name>’,

streams_type =>’CAPTURE’,

streams_name =>’STREAMADM_CAPTURE’,

queue_name => ‘STREAMADM.STREAMS_QUEUE’,

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => ‘<sup>’,

inclusion_rule => true);

END;

/

To exclude a particular table from replication

sql>  BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => ‘<schema_name>.<table_name>’,

streams_type => ‘capture’,

streams_name => ‘STREAMADM_CAPTURE’,

queue_name => ‘STREAMADM.STREAMS_QUEUE’,

include_dml => true,

include_ddl => true,

inclusion_rule => false);

END;

/

Step 9) Add propagation rules for the required schemas at the source database

sql> BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

schema_name => ‘<schema_name>’,

streams_name => ‘STREAMADM_propagation’,

source_queue_name => ‘STREAMADM.streams_queue’,

destination_queue_name => ‘STREAMADM.streams_queue@rup’,

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => ‘<sup>’,

inclusion_rule => true,

queue_to_queue => true);

END;

/

Step 10)  Export, import and instantiation of tables from source to replicated database

If the objects are not present in the replicated database

$ exp USERID=SYSTEM/<password>@sup OWNER=<schema_name>

FILE= exp<schema_name>.dmp LOG= export<schema_name>.log OBJECT_CONSISTENT=Y STATISTICS = NONE

$ imp USERID=SYSTEM/password@rup FULL=Y FILE= exp<schema_name>.dmp IGNORE=Y COMMIT=Y LOG=import<schema_name>.log STREAMS_INSTANTIATION=Y

If the objects are present in the replicated database

$ exp USERID=SYSTEM/<password>@sup OWNER=<schema_name>

FILE= exp<schema_name>.dmp ROWS=N

LOG= export<schema_name>.log OBJECT_CONSISTENT=Y STATISTICS = NONE

$ imp USERID=SYSTEM/<password>@rup full=Y FILE= exp<schema_name>.dmp IGNORE=Y commit=y LOG=import<schema_name>.log STREAMS_INSTANTIATION=Y

Step 11) Add apply rule for the required schemas at the replicated database

If  no tables are excluded from replication then use schema level rules

sql> BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

schema_name => ‘<schema_name>’,

streams_type => ‘apply’,

streams_name => ‘STREAMADM_apply’,

queue_name => ‘STREAMADM.streams_queue’,

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => ‘<sup>’,

inclusion_rule => true);

END;

/

If there are only a few number of tables for replication then use table level rules

Step 12) Specify an ‘APPLY USER’ at the replicated database

I prefer granting following privilege to schema user.

Sql> GRANT EXECUTE ON DBMS_APPLY_ADM TO ‘<schema_name>’;

sql> BEGIN

DBMS_APPLY_ADM.ALTER_APPLY(

apply_name => ‘STREAMADM_APPLY’,

apply_user => ‘<schema_name>’);

END;

sql > BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(

queue_table => ‘STREAMS_QUEUE_TABLE’,

queue_name => ‘STREAMS_QUEUE’,

queue_user => ‘<schema_name>’);

END;

sql> BEGIN

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(

privilege     => ‘ENQUEUE_ANY’,

grantee       => ‘<schema_name>’,

admin_option  => FALSE);

END;

/

sql> BEGIN

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(

privilege     => ‘DEQUEUE_ANY’,

grantee       => ‘<schema_name>’,

admin_option  => FALSE);

END;

/

sql> BEGIN

DBMS_APPLY_ADM.SET_PARAMETER(

apply_name  => ‘STREAMADM_APPLY’,

parameter   => ‘DISABLE_ON_ERROR’,

value       => ‘N’ );

END;

/

Step 13) Start the apply process at the replicated database

Sql> connect as apply user

sql> DECLARE

v_started number;

BEGIN

SELECT decode(status, ‘ENABLED’, 1, 0) INTO v_started

FROM DBA_APPLY WHERE APPLY_NAME = ‘STREAMADM_APPLY’;

if (v_started = 0) then

DBMS_APPLY_ADM.START_APPLY(apply_name  => ‘STREAMADM_APPLY’);

end if;

END;

Step 14)  Start the capture process at source database

Connect as STREAMADM

sql> BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(capture_name  =>   ‘STREAMADM_CAPTURE’);

END;

To stop the capture process and apply process use following steps

Connect as STREAMADM

Sql> BEGIN

DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name  =>   ‘STREAMADM_CAPTURE’);

END;

Connect as <schema_name> or STREAMADM

Sql> BEGIN

DBMS_APPLY_ADM.STOP_APPLY(apply_name => ‘STREAMADM_APPLY’);

END;

Happy Streaming!!!

PrintFriendly

Viewers of this post also viewed...

  • No Related Post