9 Managing Replication Objects and Queues

Disabling Replication

The DBMS_REPUTIL.REPLICATION_OFF procedure sets the state of an internal replication variable for the current session to FALSE. Because all replicated triggers check the state of this variable before queuing any transactions, modifications made to the replicated tables that use row-level replication do not result in any queued deferred transactions.

If you are using procedural replication, then call REPLICATION_OFF at the start of your procedure, as shown in the following example. This ensures that the replication facility does not attempt to use row-level replication to propagate the changes that you make.

CREATE OR REPLACE PACKAGE update_objects AS
  PROCEDURE update_emp(adjustment IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY update_objects AS
  PROCEDURE update_emp(adjustment IN NUMBER) IS
  BEGIN
   --turn off row-level replication for set update
   DBMS_REPUTIL.REPLICATION_OFF;
   UPDATE emp . . .;
   --reenable replication
   DBMS_REPUTIL.REPLICATION_ON;
  EXCEPTION WHEN OTHERS THEN
   . . . 
   DBMS_REPUTIL.REPLICATION_ON;
  END;
END;
/

Reenabling Replication

After resolving any conflicts, or at the end of your replicated procedure, be certain to call DBMS_REPUTIL.REPLICATION_ON to resume normal replication of changes to your replicated tables or materialized views. This procedure takes no arguments. Calling REPLICATION_ON sets the internal replication variable to TRUE.

Ensuring that Replicated Triggers Fire Only Once

If you have defined a replicated trigger on a replicated table, then you might need to ensure that the trigger fires only once for each change that you make. Typically, you only want the trigger to fire when the change is first made, and you do not want the remote trigger to fire when the change is replicated to the remote site.

You should check the value of the DBMS_REPUTIL.FROM_REMOTE package variable at the start of your trigger. The trigger should update the table only if the value of this variable is FALSE.

Alternatively, you can disable replication at the start of the trigger and reenable it at the end of the trigger when modifying rows other than the one that caused the trigger to fire. Using this method, only the original change is replicated to the remote sites. Then the replicated trigger fires at each remote site. Any updates performed by the replicated trigger are not pushed to any other sites.

Using this approach, conflict resolution is not invoked. Therefore, you must ensure that the changes resulting from the trigger do not affect the consistency of the data.

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

Using the RECTIFY Procedure

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:

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.

Step 2   Determine if it is necessary to check all rows and columns in the table for differences, or only a subset.

For example, it might 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 comprise the primary key (or that you designated as a substitute identity key) for the table.

Step 3   After determining which columns 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 the employee_id, salary, and department_id columns of the employees table, then your CREATE statement must be similar to the following:

CREATE TABLE hr.missing_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 data types 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 compare.

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.

CONNECT repadmin

BEGIN
   DBMS_REPCAT.SUSPEND_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 employees 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',
      reference_site      =>   'ny.example.com',
      sname2              =>   'hr',
      oname2              =>   'employees',
      comparison_site     =>   'mv4.example.com',
      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.example.com',
      max_missing         =>    500,
      commit_rows         =>    50);
END;
/

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

Figure 9-1 Determining Differences Between Replicas

Description of "Figure 9-1 Determining Differences Between Replicas"

