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
DBA Knowledge Base 