Wednesday, February 15, 2012

Oracle 11g Synchronous STREAMS step by step:

We are going to perform the table level replication using Oracle STREAMS (Synchronous) methodology the only option available in Oracle Standard Edition. Both source and target schemas are on different boxes and we going to perform the replication using DB LINKS without changing the GLOBAL_NAME parameter


The differences between Asynchronous And Synchronous Capture is covered in the following post



make sure following parameters on both source and target are set as below

ALTER SYSTEM SET  aq_tm_processes = 1 SCOPE=BOTH;

ALTER SYSTEM SET  job_queue_processes = 1000 SCOPE=BOTH;


SOURCE: SSDEVDB
TARGET: TTQADB

SSDEVDB (SOURCE)


1. Connect to the database as sys admin

CONNECT / as sysdba


2. Create or identify schema and table that needs to be replicated

CREATE USER u_source IDENTIFIED BY u_source
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT,RESOURCE TO u_source;

CREATE TABLE u_source.t1
(c1 number,
c2 Varchar2(22));


INSERT INTO u_source.t1
VALUES(1,'Bilal'); 
commit;


TTQADB (TARGET)


3. Connect to the database as sys admin

CONNECT / as sysdba

CREATE USER u_source IDENTIFIED BY u_source
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CONNECT,RESOURCE TO u_source;

Create the STRMADMIN user in both SSDEVDB (SOURCE) and TTQADB (TARGET)



CREATE TABLESPACE streams_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE 256M;
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;


TTQADB (TARGET)


CREATE TABLESPACE streams_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE 256M;
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;


Create or identify schema and table that needs to be replicated

CREATE USER u_source IDENTIFIED BY u_source
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT,RESOURCE TO u_source;


CREATE TABLE u_source.t1
(c1 number,
c2 Varchar2(22));


SSDEVDB (SOURCE)


Now we create Oracle advance queue that will be used to capture the DML changes in the source table and replicate it accordingly to the target database schema. In synchronous capture we don't have to start the capture process as it starts automatically once the table rule is created.

CONN strmadmin/strmadmin

To remove the queue first if already created

EXEC DBMS_STREAMS_ADM.REMOVE_QUEUE('ssdevdb_queue',TRUE,TRUE);

To create the queue

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.ssdevdb_queue_table',
queue_name => 'strmadmin.ssdevdb_queue',
queue_user => 'strmadmin');
END;
/

Now we will add the table from which we want to capture the DML changes from and add it in the queue we just created above:




BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'U_SOURCE.T1',
streams_type => 'SYNC_CAPTURE',
streams_name => 'SYNC_CAPTURE',
queue_name => 'strmadmin.ssdevdb_queue',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
inclusion_rule => true,
source_database => 'ssdevdb');
END;

So Queue has been setup and the table has been added to it, now we’ll create the propagation process between source and target database as below.

To drop existing propagation

EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION('ssdevdb_propagation');

To create a propagation

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'u_source.t1',
streams_name => 'ssdevdb_propagation',
source_queue_name => 'strmadmin.ssdevdb_queue',
destination_queue_name => 'strmadmin.ttqadb_queue@ttqadb_link',
include_dml => TRUE,
include_ddl => FALSE,
include_tagged_lcr => FALSE,
source_database => 'ssdevdb',
inclusion_rule => TRUE,
queue_to_queue => TRUE);
END;
/


Create the db link the points to the target schema


CREATE DATABASE LINK ttqadb_link CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'ttqadb';


TTQADB (TARGET)

On the target database connect as strmadmin user. Now we need to create the  Apply process however,  again  we’ll need to create the queue first

CONN strmadmin/strmadmin

To remove the queue first

EXEC DBMS_STREAMS_ADM.REMOVE_QUEUE('strmadmin.ttqadb_queue',true,true);

To create the queue

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.ttqadb_queue_table',
queue_name => 'strmadmin.ttqadb_queue',
queue_user => 'strmadmin');
END;
/

Creating the apply process

BEGIN
dbms_apply_adm.create_apply(
queue_name => 'strmadmin.ttqadb_queue',
apply_name => 'sync_apply',
apply_captured => false); 
END;
/

Now linking apply process to the queue and table

BEGIN
dbms_streams_adm.add_table_rules( table_name => 'U_SOURCE.T1',
streams_type => 'APPLY',
streams_name => 'SYNC_APPLY',
queue_name => 'strmadmin.ttqadb_queue',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'ssdevdb');
END;
/


Now we have to instantiate SCN number of the target table. This will create a point in time the target table started accepting changes from the source database table.


Create the database link pointing to source

CREATE DATABASE LINK ssdevdb_link CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'ssdevdb';


Test the connectivity of the link

SELECT * FROM dual@ssdevdb_link;

get the scn number

COLUMN apply_scn FORMAT 99999999999


SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER APPLY_SCN FROM dual;

Instantiate the table with the scn number

INSERT INTO u_source.t1 (select * from u_source.t1@ssdevdb_link as of scn 16549737236);
commit;

BEGIN
dbms_apply_adm.set_table_instantiation_scn(
source_object_name => 'u_source.t1',
source_database_name => 'ssdevdb',
instantiation_scn => 16549737236);
END;
/


Start the Apply process, from this point onwards all changes in source table T1 are propagated to the target table T1.

EXEC DBMS_APPLY_ADM.START_APPLY('SYNC_APPLY');

SSDEVDB(SOURCE)

insert new record in source

Insert into u_source.t1
Values(2,'DBA');
Commit;

TTQADB (TARGET)
verify the newly entered record to see if the record was replicated from source/

Select * from u_source.t1;

   
   C1 C2
---------- ----------------------
         1 Bilal
         2 DBA

6 comments:

  1. It's very great. How can I add other table to the queue?
    Thank you for advance

    ReplyDelete
  2. is capture process not required?

    ReplyDelete
  3. I don't see the capture process.

    ReplyDelete
  4. I don't see the capture process

    ReplyDelete
  5. SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER APPLY_SCN FROM dual;

    you must execute on SOURCE

    ReplyDelete
  6. not sure how the mapping of columns is made between source and targe?

    ReplyDelete