Oracle DBMS_COMPARISON package overview

During replicating data in one or another way, it happens that synchronized objects may differ. This situation may take place in the following cases:

• If changes made in one of copied databases were not captured
• The changes were captured but not forwarded
• The changed were forwarded but not applied due to some reasons

It should be admitted that full or partial synchronization may be required due to some other reasons, not related to replication. But the question is how to synchronize database objects?
In case of replication, you can try to initialize objects all over again, however it is not always acceptable. To define such problems, Oracle Database 11g has introduced integrated DBMS_COMPARISON package that supports a limited set of data types:

VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, BINARY_FLOAT, BINARY_DOUBLE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, RAW, CHAR, NCHAR.

DBMS_COMPARISON can be used on conditions that:

• At least one of compared Oracle servers has version 11.
• The second Oracle server has version 10.2 or higher.
• Such objects as tables, views with a single table, materialized view, synonyms
• The Oracle servers have the same encoding.

Now let’s examine how DBMS_COMPARISON package works.
Here are the scripts to create test databases to illustrate the case.

Database 1:

CONNECT system/oracle@db_1:1521/orcl1110
CREATE USER test_user IDENTIFIED BY oracle;
GRANT dba, connect, resource TO test_user;

CREATE PUBLIC DATABASE LINK orcl2 CONNECT TO test_user
IDENTIFIED BY oracle USING 'DB_2:1521/ORCL1020';

CREATE TABLE test_user.departments
(
   id_department NUMBER CONSTRAINT pk_department PRIMARY KEY,
   name_department VARCHAR2(50)
);

INSERT INTO test_user.departments VALUES (1001,'Administration');
INSERT INTO test_user.departments VALUES (1002,'Marketing');
INSERT INTO test_user.departments VALUES (2001,'Purchasing');
INSERT INTO test_user.departments VALUES (2002,'Human Resources');
INSERT INTO test_user.departments VALUES (4001,'Shipping');
INSERT INTO test_user.departments VALUES (4002,'Public Relations');

CREATE TABLE test_user.employees
(
   id_employees NUMBER CONSTRAINT pk_employees PRIMARY KEY,
   id_department NUMBER,
   name_employees VARCHAR2(50)
);

INSERT INTO test_user.employees VALUES (1001,1001,'Donald OConnell');
INSERT INTO test_user.employees VALUES (1002,1002,'Douglas Grant');
INSERT INTO test_user.employees VALUES (2001,2001,'Jennifer Whalen');
INSERT INTO test_user.employees VALUES (2002,2002,'Michael Hartstein');
INSERT INTO test_user.employees VALUES (4001,4001,'Pat Fay');
INSERT INTO test_user.employees VALUES (4002,4002,'Susan Mavris');

COMMIT;
/

Database 2:

CONNECT system/manager@db_2:1521/ORCL1020;
CREATE USER test_user IDENTIFIED BY oracle;
GRANT dba, connect, resource TO test_user;

CREATE TABLE test_user.departments
(
   id_department NUMBER CONSTRAINT pk_department PRIMARY KEY,
   name_department VARCHAR2(50)
);

INSERT INTO test_user.departments VALUES (1001,'Executive');
INSERT INTO test_user.departments VALUES (1002,'Accounting');
INSERT INTO test_user.departments VALUES (3001,'Finance');
INSERT INTO test_user.departments VALUES (3002,'Treasury');
INSERT INTO test_user.departments VALUES (4001,'Shipping');
INSERT INTO test_user.departments VALUES (4002,'Public Relations');

CREATE TABLE test_user.employees
(
   id_employees NUMBER CONSTRAINT pk_employees PRIMARY KEY,
   id_department NUMBER,
   name_employees VARCHAR2(50)
);

INSERT INTO test_user.employees VALUES (1001,1001,'Hermann OConnell');
INSERT INTO test_user.employees VALUES (1002,1002,'Shelley Grant');
INSERT INTO test_user.employees VALUES (3001,3001,'Neena Kochhar');
INSERT INTO test_user.employees VALUES (3002,3002,'Lex De Haan');
INSERT INTO test_user.employees VALUES (4001,4001,'Pat Fay');
INSERT INTO test_user.employees VALUES (4002,4002,'Susan Mavris');