Notice that the two missing rows tables are related by the ROWID 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 (
      sname1              =>   'hr',
      oname1              =>   'employees',
      reference_site      =>   'ny.example.com',
      sname2              =>   'hr',
      oname2              =>   'employees',
      comparison_site     =>   'mv4.example.com',
      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.example.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 DELETE operations and then performs all of the INSERT operations. 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.

Caution:

If you have any additional constraints on the "comparison" table, then you must ensure that they are not violated when you call RECTIFY. You might need to update the table directly using the information in the missing rows table. If so, then be sure to DELETE the appropriate rows from the missing rows tables.

Step 7   Repeat Steps 5 and 6 for the remaining copies of the replicated table.

Remember to use the same "reference" table each time to ensure that all copies are identical when you complete this procedure.

Step 8   Resume replication activity for the master group.

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

Pushing the Deferred Transaction Queue

Master sites are configured to push the deferred transaction queue automatically at set intervals. At materialized view sites, if you do not automatically propagate the transactions in your deferred transaction queue during the refresh of your materialized view, then you must complete the following steps to propagate changes made to the updatable materialized view to its master table or master materialized view.

This example illustrates pushing the deferred transaction queue at a materialized view site, but the process is the same at master sites and materialized view sites.

Executed As: Materialized View Administrator

Executed At: Materialized View Site

Complete the following steps:

Step 1   Connect to the materialized view site as the materialized view administrator.

CONNECT mviewadmin@mv1.example.com

Step 2   Execute the following SELECT statement to view the deferred transactions and their destinations.

Propagation of the deferred transaction queue is based on the destination of the transaction. Each distinct destination and the number of transactions pending for the destination will be displayed.

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

Step 3   Execute the DBMS_DEFER_SYS.PUSH function for each site that is listed as a destination for a deferred transaction.

DECLARE
   temp INTEGER;
BEGIN
   temp := DBMS_DEFER_SYS.PUSH (
      destination => 'orc1.example.com',
      stop_on_error => FALSE,
      delay_seconds => 0,
      parallelism => 0);
END;
/

Run the PUSH procedure for each destination that was returned in the SELECT statement you ran in Step 2.

Purging the Deferred Transaction Queue

If your system is not set to automatically purge the successfully propagated transactions in your deferred transaction queue periodically, then you must complete the following steps to purge them manually.

This example illustrates purging the deferred transaction queue at a materialized view site, but the process is the same at master sites and materialized view sites.

Executed As: Materialized View Administrator

Executed At: Materialized View Site

Complete the following steps:

Step 1   Connect to the materialized view site as the materialized view administrator.

CONNECT mviewadmin@mv1.example.com

Step 2   Purge the deferred transaction queue.

DECLARE

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

Note:

If you use the purge_method_quick parameter, deferred transactions and deferred procedure calls that have been successfully pushed can remain in the DEFTRAN and DEFCALL data dictionary views for longer than expected before they are purged. See the "Usage Notes" for DBMS_DEFER_SYS.PURGE for details.

Using the ANYDATA Type to Determine the Value of an Argument in a Deferred Call

If you are using column objects, collections, or REFs in a replicated table, then you can use the GET_ANYDATA_ARG function in the DBMS_DEFER_QUERY package to determine the value of an argument in a deferred call that involves one of these user-defined types.

The following example illustrates how to use the GET_ANYDATA_ARG function. This example uses the following user-defined types in the oe sample schema.

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

CREATE TYPE warehouse_typ 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 transaction id are available.

The user who creates the procedure must have EXECUTE privilege on the DBMS_DEFER_QUERY package and must have CREATE PROCEDURE privilege. This example uses the oe sample schema. Therefore, to run the example, you must grant the oe user these privileges. Connect as an administrative user and enter the following:

GRANT EXECUTE ON DBMS_DEFER_QUERY TO oe;

GRANT CREATE PROCEDURE TO oe;

CONNECT oe@orc1.example.com

CREATE OR REPLACE PROCEDURE get_userdef_arg AS
  call_no      NUMBER := 0;
  txn_id       VARCHAR2(128) := 'xx.xx.xx';
  anydata_val  ANYDATA;
  t            ANYTYPE;
  data_pl      phone_list_typ;     -- varray
  data_ntt     inventory_list_typ; -- nested table type
  data_p       warehouse_typ;      -- object type
  ref1         REF inventory_typ;  -- REF type
  rval         PLS_INTEGER;        -- return value
  tc           PLS_INTEGER;        -- return value
  prec         PLS_INTEGER;        -- precision
  scale        PLS_INTEGER;        -- scale
  len          PLS_INTEGER;        -- length
  csid         PLS_INTEGER;        -- character set id
  csfrm        PLS_INTEGER;        -- character set form
  cnt          PLS_INTEGER;        -- count of varray elements or number of
                                   -- object attributes
  sname        VARCHAR2(35);       -- schema name
  type_name    VARCHAR2(35);       -- type name
  version      VARCHAR2(35);
BEGIN
   FOR i IN 1 .. 5 LOOP
     anydata_val := DBMS_DEFER_QUERY.GET_ANYDATA_ARG(call_no, i, txn_id);
     -- Get the type information, including type name.
     tc := anydata_val.GetType(t);
     tc := t.GetInfo(prec, scale, len, csid, csfrm, sname, type_name,
                     version, cnt);
     -- Based on the type name, convert the anydata value to the appropriate 
     -- user-defined types.
     IF type_name = 'PHONE_LIST_TYP' THEN
       -- The anydata_val contains phone_list_typ varray instance.
       rval := anydata_val.GetCollection(data_pl);
       -- Do something with data_pl.
     ELSIF type_name = 'INVENTORY_LIST_TYP' THEN
       -- anydata_val contains inventory_list_typ nested table instance.
       rval := anydata_val.GetCollection(data_ntt);
       -- Do something with data_ntt.
     ELSIF type_name = 'WAREHOUSE_TYP' THEN
       -- The anydata_val contains warehouse_typ object instance.
       rval := anydata_val.GetObject(data_p);
       -- Do something with data_p.
     ELSIF type_name = 'INVENTORY_TYP' THEN
       -- The anydata_val contains a reference to inventory_typ object instance.
       rval := anydata_val.GetRef(ref1);
       -- Do something with ref1.
     END IF;
   END LOOP;
END;
/

Reexecuting Error Transaction as the Receiver

The following procedure reexecutes a specified deferred transaction in the security context of the user who received the deferred transaction. This procedure should not be executed until the error situation has been resolved.

Meet the following requirements to complete these actions:

Executed As: Replication Administrator

Executed At: Site Containing Errors

Replication Status: Normal

Complete the following steps:

Step 1   In SQL*Plus, connect to the master site as the replication administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

Step 2   Reexecute the error transaction.

BEGIN

DBMS_DEFER_SYS.EXECUTE_ERROR (
      deferred_tran_id => '1.12.2904',
      destination => 'orc2.example.com');
END;
/

Reexecuting Error Transaction as Alternate User

The following procedure reexecutes a specified deferred transaction in the security context of the currently connected user. This procedure should not be executed until the error situation has been resolved.

Meet the following requirements to complete these actions:

Executed As: Connected User

Executed At: Site Containing Errors

Replication Status: Normal

Complete the following steps:

Step 1   In SQL*Plus, connect to the master site as the alternate user.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

Step 2   Reexecute the error transaction.

BEGIN

DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER (
      deferred_tran_id => '1.12.2904',
      destination => 'orc2.example.com');
END;
/