Release 8.0
A54651-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Replication Release 8.0 A54651-01 |
|
This chapter describes several advanced techniques that you can use in implementing an Oracle replicated database environment:
Procedural replication can offer performance advantages for large batch-oriented operations operating on large numbers of rows that can be run serially within a 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 in "Avoiding Delete Conflicts" on page 7-20.
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, RAW, BLOB, CLOB, NCHAR, NVARCHAR, and NCLOB.
The advanced 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.
Serial execution ensures that your data remains consistent. The advanced 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:
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. Propagating the transaction serially ensures that A and B will be executing in serial order at the target site in all cases.
Alternatively, you could write the procedures carefully, to ensure serialization. For example, you could use SELECT ... FOR UPDATE for queries to ensure serialization at the originating site and at the target site if you are using parallel propagation.
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
See Disabling the Advanced Replication Facility on page 7-27.
When you generate replication support for your replicated package, the advanced replication facility creates a wrapper package in the schema of the replication propagator.
Note: Unregistering the current propagator drops all the existing generated wrappers in the propagator's schema. Replication support for wrapped stored procedures must be regenerated after a new propagator is registered.
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 master 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,
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 7-1, 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.
Asynchronous data propagation is the normal configuration for advanced replication environments. However, Oracle also supports synchronous data propagation for applications with special requirements. Synchronous data propagation occurs when an application updates a local replica of a table, and within the same transaction also updates all other replicas of the same table. Consequently, synchronous data replication is also called real-time data replication. Use synchronous replication only when applications require that replicated sites remain continuously synchronized.
Note: A replication system that uses real-time propagation of replication data is highly dependent on system and network availability because it can function only when all sites in the system are concurrently available.
The following sections explain more about synchronous data propagation and how to manage a replicated database system that uses synchronous data propagation.
As shown in Figure 7-2, whenever an application makes a DML change to a local replicated table and the replication group is using synchronous row-level replication, the change is synchronously propagated to the other master sites in the replicated environment using internal triggers. When the application applies a local change, the internal triggers issue calls to generated procedures at the remote master sites in the security context of the replication propagator. Oracle ensures that all distributed transactions either commit or rollback in the event of a failure.
Additional Information: See the discussion of distributed updates in Oracle8 Server Distributed Databases.
Because of the locking mechanism used by synchronous replication, deadlocks can occur. When an application performs a synchronous update to a replicated table, Oracle first locks the local row and then uses an AFTER ROW trigger to lock the corresponding remote row. The locks are released when the transaction commits at each site.
The necessary remote procedure calls to support synchronous replication are included in the internal trigger for each object. When you generate replication support for a replicated object, Oracle activates the triggers at all master sites to add the necessary remote procedure calls for the new site. Conversely, when you remove a master site from a master group, Oracle removes the calls from the internal triggers.
If all sites of a master group communicate synchronously with one another, applications should never experience an update conflict. However, if even one site is sending changes asynchronously to another site, applications can experience conflicts at any site in the replicated environment.
If the change is being propagated synchronously, an error is raised and a rollback will be required. If the change is propagated asynchronously, Oracle automatically detects the conflicts and either logs the conflict or, if you designate an appropriate resolution method, resolves the conflict.
Additional Information: See Chapter 5, "Conflict Resolution".
When you add a new master or snapshot site for to a replication group in an advanced replication environment, Replication Manager allows you to select the data propagation mode (method) for the new site.
See Chapter 3, "Using Multimaster Replication" and Chapter 4, "Using Snapshot Site Replication" for more information about adding master and snapshot sites to an advanced replication environment, respectively.
In some situations, you might decide to have a mixed-mode environment in which some master sites propagate a master group's changes asynchronously and others propagate changes synchronously. The order in which you add new master sites to a group with different data propagation modes can be important.
For example, suppose that you have three master sites: A, B, and C. If you first create site A as the master definition site, and then add site B with a synchronous propagation mode, site A will send changes to site B synchronously and site B will send changes to site A synchronously. There is no need to concern yourself with the scheduling of links at either site, because neither site is creating deferred transactions.
Now suppose that you create master site C with an asynchronous propagation mode. The propagation modes are now as illustrated in Figure 7-3.
You must now schedule propagation of the deferred transaction queue from site A to site C, from site B to site C, and from site C to sites A and B.
As another example, consider what would happen if you created site A as the master definition site, then added site C with an asynchronous propagation mode, then added site B with a synchronous propagation mode? Now the propagation modes would be as shown in Figure 7-4.
Each time that you add a new master site to a mixed-mode multimaster system, consider how the addition will affect the data propagation modes to and from existing sites.
Tip: You can view the data propagation modes between master group sites in a multimaster system quickly by using a Replication Manager destination map. See "Displaying a Destination Map for a Master Group" on page 3-32 for more information about master group destination maps.
To change the data propagation mode from one master site to another in a master group, use the destination map for the group in Replication Manager.
API Reference: DBMS_REPCAT.ALTER_MASTER_PROPAGATION
After you switch the propagation mode between one or more master sites in a master group:
Survivability provides the capability to continue running applications despite system or site failures. It allows applications to be run on a fail-over system, accessing the same, or very nearly the same, data as they were on the primary system when it failed. As shown in Figure 7-5, the Oracle Server provides two different technologies for accomplishing survivability: the Oracle Parallel Server and the advanced replication facility.
The Oracle Parallel Server supports fail-over to surviving systems when a system supporting an instance of the Oracle Server fails. The Oracle Parallel Server requires a cluster or massively parallel hardware platform, and thus is applicable for protection against processor system failures in the local environment where the cluster or massively parallel system is running.
In these environments, the Oracle Parallel Server is the ideal solution for survivability - supporting high transaction volumes with no lost transactions or data inconsistencies in the event of an instance failure. If an instance fails, a surviving instance of the Oracle Parallel Server automatically recovers any incomplete transactions. Applications running on the failed system can execute on the fail-over system, accessing all of the data in the database.
The Oracle Parallel Server does not, however, provide survivability for site failures (such as flood, fire, or sabotage) that render an entire site, and thus the entire cluster or massively parallel system, inoperable. To provide survivability for site failures, you can use the advanced replication facility to maintain a replica of a database at a geographically remote location.
Should the local system fail, the application can continue to execute at the remote site. Advanced replication, however, cannot guarantee the protection of all transactions. Also, special care must be taken to prevent data inconsistencies when recovering the primary site.
Note: You can also configure a standby-database to protect an Oracle database from site failures.
If you choose to use the advanced replication facility for survivability, you should consider the following issues:
Suppose, for example, you are running an order-entry system that uses replication to maintain a remote fail-over order-entry system, and the primary system fails.
At the time of the failure, there were two transactions recently executed at the primary site that did not have their changes propagated and applied at the fail-over site. The first of these was a transaction that entered a new order, and the second was a transaction that cancelled an existing order.
In the first case, someone may notice the absence of the new order when processing continues on the fail-over system, and re-enter it. In the second case, the cancellation of the order may not be noticed, and processing of the order may proceed; that is, the canceled item may be shipped and the customer billed.
What happens now, when you restore the primary site? If you simply push all of the changes executed on the fail-over system back to the primary system, you will encounter conflicts.
Specifically, there will be duplicate orders for the item originally ordered at the primary system just before it failed. Additionally, there will be data changes resulting from the transactions to ship and bill the order that was originally canceled on the primary system.
You must carefully design your system, as described in the next section, to deal with these situations.
Oracle's advanced replication facility can be used to provide survivability against site failures by using multiple replicated master sites. You must configure your system using one of the following methods. These methods are listed in order of increasing implementation difficulty.
By default, Oracle builds and populates replicas when you
When building a large replicated environment, the amount of data necessary to build and populate replicas throughout the system can generate an excessive amount of network traffic. To avoid saturating the network with the data necessary to build a large replicated environment, Oracle lets you perform offline instantiation of new sites in both basic and advanced replication systems. The following sections explain how to clone snapshots in a basic replication environment and offline instantiate master and snapshot sites in an advanced replication environment.
To reduce the network overhead associated with the creation of the same set of snapshots in many databases, you can perform snapshot "cloning" by following these steps:
Note: To make sure that you have prepared a snapshot database properly, connect to each snapshot schema in the database. Next, execute the defining queries of the proposed snapshots to ensure that they execute without error. Additionally, check to make sure that each new snapshot database has enough free space to hold the new snapshots.
Offline instantiation of a master site lets you add the new master site to a master group while limiting the amount of downtime required for existing sites in the multimaster replication system. Offline instantiation of a master site is useful primarily for systems with very large databases where the time required to transfer the data through network links to a new site would be prohibitive.
Assuming that you are using a default replication configuration that uses asynchronous row-level replication, the steps necessary to create a new master site using offline instantiation are as follows:
Additional Information: See Oracle8 Server Utilities to learn about the Import and Export utilities.
Please note the following:
Offline instantiation of a snapshot site in an advanced replication environment is useful when you need to create a large snapshot site, and the time required to transfer replication data through network to the new site would be prohibitive.
The steps necessary to create a snapshot site using offline instantiation are as follows:
CREATE SNAPSHOT sales.employee AS SELECT * FROM sales.emp@dbs1
Attention: Before creating snapshots, make sure that the master database has ample storage space.
Additional Information: See Oracle8 Server Utilities.
Snapshot site security can be configured using an alternative approach that provides both greater simplicity and security. However, the configuration must be setup using the replication management API. The Replication Manager setup wizard does not support this approach, nor can Replication Manager be used, in most cases, to administer snapshot sites that use this configuration.
This approach requires that all snapshots at the snapshot site, and corresponding master tables at the master site, be contained within a single schema. The schema owner is then authorized as the primary snapshot site administration for the specific schema and as the propagator for the snapshot site. Similarly, the schema owner is then authorized as the snapshot replication receiver at the master site. This approach is simpler and more secure because the snapshot site administrator does not need to be granted privileges to administer object in other schemas.
You can implement this approach using the following steps:
DBMS_REPCAT_ADMIN.GRANT_ADMIN_SCHEMA (userid => 'schema owner');
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'schema owner');
For example, assume your snapshot site has the global database name sales.widgetek.com and your master site has the global database name shipping.widgetek.com. At the snapshot site, create a private link to the master site as follows:
CREATE DATABASE LINK SHIPPING.WIDGETEK.COM
CONNECT TO 'snapshot_owner' IDENTIFIED BY 'snapshot_owner_password'
USING 'shipping.widgetek.com';
To avoid encountering delete conflicts, you might find it easiest to mark rows as deleted and purge them later. This section outlines a simple technique for purging these marked rows using procedural replication.
Suppose that your database contains the following MAIL_LIST table:
Name Null? Type
----------------------------- -------- --------------
CUSTNO NOT NULL NUMBER(4) PRIMARY KEY
CUSTNAME VARCHAR2(10)
ADDR1 VARCHAR2(30)
ADDR2 VARCHAR2(30)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP NUMBER(9)
PHONE NUMBER(10)
REMOVE_DATE DATE
Instead of deleting a customer when he or she requests to be removed from your mailing list, the REMOVE_DATE column would be used to indicate former customers; A NULL value would be used for current customers. After customers request removal from the mailing list, their rows are no longer updated. Such a convention avoids conflicts when the rows are actually deleted sometime later. A view of current customers could be defined as follows:
CREATE OR REPLACE VIEW corp.current_mail_list AS
SELECT custno, custname, addr1, addr2, city, state, zip, phone
FROM corp.mail_list WHERE remove_date IS NULL;
Periodically, perhaps once a year after the holiday sales, the former customers would be purged from the table using the REMOVE_DATE field. Such a delete could be performed using row-level replication just by performing the following delete:
DELETE corp.mail_list
WHERE remove_date IS NOT NULL AND remove_date<'01-JAN-95';
However, for a large company with an extensive mail order business, the number of former customers could be quite large resulting in a lot of undesired network traffic and database overhead. Instead, the procedural replication could be used using the following package:
CREATE OR REPLACE PACKAGE corp.purge AS
PROCEDURE remove_cust(purge_date IN DATE);
END;
/ CREATE OR REPLACE PACKAGE BODY corp.purge AS
PROCEDURE remove_cust(purge_date IN DATE) IS
BEGIN
-- turn off row-level replication for set delete
dbms_reputil.replication_off;
-- prevent phantom reads
LOCK TABLE corp.mail_list IN EXCLUSIVE MODE;
DELETE corp.mail_list WHERE remove_date IS NOT NULL AND
remove_date < purge_date;
dbms_reputil.replication_on;
EXCEPTION WHEN others THEN
dbms_reputil.replication_on;
END;
END;
The DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure would have been used to generate the DEFER_PURGE package during the initial replication setup. Then, the procedural replication package could be called as follows by a single master site:
BEGIN
defer_purge.remove_cust('14-APR-97','Y');
END;
The procedure, PURGE.REMOVE_CUST, would be executed locally and asynchronously executed at each master, resulting in many rows being deleted with only minimal network traffic.
To ensure that there are no outstanding transactions against the rows to be purged, your application should be written to never update logically deleted rows and the REMOVE_DATE should be old enough to ensure that the logical delete of the row is propagated before the row is purged. Thus, in the previous example, it is probably not necessary to lock the table in EXCLUSIVE mode; although this is another method of guaranteeing that these rows not be updated during the purge.
This section describes a more advanced method of designing your applications to avoid conflicts. This method, known as token passing, is similar to the workflow method described in Chapter 1. Although this section describes how to use this method to control the ownership of an entire row, you can use a modified form of this method to control ownership of the individual column groups within a row.
Both workflow and token passing allow dynamic ownership of data. With dynamic ownership, only one site at a time is allowed to update a row, but ownership of the row can be passed from site to site. Both work flow and token passing use the value of one or more "identifier" columns to determine who is currently allowed to update the row.
With workflow partitioning, you can think of data ownership as being "pushed" from site to site. Only the current owner of the row is allowed to push the ownership of the row to another site, by changing the value of the "identifier" columns.
Take the simple example of separate sites for ordering, shipping, and billing. Here, the identifier columns are used to indicate the status of an order. The status determines which site can update the row. After a user at the ordering site has entered the order, he or she updates the status of this row to SHIP. Users at the ordering site are no longer allowed to modify this row - ownership has been pushed to the shipping site.
After shipping the order, the user at the shipping site will update the status of this row to BILL, thus pushing ownership to the billing site, and so on.
To successfully avoid conflicts, applications implementing dynamic data ownership must ensure that the following conditions are met:
With workflow partitioning, only the current owner of the row can push the ownership of the row to the next site by updating the "identifier" columns. No site is given ownership unless another site has given up ownership; thus ensuring there is never more than one owner.
Because the flow of work is ordered, ordering conflicts can be resolved by applying the change from the site that occurs latest in the flow of work. Any ordering conflicts can be resolved using a form of the Priority conflict resolution method, where the priority value increases with each step in the work flow process.
The PRIORITY conflict resolution method successfully converges for more than one master as long as the priority value is always increasing.
Token passing uses a more generalized approach to meeting these criteria. To implement token passing, instead of the "identifier" columns, your replicated tables must have owner and epoch columns. The owner column stores the global database name of the site currently believed to own the row.
Once you have designed a token passing mechanism, you can use it to implement a variety of forms of dynamic partitioning of data ownership, including workflow.
You should design your application to implement token passing for you automatically. You should not allow the owner or epoch columns to be updated outside this application.
Whenever you attempt to update a row, your application should
For example, Figure 7-6 illustrates how ownership of employee 100 passes from the ACCT_SF database to the ACCT_NY database.
To grab ownership, the ACCT_NY database uses a simple recursive algorithm to locate the owner of the row. The pseudo code for this algorithm is shown below:
-- Pseudo code for locating the token owner.
-- This is for a table TABLE_NAME with primary key PK.
-- Initial call should initialize loc_epoch to 0 and loc_owner
-- to the local global name.
get_owner(PK IN primary_key_type, loc_epoch IN OUT NUMBER,
loc_owner IN OUT VARCHAR2)
{
-- use dynamic SQL (dbms_sql) to perform a select similar to
-- the following:
SELECT owner, epoch into rmt_owner, rmt_epoch
FROM TABLE_NAME@loc_owner
WHERE primary_key = PK FOR UPDATE;
IF rmt_owner = loc_owner AND rmt_epoch >= loc_epoch THEN
loc_owner := rmt_owner;
loc_epoch := rmt_epoch;
RETURN;
ELSIF rmt_epoch >= loc_epoch THEN
get_owner(PK, rmt_epoch, rmt_owner);
loc_owner := rmt_owner;
loc_epoch := rmt_epoch;
RETURN;
ELSE
raise_application_error(-20000, 'No owner for row');
END IF;
}
After locating the owner of the row, the ACCT_NY site grabs ownership from the ACCT_SF site by completing the following steps:
When the SF changes (that were in the deferred queue in Step 2 above are ultimately propagated to the NY site, the NY site will ignore them, because they will have a lower epoch number than the epoch number at the NY site for the same data.
As another example, suppose the HQ site received the SF changes after receiving the NY changes, the HQ site would ignore the SF changes because the changes applied from the NY site would have the greater epoch number.
You should design your application to implement this method of token passing for you automatically whenever you perform an update. You should not allow the owner or epoch columns to be updated outside this application. The lock that you grab when you change ownership is released when you apply your actual update. The changed information, along with the updated owner and epoch information, will be asynchronously propagated to the other sites in the usual manner.
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:
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 reexecute the error transaction. 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 can use Replication Manager 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.
Note: To enable and disable replication, you must have the EXECUTE privilege on the DBMS_REPUTIL package.
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.
Attention: Turning replication on or off affects only the current session. 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 advanced 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;
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 internal replication variable to TRUE.
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.
To disable all local replication triggers for snapshots at your current site, set the internal refresh variable 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 internal refresh variable to FALSE, as shown below:
DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(value => FALSE);
To determine the value of the internal refresh variable, call the I_AM_A_REFRESH function as shown below:
ref_stat := DBMS_SNAPSHOT.I_AM_A_REFRESH;
This section explains how you can minimize data propagation and provides examples.
You can minimize data propagation by setting the min_communication parameter to TRUE in the following DBMS_REPCAT procedures:
Note: If you need to propagate to a master site running Oracle7 release 7.3, min_communication must be set to false.
If min_communication is set to TRUE, you can further reduce data propagation in some cases by using the DBMS_REPCAT.SEND_AND_ COMPARE_OLD_VALUES procedure to only send old values that are needed for conflict detection and resolution.
Suggestion: Further minimizing propagation of old values is particularly valuable if you are replicating LOB datatypes and do not expect conflicts on these columns.
When min_communication is set to TRUE, Oracle propagates only
Additional Information: See "Understanding Column Groups" on page 5-3.
Note: When min_communication is set to FALSE, Oracle propagates the old and new values of all columns in a row when any column in the row is updated. (This is the behavior expected by Oracle7 release 7.3.)
In the table below, columns 1 and 3 together compose the primary key. There are two column groups, columns 1 - 3 and columns 4 - 6.
If you set min_communication to FALSE, Oracle sends six old values (C1 -C6) and six new values (C1 - C6) for any update.
For example, if you update column C4,
If you set min_communication to TRUE, Oracle minimizes communication.
If you update column C4, Oracle sends:
If you update columns C2 and C4, Oracle sends:
If you update column 2, Oracle sends:
If you have minimized propagation using the method described above you can further reduce data propagation in some cases by using the DBMS_REPCAT.SEND_AND_COMPARE_OLD_VALUES procedure to send old values only if they are needed to detect and resolve conflicts. For example, the latest timestamp conflict detection and resolution method does not require old values for non-key and non-timestamp columns
Suggestion: Further minimizing propagation of old values is particularly valuable if you are replicating LOB datatypes and do not expect conflicts on these columns.
Attention: You must ensure that the appropriate old values are propagated to detect and resolve anticipated conflicts. User-supplied conflict resolution procedures must deal properly with NULL old column values that are transmitted. Using SEND_AND_COMPARE_OLD_VALUES to further reduce data propagation reduces protection against unexpected conflicts.
To further reduce data propagation execute the following procedure:
DBMS_REPCAT.VARCHAR2sDBMS_REPCAT.SEND_AND_COMPARE_OLD_VALUES(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_list IN VARCHAR2 |
column_table IN DBMS_REPCAT.VARCHAR2s
operation IN VARCHAR2 := 'UPDATE',
send IN BOOLEAN := TRUE);
After executing this procedure, you must generate replication support again with min_communication set to TRUE for this change to take effect.
Note: The operation parameter allows you to decide whether or not to transmit old values for non-key columns when rows are deleted and/or when non-key columns are updated. If you do not send the old value, Oracle sends a NULL in place of the old value and assumes the old value is equal to the current value of the column at the target side when the update or delete is applied.
The specified behavior for old column values is exposed in two columns in the REPCOLUMN view: COMPARE_OLD_ON_DELETE (`Y' or `N') and COMPARE_OLD_ON_UPDATE (`Y' or `N').
The following example shows how you can further reduce data propagation by using SEND_AND_COMPARE_OLD_VALUES. Consider a table called 'SCOTT.REPORTS' with 3 columns. Column 1 is the primary key and is in its own column group (column group 1). Column 2 and column 3 are in a second column group (column group 2).
The conflict resolution strategy for the second column group is site priority. Column 2 is a VARCHAR2 column containing the site name. Column 3 is a LOB column. Whenever you update the LOB, you must also update column 2 with the global name of the site at which the update occurs. Because there are no triggers for piecewise updates to LOBs, you must explicitly update column 2 whenever you do a piecewise update on the LOB.
Suppose you generate replication support for SCOTT.REPORTS with min_communication set to TRUE and then use an UPDATE statement to modify column 2 (the site name) and column 3 (the LOB). The deferred remote procedure call (RPC) contains the new value of the site name and the new value of the LOB because they were updated. The deferred RPC also contains the old value of the primary key (column 1), the old value of the site name (Column 2), and the old value of the LOB (Column 3).
Note: The conflict detection and resolution strategy does not require the old value of the LOB. Only column C2 (the site name) is required for both conflict detection and resolution. Sending the old value for the LOB could add significantly to propagation time.
To ensure that the old value of the LOB is not propagated when either column C2 or column C3 is updated, make the following call:
DBMS_REPCAT.SEND_AND_COMPARE_OLD_VALUES(
sname => 'SCOTT',
oname => 'REPORTS'
column_list => 'C3',
operation => 'UPDATE',
send => FALSE);
You must generate replication support for SCOTT.REPORTS again with min_communication set to TRUE for this change to take effect. Suppose you subsequently use an UPDATE statement to modify column 2 (the site name) and column 3 (the LOB). The deferred RPC contains the old value of the primary key (column 1), the old and new values of the site name (column 2), and just the new value of the LOB (column 3). The deferred RPC contains NULLs for the new value of the primary key and the old value of the LOB.
Additional Information: The parameters for the SEND_AND_COMPARE_OLD_VALUES procedure are described in Table 10-182 on page 10-148 and the exceptions are described in Table 10-183 on page 10-148.
