6 Planning Your Replication Environment

Primary Keys and Replicated Tables

If possible, each replicated table should have a primary key. Where a primary key is not possible, each replicated table must have a set of columns that can be used as a unique identifier for each row of the table. If the tables that you plan to use in your replication environment do not have a primary key or a set of unique columns, then alter these tables accordingly. In addition, if you plan to create any primary key materialized views based on a master table or master materialized view, then that master must have a primary key.

Foreign Keys and Replicated Tables

When replicating tables with foreign key referential constraints, Oracle recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. Indexes are not replicated automatically. To replicate an index, add it to the master group containing its table using either the Advanced Replication interface in Oracle Enterprise Manager or the CREATE_MASTER_REPOBJECT procedure in the DBMS_REPCAT package.

Data Type Considerations for Replicated Tables

Advanced Replication supports the replication of tables and materialized views with columns that use the following data types:

• VARCHAR2

• NVARCHAR2

• NUMBER

• DATE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• RAW

• ROWID

• CHAR

• NCHAR

• CLOB with BASICFILE storage

• NCLOB with BASICFILE storage

• BLOB with BASICFILE storage

• XMLType stored as CLOB

• User-defined types that do not use type inheritance or type evolution

• Oracle-supplied types that do not use type inheritance or type evolution

The deferred and synchronous remote procedure call mechanism used for multimaster replication propagates only the piece-wise changes to the supported LOB data types when piece-wise updates and appends are applied to these LOB columns. Also, you cannot reference LOB columns in a WHERE clause of a materialized view's defining query.

You can replicate tables and materialized views that use user-defined types, including column objects, object tables, REFs, varrays, and nested tables.

Advanced Replication does not support the replication of tables and materialized views with columns that use the following data types:

• FLOAT

• BINARY_FLOAT

• BINARY_DOUBLE

• LONG

• LONG RAW

• CLOB with SECUREFILE storage

• NCLOB with SECUREFILE storage

• BLOB with SECUREFILE storage

• BFILE

• XMLType stored object relationally or as binary XML

• Expression type

• User-defined types that use type inheritance or type evolution

• Oracle-supplied types that use type inheritance or type evolution

For Advanced Replication support, you should convert LONG data types to LOBs with BASICFILE storage.

Oracle also does not support the replication of UROWID columns in master tables or updatable materialized views. However, UROWID columns are allowed in read-only materialized views.

Unsupported Table Types

Advanced Replication does not support the replication of the following types of tables and does not support materialized views based on these types of tables:

• Tables that have been compressed with the table compression feature

• Tables with columns that have been encrypted using transparent data encryption

• Tables with virtual columns

• Temporary tables

• Tables in a flashback data archive

Row-Level Dependency Tracking

When you create a table, you can specify the following options for tracking system change numbers (SCNs):

• NOROWDEPENDENCIES, the default, specifies that the SCN is tracked at the data block level.

• ROWDEPENDENCIES specifies that the SCN is tracked for each row in the table.

Using the ROWDEPENDENCIES option improves performance and scalability when using parallel propagation, but this option also requires six bytes of additional storage space for each row.

The following SQL statement creates a table with the ROWDEPENDENCIES option:

CREATE TABLE order_items 
  (order_id          NUMBER(12), 
  line_item_id       NUMBER(3)  NOT NULL, 
  product_id         NUMBER(6)  NOT NULL, 
  unit_price         NUMBER(8,2), 
  quantity           NUMBER(8)
  ) ROWDEPENDENCIES;

Oracle tracks the SCN for each row in this order_items table. You can also use the ROWDEPENDENCIES option in a CREATE CLUSTER statement if your tables are part of a cluster.

Advantages of Master Sites

Master sites have the following advantages:

• Support for highly available data access by remote sites

• Provide better support for frequent data manipulation language (DML) changes because changes are propagated automatically and, typically, at short intervals

• Allow simultaneous DML changes and data propagation without locking tables

• Can provide failover protection

To set up a master site, use either the Advanced Replication interface's Configure Master Sites for Replication Wizard or the replication management API.

Advantages of Materialized View Sites

Materialized view sites have the following advantages:

• Support disconnected computing

• Can contain a subset of its master site's or master materialized view site's data

To set up a materialized view site, you can use either the Advanced Replication interface's Configure Master and Materialized View Sites for Replication Wizard or the replication management API.

Preparing for Materialized Views

Most problems encountered with materialized view replication result from not preparing the environment properly. There are four essential tasks that you must perform before you begin creating your materialized view environment:

• Create the necessary schema.

