9
Managing Replication Objects and Queues

This chapter illustrates how to manage the replication objects and queues in your replication environment using the replication management API. This chapter contains these topics:

• Altering a Replicated Object
• Modifying Tables without Replicating the Modifications
• Converting a LONG Column to a LOB Column in a Replicated Table
• Determining Differences Between Replicated Tables
• Managing the Deferred Transactions Queue
• Managing the Error Queue

Altering a Replicated Object

As your database needs change, you may need to modify the characteristics of your replicated objects. It is important that you do not directly execute DDL to alter your replicated objects. Doing so may cause your replication environment to fail.

Altering a Replicated Object in a Quiesced Master Group

Use the ALTER_MASTER_REPOBJECT procedure in the DBMS_REPCAT package to alter the characteristics of your replicated objects in a quiesced master group. From the example below, notice that you simply include the necessary DDL within the procedure call (see the ddl_text parameter).

If any master site is lower than 9.0.0 compatibility level, then you must use the following procedure. That is, the master group must be quiesced to modify a replicated object. You control the compatibility level of a database with the COMPATIBLE initialization parameter.

Meet the following requirements to complete these actions:

Executed As: Replication Administrator

Executed At: Master Definition Site

Replication Status: Quiesced

Complete the following steps to alter a replicated object in a quiesced master group.

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Connect to the master definition site as the replication administrator.

*/

SET ECHO ON

SPOOL alter_rep_object.out

CONNECT repadmin/repadmin@orc1.world

/*

Step 2 If necessary, then quiesce the master group. See the "ALTER_MASTER_REPOBJECT Procedure" for information about when quiesce is not required.

*/

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

/*

Step 3 In a separate SQL*Plus session, check the status of the master group you are quiescing, and do not proceed until the group's status is QUIESCED.

To check the status, run the following query:

SELECT GNAME, STATUS FROM DBA_REPGROUP;

*/

PAUSE Press <RETURN> to continue when the master group's status is QUIESCED.

/*

Step 4 Alter the replicated object.

*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'hr',
      oname => 'employees',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE hr.employees ADD (timestamp DATE)';
END;
/

/*

Step 5 Regenerate replication support for the altered object.

*/

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

/*

Step 6 In a separate SQL*Plus session, check if DBA_REPCATLOG is empty, and do not proceed until it is empty.

Execute the following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:

SELECT * FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';

*/

PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty.

/*

Step 7 Resume replication activity.

*/

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

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

Modifying Tables without Replicating the Modifications

You may have a situation in which you need to modify a replicated object, but you do not want this modification replicated to the other sites in the replication environment. For example, you may want to disable replication in the following situations:

• When you are using procedural replication to propagate a change, always disable row-level replication at the start of your procedure.
• You may need to disable replication in triggers defined on replicated tables to avoid replicating trigger actions multiple times. See "Ensuring That Replicated Triggers Fire Only Once".
• Sometimes when you manually resolve a conflict, you may not want to replicate this modification to the other copies of the table.

You may need to do this, for example, if you need to correct the state of a record at one site so that a conflicting replicated update will succeed when you reexecute the error transaction. Or, you may use an unreplicated modification to undo the effects of a transaction at its origin site because the transaction could not be applied at the destination site. In this example, you can use the Replication Management tool to delete the conflicting transaction from the destination site.

To modify tables without replicating the modifications, use the REPLICATION_ON and REPLICATION_OFF procedures in the DBMS_REPUTIL package. These procedures take no arguments and are used as flags by the generated replication triggers.

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 AS
  PROCEDURE update_emp(adjustment IN NUMBER);
END;
/

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

Reenabling the Replication Facility

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 may 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 re-enable 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.

Converting a LONG Column to a LOB Column in a Replicated Table

LOB columns can be replicated, but LONG columns cannot be replicated. You can convert the datatype of a LONG column to a CLOB column and the datatype of a LONG_RAW column to a BLOB column.

Converting a LONG column to a LOB column can result in increased network bandwidth requirements because the data in such a column is replicated after conversion. Make sure you have adequate network bandwidth before completing the procedure in this section.

Complete the following steps to convert a LONG column to a LOB column in a replicated table:

Step 1 Make sure the data in the LONG column is consistent at all replication sites.

If a table containing a LONG column is configured as a master table, then Oracle does not replicate changes to the data in the LONG column. Therefore, the data in the LONG column may not match at all of your replication sites. You must make sure the data in the LONG column matches at all master sites before proceeding.

Step 2 Connect to the master definition site as the replication administrator. For example:

CONNECT repadmin/repadmin@orc1.world

Step 3 If the replication status is normal, then change the status to quiesced. For example:

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

Step 4 Run the ALTER_MASTER_REPOBJECT procedure in the DBMS_REPCAT package to convert the LONG column to a LOB column. For example:

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'staff',
      oname => 'positions',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE positions MODIFY (job_desc CLOB)');
END;
/

A LONG_RAW column can be converted to a BLOB column using a similar ALTER TABLE statement.

Step 5 Regenerate replication support for the altered master table. For example:

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

Step 6 Resume replication. For example:

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

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

Determining Differences Between Replicated Tables

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 differences between two tables when both sites are Oracle release 7.3 or higher.

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

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 would need to 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 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 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.

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

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

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.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.world',
      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 would look like if you executed the DIFFERENCES procedure on these replicas.

Figure 9-1 Determining Differences Between Replicas

Text description of the illustration repma003.gif

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 by calling the RECTIFY procedure in the as shown in the DBMS_RECTIFIER_DIFF package following example:

BEGIN
   DBMS_RECTIFIER_DIFF.RECTIFY (
      sname1              =>   '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 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.

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;
/

Managing the Deferred Transactions Queue

Typically, Advanced Replication is configured to push and purge the deferred transaction queue automatically. At times, however, you may need to push or purge the deferred transaction queue manually. The process for pushing the deferred transaction queue is the same at master sites and materialized view sites.

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/mviewadmin@mv1.world

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.world',
      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/mviewadmin@mv1.world

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;
/

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 system/manager as sysdba

GRANT EXECUTE ON DBMS_DEFER_QUERY TO oe;

GRANT CREATE PROCEDURE TO oe;

CONNECT oe/oe@orc1.world

CREATE OR REPLACE PROCEDURE get_userdef_arg AS
  call_no      NUMBER := 0;
  txn_id       VARCHAR2(128) := 'xx.xx.xx';
  anydata_val  Sys.AnyData;
  t            SYS.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;
/

Managing the Error Queue

As an administrator of a replication environment, you should regularly monitor the error queue to determine if any deferred transactions were not successfully applied at the target master site.

To check the error queue, issue the following SELECT statement (as the replication administrator) when connected to the target master site:

SELECT * FROM deferror;

If the error queue contains errors, then you should resolve the error condition and reexecute the deferred transaction. You have two options when reexecuting a deferred transaction: you can reexecute in the security context of the user who received the deferred transaction, or you can reexecute the deferred transaction with an alternate security context.

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 Connect to the master site as the replication administrator.

CONNECT repadmin/repadmin@orc2.world

Step 2 Reexecute the error transaction.

BEGIN
   DBMS_DEFER_SYS.EXECUTE_ERROR (
      deferred_tran_id => '1.12.2904',
      destination => 'ORC2.WORLD');
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 Connect to the master site as the alternate user.

CONNECT hr/hr@orc2.world

Step 2 Reexecute the error transaction.

BEGIN
   DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER (
      deferred_tran_id => '1.12.2904',
      destination => 'ORC2.WORLD');
END;
/