2 Master Replication Concepts and Architecture

Multimaster Replication

Multimaster replication, also known as peer-to-peer or n-way replication, consists of multiple master sites equally participating in an update-anywhere model. Updates made to an individual master site are propagated (sent) to all other participating master sites. Figure 2-1 illustrates a multimaster replication system.

Oracle database servers operating as master sites in a multimaster replication environment automatically work to converge the data of all table replicas, and ensure global transaction consistency and data integrity. Conflict resolution is independently handled at each of the master sites. Multimaster replication provides complete replicas of each replicated table at each of the master sites.

If the replication environment is a hybrid environment (it has multiple master sites supporting one or more materialized view sites), then the target master site propagates any of the materialized view updates to all other master sites in the multimaster replication environment. Then each master site propagates changes to their materialized views when the materialized views refresh.

Figure 2-1 Multimaster Replication

Description of "Figure 2-1 Multimaster Replication"

Single Master Replication

A single master site can also function as the target master site for one or more materialized view sites. Unlike multimaster replication, where updates to a single site are propagated to all other master sites, materialized views update only their target master site.

Conflict resolution is handled only at master sites or master materialized view sites. Materialized view replication can contain complete or partial replicas of the replicated table.

Master Sites

A master site can be both a node in a multimaster replication environment and the master for one or more materialized view sites in a single master or multimaster replication environment. The replicated objects are stored at the master site and are available for user access.

Failover

Multimaster replication can protect the availability of a mission critical database. For example, a multimaster replication environment can replicate data in your database to establish a failover site should the primary site become unavailable due to system or network outages. Such a failover site can also serve as a fully functional database to support application access when the primary site is concurrently operational.

You can use Oracle Net to configure automatic connect-time failover, which enables Oracle Net to fail over to a different master site if the first master site fails. You configure automatic connect-time failover in your tnsnames.ora file by setting the FAILOVER_MODE parameter to on and specifying multiple connect descriptors.

Load Balancing

Multimaster replication is useful for transaction processing applications that require multiple points of access to database information for the following purposes:

• Distributing a heavy application load

• Ensuring continuous availability

• Providing more localized data access

Applications that have application load distribution requirements commonly include customer service oriented applications.

Figure 2-2 Multimaster Replication Supporting Multiple Points of Update Access

Description of "Figure 2-2 Multimaster Replication Supporting Multiple Points of Update Access"

Support for Disconnected Materialized View Environments

Materialized view replication enables users to remotely store all or a subset of replicated data from a master site in a disconnected environment. This scenario is typical of sales force automation systems where an individual's laptop (a disconnected device) stores a subset of data related to the individual salesperson.

Master sites operate as the target of the materialized view environment. Master site support can be:

• A single master site supporting all of the materialized views, which reduces the possibility of divergent data because conflict resolution is performed only at master sites or master materialized view sites (in a multitier materialized view environment).

• A combination of multimaster and materialized view replication where groups of materialized views are targeted to different masters of the multimaster configuration. This configuration distributes the network load across multiple master nodes, providing improved scalability and availability should one of the master nodes become unavailable.

Oracle Real Application Clusters Compared with Replication

The two major areas where you must consider whether Advanced Replication or Oracle Real Application Clusters (Oracle RAC) better serves your needs are load balancing and survivability.

• Load Balancing: Advanced Replication provides read load balancing over multiple databases, while Oracle RAC provides read and write load balancing over multiple instances. Because each write must be performed at each replication site, replication does not offer write load balancing.

• Survivability: Replication provides greater survivability protection with regards to natural disasters, power outages, or sabotage, or both because the remaining replication sites can be positioned in a geographically different region. Oracle RAC operates on a cluster or other massively parallel system and is located in the same physical environment, and thus cannot protect against the physical problems that replication can protect against.

• Interoperability: Advanced Replication can replicate data between different platforms and operating systems that are running Oracle. The instances in an Oracle RAC environment must run on the same platform.

Asynchronous Replication

Asynchronous replication independently propagates any DML or replicated procedure execution to all of the other master sites participating in the multimaster replication environment. Propagation occurs in a separate transaction after the DML or replication procedure has been executed locally.

Asynchronous replication is the default mode of replication. Asynchronous replication requires less networking and hardware resources than does synchronous replication, resulting in better availability and performance.