• Create the necessary database links.

• Assign the appropriate privileges.

• Allocate sufficient job processes.

The Advanced Replication interface's Configure Master and Materialized View Sites for Replication Wizard automatically performs these tasks. The following discussion is provided to help you understand the replication environment and to help those who use the replication management API. After running Setup Wizard, create the necessary materialized view logs. See the Advanced Replication interface's online Help in Oracle Enterprise Manager for instructions on using the interface to set up your materialized view site.

If you are not able to use the Advanced Replication interface, then review the "Set Up Materialized View Sites" section in Chapter 2 of the Oracle Database Advanced Replication Management API Reference for detailed instructions on setting up your materialized view site using the replication management API.

The following sections describe what the Advanced Replication interface's Configure Master and Materialized View Sites for Replication Wizard or the script in the Oracle Database Advanced Replication Management API Reference does to set up your materialized view site.

Create Database Links

The defining query of a materialized view can use one or more database links to reference remote table data. Before creating materialized views, the database links you plan to use must be available. Furthermore, the account that a database link uses to access a remote database defines the security context under which Oracle creates and subsequently refreshes a materialized view.

To ensure proper behavior, a materialized view's defining query must use a database link that includes an embedded user name and password in its definition; you cannot use a public database link when creating a materialized view. A database link with an embedded name and password always establishes connections to the remote database using the specified account. Additionally, the remote account that the link uses must have the SELECT privileges necessary to access the data referenced in the materialized view's defining query.

Before creating your materialized views, you must create several administrative database links. Specifically, you should create a PUBLIC database link from the materialized view site to the master site. Doing so makes defining your private database links easier because you do not need to include the USING clause in each link. You also need private database links from the materialized view administrator to the proxy administrator and from the propagator to the receiver, but, if you use the Advanced Replication interface's Configure Master and Materialized View Sites for Replication Wizard, then these database links are created for you automatically.

See Also:

The information about security options in Oracle Database Advanced Replication Management API Reference for more information

After the administrative database links have been created, a private database link must be created connecting each replicated materialized view schema at the materialized view database to the corresponding schema at the master database. Be sure to embed the associated master database account information in each private database link at the materialized view database. For example, the hr schema at a materialized view database should have a private database link to the master database that connects using the hr user name and password.

Figure 6-1 Recommended Schema and Database Link Configuration

Description of "Figure 6-1 Recommended Schema and Database Link Configuration"

For multimaster replication, there must be no Virtual Private Database (VPD) restrictions on the replication propagator and receiver schemas. For materialized views, the defining query for the materialized view cannot be modified by VPD. VPD must return a NULL policy for the schema that performs both the create and refresh of the materialized view. Creating a materialized view with a non-NULL VPD policy is allowed when the USING TRUSTED CONSTRAINTS clause is specified. In this case, ensure that the materialized view behaves correctly. Materialized view results are computed based on the rows and columns filtered by VPD policy. Therefore, you must coordinate the materialized view definition with the VPD policy to ensure the correct results.

See Also:

• Oracle Database Administrator's Guide for more information about database links

• Oracle Database Security Guide for more information about VPD

• Oracle Label Security Administrator's Guide for information about Advanced Replication and Oracle Label Security

Creating a Materialized View Log

Before creating materialized view groups and materialized views for a remote materialized view site, ensure that you create the necessary materialized view logs at the master site or master materialized view site. A materialized view log is necessary for every master table or master materialized view that supports at least one materialized view with fast refreshes.

To create a materialized view log, you need the following privileges:

• CREATE ANY TABLE

• CREATE ANY TRIGGER

• SELECT (on the materialized view log's master)

• COMMENT ANY TABLE

1) CREATE MATERIALIZED VIEW oe.customers REFRESH FAST AS
2)  SELECT * FROM oe.customers@orc1.example.com c
3)  WHERE EXISTS
4)    (SELECT * FROM oe.orders@orc1.example.com o
5)     WHERE c.customer_id = o.customer_id AND o.order_total > 20000);

CREATE MATERIALIZED VIEW LOG ON oe.orders 
  WITH PRIMARY KEY (customer_id,order_total);

ALTER MATERIALIZED VIEW LOG ON oe.orders ADD (customer_id,order_total);

ALTER MATERIALIZED VIEW LOG ON oe.customers ADD (cust_address.postal_code);

Creating a Materialized View Environment

Materialized view environments can be created in several different ways and from several different locations. In most cases, you should use deployment templates at the master site to locally precreate a materialized view environment that will be individually deployed to the target materialized view site.

