Oracle7 Server Distributed Systems Volume II: Replicated Data

Using Procedural Replication

Procedural replication can offer performance advantages for large batch-oriented operations operating on large numbers of rows that can be run serially within your replicated environment.

A good example of an appropriate application is a purge operation (also referred to as an archive operation) run infrequently (for example, once per quarter) during off hours to remove old data, or data that was "logically" deleted from the online database. An example using procedural replication to purge deleted rows is described .

Restrictions on Procedural Replication

All parameters for a replicated procedure must be IN parameters; OUT and IN/OUT modes are not supported. The datatypes supported for these parameters are: NUMBER, DATE, VARCHAR2, CHAR, ROWID and RAW.

The symmetric replication facility cannot detect update conflicts produced by replicated procedures. Replicated procedures must detect and resolve conflicts themselves. Because of the difficulties involved in writing your own conflict resolution routines, it is best to simply avoid the possibility of conflicts.

Adhering to the following guidelines will help you ensure that your tables remain consistent at all sites.

You must disable row-level replication within the deferred procedure as described .

Only one replicated procedure should be executed at a time, as described in the next section, "Serialization of Transactions".

The replicated procedure must be packaged and the package cannot contain any functions. Standalone deferred procedures and standalone or packaged deferred functions are not currently supported.

The deferred procedures must reference only locally owned data.

The procedures should not use locally generated fields or values (for example, the procedure should not call SYSDATE).

Your data ownership should be statically partitioned (that is, ownership of a row should not change between sites)

Serialization of Transactions

Serial execution ensures that your data remains consistent. The symmetric replication facility propagates and executes replicated transactions one at a time. For example, assume that you have two procedures, A and B, that perform updates on local data. Now assume that you perform the following actions, in order:

1. execute A and B locally

2. queue requests to execute other replicas of A and B on other nodes

3. commit The replicas of A and B on the other nodes are executed completely serially, in the same order that they were committed at the originating site. If A and B execute concurrently at the originating site, however, they may produce different results locally than they do remotely. Executing A and B serially at the originating site ensures that all sites have identical results. Alternatively, you could write the procedures carefully, to ensure serialization. For example, you could lock any tables being updated in EXCLUSIVE mode to ensure serialization at the originating site.

Generating Support for Replicated Procedures

You must disable row-level replication support at the start of your procedure, and then re-enable support at the end. This ensures that any updates that occur as a result of executing the procedure are not propagated to other sites. Row-level replication is enabled and disabled by calling DBMS_REPUTIL.REPLICATION_ON and DBMS_REPUTIL.REPLICATION_OFF, as described

When you generate replication support for your replicated package, the symmetric replication facility creates a wrapper package. The wrapper package has the same name as the original, but is prefixed with the string that you supplied when you called DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT.

If you did not supply a prefix, the default, "defer_", is used. The wrapper procedure has the same parameters as the original, along with two additional parameters: CALL_LOCAL and CALL_REMOTE. These two boolean parameters determine where the procedure gets executed. When CALL_LOCAL is TRUE, the procedure is executed locally. When CALL_REMOTE is TRUE, the procedure will ultimately be executed at all other sites in the replicated environment.

The remote procedures are called directly if you are propagating changes synchronously, or the calls to these procedures are added to the deferred transaction queue, if you are propagating changes asynchronously. By default, CALL_LOCAL is FALSE, and CALL_REMOTE is TRUE.

Replication support is generated in two phases. The first phase creates the package specification at all sites. Phase two generates the package body at all sites. These two phases are necessary to support synchronous replication.

For example, suppose that you create the package UPDATE containing the procedure UPDATE_EMP, which takes one argument, AMOUNT. You replicate this object to all master sites in your replicated environment by making the following calls:

DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    sname               => 'acct_rec', 
    oname               => 'update', 
    type                => 'package', 
    use_existing_object => FALSE,
    retry               => FALSE,
    copy_rows           => TRUE,
    gname               => 'acct');

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
    sname          => 'acct_rec', 
    oname          => 'update', 
    type           => 'package',
    package_prefix => 'defer_',);

You would now invoke the replicated procedure as shown below:

defer_update.update_emp( amount      => 1.05,
                         call_local  => TRUE,
                         call_remote => TRUE);

As shown in Figure 8 - 3, the logic of the wrapper procedure ensures that the procedure is called at the local site and then at all remote sites. The logic of the wrapper procedure also ensures that when the replicated procedure is called at the remote sites, CALL_REMOTE is FALSE, ensuring that the procedure is not further propagated.

If you are operating in a mixed replicated environment with static partitioning of data ownership (that is, if you are not preventing row-level replication), the replication facility will preserve the order of operations at the remote node, since both row-level and procedural replication use the same asynchronous queue.

Figure 8 - 3. Asynchronous Procedural Replication