Asynchronous replication, however, means that the data sets at the different master sites in the replication environment can be different for a period of time before the changes have been propagated. Also, data conflicts can occur in an asynchronous replication environment.

The following describes the process of asynchronous replication:

1. A user issues DML statement or executes a wrapper for a replicated procedure.

   After a table has been set up for replication, any DML that a user commits on the table is captured for replication to all other master sites.

   For each row that is inserted, updated, or deleted, an internal trigger creates a deferred remote procedure call (RPC) and places it in the deferred transaction queue. The deferred transaction queue contains all deferred RPCs.

   If a procedure has been replicated and its wrapper is executed at a master site, then the procedure call is placed in the deferred transaction queue.

2. The deferred transaction queue stores deferred RPCs.

   Each transaction in the deferred transaction queue has a list of destinations that define where the deferred transaction should be propagated; this list contains all master sites except for the originating site. There is one deferred transaction queue for each site, and this one queue can be used by multiple replication groups.

3. Propagation sends deferred transaction queue entry to destination.

   At scheduled intervals or on-demand, the deferred transactions in the deferred transaction queue are propagated to the target destinations. Each destination can have a different interval.

4. The deferred transaction queue entry applied at a remote destination.

   As a deferred transaction is being propagated to a target destination, each deferred RPC is applied at the destination site by calling an internal package. If the deferred transaction cannot be successfully applied at the destination site, then it is resent and placed into the error queue at the destination site, where the DBA can fix the error condition and re-apply the deferred transaction.

   When a deferred transaction queue entry is applied at the remote destination, Oracle checks for data conflicts. If a conflict is detected, then it is logged at the remote location and, optionally, a conflict resolution method is invoked.

5. When a deferred transaction has been successfully pushed to all remote master sites, it is not purged from the deferred transaction queue at the originating site immediately. It can be purged later by a purge job, which runs at a user-defined interval.

Synchronous Replication

Synchronous replication propagates any changes made at a local site to other synchronously linked masters in a replication environment during the same transaction as the initial change. If the propagation fails at any of the master sites, then the entire transaction, including the initial change at the local master site, rolls back. This strict enforcement ensures data consistency across the replication environment. Unlike asynchronous replication, there is never a period of time when the data at any of the master sites does not match.

Synchronous replication also ensures that no data conflicts are introduced into the replication environment. These benefits have the cost of requiring many hardware and networking resources with no flexibility for downtime. For example, if a single master site of a six node multimaster environment is unavailable, then a transaction cannot be completed at any master site.

However, in asynchronous replication, the deferred transaction is held at the originating site until the downed site becomes available. Meanwhile, the transaction can be successfully propagated and applied at other replication sites.

Additionally, while query performance remains high because they are performed locally with synchronous replication, updates are slower because of the two-phase commit protocol that ensures that any updates are successfully propagated and applied to the remote destination sites.

The following describes the process of synchronous replication:

1. User issues DML statement or executes a wrapper for a replicated procedure.

   After a table has been set up for replication, any DML that a user commits on the target table is captured for replication to all other master replication sites.

   If a procedure has been replicated and its wrapper is executed at a master site, then the procedure call is captured for replication.

2. DML or wrapper execution is immediately propagated to destination sites.

   The internal trigger captures any DML and immediately propagates these actions to all other master sites in the replication environment. The internal trigger applies these actions in the security context of the propagator's database link and uses an internal RPC to apply these actions at the destination site.

   Like an internal trigger, a wrapper for a replicated procedure immediately propagates the procedure call to all other master sites in the replication environment.

   If the transaction fails at any one of the master replication sites, then the transaction is rolled back at all master sites. This methodology ensures data consistency across all master replication sites. Because of the need to roll back a transaction if any site fails, synchronous replication is extremely dependent on highly-available networks, databases, and the associated hardware.

Type Agreement at Replication Sites

User-defined types include all types created using the CREATE TYPE statement, including object, nested table, and VARRAY. To replicate schema objects based on user-defined types, the user-defined types themselves must exist, and must be the same, at all replication sites.

When replicating user-defined types and the schema objects on which they are based, the following conditions apply:

• All replication sites must have the same object identifier (OID), schema owner, and type name for a replicated user-defined type.

