|Oracle8i Replication Management API Reference
Release 2 (8.1.6)
Part Number A76958-01
Manage Replicated Environment with APIs, 8 of 9
When administering a replicated environment, you may want to check, periodically, whether the contents of two replicated tables are identical. The following procedures in the DBMS_RECTIFIER_DIFF package let you identify, and optionally rectify, the differences between two tables when both sites are Oracle release 7.3 or higher.
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.
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.
To restore equivalency between all copies of a replicated table, complete the following steps:
For example, it may not be necessary to check rows that have not been updated since the last time that you checked for differences. Although it is not necessary to check all columns, your column list must include all columns that make up the primary key (or that you designated as a substitute identity key) for the table.
You must create one table that can hold the data for the columns being compared. For example, if you decide to compare the EMPNO, SAL, and BONUS columns of the EMPLOYEE table, your CREATE statement would need to be similar to the following:
You must also create a table that indicates where the row is found. This table must contain three columns with the datatypes shown in the following example:
For example, if you wanted to compare the EMPLOYEE tables at the New York and San Francisco sites, your procedure call would look similar to the following:
BEGIN DBMS_RECTIFIER_DIFF.DIFFERENCES( sname1 => 'hr', oname1 => 'employee', reference_site => 'ny.com', sname2 => 'hr', oname2 => 'employee', comparison_site => 'sf.com', where_clause => '', column_list => 'empno,sal,bonus', missing_rows_sname => 'scott', missing_rows_oname1 => 'missing_rows_data', missing_rows_oname2 => 'missing_rows_location', missing_rows_site => 'ny.com', commit_rows => 50); END; /
Figure 7-1 shows an example of two replicas of the EMPLOYEE table and what the resulting missing rows tables would look like if you executed the DIFFERENCES procedure on these replicas.
Notice that the two missing rows tables are related by the ROWID and R_ID columns.
BEGIN DBMS_RECTIFIER_DIFF.RECTIFY( sname1 => 'hr', oname1 => 'employee', reference_site => 'ny.com', sname2 => 'hr', oname2 => 'employee', comparison_site => 'sf.com', column_list => 'empno,sal,bonus', missing_rows_sname => 'scott', missing_rows_oname1 => 'missing_rows_data', missing_rows_oname2 => 'missing_rows_location', missing_rows_site => 'ny.com', commit_rows => 50); END; /
The RECTIFY procedure temporarily disables replication at the "comparison" site while it performs the necessary insertions and deletions, as you would not want to propagate these changes. RECTIFY first performs all of the necessary DELETEs and then performs all of the INSERTs. This ensures that there are no violations of a PRIMARY KEY constraint.
After you have successfully executed the RECTIFY procedure, your missing rows tables should be empty.
If you have any additional constraints on the "comparison" table, you must ensure that they are not violated when you call RECTIFY. You may need to update the table directly using the information from the missing rows table. If so, be certain to DELETE the appropriate rows from the missing rows tables.