Oracle Streams Replication

In my previous posts I discussed Oracle Advanced Replication…But this method is error-prone.
I do not recommend it to use, because it works fine only for DML operations on less than 100 transactions and there is other limitations also, if you wonder to better understand how can it be configured click here.

Because of that, I started to find another method of replication…and here it is.

So let’s start…

Database 1—–ORCL

Database 2—–TEST

First of all, you should activate archive logging for each of the databases, if you don’t remember how to do this click here.

Step 1: Create stream administration user in both databases.

———————————-ORCL———————————-

SQL> connect sys/pass@orcl as sysdba
SQL> create user streamadmin identified by streamadmin default tablespace users;

———————————-TEST———————————-

SQL> connect sys/pass@test as sysdba
SQL> create user streamadmin identified by streamadmin default tablespace users;


Step 2: Grant required privileges to the user streamadmin.

———————————-ORCL———————————-

SQL> grant dba,select_catalog_role to streamadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);

———————————-TEST———————————-

SQL> grant dba,select_catalog_role to streamadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);

Step 3: Check database parameters required for setting up stream replication.

In this section everyone writes to set GLOBAL_NAMES parameter to TRUE, but I am not using it.

SQL> show parameter job_queue_processes

NAME                 TYPE    VALUE
-------------------- ------- ------
job_queue_processes  integer 10

SQL> show parameter db_recovery_file_dest_size

NAME                       TYPE        VALUE
-------------------------- ----------- -----
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME                  TYPE   VALUE
--------------------- ------ ------------------------------------------------
db_recovery_file_dest string /u0/app/oracle/product/10.2.0/flash_recovery_area

Step 5: Enable supplemental logging on the COUNTRIES table on both of the databases.

———————————-ORCL———————————-

SQL> alter table hr.countries add supplemental log data (all) columns;

———————————-TEST———————————-

SQL> alter table hr.countries add supplemental log data (all) columns;

Step 6: Create Database Links between the stream administrator users in each of the databases.

———————————-ORCL———————————-

SQL> connect streamadmin/streamadmin@ORCL
SQL> create database link test connect to streamadmin identified by streamadmin using 'test';

———————————-TEST———————————-

SQL> connect streamadmin/streamadmin@TEST
SQL> create database link orcl connect to streamadmin identified by streamadmin using 'orcl';

Step 7: Create Stream Queues under the streamadmin user to apply and capture the database changes that should be replicated.

———————————-ORCL———————————-

SQL> show user
User is "streamadmin"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');

———————————-TEST———————————-

SQL> show user
User is "streamadmin"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');

step 8: Setup data capture on both of the databases:

———————————-ORCL———————————-

SQL> BEGIN
  dbms_streams_adm.add_table_rules
  (table_name     => 'HR.COUNTRIES'
  ,streams_type   => 'CAPTURE'
  ,streams_name   => 'CAPTURE_STREAM'
  ,queue_name     => 'CAPTURE_Q'
  ,include_dml    => TRUE
  ,include_ddl    => TRUE
  ,inclusion_rule => TRUE
  );
END;
/

———————————-TEST———————————-

BEGIN
  dbms_streams_adm.add_table_rules
  (table_name     => 'HR.COUNTRIES'
  ,streams_type   => 'CAPTURE'
  ,streams_name   => 'CAPTURE_STREAM'
  ,queue_name     => 'CAPTURE_Q'
  ,include_dml    => TRUE
  ,include_ddl    => TRUE
  ,inclusion_rule => TRUE
  );
END;
/

Step 9: Setup data apply on both of the databases:

———————————-ORCL———————————-

BEGIN
  DBMS_STREAMS_ADM.add_table_rules (
  TABLE_NAME      => 'HR.COUNTRIES',
  STREAMS_TYPE    => 'APPLY',
  STREAMS_NAME    => 'APPLY_STREAM',
  QUEUE_NAME      => 'APPLY_Q',
  INCLUDE_DML     => TRUE,
  INCLUDE_DDL     => TRUE,
  SOURCE_DATABASE => 'TEST'
  );
END;
/

———————————-TEST———————————-