• If the user-defined type is an object type, then all replication sites must agree on the order and data type of the attributes in the object type. You establish the order and data types of the attributes when you create the object type. For example, consider the following object type:

  CREATE TYPE cust_address_typ AS OBJECT
       (street_address     VARCHAR2(40), 
        postal_code        VARCHAR2(10), 
        city               VARCHAR2(30), 
        state_province     VARCHAR2(10), 
        country_id         CHAR(2));
  /
  

  At all replication sites, street_address must be the first attribute for this type and must be VARCHAR2(40), postal_code must be the second attribute and must be VARCHAR2(10), city must be the third attribute and must be VARCHAR2(30), and so on.

• All replication sites must agree on the hashcode of the user-defined type. Oracle examines a user-defined type and assigns the hashcode. This examination includes the type attributes, order of attributes, and type name. When all of these items are the same for two or more types, the types have the same hashcode. You can view the hashcode for a type by querying the DBA_TYPE_VERSIONS data dictionary view.

To ensure that a user-defined type is the same at all replication sites, you must create the user-defined type in one of the following ways:

• Use the Replication Management API

• Use a CREATE TYPE Statement

• Use Export/Import

SELECT TYPE_OID FROM DBA_TYPES WHERE TYPE_NAME = 'CUST_ADDRESS_TYP';

TYPE_OID
--------------------------------
6F9BC33653681B7CE03400400B40A607

SELECT SYS_GUID() OID FROM DUAL;

Object Tables and Replication

When you replicate object tables, the following conditions apply:

• The OID of an object table must be the same at all replication sites.

• The OID of each row object in an object table must be the same at all replication sites.

You can meet these conditions by using the replication management API to add object tables to a replication group, modify object tables, and drop object tables from a replication group. For example, if you use the CREATE_MASTER_REPOBJECT procedure in the DBMS_REPCAT package to add an object table to a master group, then Oracle ensures that these conditions are met. You can also use export/import to precreate object tables at replication sites to meet these conditions.

Another option is to specify the OID for an object table when you create the object table at multiple replication sites. Complete the following steps to use this option:

1. Query the DUAL view for a globally unique OID:

   SELECT SYS_GUID() OID FROM DUAL;
   
   OID
   --------------------------------
   81D98C325D4A45D0E03408002074B239
   

2. Create the categories_tab object table with the OID returned in Step 1 at each replication site:

   CREATE TABLE oe.categories_tab5 OF oe.category_typ 
      OID '81D98C325D4A45D0E03408002074B239'
      (category_id PRIMARY KEY);
   

Tables with Collection Columns

Collection columns are columns based on VARRAY and nested table data types. Oracle supports the replication of collection columns. When you add a table with a collection column to a replication group, the data in the collection column is replicated automatically. If the collection column is a varray, then a varray larger than four kilobytes is stored as a BLOB.

If the collection column is a nested table, then Oracle Database performs row-level replication for each row in the nested table's storage table. For example, changes in five rows of a storage table result in five distinct remote procedure calls (RPCs), and five distinct conflict detection and optional resolution phases. The storage table can be stored as an index-organized table.

In addition, DML on a row that contains a nested table results in separate RPCs for the parent table and for each affected row in the nested table's storage table. Oracle does not perform referential integrity checks between the rows in the parent table and the rows in the storage table unless you explicitly specified integrity constraints during the creation of the parent table. Oracle recommends that you specify such constraints for replicated tables to detect all conflicts.

To ensure conflict detection between a nested table and its storage table, Oracle recommends that you define a deferrable foreign key constraint between them. Without a deferrable foreign key constraint, a conflict can insert rows in the storage table that cannot be accessed. A deferrable foreign key constraint causes an error to be raised in these situations so that the conflict is detected. You use the DEFERRED clause of the SET CONSTRAINTS statement to defer a constraint.

The following actions are not allowed directly on the storage table of a nested table in a replicated table:

• Adding the storage table to a replication group

• Altering the storage table

• Dropping the storage table

• Generating replication support on the storage table

These actions can occur indirectly when they are performed on the parent table of the storage table. In addition, you cannot replicate a subset of the columns in a storage table.

Tables with REF Columns

A REF is an Oracle built-in data type that is a logical "pointer" to a row object in an object table. A scoped REF is a REF that can only contain references to a specified object table, while an unscoped REF can contain references to any object table in the database. A scoped REF requires less storage space and provides more efficient access than an unscoped REF. Oracle supports the replication of tables with REFs.