You can also individually create the materialized view environment by establishing a connection to the materialized view site and building the materialized view environment directly.

Creating a Materialized View Environment Using the Replication Management Interface

See the Advanced Replication interface's online Help in Oracle Enterprise Manager for information about using deployment templates to centrally create a materialized view environment using the Advanced Replication interface.

See the Advanced Replication interface's online Help in Enterprise Manager for information about individually creating the materialized view environment with a direct connection to the remote materialized view site using the Advanced Replication interface.

Figure 6-2 Flowchart for Creating Materialized Views

Description of "Figure 6-2 Flowchart for Creating Materialized Views"

Avoiding Problems When Adding a New Materialized View Site

After you have created a materialized view environment with one or more materialized view sites, you might need to add new materialized view sites. You might encounter problems when you try to perform a fast refresh on the materialized views you create at a new materialized view site if both of the following conditions are true:

• Materialized views at the new materialized view site and existing materialized views at other materialized view sites are based on the same master table or master materialized view.

• Existing materialized views can be refreshed while you create the new materialized views at the new materialized view site.

The problem arises when the materialized view logs for the masters are purged before a new materialized view can perform its first fast refresh. If this happens and you try to perform a fast refresh on the materialized views at the new materialized view site, then you might encounter the following errors:

ORA-12004 REFRESH FAST cannot be used for materialized view materialized_view_name
ORA-12034 materialized view log on materialized_view_name younger than last refresh

If you receive these errors, then the only solution is to perform a complete refresh of the new materialized view.

To avoid this problem, choose one of the following options:

• Use deployment templates to create the materialized view environment at materialized view sites. You will not encounter this problem if you use deployment templates.

  See Also:

  Chapter 4, "Deployment Templates Concepts and Architecture" for information about deployment templates

• Create a dummy materialized view at the new materialized view site before you create your production materialized views. The dummy materialized view ensures that the materialized view log will not be purged while your production materialized views are being created.

If you choose to create a dummy materialized view at the materialized view site, complete the following steps:

1. Create a dummy materialized view called dummy_mview based on the master table or master materialized view. For example, to create a dummy materialized view based on a master table named sales, issue the following statement at the new materialized view site:

   CREATE MATERIALIZED VIEW dummy_mview REFRESH FAST AS 
     SELECT * FROM pr.sales@orc1.example.com WHERE 1=0; 
   

2. Create your production materialized views at the new materialized view site.

3. Perform fast refresh of your production materialized views at the new materialized view site.

4. Drop the dummy materialized view.

Scheduling Periodic Pushes

You can schedule periodic intervals between pushes of a site's deferred transaction queue to a remote destination. Examples of periodic intervals are once an hour or once a day. To do so, you can use the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure and specify the settings shown in Table 6-3.

You can also use the Advanced Replication interface in Oracle Enterprise Manager to schedule periodic pushes. To do so, set delay seconds to 0 (zero) when configuring a scheduled link.

Then configure the interval (the "then push every" control) to push the deferred transaction queue periodically.

The following is an example that schedules a periodic push once an hour:

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc2.example.com',
      interval => 'SYSDATE + (1/24)',
      next_date => SYSDATE,
      delay_seconds => 0);
END;
/

Scheduling Continuous Pushes

Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. To do so, use the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure and specify the settings shown in Table 6-4.

With this configuration, Oracle continues to push transactions that enter the deferred transaction queue for the duration of the entire interval. If the deferred transaction queue has no transactions to propagate for the amount of time specified by the delay_seconds parameter, then Oracle releases the resources used by the job and starts fresh when the next job slave becomes available.

If you are using serial propagation by setting the parallelism parameter to 0 (zero), then you can simulate continuous push by reducing the settings of the delay_seconds and interval parameters to an appropriate value for your environment. However, if you are using serial propagation, simulating continuous push is costly when the push job must initiate often.

The following is an example that simulates continual pushes:

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc2.example.com',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

Scheduling Periodic Purges

You can schedule periodic purges of a site's deferred transaction queue. Examples of periodic purges are purges that occur once a day or once a week. To do so, you can use the DBMS_DEFER_SYS.SCHEDULE_PURGE procedure and specify the settings shown in Table 6-5.

You can also use the Advanced Replication interface in Oracle Enterprise Manager to schedule periodic purges. To do so, set delay seconds to 0 (zero). Then configure the interval (the "then purge every" control) to purge the deferred transaction queue.

The following is an example that schedules a periodic purge once a day:

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
      next_date => SYSDATE,
      interval => 'SYSDATE + 1',
      delay_seconds => 0);