COMMIT;
/

Now we are ready to compare and then synchronize the databases. Let’s connect to database 1, create a comparison for table test_user.departments (we will repeat these actions for table test_user.employees).

BEGIN
   DBMS_COMPARISON.CREATE_COMPARISON
   (
      comparison_name => 'compare_departments',
      schema_name     => 'test_user',
      object_name     => 'departments',
      dblink_name     => 'orcl2'
   );
END;
/

Now launch the comparison:

SET SERVEROUTPUT ON
DECLARE
   consistent BOOLEAN;
   scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
   consistent := DBMS_COMPARISON.COMPARE
                 (
                    comparison_name  => 'compare_departments',
                    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.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Differences were found.');
   END IF;
END;
/

Result:

=========================================================================
Scan ID: 1
Differences were found.

Scan ID may differ from 1. This means that the comparison was not the first on the current Oracle server. It’s good to remember this value as it will be useful. Differences in our case were found, so we can take a good look at them.

There are two queries stated below, the first will show how many differences are in two tables, the second will show the list of rows that differ.

Query 1 (how many differences are in two tables):

SELECT
   c.OWNER AS COMPARISON_OWNER,
   c.COMPARISON_NAME,
   c.SCHEMA_NAME,
   c.OBJECT_NAME,
   s.CURRENT_DIF_COUNT AS DIFFERENCES
FROM DBA_COMPARISON c
   INNER JOIN DBA_COMPARISON_SCAN s
      ON c.COMPARISON_NAME = s.COMPARISON_NAME
         AND c.OWNER = s.OWNER
WHERE
   s.SCAN_ID = 1;

Result:

=========================================================================
COMPARISON_OWNER  COMPARISON_NAME     SCHEMA_NAME OBJECT_NAME DIFFERENCES
----------------- ------------------- ----------- ----------- -----------
TEST_USER         COMPARE_DEPARTMENTS TEST_USER   DEPARTMENTS 6

Query 2 (the list of rows that differ):

SELECT
   c.COLUMN_NAME AS IndexColumn,
   r.INDEX_VALUE AS IndexValue,
   DECODE(r.LOCAL_ROWID,NULL,'-','+') AS LocalRowExists,
   DECODE(r.REMOTE_ROWID,NULL, '-','+') AS RemoteRowExists
FROM DBA_COMPARISON_COLUMNS c
   INNER JOIN DBA_COMPARISON_ROW_DIF r
      ON c.COMPARISON_NAME = r.COMPARISON_NAME
         AND c.OWNER = r.OWNER
   INNER JOIN DBA_COMPARISON_SCAN s
      ON r.SCAN_ID = s.SCAN_ID
WHERE
   c.COMPARISON_NAME = 'COMPARE_DEPARTMENTS' AND
   s.PARENT_SCAN_ID = 1 AND
   r.STATUS = 'DIF' AND
   c.INDEX_COLUMN = 'Y' AND
ORDER BY
   r.INDEX_VALUE;

Result:

=========================================================================
INDEXCOLUMN   LOCALROWEXISTS REMOTEROWEXISTS INDEXVALUE
------------- -------------- --------------- ----------
ID_DEPARTMENT  +              +               1001
ID_DEPARTMENT  +              +               1002
ID_DEPARTMENT  +              -               2001
ID_DEPARTMENT  +              -               2002
ID_DEPARTMENT  -              +               3001
ID_DEPARTMENT  -              +               3002
-------------------------------------------------------
6 rows selected

After looking through the differences and ensuring that they are exactly those we want to synchronize, now is the time to synchronize them.

SET SERVEROUTPUT ON
DECLARE
   scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
   DBMS_COMPARISON.CONVERGE
   (
      comparison_name    => 'compare_departments',
      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);
END;
/

Result:

=========================================================================
Local Rows Merged:   0
Remote Rows Merged:  4
Local Rows Deleted:  0
Remote Rows Deleted: 2

Note that CMP_CONVERGE_LOCAL_WINS parameter means that data on the local server will be considered as etalon, and CMP_CONVERGE_REMOTE_WINS means that data on the remote server will be etalon.