Master Site Roles/Users

Depending on your security requirements, the following three roles can be consolidated into a single replication administrator. In fact, most multimaster replication environments use a single user to perform the replication administration, propagation, and receiving roles. If you have more stringent security requirements, then you can assign the following roles to different users.

Database Links and Replication

Database links provide the conduit to replicate data between master sites and materialized view sites. In a multimaster environment, there is a database link from each individual master site to all other master sites. Another way to look at the configuration of database links is that there are N - 1 database links for each master site, where N is the total number of master sites.

Figure 2-3 Each Arrow Represents a Database Link

Description of "Figure 2-3 Each Arrow Represents a Database Link"

In Figure 2-3, each master site has two database links to the other master sites (N-1 or in this case 3 - 1 = 2). This configuration ensures the bi-directional communication channels between master sites needed for multimaster replication. Notice that for a materialized view site, only a link from the materialized view site to the master site is required. The master site does not need a database link to the materialized view site.

The most basic setup has a database link from the replication administrator at the individual master site to the replication administrators at the other participating master replication sites.

A common approach, however, adds an additional set of database links to your replication environment. Before creating any replication administrator database links, you create public database links between all of the participating master sites, without specifying a CONNECT TO clause. The public database links specify the target of each database link with the USING clause, which specifies the service name of a remote database.

After creating the public database links, you can create the private replication administrator database links. When creating private database links, you specify the CONNECT TO clause, but the associated public database link eliminates the need to specify a USING clause.

The approach of using both public and private database links reduces the amount of administration needed to manage database links. Consider the following advantages:

• Multiple sets of private database links can share the same public link, further simplifying the administration of database links.

• If the target database of a database link changes but the service name for the target database remains the same, then you only need to change the tnsnames.ora file entry for the target database. Remember that the USING clause specifies the service name for the remote target database. All private database links for the same target point to the destination defined in the USING clause in the public database link.

  For example, if a database is moved to a different server but keeps the same service name, then you can update the tnsnames.ora file entry for the remote database at each replication site, and you do not need to re-create the database link.

As previously described, the replication administrator usually performs the tasks of administration and propagation in a multimaster environment. Because a single user performs these tasks, only one set of private database links must be created for the replication administrator.

However, in multimaster replication environments where propagation is performed by users other than the replication administrator, the appropriate set of private database links must be created for each of these alternate users.

HQ.MYCOMPANY.COM = 
(DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))
   (CONNECT_DATA=(SID=hqdb)(SERVER=DEDICATED)))

CREATE PUBLIC DATABASE LINK "HQ.MYCOMPANY.COM" USING 
'(DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))
   (CONNECT_DATA=(SID=hqdb)(SERVER=DEDICATED)))';

Replication Objects

The most visible part of your replication environment is the replicated objects themselves. Of these replicated objects, replicated tables are the foundation of your replication environment. The following sections discuss replicating the related database objects. These discussions highlight the benefits and potential limitations of replicating the following types of database objects:

• Tables

• Indexes

• Packages and Package Bodies

• Procedures and Functions

• User-Defined Types and Type Bodies

• Triggers

• Views, Object Views, and Synonyms

• Indextypes

• User-Defined Operators

1) CREATE OR REPLACE TRIGGER hr.insert_time
2)    BEFORE
3)       INSERT OR UPDATE ON hr.employees FOR EACH ROW
4)    BEGIN
5)       IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
6)          :NEW.TIMESTAMP := SYSDATE;
7)       END IF;
8)    END;
9) /

Alternatives to Replicating Sequences

Because two sequences at different databases can generate the same value, replicating sequences is not supported.

Three alternatives to replicating sequences guarantee the generation of unique values and avoid any uniqueness data conflicts. You can retrieve a unique identifier by executing the following select statement:

SELECT SYS_GUID() OID FROM DUAL;

This SQL statement returns a 16-byte globally unique identifier. This value is based on an algorithm that uses time and datestamp and machine identifier to generate a globally unique identifier. The globally unique identifier appears in a format similar to the following:

4595EF13AB785E73E03400400B40F58B

An alternate solution to using the SYS_GUID() function is to create a sequence at each of the master sites and concatenate the site name (or other globally unique value) with the local sequence. This approach helps you to avoid any potential duplicate sequence values and helps in preventing insert conflicts as described in the "Conflict Resolution Concepts" section.

