Tutorial: Converging Divergent Data

This example continues the scenario described in "Tutorial: Comparing Data in Two Different Databases". Complete the steps in that topic before continuing.

When a shared database object has diverged at two different databases, you can use the CONVERGE procedure in the DBMS_COMPARISON package to converge the two instances of the database object. After the CONVERGE procedure runs successfully, the shared database object is consistent at the two databases. To run the CONVERGE procedure, you must specify the following information:

• The name of an existing comparison created using the CREATE_COMPARISON procedure in the DBMS_COMPARISON package

• The scan ID of the comparison that you want to converge

The scan ID contains information about the differences that will be converged. In this example, the name of the comparison is compare_departments and the scan ID is 1.

Also, when you run the CONVERGE procedure, you must specify which database "wins" when the shared database object is converged. If you specify that the local database wins, then the data in the database object at the local database replaces the data in the database object at the remote database when the data is different. If you specify that the remote database wins, then the data in the database object at the remote database replaces the data in the database object at the local database when the data is different. In this example, the local database ii1.example.com wins.

To converge divergent data in the hr.departments table at the ii1.example.com and ii2.example.com databases: 

1. On a command line, open SQL*Plus and connect to the ii1.example.com database as the administrative user who owns the database link created in "Tutorial: Preparing to Compare and Converge Data". For example, if the SYSTEM user owns the database link, then connect as the SYSTEM user:

   sqlplus system@ii1.example.com
   Enter password: password
   

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

2. Run the CONVERGE procedure to converge the hr.departments table at the two databases:

   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;
   /
   
   Local Rows Merged: 0
   Remote Rows Merged: 2
   Local Rows Deleted: 0
   Remote Rows Deleted: 1
   
   PL/SQL procedure successfully completed.
   

The CONVERGE procedure synchronizes the portion of the database object compared by the specified scan and returns information about the changes it made. Some scans might compare a subset of the database object. In this example, the specified scan compared the entire table. So, the entire table is synchronized, assuming no new differences appeared after the comparison scan completed.

The local table wins in this example because the converge_options parameter is set to DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS in the procedure. That is, for the rows that are different in the two databases, the rows at the local database replace the corresponding rows at the remote database. If some rows exist at the remote database but not at the local database, then the extra rows at the remote database are deleted. If instead you want the remote database to win, then set the converge_options parameter to DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS in the procedure.

In addition, if you run the CONVERGE procedure on a shared database object that is part of an Oracle Streams replication environment, then you might not want the changes made by the procedure to be replicated to other databases. In this case, you can set the following parameters in the CONVERGE procedure to values that will prevent the changes from being replicated:

• local_converge_tag

• remote_converge_tag

When one of these parameters is set to a non-NULL value, a tag is set in the session that makes the changes during convergence. The local_converge_tag parameter sets the tag in the session at the local database, while the remote_converge_tag parameter sets the tag in the session at the remote database. If you do not want the changes made by the CONVERGE procedure to be replicated, then set these parameters to a value that will prevent Oracle Streams capture processes and synchronous captures from capturing the changes.