DBMS_COMPARISON for “Compare and Repair” - Oracle 11g

Oracle Database 11g Streams introduces advancements in the DBMS_COMPARISON package.OracleStreams now provides functionality that has been in Quest Shar eplex since Oracle 8i.Shareplex is one of Quest’s enterprise products that performs redo and archivelog mining and provides a similar level of functio nality to Oracle Streams.

Lots of Oracle customers have preferred to utilize Quest Shareplex over Oracle Streams and Oracle Advanced Replication in the past for data replication because of its ease of use and maintenance.Oracle Streams now makes another big stride and fills this gap with the new data comparison and convergence APIs.

Even though the DBMS_COMPARISON package is introduced as a Streams feature, it can be use independently of Streams.You can use the DBMS_COMPARISON package to detect divergences and rectify discrepancies in a non-Streams environment.

Quest always had the capability to perform what they call a compare and repair operation.A compare and repair operation provides you with the ability to compare row for row of a source table against a remote target table.

As of Oracle Database 11g,Oracle provides similar functionality that enables the comparison of the rows in an object,such as a table, against another object in the same or a different database.If there are deltas in the database objects,then the DBMS_COMPARISON package can be used to converge the database objects so that they are consistent.

The compare procedure does not support comparison of LONG, LONG RAW,ROWID, CLOB, NCLOB, BLOB, BFILE, user-defined types, or Oracle-supplied types such as XMLTypes.

DBMS_COMPARISON Requirements

The DBMS_COMPARISON package has database release requirements.The localdatabase must be at least at Oracle version 11.1, and the remote database must be at least at Oracle version 10.1.0. DBMS_COMPARISON also requires that at least one index be available on the objects being compared, preferably a NUMBER column.In addition,the database character set of the source and target comp arison database must be the same.

Supported Database Object Types

The DBMS_COMPARISION package can compare databases between live sources for the following types of database objects:

  • Tables
  • Single-table views
  • Materialized views
  • Synonyms for tables, single-table views, and materialized views

Data does not have to be compared against the same object type.You can compare a table with a view.You can also compare a table in one database against a materialized view on another database. Database objects can be of different types.Furthermore,the columns names can also be different as long as they are of the same datatype.

Comparison

Behind the scenes, Oracle does not perform row-for-row comparisons as you would expect.Oracle’s comparison algorithm uses the ora_hash function to compute a hash value for a specified number of rows called a bucket.

Buckets improve the performance of table scans by splitting the table into ranges of rows.Each range of rows is processed independently.

The number of buckets determined for acompared object will be the number of rows in a specified table divided by the MAX_NUM_BUCKETS parameter in theDBMS_COMPARISION. CREATE_COMPARISON procedure.

The MAX_NUM_BUCKETS parameter determines the number of rows performed per scan.If there are no differences found in the specified bucket of rows between the source and target tables,the bucket is split, and another scan is performed.

If the split bucket from the previous proces returns different results in the hash value, the buckets are split again, and another hash value comparison is performed.This process is repeated until the differing rows are identified or until the MIN_ROWS_IN_BUCKET threshold is reached.

When the MIN_ROWS_IN_BUCKET is reached, the result is posted to theDBA_COMPARISON_SCAN_SUMMARY view.

By default,the number of MAX_NUM_BUCKETS and MIN_ROWS_IN_BUCKET parameters are set to 1000 and 10000, respectively, as shown in a snippet of code from the $ORACLE_HOME/rdbms/admin/dbmscmp.sql file:

-- Other Default values
CMP_MAX_NUM_BUCKETS CONSTANT PLS_INTEGER := 1000;
CMP_MIN_ROWS_IN_BUCKET CONSTANT PLS_INTEGER := 10000;

Begin a comparison by first defining comparison boundaries including a table and column level of granularity using the create_comparison procedure:

begin
dbms_comparison.create_comparison
(comparison_name => 'COMPARE_DOCS',
schema_name => 'RODBA',
object_name => 'DOCS',
dblink_name => 'DBA11g',
column_list => ' DOCUMENT_ID,NAME ');
end;
/

Two additional parameters to the create_comparison procedure, SCAN_PERCENT and SCAN_MODE, allow you to specify the level of comparison and percentage of the table to scan.The following are valid values for SCAN_MODE:

  • CMP_SCAN_MODE_FULL: The default mode where the complete table is scanned.
  • CMP_SCAN_MODE_RANDOM: You can compare random portions of the table.
  • CMP_SCAN_MODE_CYCLIC: You can cycle through a percentage threshold at a time.Subsequent scans continue where the last comparison ended.
  • CMP_SCAN_MODE_CUSTOM: You can specify the amount of data that is compared at one time based on an index you specify.

Please note that data comparisons can affect the performance of theinvolved databases.You should consider the cyclic level of comparison since you can allocate,for example,10 percent of the rowsets at a time and continue until the comparison completes.By using this approach,you can mitigate possible performance bottlenecks to the production database environment.

The SCAN_PERCENT parameter is applicable when the SCAN_MODE parameter is set to either CMP_SCAN_MODE_RANDOM or CMP_SCAN_MODE_CYCLIC.

Once the comparison is defined, we can run the comparison difference algo rithm and collect the deltas between the source and the target.For each execution of the compare function,a scan ID is produced:

declare
v_comp boolean;
v_scan_info dbms_comparison.comparison_type;
begin
v_comp := dbms_comparison.compare
(comparison_name => 'COMPARE_DOCS',
scan_info => v_scan_info,
perform_row_dif => TRUE);
dbms_output.put_line ('Scan ID: '||v_scan_info.scan_id);
if v_comp = TRUE then
dbms_output.put_line ('No deltas were found.');
else
dbms_output.put_line ('Deltas were found.');
end if;
end;
/

