Oracle Corporation and its affiliates disclaim any responsibility for any damages caused by the use of this software in unsafe applications. This software and documentation may provide access to or information about third party content, products and services.
Audience
In particular, Oracle Database Advanced Replication contains conceptual information about Advanced Replication, as well as information about planning your replication environment and troubleshooting replication problems.
Documentation Accessibility
Related Documents
Printed documentation is available for purchase from the Oracle Store at http://oraclestore.oracle.com/. Visit the Oracle Technology Network (OTN) to download free release notes, installation documentation, white papers, or other materials.
Conventions
Introduction to Advanced Replication
Overview of Replication
Users can also access data from the replication site with the lowest cost of access, usually the site closest to them geographically. Some users can access one server while other users can access several servers, reducing the load on all servers.
Applications that Use Replication
Materialized views allow users to work on a subset of a database while disconnected from the central database server. Advanced Replication can replicate data in environments that use different releases of Oracle and in environments where Oracle runs on different operating systems.
Replication Objects, Groups, and Sites
Replication Objects
Replication Groups
Replication Sites
A replication group on a materialized display site is based on a root group and is more specifically referred to as a materialized display group. A root site maintains a full copy of all objects in a replication group, while materialized views on a materialized view site can contain all or a subset of the table data within a root group.
Types of Replication Environments
However, one materialized view page can contain only one materialized view of the Employees table, while another materialized view page can contain materialized views of both the Employees and Departments tables. Materialized view pages contain an image, or materialized view, of table data from a specific point in time.
Multimaster Replication
For example, if the hr_repg master group contains the employee and department tables, all master sites participating in the master group must maintain a complete copy of the employee and department tables. Materialized views in a refresh group can belong to one or more materialized view groups and are refreshed simultaneously to ensure that the data in all materialized views in the refresh group corresponds to the same transactionally consistent point in time.
Master Group Quiesce
Using asynchronous replication means that data conflicts are possible because the same row value can be updated at two different master sites at nearly the same time.
Materialized View Replication
However, you can use techniques to avoid conflicts and, if conflicts do occur, Oracle provides predefined mechanisms that can be implemented to resolve them. A materialized view can be read-only, updateable, or write-only, and these types of materialized views offer benefits in addition to those listed above.
Overview of Read-Only Materialized Views
Overview of Updatable Materialized Views
For changes made to an updated rendered view to be pushed back to the master during refresh, the updated rendered view must belong to a materialized view group. Oracle propagates the changes made to an updated materialized view to the external master table or the materialized master view of the materialized view.
Overview of Writeable Materialized Views
Allows users to query and update a local replicated dataset even when disconnected from the master site or master materialized view site. In addition, because materialized views can reside in a database that contains much less data, the disk space and memory requirements for materialized view clients can be less than the requirements for an Oracle server that contains master sites.
Overview of Row and Column Subsetting with Materialized Views
Materialized View Refresh
Refresh Groups
Materialized View Log
When a materialized view is quickly updated, entries in the materialized view's associated materialized view log that have appeared since the materialized view was last updated are applied to the materialized view.
Deployment Templates
Multimaster and Materialized View Hybrid Configurations
Materialized view refreshes are set-oriented, propagating changes from multiple transactions in a more efficient, batch-oriented operation, but at less frequent intervals. If contention occurs due to changes to multiple copies of the same data, then conflict detection and resolution always occurs at the master or materialized view master.
Scheduled Links
Replicated master tables must contain data for the full table being replicated, whereas materialized views can replicate subsets of master table data.
Administration Tools for a Replication Environment
Advanced Replication Interface in Oracle Enterprise Manager
Replication Management API
Replication Catalog
Distributed Schema Management
Replication Conflicts
Other Options for Multimaster Replication
Synchronous Replication
Procedural Replication
Conflict Detection and Procedural Replication
Master Replication Concepts and Architecture
Master Replication Concepts
What is Master Replication?
Updates made to an individual master site are propagated (sent) to all other participating master sites. If the replication environment is a hybrid environment (it has multiple master sites that support 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.
Single Master Replication
Multimaster replication, also known as peer-to-peer or n-way replication, consists of multiple master sites that participate equally in an update-anywhere model. Multimaster replication provides full replicas of each replicated table on each of the master sites.
Master Sites
Why Use Multimaster Replication?
Failover
Load Balancing
Support for Disconnected Materialized View Environments
Oracle Real Application Clusters Compared with Replication
RAC runs on a cluster or other massively parallel system and is in the same physical environment, so it cannot protect against the physical problems that replication can protect against. Interoperability: Advanced Replication can replicate data between different platforms and operating systems running Oracle.
Multimaster Replication Process
Asynchronous Replication
If a procedure has been replicated and its wrapping is done at a main site, the procedure call is placed in the deferred transaction queue. When a deferred transaction queue entry is applied to the remote destination, Oracle checks for data conflicts.
Conflict Resolution Concepts
Like an internal trigger, a wrapper for a replicated procedure immediately propagates the procedure call to all other primary pages in the replication environment. If the conflict is not resolved, then the data conflict is logged in the error queue at the destination site.
How Replication Works with Object Types and Collections
When a data conflict is logged in the error queue, the database administrator is responsible for manually resolving the data conflict. If you choose to use Oracle-supplied or user-defined conflict resolution methods, the Oracle server automatically attempts to resolve the data conflict.
Type Agreement at Replication Sites
Using a CREATE TYPE Statement You can use a CREATETYPE statement at a replication site to create the type. The type has exactly the same data type for each attribute at all replication sites.
Object Tables and Replication
Without a deferrable foreign key constraint, a conflict can insert rows into the storage table that are not accessible. These actions can occur indirectly when performed on the parent table of the storage table.
Master Replication Architecture
REFs created with WITH ROWID option If the WITH ROWID option is specified for a REF column, Oracle keeps a hint of the rowid of the row object referenced in the REF. The ROWID information in the REF is meaningless on the other sites and Oracle does not automatically correct the rowid hint.
Master Site Mechanisms
Oracle can find the referenced object directly using the rowid contained in the REF, without the need to retrieve the rowid from the OID index. Replication of a REF created using the WITH ROWID option results in an incorrect rowid hint at each replication site except the site where the REF was first created or.
Master Site Roles/Users
If the recipient does not have the appropriate privileges to use the call in the deferred transaction, then the entire transaction is deferred. You can register a receiver using the REGISTER_USER_REPGROUP procedure in the DBMS_REPCAT_ADMIN package.
Database Links and Replication
END IF;
END;
After the object is replicated to the other sites, the operator will act as if it was created locally. However, because it is a replicated object, you can still delete or change it using the Replication Manager API.
Alternatives to Replicating Sequences
Internal Triggers
Deferred Transactions
Internal Procedure
Queues
Administrative Mechanisms
Replication Modes of Operation
Replication Mode Control
Administrative Requests
Any materialized view sites affected by a DDL change are updated the next time you perform a reload of the materialized view site. While all master sites can communicate with each other, materialized view sites can only communicate with their associated master sites.
Administrative Request Queue
Ultimately, each master page propagates the status of its administrative requirements to the master definition page. This state is only possible on a master site that is not the master definition site.
Organizational Mechanisms
The master site definition for a master group, however, lists the administrative requirements that must be performed at each of the master sites. The administrative request queue at the master definition site allows the DBA to monitor the administrative requests of all master sites in the replication environment.
Master Group
DO_CALLBACK: If a request to a master site is in the DO_CALLBACK state, then this means that the master site must inform the master definition site about the success or failure of the request. The organization of the master group at the master site plays an integral role in the organization of reproduction objects in a materialized view site.
Column Groups
Propagation Mechanism
Propagation Types
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 pages to support synchronous data replication at the row level. Instead, data replication RPCs are executed within the boundary of the same transaction that modifies the local replica.
Synchronous Data Propagation
Destination of synchronously replicated transactions The necessary remote procedure calls to support synchronous replication are included in the internal activation for each object. However, if even one site is sending changes asynchronously to another site, then applications may experience conflicts at each site in the replication environment.
Understanding Mixed-Mode Multimaster Systems
Initiating Propagation
Performance Mechanisms
Parallel Propagation
Implementing Parallel Propagation For most users, setting the Parallel Propagation parameter to a value of 1 provides sufficient performance. If you want to try to achieve greater propagation throughput than with a value of 1, set your Parallel Propagation value to 2.
Minimum Communication
Set the parallel spread to the value that provides the best performance in your environment based on your testing. Tuning Parallel Propagation To get the most performance benefits from parallel propagation, reduce the amount of dependent transactions that are created.
Delay Seconds
Also, if you are using serial propagation with the parallelism parameter set to 0, you probably don't want to set a large delay_seconds value. So if you're using burst propagation, consider setting delay_seconds to 60 seconds or less.
Replication Protection Mechanisms
Unlike parallel propagation, serial propagation checks the queue only after the duration of the delay_seconds value has elapsed. If you set a value of 20 minutes for parallel distribution, the parallel push checks once per minute.
Data Propagation Dependency Maintenance
When you use the ROWDEPENDENCIES clause for a table, multiple SCNs can be stored in a single data block. That is, a separate SCN tracks changes for each row that is stored in the data block.
Conflict Resolution Mechanisms
To minimize transactional dependencies created at the data block level, avoid situations where data block changes are concentrated in one or a small number of data blocks. For example, when a replicated table is experiencing high levels of INSERT activity, you can divide the storage of new rows into multiple blocks of data by creating multiple free lists for the table.
Row Identification During Conflict Detection
A better solution is to create a memory sequence and sequence numbers to optimize primary key generation and improve parallel propagation performance.
Resolution of Data Conflicts
See also: Advanced Replication Interface Online Help to learn how to implement contention resolution with Oracle. Enterprise Manager and see the Oracle Database Advanced Replication Management API Reference to learn how to implement contention resolution using the Replication Management API.
Materialized View Concepts and Architecture
Materialized View Concepts
What is a Materialized View?
Therefore, if changes have been made to the master since the last update, a materialized view update takes some time to apply the changes to the materialized view. However, if no changes have been made at all to the master since the last update of a materialized view, then the materialized view update should be very fast.
Why Use Materialized Views?
When a fast update is performed on a materialized view, Oracle must examine all the changes in the master table or master materialized view since the last update to see if any apply to the materialized view.
Ease Network Loads
Materialized views are updated through an efficient batch process from a single master site or master materialized view site. While multimaster replication requires constant communication over the network, materialized view replication requires only periodic updates.
Create a Mass Deployment Environment
They have lower network requirements and dependencies than multimaster replication due to the timing of materialized view replication. In addition to not requiring a dedicated network connection, data replication with materialized views increases data availability by providing local access to the target data.
Enable Data Subsetting
Enable Disconnected Computing
Read-Only, Updatable, and Writeable Materialized Views
Read-Only Materialized Views
Updatable Materialized Views
Propagate all deferred transactions to databases that contain updatable materialized views before performing administration operations on the master cluster. If you do not perform these actions, then close the primary group before performing administration operations on it.
Writeable Materialized Views
You can also use the Advanced Replication interface in Oracle Enterprise Manager to create a materialized view set and add a materialized view to it.
Available Materialized Views
Primary Key Materialized Views
Object Materialized Views
ROWID Materialized Views
Complex Materialized Views
A Comparison of Simple and Complex Materialized Views For certain applications, you may want to consider using a complex materialized view. However, the simple materialized views can be refreshed more efficiently using fast refresh and materialized view logs.
Required Privileges for Materialized View Operations
The materialized view in Database II exhibits efficient query performance because the join operation completed during the materialized view refresh. Simple materialized views with a joined view: Method B in Figure 3-2 shows two simple materialized views in Database II, as well as a view that performs the join in the materialized view database.
Creator Is Owner
The privileges required to perform these operations depend on whether the same user performs them or different users perform them. If the owner of a materialized view on the materialized view site has a private database connection to the master site or master page of the materialized view, then the database connection is linked to the master owner on the master site or page main of the materialized view.
Creator Is Not Owner
Refresher Is Owner
Refresher Is Not Owner
Data Subsetting with Materialized Views
Secure sensitive data: Users can only see data that matches the defining query for the materialized view. CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS SELECT * FROM [email protected].
Materialized Views with Subqueries
CREATE MATERIALIZED VIEW oe.customers QUICK REFRESH TO UPDATE AS SELECT * FROM [email protected] c. CREATE MATERIALIZED VIEW oe.inventories QUICK REFRESH FOR UPDATE AS SELECT * FROM [email protected] i.
Restrictions for Materialized Views with Subqueries
Restrictions for Materialized Views with Unions Containing Subqueries
CREATE MATERIALIZED VIEW oe.orders AS SELECT * FROM [email protected] o WHERE EXISTS. SELECT * FROM [email protected] c. WHERE o.customer_id = c.customer_id -- first reference to orders table AND c.credit_limit > 50.
Determining the Fast Refresh Capabilities of a Materialized View
Examples of materialized views with joins containing subqueries The following statement creates the materialized view of oe.commands. DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM [email protected] o WHERE EXISTS (SELECT * FROM [email protected] c . WHERE o.customer_id = c.customer_id AND c.credit_limit >) ' );
Multitier Materialized Views
Figure 3–9 illustrates an example of a level 1 (orders_1) and level 2 (orders_2) materialized master view. For example, the master for level 2_2 materialized display orders is level 1_1 materialized display orders.
Scenario for Using Multitier Materialized Views
The local-level materialized views contain the subset of data from the level 1 materialized views that apply to their local customers. The statement queries the realized view in the database at the UK office, reg_uk.example.com.
Restrictions for Using Multitier Materialized Views
Stay in a different database than the materialized view pool in its main materialized view page. Be based on a materialized view in a materialized view set that is owned by PUBLIC on the parent of the materialized view.
How Materialized Views Work with Object Types and Collections
The type is in the same schema in both the materialized view site and the master site. Log in to the materialized view page as the user who owns the type on the home page.
Column Subsetting of Masters with Column Objects
The type has the same object ID on both the materialized view site and the master site. If this user does not exist on the materialized display site, create the user.
Materialized Views Based on Object Tables
The WITH OBJECT ID clause is required when you create a materialized view log on an object table. Consequently, REFs to the rows in the object table remain valid on the materialized view site.
Materialized Views with Collection Columns
However, you can use row nesting in the parent table of a nested table, and doing so may result in a subset of the nested tables in the materialized view. For the parent table of a nested table to refresh quickly, both the parent table and the nested table's storage table must have a materialized view record.
Materialized Views with REF Columns
CREATE MATERIALIZED VIEW oe.cust_address_objtab_mv FROM oe.cust_address_typ AS SELECT * FROM [email protected];. For example, if you created the customer_with_ref_mv materialized view before you created the cust_address_objtab_mv materialized view, then you could not use the SCOPEFOR clause when you created the customers_with_ref_.
Materialized View Registration at a Master Site or Master Materialized View Site
Logging REF columns in the materialized view log If necessary, you can log REF columns in the materialized view log. REFs created using the WITH ROWID clause If the WITHROWID clause is specified for a REF column, then Oracle retains the rowid of the object referenced in the REF.
Viewing Information about Registered Materialized Views
Iterating a REF created using the WITHROWID clause results in an incorrect row hint at every place in the iteration except where the REF was first created or modified. In this case, you can use the VALIDATESTRUCTURE option of the ANALYZE TABLE statement to determine which row hints at each replication site are incorrect.
Internal Mechanisms
For example, suppose a materialized view named orders_lev1 is based on the master table oe.orders, and a materialized view named orders_lev2 is based on orders_lev1. However, if you try to refresh orders_lev2, Oracle returns an error because orders_lev1 no longer exists.
Manual Materialized View Registration
Materialized View Architecture
Note that a master materialized view can have both a materialized view log and an updateable materialized view log. Be sure to consider the additional space these logs require when planning your main site with materialized rendering.
Master Site and Master Materialized View Site Mechanisms
Master Table or Master Materialized View
Internal Trigger for the Materialized View Log
One materialized view log can support multiple materialized views on its master table or master materialized view. Primary key: The materialized view records changes to the master table or master materialized view based on the primary key of the affected rows.
Materialized View Site Mechanisms
Limiting import of materialized view logs to another schema Materialized view logs are exported with the schema name explicitly specified in the DDL statements. Therefore, materialized view logs cannot be imported into a schema different from the schema from which they were exported.
Index
An error is written to the import log file and the items are not imported if you try to use an import with the Data Pump import tool that specifies the REMAP_SCHEMA import parameter to import an output dump file that contains materialized view logs in the specified schema.
Updatable Materialized View Log
Oracle uses the information in the updatable materialized view registry and deletes the inserted row. A materialized view should be an exact copy of the master when the fast refresh is complete.
Internal Trigger for the Updatable Materialized View Log
Because the materialized view is writable and not updatable, the transaction is not stored in the transaction queue pushed to the materialized view page.
Materialized View Groups
Group B in the materialized view site contains all objects in group B in the parent site. Using multiple refresh sets to refresh the contents of a single materialized view set can be introduced.
Refresh Group Size
Although you may want to define a single refresh group for each materialized display group, it may be more efficient to use one large refresh group that contains objects from multiple materialized display groups. One configuration to avoid is using multiple refresh groups to refresh the contents of a single materialized display group.
Refresh Process
Therefore, in cases where the network connection is difficult to maintain, consider using smaller reload groups. That is, if there have been no changes to the master tables or master materialized views since the last refresh for a particular materialized view, then almost no extra time is needed for the materialized view during materialized view group refresh.
Refresh Types
A quick refresh of the level 2 materialized view oe.customers_sf pulls the credit limit value of 5000 back to this materialized view at sf.ca. A new quick refresh of the level 1 materialized view oe.customers_region pulls the credit limit value of 5000 back to this materialized view at ca.us.
Initiating a Refresh
A quick refresh of the level 2 oe.customers_sf materialized view pushes the new value for the credit_limit to oe.customers_region materialized view at ca.us. A quick refresh of the level 2 materialized view oe.customers_sf pulls the credit_limit value of 5000 back to this materialized view on sf.ca for the second time.
Constraints and Refresh
For example, immediately after a bulk data load into a master table, the dependent materialized views no longer represent the master table's data. Instead of waiting for the next scheduled automatic refresh of sets, you can manually refresh sets of dependent materialized views to immediately propagate new main table rows to the corresponding materialized views.
Deployment Templates Concepts and Architecture
Mass Deployment Challenge
The problems encountered in the former scenario have created technologies and resources dedicated to the art of efficient mass deployment. For the purposes of Advanced Replication, the discussion of mass deployment is limited to data distribution and data infrastructure.
Deployment Templates and the Mass Deployment Goal
Mass deployment is the term used to describe the process of distributing database infrastructure, data, and front-end applications to a large number of users.
Oracle Deployment Templates Concepts
This template stores all the information needed to deploy a materialized rendering environment, including the data definition language (DDL) to create the objects in the remote location and target refresh group.
Deployment Template Elements
General Template Information
REFRESH_GROUP_NAME TEMPLATE_COMMENT PUBLIC_TEMPLATE PARAMETER_NAME DEFAULT_PARM_VALUE PROMPT_STRING PARM_VALUE USER_NAME. REFRESH_GROUP_NAME TEMPLATE_COMMENT PUBLIC_TEMPLATE PARAMETER_NAME DEFAULT_PARM_VALUE PROMPT_STRING USER_OVERRIDE.
Template Object Definitions
Materialized views created with a deployment template are automatically added to the refresh group defined for the template. You also cannot use deployment templates to instantiate any objects based on these types of objects.
Template Parameters
Template Parameters in the WHERE Clause and Security
User Authorization
Deployment Sites
Deployment Template Packaging and Instantiation
Online Instantiation
AS SELECT is used to populate the materialized views with data over a network from the main site. The duration of the connection depends on the number of objects created, the complexity of the materialized view subqueries and the amount of data.
Offline Instantiation
Offline instantiation of multilevel materialized views When you use deployment templates to create a materialized view page using offline instantiation, the conflict resolution methods defined in parent tables do not descend to the materialized view page. These conflict resolution methods may be required to ensure data consistency if you plan to create materialized views based on this materialized view page (multiple materialized views).
Scenarios for Instantiating a Deployment Template
Offline instantiation is an ideal solution for mass deployment situations where many laptops and other disconnected computers instantiate the target template. If you are using web instantiation, the conflict resolution methods are retired during instantiation.
Deployment Template Architecture
Template Definitions Stored in System Tables
Use of Standard DDL
Packaging and Instantiation Process
Packaging a Deployment Template for Online Instantiation
Packaging a Deployment Template for Offline Instantiation
In a mass deployment environment, most materialized view environments use the offline instantiation method to create the necessary materialized view environment. The script or binary file can be copied to a CD-ROM, floppy disk, or other storage media, or it can be posted to a Web or FTP site for download to the remote materialized view site.
Packaging and Instantiation Options
After the parameters are checked, the objects created by the template are added to the refresh group specified when the template was created. When you package the deployment template, a script or binary file is created to store the DDL needed to create the materialized rendering environment, the parameter values used during the instantiation process, and the DML needed to populate the materialized rendering environment with data.
After Instantiation
You can use the Advanced Replication interface in Oracle Enterprise Manager, or DBMS_REFRESH.CHANGE procedure, to change the refresh interval and next refresh data of a refresh group. To change these settings with the DBMS_REFRESH.CHANGE procedure, set the interval and next_date parameters as appropriate.
Deployment Template Design
If materialized view sites do not have a persistent network connection to the master site, they can refresh their refresh groups on demand.
Column Subsetting with Deployment Templates
If you add a materialized view that replicates the pk, empid, salary, and level columns (illustrated in Figure 4-6), you must also include the Timestamp column because it is used for conflict resolution for columns contained in column group A. Note: Although possible to configure column subsetting within a column group, it is not recommended because it may result in data inconsistency between sites.
Row Subsetting
Row Subsetting with an Assignment Table
CREATE MATERIALIZED VIEW Oe.product_information REFRESH FAST FOR UPDATE AS SELECT * FROM [email protected] pi. After the next materialized view refresh, the data for the product is added to the materialized view page that tracks product information for the warehouse.
Data Sets
CREATE MATERIALIZED VIEW sales.orders AS SELECT * FROM [email protected] o -- conditions for customers. CREATE MATERIALIZED VIEW sales.customer AS. SELECT c_id FROM [email protected] c -- conditions for customers.
Additional Design Considerations
Local Control of Materialized View Creation
Local Materialized View Control
Because multi-master replication does not allow subsetting of row and column data, updatable materialized view sites are sometimes created primarily for their data subset capability. Materialized view groups created with the Materialized View Group Wizard or the Replication Management API provide the localized control needed to meet the requirements of secure, updatable materialized view sites.
Conflict Resolution Concepts and Architecture
Understanding Your Data and Application Requirements
Types of Replication Conflicts
Update Conflicts
Uniqueness Conflicts
Delete Conflicts
Data Conflicts and Transaction Ordering
Conflict Detection
When a materialized view site pushes deferred transactions to its corresponding home page or materialized view home page, the receiving site performs conflict detection and resolution. Therefore, it is not necessary for a materialized view page to perform work to detect or resolve replication conflicts.
How Oracle Detects Different Types of Conflicts
The refresh mechanism ensures that, upon completion, the data in a materialized view is the same as the data in the corresponding master table or materialized master view, including the results of any conflict resolution.
Identifying Rows During Conflict Detection
Conflict Resolution
Oracle also provides several conflict resolution methods to handle unique conflicts, although these methods cannot guarantee data convergence. Oracle does not provide any built-in conflict resolution methods to handle delete or order conflicts.
Multitier Materialized Views and Conflict Resolution
Note that every updatable materialized view derives conflict resolution methods from its master, even if the updatable materialized view does not have any. Also note that a read-only materialized view does not subtract conflict resolution methods from its master.
Column Subsetting of Updatable Materialized Views and Conflict Resolution
Regenerate replication support for the affected objects at the definition master using the GENERATE_REPLICATION_SUPPORT procedure in DBMS_. Regenerate replication support for materialized views with the lowest number of levels that do not yet have regenerated replication support.
Nested Tables and Conflict Resolution
It is only the updates to the nested table column (changing the rows of the storage table and NESTED_TABLE_ID) that are problematic. Consider an example where the following rows are inserted directly into the nested table at univ1.example.com:.
Techniques for Avoiding Conflicts
These recommendations continue to apply with multilevel nesting, where the storage table row becomes a parent of another storage table's rows.
Use Column Groups
Use Primary Site and Dynamic Site Ownership Data Models
For example, related department applications can read the status code of a product order, e.g. enterable, shippable, billable, to determine when they can and cannot update the order.
Avoiding Specific Types of Conflicts
For example, select conflict resolution methods that provide convergence in multimaster configurations where ordering conflicts are possible. The example in Table 5–1 shows how having three main sites can lead to ordering conflicts.
Conflict Resolution Architecture
Headquarters A has priority 30; Master Site B has priority 25; and Master Site C has priority 10; x is a column in a particular row in a column group assigned to the site-priority conflict resolution method.
Support Mechanisms
Column Objects and Column Groups An Oracle object based on a user-defined type that occupies a single column in a table is a column object. That is, given a column group and a column object, the column object and all its attributes must be inside the column group, or the column object and all its attributes must be excluded from the column group.
Error Queue
Object tables and column groups An object table is a special kind of table in which each row represents an object based on a user-defined type. You can specify column groups that contain a subset of the columns in an object table.
Common Update Conflict Resolution Methods
Nested Tables and Column Groups A nested table's storage table is treated as an independent table in conflict resolution. Therefore, you can create a column group based on a subset of the columns in a storage table.
Latest Timestamp
Using the latest timestamp method, Oracle selects the latest update and applies the address with the correct zip code. Target environments The latest timestamp conflict resolution method works to converge replication environments with two or more primary sites.
Overwrite
Implement Latest Timestamp See the online help for the Advanced Replication interface to learn how to define a latest timestamp conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.
Additional Update Conflicts Resolution Methods
Additive
Average
The average conflict resolution method averages the new column value from the original location with the current value at the destination location. The average method cannot guarantee convergence if your replication environment has more than one master site.
Discard
The average method can be useful for scientific applications where two values are averaged rather than one value chosen over the other (for example, to calculate average temperature or average weight). Deploying Intermediate For more information about defining an Intermediate conflict resolution method using the Advanced Replication interface in Oracle Enterprise Manager, see the Advanced Replication interface online help.
Earliest Timestamp
Deploy Discard For more information about defining a discard conflict resolution method using the Advanced Replication interface in Oracle Enterprise Manager, see the Advanced Replication interface online help.
Maximum
Target environments If you have defined the maximum conflict resolution method and the target column used to resolve the conflict is always incrementing across all sites, then this method guarantees data convergence with any number of master sites and materialized view sites. Implement Maximum See the online help for the Advanced Replication interface to learn how to define a maximum conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.
Minimum
Target Environments If you have defined the minimum conflict resolution method and the target column used to resolve the conflict is always decreasing across all sites, then this method guarantees data convergence with any number of master sites and materialized display sites. Deploy minimum For more information about defining a minimum conflict resolution method using the Advanced Replication interface in Oracle Enterprise Manager, see the Advanced Replication interface online help.
Priority Groups
Support mechanisms You must define the priority of the values in the target column. Implement priority groups See the Advanced Replication interface online help to learn how to define a priority group conflict resolution method with the Advanced Replication interface in Enterprise Manager.
Site Priority
Implementing Site Priority See the Advanced Replication Interface online help to learn how to define a site priority contention resolution method with the Advanced Replication Interface in Oracle Enterprise Manager. Note: The priority group column in the DBA_REPPRIORITY view shows the site priority group and the order status group.
Uniqueness Conflicts Resolution Methods
Append Site Name
Append Sequence
If the generated portion of the column value exceeds the length of the column, the conflict method does not resolve the error. This method does not guarantee convergence with multiple capitals and should be used with a notification option.
Delete Conflict Resolution Methods
The resulting discrepancies must be resolved manually; therefore, this method is intended to be used with some form of reporting facility. This method can be useful when the availability of the data is more important than the complete accuracy of the data.
Send and Compare Old Values
Unlike the append methods, the uniqueness throwing method minimizes the spread of data until the accuracy of the data can be verified. REPCOLUMN data dictionary view: COMPARE_OLD_ON_DELETE (Y or N) and COMPARE_OLD_ON_UPDATE (Y or N).
Send and Compare Example
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). 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 only the new value of the LOB (column 3).
Send and Compare When Using Column Objects
The deferred RPC contains zeros for the new value of the primary key and the old value of the LOB. See also: Oracle Database Advanced Replication Management API Reference for details on DBMS_REPCAT.SEND_OLD_.
Planning Your Replication Environment
Considerations for Replicated Tables
Primary Keys and Replicated Tables
When duplicating tables with foreign key referential constraints, Oracle recommends that you always index the foreign key columns and duplicate those indexes unless updates and deletes are not allowed on the parent table. To replicate an index, add it to the master group that contains its table using 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
In addition, if you plan to create any primary key materialized view based on a master table or master materialized view, then that master must have a primary key. Oracle also does not support repeating UROWID columns in pivot tables or updatable materialized views.
Unsupported Table Types
For advanced replication support, you should convert LONG data types to LOBs with BASICFILE storage.
Row-Level Dependency Tracking
Initialization Parameters
If set, it must be set to the same value as the maximum number of tasks that can run concurrently, plus one. This value is the number of parallel execution processes that Oracle creates when the instance is started.
Master Sites and Materialized View Sites
Advantages of Master Sites
Advantages of Materialized View Sites
Preparing for Materialized Views
Create Materialized View Site Users
Create Master Site Users
Create Schemas at Materialized View Site
Create Database Links
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 creation and refresh of the materialized view.
Assign Privileges
Creating an external materialized view with a non-NULL VPD policy will not generate an error, but may produce incorrect results.
Schedule Purge at Master Site
Rescheduling the purge operation does not harm the master site, but may change the properties of the purge schedule.
Schedule Push
Allocate Job Slaves
Creating a Materialized View Log
Logging Columns in the Materialized View Log
Creating a Materialized View Environment
Creating a Materialized View Environment Using the Replication Management Interface
Creating a Materialized View Environment Using the Replication Management API
Avoiding Problems When Adding a New Materialized View Site
Create a dummy materialized view at the new materialized view site before creating your production materialized views. Create a dummy materialized view named dummy_mview based on the master table or master materialized view.
Interoperability in an Advanced Replication Environment
If you receive these errors, the only solution is to completely refresh the new materialized view. A virtual materialized view ensures that the materialized view log will not be deleted during your production.
Guidelines for Scheduled Links
Scheduling Periodic Pushes
Scheduling Continuous Pushes
Guidelines for Scheduled Purges of a Deferred Transaction Queue
Scheduling Periodic Purges
Scheduling Continuous Purges
Serial and Parallel Propagation
Preparing Materialized View Sites for Instantiation of Deployment Templates
Will you use online instantiation or offline instantiation to instantiate the deployment template on the materialized viewports. Whether the rollback segments that may be required by the deployment template exist on the materialized view site and are online.
Network Connectivity
If the required database connections are not part of the deployment template, then there are required database connections from the materialized view site to the master site.
Database Version
Materialized View Site Setup
The script you create must be distributed with the offline instantiator file and run before the offline instantiator file.
Create Necessary Schemas
Online or Offline Instantiation
Create Necessary Rollback Segments
Security and Replication
Designing for Survivability
Oracle Real Application Clusters versus Replication
Designing a Replication Environment for Survivability
Implementing a Survivable System