Additionally, you can create a sequence at each of the master sites so that each site generates a unique value in your replication environment. You can accomplish this by using a combination of starting, incrementing, and maximum values in the CREATE SEQUENCE statement. For example, you might configure the following:

Using a similar approach, you can define different ranges for each master site by specifying a START WITH and MAXVALUE that would produce a unique range for each site.

Internal Triggers

Oracle uses internal triggers to capture and store information about updates to replicated data. Internal triggers build remote procedure calls (RPCs) to reproduce data changes made to the local site at remote replication sites. These deferred RPCs are stored in the deferred transaction queue and are propagated to the other master sites participating in the replication environment. The internal triggers supporting data replication are essentially components within the Oracle server executable. Therefore, Oracle can capture and store updates to replicated data very quickly with minimal use of system resources.

Deferred Transactions

Oracle forwards data replication information by propagating (that is, sending and executing) the RPCs that are generated by the internal triggers described previously. These RPCs are stored in the deferred transaction queue. In addition to containing the execution command for the internal procedure at the destination site, each RPC also contains the data to be replicated to the target site. Oracle uses distributed transaction protocols to protect global database integrity automatically and ensure data survivability.

Internal Procedure

When a deferred RPC created by an internal trigger is propagated to the other master sites participating in a replication environment, an internal procedure at the destination site is used to apply the deferred RPC at the remote site. These internal procedures are activated automatically when you generate replication support for a table. These internal procedures are executed based on the RPCs that are received from the deferred transaction queue of the originating site.

Queues

The following queues manage the transactions that are generated by Advanced Replication:

Replication Modes of Operation

There are three modes of operation for a replication environment.

Replication Mode Control

Though there are three modes of replication operation, there are only two mechanisms to control these modes (recall that the quiescing mode is a transition from a normal to quiesced mode).

Administrative Requests

To configure and manage a replication environment, each participating server uses Oracle's replication management API. A server's replication management API is a set of PL/SQL packages encapsulating procedures and functions administrators can use to configure Oracle's replication features. The Advanced Replication interface in Oracle Enterprise Manager Cloud Control also uses the procedures and functions of each site's replication management API to perform work.

An administrative request is a call to a procedure or function in Oracle's replication management API. For example, when you use the Advanced Replication interface in Oracle Enterprise Manager Cloud Control to create a master group, the interface completes the task by making a call to the DBMS_REPCAT.CREATE_MASTER_REPGROUP procedure. Some administrative requests generate additional replication management API calls to complete the request.

Administrative Request Queue

Often referred to as the administrative request queue, the DBA_REPCATLOG view stores administrative requests that manage and modify your replication environment. Some DBMS_REPCAT procedures that are executed are listed in the administrative request queue. For example, if you wanted to add a replicated table to an existing master group, then you would see a request naming the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure.

You can view the administrative request queue by querying the DBA_REPCATLOG view or viewing the Administrative Requests page in the Advanced Replication interface in Oracle Enterprise Manager Cloud Control.

Each request has a status that displays the state of the request. Here are the possible states:

• READY: The READY state indicates that the request is ready to be executed. If you monitor the administrative request queue and a request remains in the READY state for a long time, then a request in front of the ready request might be waiting for a callback. Typically, administrative requests in the READY state are waiting for a job to execute them. You can execute them manually by using the DO_DEFERRED_REPCAT_ADMIN procedure in the DBMS_REPCAT package.

• AWAIT_CALLBACK: The AWAIT_CALLBACK state indicates that the request is waiting for a request to be executed at another site and is waiting for confirmation of the request execution. After the request receives the callback, the request is either removed or has its status changed. The request is removed from the queue if it was applied successfully, or its status is changed to ERROR if it failed. This state is only possible at the master definition site.

• ERROR: If a request cannot be successfully executed, then it is placed in the ERROR state. The error number appears in the ERRNUM column and the error message appears in the MESSAGE column of the administrative request queue (or ERROR in the Status field on the Administrative Requests page when using the Advanced Replication interface in Oracle Enterprise Manager Cloud Control).

  Note:

  If a request is in the ERROR state, then resolve the error condition as described by the error number and resubmit the request.

• DO_CALLBACK: If a request at a master site is in the DO_CALLBACK state, then it means that the master site must inform the master definition site about the success or failure of the request. This state is only possible at a master site that is not the master definition site.

The administrative request queue of each master site lists only the administrative requests to be performed at that master site. The master definition site for a master group, however, lists administrative requests to be performed at each of the master sites. The administrative request queue at the master definition site lets the DBA monitor administrative requests of all the master sites in the replication environment.

Master Group

In a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are always updated in a transactionally consistent manner.

By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. That is not to say that replication groups and schemas must correspond with one another. Objects in a replication group can originate from several database schemas, and a schema can contain objects that are members of different replication groups. The restriction is that a replication object can be a member of only one group.

In a multimaster replication environment, the replication groups are called master groups. Corresponding master groups at different sites must contain the same set of replication objects (see "Replication Objects"). Figure 2-4 illustrates that master group hr_mg contains an exact replica of the replicated objects at each master site.

Figure 2-4 Master Group hr_mg Contains Same Replication Objects at All Sites

Description of "Figure 2-4 Master Group hr_mg Contains Same Replication Objects at All Sites"

The master group organization at the master site plays an integral role in the organization of replication objects at a materialized view site.

Additionally, Figure 2-5 illustrates that each site can contain multiple replication groups, though each group must contain the same set of objects at each master site.

Figure 2-5 Master Groups Are Identical at Each Master Site

Description of "Figure 2-5 Master Groups Are Identical at Each Master Site"

Column Groups

Column groups provide the organizational mechanism to group all columns that are involved in a conflict resolution routine. If a conflict occurs in one of the columns of the group, then the remainder of the group's columns can resolve the conflict. For example, if a column group for a table contains a min_price, list_price, cost_price, and timestamp field and a conflict arises for the list_price field, then the timestamp field can resolve the conflict, if a timestamp conflict resolution routine has been used.

Initially, you might think that you should put all columns in the table into a single column group. Although this makes setup and administration easier, it might decrease the performance of your replicated table and might increase the potential for data conflicts. As described in the "Performance Mechanisms", if a conflict occurs in one column group of a table, then the minimum communication feature does not send data from other column groups in the table. Therefore, placing all columns into a single column group might negate the advantages of the minimum communication feature, unless you use the SEND_OLD_VALUES and COMPARE_OLD_VALUES procedures in the DBMS_REPCAT package.

Propagation Types

As the internal trigger captures any DML applied to a replicated table, the DML must be propagated (or sent) to the other master sites in the replication environment. Internal triggers are described in the section "Internal Triggers".

Advanced Replication supports both asynchronous and synchronous replication.

Asynchronous

Typical replication configurations use asynchronous data replication. Asynchronous data replication occurs when an application updates a local replica of a table, stores replication information in a local queue, and then forwards the replication information to other replication sites at a later time. Consequently, asynchronous data replication is also called store-and-forward data replication.

As Figure 2-6 shows, Oracle uses its internal triggers, deferred transactions, deferred transaction queues, and job queues to propagate data-level changes asynchronously among master sites in a replication environment, as well as from an updatable materialized view to its master table.

Figure 2-6 Asynchronous Data Replication Mechanisms

Description of "Figure 2-6 Asynchronous Data Replication Mechanisms"

Synchronous

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 at least one other replica 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.

Figure 2-7 Synchronous Data Replication Mechanisms

Description of "Figure 2-7 Synchronous Data Replication Mechanisms"

As Figure 2-7 shows, Oracle uses the same internal triggers to generate remote procedure calls (RPCs) that asynchronously replicate data-level changes to other replication sites to support synchronous, row-level data replication. However, Oracle does not defer the execution of such RPCs. Instead, data replication RPCs execute within the boundary of the same transaction that modifies the local replica. Consequently, a data-level change must be possible at all synchronously linked sites that manage a replicated table; otherwise, a transaction rollback occurs.

Synchronous Data Propagation

As shown in Figure 2-8, 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 replication 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.

Figure 2-8 Propagating Changes Using Synchronous Row-Level Replication

Description of "Figure 2-8 Propagating Changes Using Synchronous Row-Level Replication"

Understanding Mixed-Mode Multimaster Systems

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, then site A sends changes to site B synchronously and site B sends changes to site A synchronously. There is no need to be concerned about 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 2-9.

Figure 2-9 Selecting a Propagation Mode

Description of "Figure 2-9 Selecting a Propagation Mode"

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