In this example,PERFORM_ROW_DIF is set to TRUE.With this parameter set, theindividual row deviation’s information will be collected.By setting this parameter to FALSE, deviation information will be collected only at the table level.Your output should look similar to the following:

Scan ID: 99
Deltas were found.
PL/SQL procedure successfully completed.

For optimal performance for object-level comparisons, you can adjust theMAX_NUM_BUCKETS and MIN_ROWS_IN_BUCKET parameters in the create_ comparisonprocedure.

Comparison Maintenance

After much iteration of data comparisons,you may need to purge previous comparison collections. The nls_timestamp_format can be specified at the database level in the initialization parameter:

nls_timestamp_format='RRRR-MM-DD HH24:MI:SS'

To purge all previous comparisons, you can use the following procedure, specifying nulls for SCAN_ID and PURGE_TIME:

begin
dbms_comparison.purge_comparison
(comparison_name => 'COMPARE_DOCS',
scan_id => NULL,
purge_time => NULL);
end;
/

On the other hand,you can also purge an individual scan of a comparison.The requirement to perform this task are the comparison name and the scan ID.You can determine the SCAN_ID for a specified comparison name by querying theDBA_COMPARISON_ SCAN_SUMMARY view:

select distinct root_scan_id
from dba_comparison_scan_summary
where comparison_name = 'COMPARE_DOCS';

Once the SCAN_ID is identified, you can perform the actual purge:

begin
dbms_comparison.purge_comparison
(comparison_name => 'COMPARE_DOCS',
scan_id => 99,
purge_time => NULL);
end;
/

You can also purge a plan for a specified period. All scans prior to that time period will be deleted.To purge all comparisons since April 15, 2007, as of noon, you can use the following:

begin
dbms_comparison.purge_comparison
(comparison_name => 'COMPARE_DOCS',
purge_time => '2007-04-15 12:00:00');
end;
/

You can also drop the comparison and all associated scans using the drop_comparison procedure: exec dbms_comparison.drop_comparison('COMPARE_DOCS');

Rechecking a Prior Comparison

You can recheck the previous comparison scans using theDBMS_COMPARISION.RECHECK function. This function is capable of capturing in-flight data.It can also be used to provide a quick check of rows that are different.

The recheck function checks the current data in the database objects for differences that were recorded in the specific comparison scan.The recheck function takes a parameter of the comparison name,scan ID,and row difference.Let’s perform a recheck for a comparison on the DOCS table, which generated a SCAN_ID of 99:

set serveroutput on
declare
v_comp boolean;
begin
v_comp := dbms_comparison.recheck
(comparison_name => 'COMPARE_DOCS',
scan_id => 99);
if v_comp = TRUE then
dbms_output.put_line('No deltas were found.');
else
dbms_output.put_line('Deltas were found.');
end if;
end;
/

Your output is similar to the following:

Deltas were found.PL/SQL procedure successfully completed.

Converging Shared Database Objects (Repair)

The primary purpose of the converge procedure is to resynchronize the differences from the source compared to the target database. Once you identify what is considered to be the “truth”database,albeit the target or the source, you can manually sync the row differences across the database link.The best option is to let Oracle synchronize the row differences identified in a com parison scan.

You can converge and specify whether the source or target is the master and should win on the conflict resolution.The following example specifies that the local database is the conflict winner:

set serveroutput on
declare
v_scan_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge
(comparison_name => 'COMPARE_DOCS',
scan_id => 99,
scan_info => v_scan_info,
converge_options => dbms_comparison.cmp_converge_local_wins);
dbms_output.put_line ('Local Rows Merged: '||v_scan_info.loc_rows_merged);
dbms_output.put_line ('Remote Rows Merged: '||v_scan_info.rmt_rows_merged);
dbms_output.put_line ('Local Rows Deleted: '||v_scan_info.loc_rows_deleted);
dbms_output.put_line ('Remote Rows Deleted: '||v_scan_info.rmt_rows_deleted);
end;
/
Your output is similar to the following:
Local Rows Merged: 0
Remote Rows Merged: 1
Local Rows Deleted: 0
Remote Rows Deleted: 1
PL/SQL procedure successfully completed.
By modifying this:
converge_options => dbms_comparison.cmp_converge_local_wins);
to this:
converge_options => dbms_comparison.cmp_converge_remote_wins);

you can specify the remote database server to be the master source.There are several DBA_ additional information captured by the DBMS_COMPARISON package.You can join the DBA_COMPARISON and DBA_COMPARISON_SCAN_SUMMARY views to produce a high-level report indicating the number of records that are out of sync using the following query:

select c.comparison_name, c.schema_name,
c.object_name, cs.current_dif_count diff
from dba_comparison c,
dba_comparison_scan_summary cs
where c.comparison_name = cs.comparison_name
and c.owner = cs.owner
and cs.scan_id = 99;
COMPARISON_NAME SCHEMA_NAME OBJECT_NAME DIFF
--------------- ----------- ---------- ----
COMPARE_DOCS RODBA DOCS 1

Other pertinent views relevant to comparisons are as follows:

  • DBA_COMPARISON_COLUMNS
  • DBA_COMPARISON_SCAN
  • DBA_COMPARISON_SCAN_VALUES
  • DBA_COMPARISON_ROW_DIF

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics