[Chapter 15] 15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables

15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables

If you are not sure whether the data at two sites are identical, you can use the DBMS_RECTIFIER_DIFF package to find out. The DIFFERENCES procedure compares the data in a table at a master site with the same table at a reference site. After determining the differences, you can use DBMS_RECTIFIER_DIFF.RECTIFY to synchronize the tables.

15.5.1 Getting Started with DBMS_RECTIFIER_DIFF

The DBMS_RECTIFIER_DIFF package is created when the Oracle database is installed. The dbmsrepc.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catrep.sql , which must be run to install the advanced replication packages. The wrapped sql script prvtrctf.sql creates the public synonym DBMS_RECTIFIER_DIFF. No EXECUTE privileges are granted on DBMS_RECTIFIER_DIFF; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

Table 15.6 summarizes the DBMS_RECTIFIER_DIFF programs.

Table 15.6: DBMS_RECTIFIER_DIFF.DIFFERENCES Programs
Name	Description	Use in SQL?
DIFFERENCES	Determines differences between truth table and comparison table	No
RECTIFY	Synchronizes comparison table with truth table	No

15.5.2 DBMS_RECTIFIER_DIFF Interface

This section describes the programs available in the DBMS_RECTIFIER_DIFF package.

15.5.2.1 The DBMS_RECTIFIER.DIFFERENCES procedure

The DIFFERENCES procedure compares the data in a table at a master site with the same table at a reference site. The reference need not be the master definition site.

The procedure stores discrepancies between the reference table and comparison table in a "missing rows" table, which the user must create. It populates the table specified by the missing_rows_oname1 parameter with rows that exist in the reference table but not the comparison table, and rows that exist in the comparison table but not the reference table. The table identified by the missing_rows_oname2 parameter has one record for every record in missing_rows_oname1, which identifies which site has the record.

Here is the specification:

PROCEDURE DBMS_RECTIFIER_DIFF.DIFFERENCES   (sname1 IN VARCHAR2,    oname1 IN VARCHAR2,    reference_site IN VARCHAR2 := '',    sname2 IN VARCHAR2,    oname2 IN VARCHAR2,    comparison_site IN VARCHAR2 := '',    where_clause IN VARCHAR2 := '',    {column_list IN VARCHAR2 := '' |    array_columns IN dbms_utility.name_array,},    missing_rows_sname IN VARCHAR2,    missing_rows_oname1 IN VARCHAR2,    missing_rows_oname2 IN VARCHAR2,    missing_rows_site IN VARCHAR2 := '',    max_missing IN INTEGER,    commit_rows IN INTEGER := 500);

Parameters are summarized in the following table.

Name	Description
sname1	Name of schema that owns oname1.
oname1	Table at reference_site (truth table).
reference_site	The global_name of site with truth table. If NULL or ` ' (default), truth table is assumed to be local.
sname2	Name of schema that owns oname2.
oname2	The comparison table.
comparison_site	or ` ', table is assumed to be local.
where_clause	Optional predicate that can be used to limit set of rows compared (e.g.,`WHERE STATE = `CA'').
column_list	Comma-separated list of one or more columns whose values are to be compared. If NULL or ` ' (default), then all columns are used. There should not be any whitespace after the commas.
array_columns	PL/SQL table of column names; either column_list or array_columns can be passed, not both.
missing_rows_sname	Name of schema that owns missing_rows_oname1.
missing_rows_oname1	Name of table containing records that do not exist in both truth table and comparison table.
missing_rows_oname2	Table that holds information telling which table owns each record in missing_rows_oname1.
missing_rows_site	The global_name of site where tables missing_rows_oname1 and missing_rows_oname2 exist; if NULL or ` ' (default), tables are assumed to be local.
max_missing	The maximum number or rows to insert into missing_rows_oname1 before exiting; can be any value > 1.
comming_rows	Commit rows inserted into missing_row_oname1 after this many records.

15.5.2.1.1 Exceptions

The DIFFERENCES procedure may raise the following exceptions:

