Topic:            7.3 DBMS_RECTIFIER_DIFF Package Example
Author:            FTODD
Modified:           17 Dec 96 



7.3 DBMS_RECTIFIER_DIFF Package Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Verified on RDBMS 7.3.2.2

Recommendations
~~~~~~~~~~~~~~~
We recommend that you read the relevant information:
Oracle7 Server Distributed Systems Volume II (Replicated Data) manual,
Administering A Replicated Environment chapter.

You should also attend the Oracle Symmetric Replication course if you
have not already done so.

Explanation
~~~~~~~~~~~
The Table Comparison utility is used for the most part to compare
replicated tables at master sites; you can, however, also compare
a Master Site table with a snapshot site table for differences.
It is not necessary to rectify a snapshot, because it can always
be refreshed.  

Replicated tables my diverge, for example, due to unresolved update
conflicts or possible operational errors.

The replicated environment need not be quiesced to use the utility
but we recommend you do quiesced the environment.

Supported Datatypes:
CHAR   VARCHAR2   DATE   NUMBER   RAW   ROWID   

The DIFFERENCES procedure uses the set difference operation to
determine which rows are different.  The difference between a
reference table and a comparison table is the union of rows
in the reference table that are missing from the comparison
table and the rows in the comparison table that are missing
from the reference table.

The RECTIFY procedure uses the information contained in the
two missing rows tables to rectify the tabe at the comparison
site.  The effects of the procedure:

- Rows that exist in the comparison site table but are missing
  from the reference site table are deleted from the former.

- Rows that exist in the reference table but are missing from     
  the comparison table are inserted into the latter.

- At the successful completion of the procedure, the reference
  and comparison tables become equivalent.

You must designate one site as the 'truth' site and invoke
DIFFERENCES and RECTIFY procedures with the same designated
site.  Switching sites is not advised, because it modifies
the 'truth' and introduces differences.

You must consider the referential integrity between the tables
BEFORE you invoke the RECTIFY procedure.

Example
~~~~~~~
AT THE MASTER DEFINITION SITE, eg rep1.world

STEP 1: Query user1 EMP table on both masters and notice differences.

STEP 2: Suspend activity for user1 replication group. 

execute DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('GROUP1');
select * from DBA_REPCAT;   - note STATUS of QUIESCED on ALL masters.

STEP 3: Create tables needed for comparison and rectification for
        user1 EMP table. Tables will be owned by the REPADMIN user. 
        Table names are user definable. 
        In this exercise we will be using MISSING_ROWS_DATA_EMP and 
        MISSING_ROWS_LOCATION_EMP.

drop table REPADMIN.MISSING_ROWS_DATA_EMP;

create table MISSING_ROWS_DATA_EMP as select * from USER1.EMP where 1=2;

Do a describe on the MISSING_ROWS_DATA_EMP table and confirm is has the 
same structure as user1 EMP table.
Query the MISSING_ROWS_DATA_EMP table and confirm the table is empty.

drop table REPADMIN.MISSING_ROWS_LOCATION_EMP;

create table MISSING_ROWS_LOCATION_EMP ( PRESENT varchar2(30),
                                         ABSENT  varchar2(30),
                                         R_ID    rowid );

STEP 4: Execute table comparison utility for user1 EMP table.
        rep1 is the reference site and rep2 the comparison site.

execute DBMS_RECTIFIER_DIFF.DIFFERENCES('USER1','EMP','rep1.world',
'USER1','EMP','rep2.world','','','REPADMIN','MISSING_ROWS_DATA_EMP',
'MISSING_ROWS_LOCATION_EMP','rep1.world',200,50);

Query the MISSING_ROWS_DATA_EMP table.
Query the MISSING_ROWS_LOCATION_EMP table. 

At this point you have identified all the rows that are NOT synchronized 
between the two tables. You have the choice of manually correcting any 
errors that were detected or using the new Rectify procedure,

STEP 5: Execute table rectifier utility for user1 EMP table.
        rep1 is the reference site and rep2 the comparison site.
        Rectify will make the comparison site the same as the
        reference site.

execute DBMS_RECTIFIER_DIFF.RECTIFY('USER1','EMP','rep1.world','USER1',
'EMP','rep2.world','','REPADMIN','MISSING_ROWS_DATA_EMP', 
'MISSING_ROWS_LOCATION_EMP','rep1.world',50);

Query the MISSING_ROWS_DATA_EMP and MISSING_ROWS_LOCATION_EMP tables. 
They should be empty if successful.

Query user1 EMP table on both master nodes.
The tables should be identical again if successful.

STEP 6: Resume activity for user1 replication group. 

execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY('GROUP1');
select * from DBA_REPCAT;   - note STATUS of NORMAL on ALL masters.