Tuesday, February 21, 2012

Data synchronization/ replication using ORACLE 11g DBMS_COMPARISON

Data synchronization script from UAT to STAGE using ORACLE 11g DBMS_COMPARISON Package

SOUCE USER :    uat       SOURCE DATABASE: uatdb

For this documentation, I am going to create a EMP table in uat and live users. In UAT though I am also going to insert couple of records and see the results of the replication in  live user at the target end.

1. Create database link in sys schema on UAT connecting to STAGE
        connect / as sysdba

CREATE DATABASE LINK stage_link connect to live identified by ***** using 'stagedb';

2. Test the connectivity b/w UAT to STAGE

SELECT * FROM DUAL@stage_link;

3. Connect to uat schema and create the emp table.

 Note: To make the dbms_comparison work, both the source and target table will either need Primary Key or Unique Key      
  conn uat/*****
 SQL> create table emp   (a number primary key,  b varchar2(22));
Table created.
SQL> insert into emp  values(1, 'Bilal');
1 row created.
SQL> insert into emp   values(2,'Ferdous');
1 row created.
SQL> commit;
Commit complete.

5. Now connect to target database and create the emp table structure only (No data)
connect live/****
SQL> create table emp   (a number primary key,  b varchar2(22));

4. Now connect to source database (UAT_DB) as sysdba and create a DBMS_COMPARISON as below

        conn / as sysdba

In our example below, we are going to create the comparision name for the two tables that will be involved in the replication using DBMS_COMPARISON package.

5. Create the comparison by providing the comparision name, source schema name, source object, db link name, remote schema name and remote object name

DBMS_COMPARISON.CREATE_COMPARISON  ( comparison_name => 'emp_replication',  schema_name=> 'uat',object_name => 'emp' ,dblink_name =>'stage_link'  , remote_schema_name=>'LIVE', remote_object_name=>'emp');
PL/SQL procedure successfully completed.

Below script is used to drop the comparison we just created


6. Below script compares both source and target tables and returns a output of unique scan ID and message if there is a row difference b/w two tables as show below

set serveroutput on
consistent   BOOLEAN;
BEGIN  consistent := DBMS_COMPARISON.COMPARE( comparison_name => 'emp_replication',
        scan_info => scan_info, perform_row_dif => TRUE);
        DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
 IF consistent=TRUE THEN  
        DBMS_OUTPUT.PUT_LINE('No differences were found.');
          DBMS_OUTPUT.PUT_LINE('Differences were found.');
Scan ID: 1
Differences were found.
PL/SQL procedure successfully completed.

7. Now pass the scand id we got in above steps as show in red and run the script

DBMS_COMPARISON.CONVERGE( comparison_name  => 'emp_replication',
        scan_id => 1 , scan_info => scan_info, converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);
      DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
        DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
        DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
        DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
PL/SQL procedure successfully completed.

8. Connect to the target and see if you see the changes

Conn live/*****

SQL> select * from emp;

        A B
---------- ----------------------
         1 Bilal
         2 Ferdous

Success !!