Script to Rectify Differences between two tables in a Replicated Environment
Product Name, Product Version
Oracle Server Version 7.x, 8.x, 9.x
Platform Platform Independent
Date Created 22-Nov-2002
Instructions
Execution Environment:
SQL, SQL*Plus, iSQL*Plus
Access Privileges:
Requires DBA or RepAdmin privileges
Usage:
sqlplus
Instructions:
Run the scripts in the following order:
1. DBMSR_CREATE.SQL
2. DBMSR_DIFF.SQL
3. DBMSR_RECTIFY.SQL
The scripts will prompt for input from the user so you should read the
beginning of each script carefully. Pay special attention to the script
DBMSR_RECTIFY.SQL, this is the script that determines what records will be
deleted or added to the sites specified as the COMPARISON site.
The following data types are supported by the scripts:
- CHAR
- DATE
- NUMBER
- RAW
- ROWID
- VARCHAR2
The following data types are not supported by the scripts:
- LOBS (CLOB or BLOB)
- Columns based on user-defined types
The following init
the scripts:
- SORT_DIRECT_WRITES = TRUE
- SORT_WRITE_BUFFERS =
- SORT_WRITE_BUFFER_SIZE =
- SORT_AREA_SIZE = 1M or higher
- compatibility = 7.3 or higher
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected. The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
The following set of scripts will allow you to rectify data differences between
two master to master replicated environment database objects in Oracle version
7.3 and higher. You can use these procedures to resynchronize the contents of
replicated objects that have data discrepancies within your replication
environment.
The scripts contained in this document used the DBMS_RECTIFIER_DIFF package
the procedures contained in the DBMS_RECTIFIER_DIFF package are:
DIFFERENCES:
The DIFFERENCES procedure compares two replicas of a table, and determines
all rows in the first replica that are not in the second and all rows in the
second that are not in the first. The output of this procedure is stored in
two user-created tables. The first table stores the values of the missing
rows, and the second table is used to indicate which site contains each row.
RECTIFY:
The RECTIFY procedure uses the information generated by the DIFFERENCES
procedure to rectify the two tables. Any rows found in the first table and
not in the second are inserted into the second table. Any rows found in the
second table and not in the first are deleted from the second table.
Guidelines for using the DBMS_RECTIFIER_DIFF package:
1. Make sure that each missing_rows table is cleaned up after each rectify.
2. Multiple users should not use the same missing_rows tables.
3. Designate one of the two sites as the truth or reference site.
4. Using this utility could cause problems with referential integrity.
5. It is NOT a requirement that you call dbms_repcat.suspend_master_activity
before running the dbms_rectifier_diff.differences procedure. However, not
quiescing can result in inconsistencies in your data.
DBMS_RECTIFIER_DIFF.DIFFERENCES
-------------------------------
This procedure can take quite a while to run and identifies differences, which
then need to be processed with the DBMS_RECTIFIER_DIFF.RECTIFY procedure. If
the volume of data is large it may be easier to simple reinstantiate the
comparison table by importing an export of the table in question.
The procedure header:
DBMS_RECTIFIER_DIFF.DIFFERENCES(
SNAME1 IN VARCHAR2,
ONAME1 IN VARCHAR2,
REFERNECE_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);
Parameters:
1. Sname1 - Name of the schema that owns oname1.
2. Oname1 - Name of the table at the reference site.
3. Reference_site - Global_name of the site that will contain the truth
table. If this is set to null it will be assigned the
value of the local site.
4. Sname2 - Schema that owns oname2.
5. Oname2 - Comparison table.
6. Comparison_site - Global_name of the site that contains comparison table.
7. Where_clause - Optional parameter used to limit the number of rows that
will be compared.
8. Column_list - Comma separated list of one or more columns whose values
will be compared.
9. Missing_rows_sname - Schema that owns missing_rows_oname1.
10. Missing_rows_oname1 - Table containing rows that don't exist on both sites
11. Missing_rows_oname2 - Table that holds information stating which table
owns each row listed in missing_rows_oname1.
12. Missing_rows_site - Global name of the site that contains
missing_rows_oname1 and missing_rows_oname2.
13. Max_missing - Maximum number of rows that can be inserted into
missing_rows_oname1. This can be set to any number that
is greater than 1.
14. Commit_rows - A commit will be issued after this many rows have been
inserted into missing_row_oname1.
The execution of this procedure may generate the following exceptions:
ORA-23377 - oname1 is not the same as missing_rows_oname1.
ORA-23368 - either sname, oname, missing_rows_sname or missing_rows_oname
is null.
ORA-23366 - the max_missing parameter is set to anumber less than 1.
ORA-23302 - a remote site is not accessible.
ORA-23308 - tables oname1, oname2, missing_rows_oname1, or missing_rows_oname2
do not exist.
ORA-23365 - the reference_site, comparison_site, or missing_rows_site does
not contain a site name.
DBMS_RECTIFIER_DIFF.RECTIFY
---------------------------
This procedure synchronizes the reference table(oname1). Before executing this
procedure make sure that any updates that will be performed on the comparison
table(oname2) will not violate any constraints. This procedure does not modify
the reference table.
Procedure header:
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);
Parameters.
1. sname1 - Schema that owns oname1.
2. oname1 - The truth Table at the reference_site.
3. reference_site - Global_name of the site that contains the truth table.
4. sname2 - Schema that owns oname2.
5. oname2 - Comparison table.
6. comparison_site - Global name of the site that contains comparison table.
7. column_list - Comma-separated list of the columns that are being
compared.
8. array_columns - A PL/SQL table of column names, either column_list or
array_columns can be passed.
9. missing_rows_sname - Schema that owns missing_rows_oname1.
10. missing_rows_sname1 - Table containing records that do not exist in both
the truth table and comparison table.
11. missing_rows_sname2 - Table that holds information that states which table
contains each missing record.
12. missing_rows_site - Global name of the site where tables
missing_rows_oname1 and missing_rows_oname2 reside.
13. commit_rows - Forces a commit after so many rows have been inserted into
missing_row_oname1.
The execution of the DBMS_RECTIFIER_DIFF.RECTIFY procedure can generate the
following exceptions:
ORA-23368 - sname, oname, missing_rows_sname or missing_rows_oname is
set to NULL.
ORA-23366 - parameter max_missing is set to a number that is less than 0.
ORA-23302 - remote site is not accessible.
ORA-23308 - oname1, oname2, missing_rows_oname1, or missing_rows_oname2 does
not exist.
ORA-02094 - replication is not linked to the kernel.
ORA-23365 - reference_site, comparison_site, or missing_rows_site does not
contain a site name.
Restrictions on the execution of this procedure.
1. If duplicate rows exist in the reference table but not in the comparison
table they will be inserted into the comparison table.
2. If duplicate rows exist in the comparison table but not in the reference
table they will deleted from the comparison table.
References
Distributed Systems Volume II (Oracle7) or Oracle8 Replication manuals
for a complete explanation of how to use package DBMS_RECTIFIER_DIFF
prior to running these scripts.
Script
rem script DBMSR_CREATE.SQL
rem RUN THIS SCRIPT FIRST!
rem
set echo off
set verify off
set concat on
prompt #################################################################
prompt CREATE MISSING_ROWS_DATA AND MISSING_ROWS_LOCATION TABLES
prompt #################################################################
prompt
prompt #################################################################
prompt #
prompt # This script will ask for input to create two different
prompt # tables that will be used to store and rectify differences
prompt # between two replicas in the replicated environment.
prompt #
prompt # INPUT:
prompt #
prompt # 1) SCHEMA to hold the MISSING ROWS tables.
prompt # 2) Table1 >MISSING_ROWS_DATA..(Data for missing rows)
prompt # 3) Table2 >MISSING_ROWS_LOCATION..(Location of missing rows)
prompt # 4) REFERENCE Table, the replication table that is being
prompt # Rectified.
prompt #
prompt #################################################################
prompt
accept SCHEMA prompt 'Enter SCHEMA to hold the MISSING ROWS table: '
prompt
prompt
prompt Example: MR_DATA_
accept MR_DATA1 prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept MR_LOC1 prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
prompt Example:
accept Table1 prompt 'Enter name of the REFERENCE table: '
create table &SCHEMA..&MR_DATA1
as select * from &TABLE1 where
0=1;
create table &SCHEMA..&MR_LOC1
(
present VARCHAR(128),
absent VARCHAR(128),
r_id ROWID
);
commit;
---------------------------------cut----------------------------------
rem script DBMSR_DIFF.SQL
rem RUN THIS SCRIPT SECOND!
rem
set echo off
set verify off
prompt
REM DBMS_RECTIFIER_DIFF.DIFFERENCES procedure compares two replicas of a
REM table, and determines all rows in the FIRST (REFERENCE) replica that
REM are not in the SECOND (COMPARISON) and all rows in the second that
REM are not in the first. The output of this procedure is stored in two
REM USER-CREATED tables. Table A stores the values of the missing rows,
REM and Table B is used to indicate which site contains each row.
prompt #############################################################
prompt DBMS_RECTIFIER_DIFF.DIFFERENCES
prompt #############################################################
prompt
prompt The REFERENCE site will be used to update all other replicas
prompt of the table as needed
prompt #############################################################
prompt
accept SCHEMA1 prompt 'Enter name of schema on REFERENCE site: '
accept OBJECT1 prompt 'Enter name of table on REFERENCE site: '
accept REFERENCE1 prompt 'Enter name of REFERENCE site: '
prompt
accept SCHEMA2 prompt 'Enter name of schema on COMPARISON site: '
accept OBJECT2 prompt 'Enter name of table on COMPARISON site: '
accept COMPARISON2 prompt 'Enter name of COMPARISON site: '
prompt
accept MR_SCHEMA prompt 'Enter schema holding missing rows tables: '
prompt Example: MR_DATA_
accept MR_DATA2 prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept MR_LOC2 prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
accept SITE prompt 'Enter name of site that contains MISSING ROWS tables: '
prompt
BEGIN DBMS_RECTIFIER_DIFF.DIFFERENCES(
sname1 => '&SCHEMA1',
oname1 => '&OBJECT1',
reference_site => '&REFERENCE1',
sname2 => '&SCHEMA2',
oname2 => '&OBJECT2',
comparison_site => '&COMPARISON2',
where_clause => '',
column_list => '',
missing_rows_sname => '&MR_SCHEMA',
missing_rows_oname1 => '&MR_DATA2',
missing_rows_oname2 => '&MR_LOC2',
missing_rows_site => '&SITE',
max_missing => 100,
commit_rows => 50);
END;
/
prompt
prompt ################################################################
prompt
prompt You may now review your MISSING ROWS DATA table and your MISSING
prompt ROWS LOCATION table to determine where the differences are
prompt
prompt ################################################################
---------------------------------cut----------------------------------
rem script DBMSR_RECTIFY.SQL
rem RUN THIS SCRIPT LAST!
rem
set echo off
set verify off
prompt
REM DBMS_RECTIFIER_DIFF.RECTIFY resolves the differences between two
REM tables. It uses the information generated by the DIFFERENCES
REM procedure to rectify the two tables. Any rows found in the FIRST
REM (REFERENCE) table and not in the SECOND(COMPARISON) table are
REM INSERTED into the SECOND table. Any rows found in the SECOND table
REM and not in the first are DELETED from the SECOND table.
prompt #############################################################
prompt DBMS_RECTIFIER_DIFF.RECTIFY
prompt #############################################################
prompt
prompt The REFERENCE site will be used to update all other replicas
prompt of the table as needed
prompt #############################################################
prompt
accept SCHEMA1R prompt 'Enter name of schema on REFERENCE site: '
accept OBJECT1R prompt 'Enter name of table on REFERENCE site: '
accept REFERENCE1R prompt 'Enter name of REFERENCE site: '
prompt
accept SCHEMA2R prompt 'Enter name of schema on COMPARISON site: '
accept OBJECT2R prompt 'Enter name of table on COMPARISON site: '
accept COMPARISON2R prompt 'Enter name of COMPARISON site: '
prompt
accept MR_SCHEMAR prompt 'Enter schema holding missing rows tables: '
prompt
prompt Example: MR_DATA_
accept MR_DATA1R prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept LOC1R prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
accept SITER prompt 'Enter name of site that contains MISSING ROWS tables: '
prompt
BEGIN DBMS_RECTIFIER_DIFF.RECTIFY(
sname1 => '&SCHEMA1R',
oname1 => '&OBJECT1R',
reference_site => '&REFERENCE1R',
sname2 => '&SCHEMA2R',
oname2 => '&OBJECT2R',
comparison_site => '&COMPARISON2R',
column_list => '',
missing_rows_sname => '&MR_SCHEMAR',
missing_rows_oname1 => '&MR_DATA1R',
missing_rows_oname2 => '&LOC1R',
missing_rows_site => '&SITER',
commit_rows => 50);
END;
/
Sample Output
Before you begin, you need to decide if you want to quiesce the replication
system that you are trying to rectify. It is NOT a requirement that you call
suspend_master_activity before running the dbms_rectifier_diff.differences
procedure (script 2). However, not quiescing can result in inconsistencies
in your data.
Before we begin, this is how my tables appear. They are out of synch.
SQL> SELECT * FROM SCOTT.DEPT@R1734.WORLD;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ESCD ORLANDO
60 RDBMS ORLANDO
6 rows selected.
SQL> SELECT * FROM SCOTT.DEPT@R2734.WORLD;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
Here I run the first script which creates the tables I need to find the
differences of the 2 sites:
SQL> @create
#################################################################
CREATE MISSING_ROWS_DATA AND MISSING_ROWS_LOCATION TABLES
#################################################################
#################################################################
# #
# This script will ask for input to create two different #
# tables that will be used to store and rectify differences #
# between two replicas in the replicated environment. #
# #
# INPUT: #
# #
# 1) SCHEMA to hold the MISSING ROWS tables. #
# 2) Table1 >MISSING_ROWS_DATA..(Data for missing rows) #
# 3) Table2 >MISSING_ROWS_LOCATION..(Location of missing rows) #
# 4) REFERENCE Table, the replication table that is being #
# Rectified. #
# #
#################################################################
Enter SCHEMA to hold the MISSING ROWS table:REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Example:
Enter name of the REFERENCE table: SCOTT.DEPT
Table created.
Table created.
Commit complete.
SQL> spool off
Here I run the second script that finds the differences of sites: R1734.WORLD
and R2734.WORLD
SQL> @diff
#############################################################
DBMS_RECTIFIER_DIFF.DIFFERENCES
#############################################################
The REFERENCE site will be used to update all other replicas
of the table as needed
#############################################################
Enter name of schema on REFERENCE site: SCOTT
Enter name of table on REFERENCE site: DEPT
Enter name of REFERENCE site: R2734.WORLD
Enter name of schema on COMPARISON site: SCOTT
Enter name of table on COMPARISON site: DEPT
Enter name of COMPARISON site: R1734.WORLD
Enter schema holding missing rows tables: REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Enter name of site that contains MISSING ROWS tables: R1734.WORLD
PL/SQL procedure successfully completed.
##################################################################
You may now review your MISSING ROWS DATA table and your
MISSING ROWS LOCATION table to determine where the differences are
##################################################################
SQL> SPOOL OFF
Here I run the third and final script that will rectify the differences
between both sites. I chose site: R2734.WORLD as my reference site.
SQL> @rectify
#############################################################
DBMS_RECTIFIER_DIFF.RECTIFY
#############################################################
The REFERENCE site will be used to update all other replicas
of the table as needed
#############################################################
Enter name of schema on REFERENCE site: SCOTT
Enter name of table on REFERENCE site: DEPT
Enter name of REFERENCE site: R2734.WORLD
Enter name of schema on COMPARISON site: SCOTT
Enter name of table on COMPARISON site: DEPT
Enter name of COMPARISON site: R1734.WORLD
Enter schema holding missing rows tables: REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Enter name of site that contains MISSING ROWS tables: R1734.WORLD
PL/SQL procedure successfully completed.
Here is the output of my tables after it has been rectified. They are now
in synch.
SQL> select * from scott.dept@r1734.world;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
SQL> select * from scott.dept@r2734.world;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
After a successful execution of the the rectify procedure, the MISSING ROWS
DATA and MISSING ROWS LOCATION tables should be empty:
SQL> select * from missing_rows;
no rows selected
SQL> select * from location;
no rows selected
SQL> spool off
No comments:
Post a Comment