Name	Number	Description
badmrname	-23377	The oname1 is the same as missing_rows_oname1
badname	-23368	The sname, oname, missing_rows_sname, or missing_rows_oname is NULL or ` '
badnumber	-23366	The max_missing is less than 1 or NULL
dbms_repcat.commfailure	-23302	Remote site is not accessible
dbms_repcat.missingobject	-23308	The tables oname1, oname2, missing_rows_oname1, or missing_rows_oname2 do not exist
nosuchsite	-23365	The reference_site, comparison_site, or missing_rows_site does not name a site

15.5.2.1.2 Restrictions

Note the following restrictions on calling the DIFFERENCES procedure:

You must create tables missing_rows_sname.missing_rows_oname1 and missing_rows_sname.missing_rows_oname2 before running this procedure.
The columns in table missing_rows_oname1 must match the columns passed to column_list or array_columns exactly.
The replication group to which the tables belong must be quiesced.

15.5.2.1.3 Example

For an example of how to use the DIFFERENCES procedure, see the example under the RECTIFY procedure.

15.5.2.2 The DBMS_RECTIFIER_DIFF. RECTIFY procedure

The DIFFERENCES procedure paves the way for its companion procedure, RECTIFY, which synchronizes the reference table. Before running the RECTIFY procedure, always make sure that the updates to the comparison table will not violate any integrity, check, or NOT NULL constraints. Note that this procedure does not modify the reference table. Here's the specification:

PROCEDURE DBMS_RECTIFIER_DIFF.RECTIFY    (sname1 IN VARCHAR2,    oname1 IN VARCHAR2,    reference_site IN VARCHAR2 := '',    sname2 IN VARCHAR2,    oname2 IN VARCHAR2,    comparison_site IN VARCHAR2 := '',    {column_list IN VARCHAR2 := '' |    array_columns IN dbms_utility.name_array},    missing_rows_sname IN VARCHAR2,    missing_rows_oname1 IN VARCHAR2,    missing_rows_oname2 IN VARCHAR2,    missing_rows_site IN VARCHAR2 := '',    commit_rows IN INTEGER := 500);

Parameters are summarized in the following table.

Name	Description
sname1	Name of schema that owns oname1.
oname1	Table at reference_site (truth table).
reference_site	The global_name of site with truth table; if NULL or ` ' (default), truth table is assumed to be local.
sname2	Name of schema that owns oname2.
oname2	The comparison table.
comparison_site	The global_name of the site with comparison table. If NULL or ` ', table is assumed to be local.
column_list	A comma-separated list of one or more columns whose values are to be compared; if NULL or ` ' (default), then all columns are used. There should not be any white space after the commas.
array_columns	PL/SQL table of column names; either column_list or array_columns can be passed, not both.
missing_rows_sname	Name of schema that owns missing_rows_oname1.
missing_rows_oname1	The name of the table containing records that do not exist in both truth table and comparison table.
missing_rows_oname2	The table that holds information telling which table owns each record in missing_rows_oname1.
missing_rows_site	The global_name of the site where tables missing_rows_oname1 and missing_rows_oname2 exist; if NULL or ` ' (default), tables are assumed to be local.
comming_rows	Commit rows inserted into missing_row_oname1 after this many records.

15.5.2.2.1 Exceptions

The RECTIFY procedure may raise the following exceptions:

Name	Number	Description
badname	-23368	The sname, oname, missing_rows_sname, or missing_rows_oname is NULL or ` '
badnumber	-23366	The max_missing is less than 1 or NULL
dbms_repcat.commfailure	-23302	Remote site is not accessible
dbms_repcat.missingobject	-23308	The tables oname1, oname2, missing_rows _ oname1, or missing_rows_oname2 do not exist
dbms_repcat.norepoption	-2094	Replication option is not linked to kernel
nosuchsite	-23365	The reference_site, comparison_site, or missing _ rows_site does not name a site

15.5.2.2.2 Restrictions

Note the following restrictions on calling RECTIFY:

The DIFFERENCES procedure must have been run prior to running RECTIFY.
The replication group to which the tables belong should still be quiesced.
If duplicate rows exist in the reference table but not the comparison table they will be inserted into the comparison table.
If duplicate rows exist in the comparison table but not the reference table they will be deleted from the comparison table.

15.5.2.2.3 Example

Assume that the table SPROCKET.DAILY_SALES is replicated between sites D7CA.BIGWHEEL.COM (the references site) and D7NY.BIGWHEEL.COM (the comparison site). The following table shows the description of the DAILY_SALES table.

Column Name	Data Type
sales_id	NUMBER(9)
distributor_id	NUMBER(6)
product_id	NUMBER(9)
units	NUMBER(9,2)

The following steps executed at D7CA.BIGWHEEL.COM would populate the tables missing_rows_daily_sales and missing_location_daily_sales and rectify these differences. These steps should be executed under the designated replication administrator account. Note that storage parameters are left out of the example for the sake of brevity and clarity, but they should be included whenever you run the DBMS_RECTIFIER_DIFF.DIFFERENCES procedure.

	CREATE TABLE missing_rows_daily_sales ( 	sales_id	NUMBER(9), 	distributor_id	NUMBER(6), 	product_id	NUMBER(9), 	units		NUMBER(9,2) 	);  	CREATE TABLE missing_location_daily_sales ( 	present	VARCHAR2(128), 	absent	VARCHAR2(128), 	r_id	ROWID 	);  	BEGIN 		DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('SPROCKET');  		DBMS_RECTIFIER_DIFF.DIFFERENCES( - 			sname1			=> 'SPROCKET',  			oname1			=> 'DAILY_SALES',  			reference_site		=> 'D7CA.BIGWHEEL.COM',  			sname2			=> 'SPROCKET',  			oname2			=> 'SPROCKET',  			comparison_site		=> 'D7NY.BIGWHEEL.COM',  			where_clause		=> NULL,  			column_list		=> 'SALES_ID,DISTRIBUTOR_ID,PRODUCT_		            							  ID,UNITS',  			missing_rows_sname	=> 'REPADMIN',  			missing_rows_oname1	=> 'MISSING_ROWS_DAILY_SALES',  			missing_rows_oname2	=> 'MISSING_LOCATIONS_DAILY_SALES , 			missing_rows_site	=> 'D7CA.BIGWHEEL.COM',  			max_missing		=> 500,  			comit_rows		=> 100);  		DBMS_RECTIFIER_DIFF.RECTIFY( - 			sname1			=> 'SPROCKET',  			oname1			=> 'DAILY_SALES',  			reference_site		=> 'D7CA.BIGWHEEL.COM',  			sname2			=> 'SPROCKET',  			oname2			=> 'SPROCKET',  			comparison_site		=> 'D7NY.BIGWHEEL.COM',  						       ID,UNITS',  			missing_rows_sname	=> 'REPADMIN',  			missing_rows_oname1	=> 'MISSING_ROWS_DAILY_SALES',  			missing_rows_oname2	=> 'MISSING_LOCATIONS_DAILY_SALES - 			missing_rows_site	=> 'D7CA.BIGWHEEL.COM',  			comit_rows		=> 100); 	END;

TIP: These procedures can take a long time to run. If the volume of data is significant, it will probably be easier for you to simply reinstantiate the comparison table by importing an export of the reference table.


15.4 DBMS_OFFLINE_OG: Performing Site Instantiation		15.6 DBMS_REPUTIL: Enabling and Disabling Replication