BEGIN
  DBMS_STREAMS_ADM.add_table_rules (
  TABLE_NAME      => 'HR.COUNTRIES',
  STREAMS_TYPE    => 'APPLY',
  STREAMS_NAME    => 'APPLY_STREAM',
  QUEUE_NAME      => 'APPLY_Q',
  INCLUDE_DML     => TRUE,
  INCLUDE_DDL     => TRUE,
  SOURCE_DATABASE => 'ORCL'
  );
END;
/

Step 10: Setup propagation process on both of the databases:

It is basically setting up relationship between the capture processes on one database and apply process on the other database.

———————————-ORCL———————————-

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
  TABLE_NAME             => 'HR.COUNTRIES',
  STREAMS_NAME           => 'ORCL_TO_TEST',
  SOURCE_QUEUE_NAME      =>'CAPTURE_Q',
  DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST',
  INCLUDE_DML            => TRUE,
  INCLUDE_DDL            => TRUE,
  SOURCE_DATABASE        => 'ORCL');
END;
/

———————————-TEST———————————-

BEGIN
  DBMS_STREAMS_ADM.add_table_propagation_rules(
  TABLE_NAME             => 'HR.COUNTRIES',
  STREAMS_NAME           => 'TEST_TO_ORCL',
  SOURCE_QUEUE_NAME      =>'CAPTURE_Q',
  DESTINATION_QUEUE_NAME => 'APPLY_Q@ORCL',
  INCLUDE_DML            => TRUE,
  INCLUDE_DDL            => TRUE,
  SOURCE_DATABASE        => 'TEST'
  );
END;
/

Step 11: Setup schema instantiation SCN on both of the databases.

———————————-ORCL———————————-

DECLARE
iscn NUMBER;
BEGIN
     iscn := dbms_flashback.get_system_change_number();

     dbms_apply_adm.set_table_instantiation_scn@TEST(
     source_object_name    => 'HR.COUNTRIES'
     ,source_database_name => 'ORCL'
     ,instantiation_scn    => ISCN);
END;
/

———————————-TEST———————————-

DECLARE
iscn NUMBER;
BEGIN
     iscn := dbms_flashback.get_system_change_number();

     dbms_apply_adm.set_table_instantiation_scn@ORCL(
     source_object_name    => 'HR.COUNTRIES'
     ,source_database_name => 'TEST'
     ,instantiation_scn    => ISCN);
END;
/

Step 12: Start capture and apply process:

DISABLE_ON_ERROR parameter values:

‘N’ streaming process will be continued even when it encounters errors.
The default value is ‘Y’ which stops process automatically on the first error encountered.

———————————-ORCL———————————-

BEGIN
  dbms_apply_adm.set_parameter(
  apply_name => 'APPLY_STREAM'
  ,parameter => 'DISABLE_ON_ERROR'
  ,value     => 'N');

  dbms_apply_adm.start_apply(apply_name=> 'APPLY_STREAM');

  dbms_capture_adm.start_capture(capture_name=> 'CAPTURE_STREAM');
END;
/

———————————-TEST———————————-

BEGIN
  dbms_apply_adm.set_parameter(
  apply_name => 'APPLY_STREAM'
  ,parameter => 'DISABLE_ON_ERROR'
  ,value     => 'N');

  dbms_apply_adm.start_apply(apply_name=> 'APPLY_STREAM');

  dbms_capture_adm.start_capture(capture_name=> 'CAPTURE_STREAM');
END;
/

 

If any error happens the following query shows it:

select * from dba_apply_error;

 

Let’s test it…

———————————-ORCL———————————-

SQL> connect hr/hr@orcl

SQL> select * from countries;

COUNTRY_ID COUNTRY_NAME    REGION_ID
---------- --------------- ----------
AR         Argentina       2

SQL> insert into contries values('AA','Aaaa',1);
SQL> commit;

SQL> select * from countries;

COUNTRY_ID COUNTRY_NAME  REGION_ID
---------- ------------- ----------
AA         Aaaa          1
AR         Argentina     2

———————————-TEST———————————-

SQL> connect hr/hr@test

SQL> select * from countries;

COUNTRY_ID COUNTRY_NAME REGION_ID
---------- ------------- ----------
AA          Aaaa         1
AR          Argentina    2
Advertisements

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: