Oracle7 Server Distributed Systems Volume II: Replicated Data

Modifying Tables without Replicating the Modifications

There may be times when you want to make a modification to a replicated object, but you do not want this modification replicated to the other sites in the replicated environment. For example, you might want to disable replication in the following situations:

When you are using procedural replication to propagate a change, you should 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 as described .

Sometimes when you manually resolve a conflict, you might not want to replicate this modification to the other copies of the table.

You might 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 reapply it by calling DBMS_DEFER_SYS.EXECUTE_ERROR. Or you might 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 would call DBMS_DEFER_SYS.DELETE_ERROR to delete the conflicting transaction from the error tables at 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.

Note: You must be granted the EXECUTE privilege on the DBMS_REPUTIL package.

Disabling the Symmetric Replication Facility

The DBMS_REPUTIL.REPLICATION_OFF procedure sets the state of the DBMS_REPUTIL.REPLICATION_IS_ON package 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.

Attention: Because REPLICATION_IS_ON is a variable in a PL/SQL package specification, its state is session bound. That is, other users logged on to the same schema are not restricted from placing committed changes in the deferred transaction queue.

If you are using procedural replication, you should call REPLICATION_OFF at the start of your procedure, as shown in the following example. This ensures that the symmetric 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;

Re-enabling the Symmetric 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 snapshots. This procedure takes no arguments. Calling REPLICATION_ON sets the package variable DBMS_REPUTIL.REPLICATION_IS_ON to TRUE.

Triggers and Replication

If you have defined a replicated trigger on a replicated table, you may need to ensure that the trigger fires only once for each change that you make. Typically, you will only want the trigger to fire when the change is first made, and you will 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 will fire at each remote site. Any updates performed by the replicated trigger will not be 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.

Enabling/Disabling Replication for Snapshots

To disable all local replication triggers for snapshots at your current site, set the I_AM_A_REFRESH package state to TRUE by calling SET_I_AM_A_REFRESH, as shown in the following example:

DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(value => TRUE);

To re-enable the triggers set the package state to FALSE, as shown below:

DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(value => FALSE);

To determine the value of the package variable REP$WHAT_AM_I.I_AM_A_SNAPSHOT, call the I_AM_A_REFRESH function as shown below:

ref_stat := dbms_snapshot.i_am_a_refresh;

To check if a snapshot is refreshing or if a master site has replication turned off, you can also call the ACTIVE function in each table's corresponding $TP package.