synchronize table

DBMS_COMPARISON for compare and synchronize table

The DBMS_COMPARISON package API

• The DBMS_COMPARISON package enables you to compare database objects at different databases and identify differences in them.
• This package also enables you converge the database objects so that they are consistent at different databases.
• Typically, this package is used in environments that share a database object at multiple databases.
Scans
• Each time the COMPARE function is run, one or more new scans are performed for the specified comparison.
• A scan checks for differences in some or all of the rows in a shared database object at a single point in time.
• The comparison results for a single execution of the COMPARE function can include one or more scans.
• You can compare database objects multiple times, and a unique scan ID identifies each scan in the comparison results.

Buckets
• A bucket is a range of rows in a database object that is being compared.
• Buckets improve performance by splitting the database object into ranges and comparing the ranges independently.

How DBMS_COMPARISON Works?

• The DBMS_COMPARISON package uses the ORA_HASH function on the specified columns in all the rows in a bucket to compute a hash value for the bucket.
• If the hash values for two corresponding buckets match, then the contents of the buckets are assumed to match.
• The ORA_HASH function is an efficient way to compare buckets because row values are not transferred between databases. Instead, only the hash value is transferred.

How Scans and Buckets Identify Differences
This section describes two different comparison scenarios to show how scans and buckets identify differences in shared database objects.
In each scenario, the max_num_buckets parameter is set to 3 in the CREATE_COMPARISON procedure.
Therefore, when the COMPARE or RECHECK function is run for the comparison, the comparison uses a maximum of three buckets in each scan.

Description of Figure 12-1 follows

DBMS_COMPARISON.CREATE_COMPARISON
DBMS_COMPARISON.COMPARE
DBMS_COMPARISON.CONVERGE
These configuration should be configured on master site

sqlplus / as sysdba

grant all on DBMS_COMPARISON to hr;
grant create database link to hr;
exit;

sqlplus hr/hr

create database link rmthr192 connect to hr identified by hr using 'ORCL'

BEGIN
DBMS_COMPARISON.CREATE_COMPARISON(
comparison_name => 'employees_compare',
schema_name => 'HR',
object_name => 'EMPLOYEES2',
remote_schema_name => 'HR',
remote_object_name => 'EMPLOYEES2',
dblink_name => 'rmthr192');
END;
/

CREATE or REPLACE PROCEDURE proctblscan(scan_info_v out number)
IS
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE(
comparison_name => 'employees_compare',
scan_info => scan_info,
perform_row_dif => TRUE);
DBMS_OUTPUT.PUT_LINE(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;
*/
scan_info_v := scan_info.scan_id;
END;
/

CREATE or REPLACE PROCEDURE proctblsync(scan_id_v IN NUMBER)
IS
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE(
comparison_name => 'employees_compare',
scan_id => scan_id_v,
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;
/
-bash-3.2$ cat offlinetablesync.sh
#!/bin/bash
####################################################################
##############compare and synchronize table#########################
. /home/orarac/.bash_profile
VAR=`sqlplus -s hr/hr << EOF SET SERVEROUTPUT ON SET FEEDBACK OFF DECLARE scan_info number; BEGIN proctblscan(scan_info_v => scan_info );
END;
/`
EOF

echo $VAR
export VAR
sqlplus -s hr/hr << EOF2
SET SERVEROUTPUT ON
EXEC proctblsync($VAR);
EXIT;
EOF2

ref

http://docs.oracle.com/cd/B28359_01/server.111/b28322/man_comp.htm#BABDDDCB
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm#CHDGJFJB
http://mahmoudoracle.blogspot.in/2012/08/dbmscomparison-package.html#.Ufot8m1af74

v:* {behavior:url(#default#VML);}
o:* {behavior:url(#default#VML);}
w:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
Description of Figure 12-1 follows

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

MicrosoftInternetExplorer4

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s