Figure 2-10 Ordering Considerations

Description of "Figure 2-10 Ordering Considerations"

Each time that you add a new master site to a mixed-mode multimaster system, consider how the addition affects the data propagation modes to and from existing sites.

Initiating Propagation

When synchronous propagation is used, the propagation of the DML is handled immediately and is automatically initiated. If asynchronous propagation is used, then you can use the following methods to propagate the deferred transactions:

• Scheduled job: In most cases, use a scheduled job to automatically propagate the deferred transactions at a set interval.

• Manual propagation: You can also manually propagate the changes by executing a stored procedure or using the Advanced Replication interface in Oracle Enterprise Manager Cloud Control. You might occasionally need to manually propagate your deferred transactions if you do not want to wait for the job queue to automatically propagate the deferred transactions.

Parallel Propagation

With parallel propagation, Oracle asynchronously propagates replicated transactions using multiple, parallel transit streams for higher throughput. When necessary, Oracle orders the execution of dependent transactions to ensure global database integrity.

Parallel propagation uses the pool of available parallel processes. This is the same facility Oracle uses for other parallel operations such as parallel query, parallel load, and parallel recovery. Each server process propagates transactions through a single stream. A parallel coordinator process controls these server processes. The coordinator tracks transaction dependencies, allocates work to the server processes, and tracks their progress.

Parallel processes remain associated with a parallel operation on the server throughout the execution of that operation. When the operation is complete, those server processes become available to process other parallel operations. For example, when Oracle Database performs a parallel push of the deferred transaction queue to its destination, all parallel processes used to push the queue remain dedicated to the push until it is complete.

To configure a pool of parallel processes for a server properly, you must consider several issues related to the configuration of a replication system.

• When you configure all scheduled links to use serial propagation, the replication system does not use parallel processes. Therefore, you do not need to adjust the size of any server's pool of parallel processes to account for replication. Typically, serial propagation is used only for backward compatibility.

• When you configure one or more scheduled links to use parallel propagation, you must consider the number of parallel processes that each link uses to push changes to its destination. Furthermore, you should also consider how long each push holds parallel servers from being used by other operations. For example, when you configure a scheduled link for continuous propagation with a large value for delay seconds, Oracle holds on to the parallel processes used to push transactions to its destination. Therefore, you should increase the number of parallel processes for the corresponding database server to ensure that there are enough processes for other parallel operations on the server.

To configure a database server's pool of parallel query processes, use the following initialization parameters:

• PARALLEL_MAX_SERVERS

• PARALLEL_MIN_SERVERS

Minimum Communication

To detect and resolve an update conflict for a row, the propagating site must send a certain amount of data about the new and old versions of the row to the receiving site. By default, Oracle minimizes the amount of data that must be communicated to detect conflicts for each changed row in the table. Specifically, Oracle propagates:

• The primary key value and the old value of each column in each modified column group (the value before the modification)

• The new value of each updated column

Delay Seconds

Though not directly a performance mechanism, properly configuring the delay_seconds parameter can give you greater control over the timing of your propagation of deferred transactions.

When you are pushing deferred transactions, you set the delay_seconds parameter in the SCHEDULE_PUSH procedure or the PUSH function. When you are purging deferred transactions, you set the delay_seconds parameter in the SCHEDULE_PURGE procedure or the PURGE function. These procedures and functions are in the DBMS_DEFER_SYS package.

The delay_seconds parameter controls how long a job remains aware of the deferred transaction queue. The effects of the delay_seconds parameter can best be illustrated with the following two examples:

delay_seconds = 0 (default)

If a scheduled job with a 60 minute interval wakes up at 2:30 pm and checks the deferred transaction queue, then any existing deferred transactions are propagated. The propagation takes 2 minutes and therefore the job is complete at 2:32 pm.

If a deferred transaction enters the queue at 2:34 pm, then the deferred transaction is not propagated because the job is complete. In this scenario, the deferred transaction will be propagated at 3:30 pm.

delay_seconds = 300

If a scheduled job with a 60 minute interval wakes up at 2:30 pm and checks the deferred transaction queue, then any existing deferred transactions are propagated. The propagation takes 2 minutes and therefore the job is complete at 2:32 pm.

