#

| Chapter 15 Advanced Replication |

## 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

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.