9
Managing Replic ation Objects and Queues

Step 5 Regenera te replication support for the altered master table.

BEGIN 
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'staff',
      oname => 'positions', 
      type => 'TABLE',
      min_communication =>
TRUE); 
END;
/

Step 6 Resume replication.

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'sales_mg');
END;
/

Step 7 If materialized views are b ased on the altered table at any of the master sites, then rebuild these materialized views.

Determining Differences Between Replicated Ta bles

It is possible for the differences to arise in replicated tables. When administering a replication 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 di fferences between two tables.

Using the DIFFERENCES Procedure

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 r ows in the second that are not in the first. The output of this procedure is stored in two user-created tables. The first table store s the values of the missing rows, and the second table is used to indicate which site contains each row.

Using the RECTIFY Procedure

The RECTIFY procedure uses the information generated by the DIFF ERENCES procedure to rectify the two tables. Any rows found in the first table and not in the second are inserted into the sec ond 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:

Step 1 Select one copy of the table to be the "reference" table.

This copy will be used to update all other replicas of the table as needed.

For example, it may not be necessary to ch eck rows that have not been updated since the last time that you checked for differences. Although it is not necessary to check all c olumns, 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.

Step 3 After determining which col umns you will be checking in the table, create two tables to hold the results of the comparison.

You must create one table that can hold the data for the columns being compared. For example, if you decide to compare th e employee_id, salary, and department_id columns of the employees table, then you r CREATE statement would need to be similar to the following:

CREATE TABLE hr.mi
ssing_rows_data (
  employee_id     NUMBER(6),
  salary          NUMBER(8,2),
  department_id   NUMBER(4));

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:

CREATE TABLE hr.missing_rows_location (
  present     VARCHAR2(128),
  absent      VARCHAR2(128),
  r_id        ROWID);

Step 4 Suspend replication activity for the replication group containing the tables that you want to c ompare.

Although suspending replication activity for the group is not a requirement, rectifying tables that were not quiesced first can result in inconsistencies in your data.

CO
NNECT repadmin/repadmin

BEGIN
DBMS_REPCAT.S
USPEND_MASTER_ACTIVITY (
      gname => 'hr_repg');
END;
/

Step 5 At the site containing the "reference" table, call the DIFFERENCES procedure.

For example, if you wanted to compare the emplo yees tables at the New York and San Francisco sites, then your procedure call would look similar to the following:

BEGIN
DBMS_RECTIFIER_DIFF.DIFFERENCES (
      sname1              =>   'hr',
      oname1              =>   'employees',
<
/a>      reference_site      =>   'ny.world',
      sname2              =>   'hr',

  oname2              =>   'employees',
      comparison_site     =>   'mv4.world',

   where_clause        =>   '',
      column_list         =>   'employee_id,salary,department_id',
      missing_rows_sname  =>   'hr',
      missing_rows_oname1 =>   'missing_rows_data',
      missing_rows_oname2 =>   'missing_rows_location',
      missing_rows_site   =>   'ny.wo
rld',
      max_missing         =>    500,
      commit_rows         =>    50);
END;
/

Figure 9-1 shows an example of two replicas of the employee table and what the resulting missing rows tables wo uld look like if you executed the DIFFERENCES procedure on these replicas.

Figure 9-1 Determining Differences Between Replicas

Text description of the ill ustration repma003.gif

Notice that the two missing rows tables are related by the R OWID and r_id columns.

Step 6 Rectify the table at the "comparison" site to be equivalent to the table at the "reference" site.

BEGIN
DBMS_RECTIFIER_DIFF.RECTIFY (
      snam
e1              =>   'hr',
      oname1              =>   'employees',
      reference_
site      =>   'ny.world',
      sname2              =>   'hr',
      oname2
   =>   'employees',
      comparison_site     =>   'mv4.world',
      column_list
    =>   'employee_id,salary,department_id',
      missing_rows_sname  =>   'hr',

 missing_rows_oname1 =>   'missing_rows_data',
      missing_rows_oname2 =>   'missing_rows_location',
      missing_rows_site   =>   'ny.world',
      commit_rows         =>    50);
END;
/

The RECTIFY procedur e temporarily disables replication at the "comparison" site while it performs the necessary insertions and deletions, as you would no t want to propagate these changes. RECTIFY first performs all of the necessary DELETE operations and then p erforms all of the INSERT operations. This ensures that there are no violations of a PRIMARY KEY constraint.

BEGIN

DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'hr_repg');
END;
/

CONNECT mviewadmin/mviewadmin@mv1.world

SELECT DISTINCT(dblink), COUNT(deferred_tran_id) 
   FROM deftran
dest GROUP BY dblink;

DECLARE
   temp INTEGER;
BEGIN
   temp := DBMS_DEFER_SYS.PUSH (
      destination => 'orc1.world',
      stop_on_erro
r => FALSE,
      delay_seconds => 0,
      parallelism => 0);
E
ND;
/

CONNECT mviewadmin/mviewadmin@mv1.world

DECLARE
   temp INTEGER;
BEGIN
   temp := DBMS_DEFER_SYS.PURGE (
      purge_method => DBMS_DEFER_SYS.PURGE_METHOD_QUICK);
END;
/

CREATE TYP
E phone_list_typ AS VARRAY(5) OF VARCHAR2(25);
/

CREATE TYPE warehouse_t
yp AS OBJECT
    (warehouse_id       NUMBER(3), 
     warehouse_name     VARCHAR2(35), 
     location_id        NUMBER(4)
    );
/

CREATE TYPE inventory_typ AS OBJECT
    (product_id          NUMBER(6), 
     warehouse
           warehouse_typ,
     quantity_on_hand    NUMBER(8)
    );
/

CREATE TYPE inventory_list_typ AS TABLE OF inventory_typ;
/

The following procedure retrieves the argument value for collection, object, and REF instances of calls stored in the deferred transactions queue. This procedure assumes that the call number and transacti on id are available.

The user who creates the procedure must have EXECUTE privil ege on the DBMS_DEFER_QUERY package and must have CREATE PROCEDURE privilege. This example use s the oe sample schema. Therefore, to run the example, you must grant the oe user these privileges.

SELECT * FROM deferror;

CONNECT repadmin/repadmin@orc2.world

BEGIN
DBMS_DEFER_SYS.EXECUTE_ERROR (
      deferred_tran_id => '1.12.2904',

   destination => 'ORC2.WORLD');
END;
/

CONNECT hr/hr
@orc2.world

BEGIN
DBMS_DEFER_SYS.EXECUTE_E
RROR_AS_USER (
      deferred_tran_id => '1.12.2904',
      destination => 'ORC2.WO
RLD');
END;
/