If a deferred transaction enters the queue at 2:34 pm, then the deferred transaction is propagated because the job remains aware of the deferred transaction queue for 300 seconds (5 minutes) after the job has completed propagating whatever was in the queue. In this scenario, the deferred transaction is propagated at 2:34 pm.

Why not just set the job to execute more often? Starting and stopping the job has a greater amount of overhead than starting the job and keeping it aware for a set period of time. In addition to decreasing the overhead associated with starting and stopping these jobs, using the delay_seconds parameter can reduce the amount of redo logging required to support scheduled jobs.

As with most performance features, there is a point of diminishing returns. Keep the length of the delay_seconds parameter in check for the following reasons:

• Parallel Propagation: Each parallel process that is used when pushing the deferred transaction queue is not available for other parallel activities until the propagation job is complete. A long delay_seconds value might keep the parallel process unavailable for other operations. To use parallel propagation, you set the parallelism parameter to 1 or higher in the SCHEDULE_PUSH procedure or the PUSH function.

• Serial Propagation: If you are using serial propagation (not parallel propagation), then the delay_seconds value causes the open session to "sleep" for the entire length of the delay, providing none of the benefits earlier described. To use serial propagation, you set the parallelism parameter to 0 (zero) in the SCHEDULE_PUSH procedure or the PUSH function.

• Precise Purge: If you specify the purge_method_precise method when using the DBMS_DEFER_SYS.PURGE procedure and you have defined a large delay_seconds value, then you might experience performance degradation when performing the specified purge. Using purge_method_precise is more expensive than the alternative (purge_method_quick), but it ensures that the deferred transactions and procedure calls are purged after they have been successfully pushed.

As a general rule of thumb, there are few viewable benefits of setting the delay_seconds parameter to a value greater than 20 minutes (which is 1200 seconds for the parameter setting).

Additionally, if you are using serial propagation by setting the parallelism parameter to 0, then you probably do not want to set a large delay_seconds value. Unlike parallel propagation, serial propagation only checks the queue after the duration of the delay_seconds value has elapsed. If you use serial propagation and set delay_seconds to 20 minutes, then the scheduled job sleeps for the entire 20 minutes, and any deferred transactions that enter the deferred transaction queue during that time are not pushed until 20 minutes have elapsed. Therefore, if you are using serial propagation, then consider setting delay_seconds to a value of 60 seconds or lower.

If you set a value of 20 minutes for parallel propagation, then the parallel push checks once a minute. If you can afford this resource lock, then the relatively high delay_seconds value of 20 minutes is probably most efficient in your environment. If, however, you cannot afford this resource lock, then consider setting the delay_seconds value to 10 or 20 seconds. Although you must execute the jobs more often than if the value was set to 1200 seconds, you still gain many of the benefits of the delay_seconds feature (versus the default value of 0 seconds).

Data Propagation Dependency Maintenance

Oracle maintains dependency ordering when propagating replicated transactions to remote sites. For example, consider the following transactions:

1. Transaction A cancels an order.

2. Transaction B sees the cancellation and processes a refund.

Transaction B depends on transaction A because transaction B sees the committed update canceling the order (transaction A) on the local system.

Oracle propagates transaction B (the refund) after it successfully propagates transaction A (the order cancellation). Oracle applies the updates that process the refund after it applies the cancellation.

SELECT OWNER, TABLE_NAME FROM DBA_TABLES 
  WHERE DEPENDENCIES = 'ENABLED';

Row Identification During Conflict Detection

To detect replication conflicts accurately, Oracle must be able to uniquely identify and match corresponding rows at different sites during data replication. Typically, Oracle's replication facility uses the primary key of a table to uniquely identify rows in the table. When a table does not have a primary key, you must designate an alternate key—a column or set of columns that Oracle can use to uniquely identify rows in the table during data replication.

Resolution of Data Conflicts

Oracle provides a mechanism that enables you to define a conflict resolution method that resolves a data conflict when detected. Oracle provides several prebuilt conflict resolution methods:

• Latest and Earliest Timestamp

• Overwrite and Discard

• Maximum and Minimum

• Additive and Average

• Timestamp

• Priority Group

• Site Priority

If the prebuilt Oracle conflict resolution methods do not meet the needs of your replication environment, then you have the option of writing your own conflict resolution method using PL/SQL and implementing it as a user-defined conflict resolution method. See Chapter 5, "Conflict Resolution Concepts and Architecture" to learn how conflict resolution works.