END;
/

Scheduling Continuous Purges

To configure continuous purging of a site's deferred transaction queue, you can use the DBMS_DEFER_SYS.SCHEDULE_PURGE procedure and specify the settings shown in Table 6-6.

You can also use the Advanced Replication interface to configure continuous purge. To do so, on the Purge Schedule page, set Delay Seconds to 500,000 and set interval (the Every field) to a value less than the Delay Seconds setting.

The following is an example that simulates continuous purges:

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
      next_date => SYSDATE,
      interval => 'SYSDATE + (1/144)',
      purge_method => dbms_defer_sys.purge_method_quick,
      delay_seconds => 500000);
END;
/

Preparing Materialized View Sites for Instantiation of Deployment Templates

If you decide to use deployment templates, then you must prepare your materialized view sites for instantiation. If a deployment template has been designed well, then little preparation is necessary at the remote materialized view site. This section describes the most common preparations that must be performed at the remote materialized view site. After any required preparations have been completed, you are ready to perform either an online or offline instantiation.

Use the following questions to assess which actions are necessary to prepare the remote materialized view site for instantiation:

• Does the remote materialized view site have network connectivity to the target master site?

• Does the materialized view site have an Oracle9i Release 2 (9.2) or later database?

• Has the remote materialized view site been set up to support materialized view replication?

• Do the schemas required by the deployment template exist at the materialized view site?

• If required database links are not part of the deployment template, then do the required database links from the materialized view site to the master site exist?

• Will you use online instantiation or offline instantiation to instantiate the deployment template at the materialized view sites?

• Do the rollback segments that might be required by the deployment template exist at the materialized view site and are they online?

The following sections provide guidance for the issues raised by each of these questions.

Oracle Real Application Clusters versus Replication

Oracle Real Application Clusters (Oracle RAC) supports fail over to surviving systems when a system supporting an instance of the Oracle server fails. Oracle RAC 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, Oracle RAC is a good 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 in an Oracle RAC environment, then a surviving instance automatically recovers any incomplete transactions. Applications running on the failed system can execute on the fail over system, accessing all data in the database.

Oracle RAC does not, however, provide survivability for site failures (such as power outages, 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 multimaster replication to maintain a replica of a database at a geographically remote location. In addition, you can use multimaster replication to replicate data between sites running different operating systems or different releases of Oracle or both.

Should the local system fail, the application can continue to execute at the remote site. Using multimaster replication, some administrative procedures might be necessary to recover transactions at the failed site and to prevent data inconsistencies when restarting the failed site.

Designing a Replication Environment for Survivability

If you choose to use the replication facility for survivability, then consider the following issues:

• The replication facility must be able to keep up with the transaction volume of the primary system.

• If a failure occurs at the primary site, then recently committed transactions at the primary site might not have been asynchronously propagated to the failover site yet. These transactions appear to be lost.

  These "lost" transactions must be dealt with when the primary site is recovered.

  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 failover site. The first of these was a transaction that entered a new order, and the second was a transaction that canceled an existing order.

  In the first case, someone might notice the absence of the new order when processing continues on the fail over system, and reenter it. In the second case, the cancellation of the order might not be noticed, and processing of the order might proceed; that is, the canceled item might be shipped and the customer billed.

  What happens when you restore the primary site? If you simply push all of the changes executed on the failover system back to the primary system, then you will encounter conflicts.

  Specifically, duplicate orders exist for the item originally ordered at the primary system just before it failed. Additionally, data changes result from the transactions to ship and bill the order that was originally canceled on the primary system.

You must carefully design your system to deal with these situations. The next section explains this process.

Implementing a Survivable System

Advanced Replication provides survivability against site failures by using multiple replicated master sites. You must configure your system using one of the following methods, which are listed in order of increasing implementation difficulty:

• The failover site is used for read access only. That is, no updates are allowed at the failover site, even when the primary site fails.

• After a failure, the primary site is restored from the fail over site using export/import, or through full backup.

• Full conflict resolution is employed for all data/transactions. This requires careful design and implementation. You must ensure proper resolution of conflicts that can occur when the primary site is restored, such as duplicate transactions.

• Provide your own special applications-level routines and procedures to deal with the inconsistencies that occur when the primary site is restored, and the queued transactions from the active fail over system are propagated and applied to the primary site.

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 option to on and specifying multiple connect descriptors.

BEGIN
DBMS_REPCAT.REPCAT_IMPORT_CHECK( gname     =>   'hr_repg',
                                 master    =>   FALSE);
END;
/