Oracle8 Server Replication

Overview of The Oracle8 Server Replication Manual

Audience

Knowledge Assumed of the Reader

How The Oracle8 Server Replication Manual Is Organized

Conventions Used in This Manual

Your Comments Are Welcome

What Is Replication?

Basic Replication

Advanced (Symmetric) Replication

Basic Replication Concepts

Uses of Basic Replication

Read-Only Table Snapshots

Snapshot Refreshes

Other Basic Replication Options

Advanced Replication Concepts

Uses for Advanced Replication

Advanced Replication Configurations

Advanced Replication and Oracle's Replication Manager

Replication Objects, Groups, Sites, and Catalogs

Oracle's Advanced Replication Architecture

Replication Administrators, Propagators, and Receivers

Replication Conflicts

Unique Advanced Replication Options

Quick Start: Building a Basic Replication Environment

A Simple Example

Preparing a Database for Snapshots

Necessary Schemas

Necessary Database Links

Planning for Snapshot Refreshes

Creating Snapshot Logs

Snapshot Log Names

Required Privileges

Timing of Snapshot Log Creation

Special Requirements for Primary Key Snapshots

Snapshot Log Storage Parameters

Special Requirements for Subquery Snapshots

Internal Mechanisms of Snapshot Log Creation

Creating Simple Snapshots

Snapshot Names

Required Privileges

Special Requirements for Primary Key Snapshots

Snapshot Refresh Settings

A Snapshot's Defining Query

Snapshot Storage Settings

Clustered Snapshots

Data Load Options

Creating Snapshots with Subqueries

Advanced Subsetting with Subqueries

Restrictions for Snapshots for Subquery

Creating Refresh Groups

Refresh Settings

Refresh Types

Rollback Segment Setting

Managing a Basic Replication Environment

Managing Snapshot Logs

Managing Read-Only Snapshots

Managing Snapshot Refreshes and Refresh Groups

Tuning Performance for Snapshots

Indexing Snapshots

Tuning Subquery Snapshots

Other Basic Replication Options

Complex Snapshots

ROWID Snapshots

Individual Snapshot Refreshes

Monitoring Basic Replication Environments

Quick Start: Building a Multimaster Replication Environment

A Simple Example

Preparing for Multimaster Replication

The Replication Setup Wizard

Starting SNP Background Processes

Managing Scheduled Links

Creating a Scheduled Link

Editing a Scheduled Link

Viewing the Status of a Scheduled Link

Deleting a Scheduled Link

Purging a Site's Deferred Transaction Queue

Specifying a Site's Purge Schedule

Manually Purging a Site's Deferred Transaction Queue

Managing Master Groups

Creating a Master Group

Deleting a Master Group

Suspending Replication Activity for a Master Group

Resuming Replication Activity for a Master Group

Adding Objects to a Master Group

Altering Objects in a Master Group

Removing Objects from a Master Group

Adding a Master Site to a Master Group

Removing a Master Site from a Master Group

Relocating a Master Group's Definition Site

Generating Replication Support for Master Group Objects

Executing DDL Within a Master Group

Validating a Master Group

Viewing Information About Master Groups

Managing Administration Requests

Displaying Administration Requests

Applying Administration Requests

Purging Administration Requests

More about Administration Request Mechanisms

Advanced Multimaster Replication Options

Planning for Parallel Propagation

Understanding Replication Protection Mechanisms

Monitoring a Multimaster Replication System

Managing Deferred and Error Transactions

Managing Local Jobs

Data Dictionary Views

Quick Start: Adding a Snapshot Site to an Advanced Replication System

A Simple Example

Preparing for Snapshot Site Replication

The Replication Setup Wizard

Planning Scheduled Links and Snapshot Refreshes

Starting SNP Background Processes

Managing Snapshot Logs

Creating a Snapshot Log

Altering a Snapshot Log

Deleting a Snapshot Log

Managing Snapshot Groups

Creating a Snapshot Group

Registering a Snapshot Group at its Master Site

Unregistering a Snapshot Group at its Master Site

Adding Objects to a Snapshot Group

Altering Objects in a Snapshot Group

Deleting Objects from a Snapshot Group

Editing a Snapshot Group

Deleting a Snapshot Group

Managing Snapshots

Creating a Snapshot

Creating Snapshots with Subqueries

Regenerating Replication Support for an Updatable Snapshot

Altering a Snapshot

Deleting a Snapshot

Managing Refresh Groups

Creating a Refresh Group

Adding Snapshots to a Refresh Group

Deleting Snapshots from a Refresh Group

Changing Refresh Settings for a Snapshot Group

Manually Refreshing a Group of Snapshots

Deleting a Refresh Group

Monitoring Snapshot Sites in an Advanced Replication System

Data Dictionary Views

Overview

When to Use Conflict Resolution Methods

Understanding Column Groups

Shadow Column Groups

Detecting Conflicts

Types of Conflicts

Designating a Conflict Resolution Method

Detecting Update Conflicts in a Column Group

Selecting a Conflict Resolution Method

System-Defined Routines for Update Conflicts

System-Defined Routines for Unique Constraint Conflicts

Avoiding Ordering Conflicts

Why Use Multiple Resolution Methods

Summary of Standard Conflict Resolution Methods

Minimum and Maximum Update

Earliest and Latest Timestamp Update

Additive and Average Update

Priority Group and Site Priority Update

Overwrite and Discard Update

Append Site Name/Sequence Uniqueness

Discard Uniqueness

Conflict Notification

Declaring a Method

Developing a Resolution Strategy

Define Functional Boundaries

Use Generated Primary Key

Conflict Resolution Methods for Dynamic Ownership

Using Timestamp Resolution Method

User-Defined Methods

Avoid Deletes

Setting the Propagation Interval

Using Column Groups

Creating a Column Group with Members

Adding Members to an Existing Column Group

Removing Members from a Column Group

Dropping a Column Group

Creating an Empty Column Group

Designating a Routine for a Table

Changing a Routine

Generating Replication Procedures

Dropping a Routine

Using Priority Groups

Creating a Priority Group

Adding Members to a Priority Group

Altering the Value of a Member

Altering the Priority of a Member

Dropping a Member by Value

Dropping a Member by Priority

Dropping a Priority Group

Using Site Priority

Creating a Site Priority Group

Adding a Site to the Group

Altering the Priority Level of a Site

Altering the Site Associated with a Priority Level

Dropping a Site by Site Name

Dropping a Site by Priority Level

Dropping a Site Priority Group

Viewing Information

Examples

Conflict Notification Log

Sample Conflict Notification Package and Package Body

Creating the Object Group and Replicated Objects

Declaring Conflict Resolution Methods for Update Conflicts

Declaring Conflict Resolution Methods for Unique Conflicts

Sample Timestamp and Site Maintenance Trigger

Writing Your Own Conflict Resolution Routine

Conflict Resolution Routine Parameters

Resolving Update Conflicts

Resolving Uniqueness Conflicts

Resolving Delete Conflicts

Restrictions

Example Conflict Resolution Routine

Determining the Cause of an Error

Determining the Type of an Argument

Determining the Value of an Argument

Resolving an Error Manually

Re-executing a Transaction

Deleting a Transaction from the DEFERROR View

Deleting a Transaction from the Deferred Transaction Queue

Recovery

Changing Master Definition Sites

Changing a Snapshot Site's Master

Auditing Successful Conflict Resolution

Gathering Conflict Resolution Statistics

Viewing Conflict Resolution Statistics

Canceling Conflict Resolution Statistics

Removing Statistics Information

Determining Outstanding Changes

Applying Outstanding Changes

Removing Log Information

Performing Checks on Imported Data

Supplying Asynchronous DDL

Determining Differences Between Replicated Tables

Updating The Comments Fields in Views

Partitioned Tables and Indexes

Using Procedural Replication

Restrictions on Procedural Replication

Serialization of Transactions

Generating Support for Replicated Procedures

Using Synchronous Data Propagation

Understanding Synchronous Data Propagation

Adding New Sites to an Advanced Replication Environment

Altering a Master Site's Data Propagation Mode

Designing for Survivability

Oracle Parallel Server versus Advanced Replication

Designing for Survivability

Implementing a Survivable System

Snapshot Cloning and Offline Instantiation

Snapshot Cloning for Basic Replication Environments

Offline Instantiation of a Master Site in an Advanced
Replication System

Offline Instantiation of a Snapshot Site in an Advanced
Replication System

Alternative Security Setup for an Advanced Replication Snapshot Site

Avoiding Delete Conflicts

Using Dynamic Ownership Conflict Avoidance

Workflow

Token Passing

Locating the Owner of a Row

Grabbing Ownership

Applying the Change

Modifying Tables without Replicating the Modifications

Disabling the Advanced Replication Facility

Re-enabling the Advanced Replication Facility

Triggers and Replication

Enabling/Disabling Replication for Snapshots

Minimizing Data Propagation

Further Reducing Data Propagation

Diagnosing Problems with Database Links

Diagnosing Problems with DBA_REPCATLOG Entries

Entries Not Removed from Log

Disabling Job Queues

Diagnosing Problems with Job Queues

Diagnosing Problems with Master Sites

Replicated Objects Not Created at New Master Site

DDL Changes Not Propagated to Master Site

DML Changes Not Asynchronously Propagated to Other Sites

DML Cannot be Applied to Replicated Table

Bulk Updates and Constraint Violations

Re-creating a Replicated Object

Unable to Generate Replication Support for a Table

Problems with Replicated Procedures or Triggers

Diagnosing Problems with the Deferred Transaction Queue

Diagnosing Problems with Snapshots

Problems Creating Replicated Objects at Snapshot Site

Problems with Snapshot Refresh

Listing Information about Deferred Transactions

Creating a Deferred Transaction

Security

Specifying a Destination

Initiating a Deferred Transaction

Deferring a Remote Procedure Call

Queuing a Parameter Value for a Deferred Call

Adding a Destination to the DEFDEFAULTDEST View

Removing a Destination from the DEFDEFAULTDEST View

Executing a Deferred Transaction

LOB Storage

DEFLOB View of Storage for RPC

Packages

Examples of Using Oracle's Replication Management API

Prerequisites to Consider

Replication Manager and Oracle Replication Management API

DBMS_DEFER Package

DBMS_DEFER.CALL

DBMS_DEFER.COMMIT_WORK

DBMS_DEFER.datatype_ARG

DBMS_DEFER.TRANSACTION

DBMS_DEFER_QUERY Package

DBMS_DEFER_QUERY.GET_ARG_FORM

DBMS_DEFER_QUERY.GET_ARG_TYPE

DBMS_DEFER_QUERY.GET_CALL_ARGS

DBMS_DEFER_QUERY.GET_datatype_ARG

DBMS_DEFER_SYS Package

DBMS_DEFER_SYS.ADD_DEFAULT_DEST

DBMS_DEFER_SYS.DELETE_DEFAULT_DEST

DBMS_DEFER_SYS.DELETE_DEF_DESTINATION

DBMS_DEFER_SYS.DELETE_ERROR

DBMS_DEFER_SYS.DELETE_TRAN

DBMS_DEFER_SYS.DISABLED

DBMS_DEFER_SYS.EXCLUDE_PUSH

DBMS_DEFER_SYS.EXECUTE_ERROR

DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER

DBMS_DEFER_SYS.PURGE

DBMS_DEFER_SYS.PUSH

DBMS_DEFER_SYS.REGISTER_PROPAGATOR

DBMS_DEFER_SYS.SCHEDULE_PURGE

DBMS_DEFER_SYS.SCHEDULE_PUSH

DBMS_DEFER_SYS.SET_DISABLED

DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR

DBMS_DEFER_SYS.UNSCHEDULE_PURGE

DBMS_DEFER_SYS.UNSCHEDULE_PUSH

DBMS_OFFLINE_OG Package

DBMS_OFFLINE_OG.BEGIN_INSTANTIATION

DBMS_OFFLINE_OG.BEGIN_LOAD

DBMS_OFFLINE_OG.END_INSTANTIATION

DBMS_OFFLINE_OG.END_LOAD

DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS

DBMS_OFFLINE_SNAPSHOT Package

DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD

DBMS_OFFLINE_SNAPSHOT.END_LOAD

DBMS_RECTIFIER_DIFF Package

DBMS_RECTIFIER_DIFF.DIFFERENCES

DBMS_RECTIFIER_DIFF.RECTIFY

DBMS_REFRESH.SUBTRACT

DBMS_REPCAT Package

DBMS_REPCAT.ADD_GROUPED_COLUMN

DBMS_REPCAT.ADD_MASTER_DATABASE

DBMS_REPCAT.ADD_PRIORITY_datatype

DBMS_REPCAT.ADD_SITE_PRIORITY_SITE

DBMS_REPCAT.ADD_conflicttype_RESOLUTION

DBMS_REPCAT.ALTER_MASTER_PROPAGATION

DBMS_REPCAT.ALTER_MASTER_REPOBJECT

DBMS_REPCAT.ALTER_PRIORITY

DBMS_REPCAT.ALTER_PRIORITY_datatype

DBMS_REPCAT.ALTER_SITE_PRIORITY

DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE

DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION

DBMS_REPCAT.CANCEL_STATISTICS

DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP

DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP/
DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY

DBMS_REPCAT.COMMENT_ON_REPGROUP

DBMS_REPCAT.COMMENT_ON_REPSITES

DBMS_REPCAT.COMMENT_ON_REPOBJECT

DBMS_REPCAT.COMMENT_ON_conflicttype_RESOLUTION

DBMS_REPCAT.CREATE_MASTER_REPGROUP

DBMS_REPCAT.CREATE_MASTER_REPOBJECT

DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT

DBMS_REPCAT.DEFINE_COLUMN_GROUP

DBMS_REPCAT.DEFINE_PRIORITY_GROUP

DBMS_REPCAT.DEFINE_SITE_PRIORITY

DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN

DBMS_REPCAT.DROP_COLUMN_GROUP

DBMS_REPCAT.DROP_GROUPED_COLUMN

DBMS_REPCAT.DROP_MASTER_REPGROUP

DBMS_REPCAT.DROP_MASTER_REPOBJECT

DBMS_REPCAT.DROP_PRIORITY

DBMS_REPCAT.DROP_PRIORITY_GROUP

DBMS_REPCAT.DROP_PRIORITY_datatype

DBMS_REPCAT.DROP_SITE_PRIORITY

DBMS_REPCAT.DROP_SITE_PRIORITY_SITE

DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP

DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT

DBMS_REPCAT.DROP_conflicttype_RESOLUTION

DBMS_REPCAT.EXECUTE_DDL

DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT

DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER

Altering Propagation Mode

DBMS_REPCAT.GENERATE_SNAPSHOT_SUPPORT

DBMS_REPCAT.MAKE_COLUMN_GROUP

DBMS_REPCAT.PURGE_MASTER_LOG

DBMS_REPCAT.PURGE_STATISTICS

DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP

DBMS_REPCAT.REGISTER_SNAPSHOT_REPGROUP

DBMS_REPCAT.REGISTER_STATISTICS

DBMS_REPCAT.RELOCATE_MASTERDEF

Usage Notes

DBMS_REPCAT.REMOVE_MASTER_DATABASES

DBMS_REPCAT.REPCAT_IMPORT_CHECK

DBMS_REPCAT.RESUME_MASTER_ACTIVITY

DBMS_REPCAT.SEND_AND_COMPARE_OLD_VALUES

DBMS_REPCAT.SET_COLUMNS

DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY

DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER

DBMS_REPCAT.UNREGISTER_SNAPSHOT_REPGROUP

DBMS_REPCAT.VALIDATE

DBMS_REPCAT.WAIT_MASTER_LOG

DBMS_REPCAT_ADMIN Package

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA

DBMS_REPCAT_ADMIN.GRANT_ADMIN_SCHEMA

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_SCHEMA

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_SCHEMA

DBMS_REPCAT_AUTH Package

DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT

DBMS_REPCAT_AUTH.REVOKE_SURROGATE_REPCAT

DBMS_REPUTIL Package

DBMS_REPUTIL.REPLICATION_OFF

DBMS_REPUTIL.REPLICATION_ON

DBMS_SNAPSHOT Package

DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION

DBMS_SNAPSHOT.END_TABLE_REORGANIZATION

DBMS_SNAPSHOT.I_AM_A_REFRESH Function

DBMS_SNAPSHOT.PURGE_LOG

DBMS_SNAPSHOT.REFRESH

DBMS_SNAPSHOT.REGISTER_SNAPSHOT

DBMS_SNAPSHOT.SET_I_AM_A_REFRESH

DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT

Package Variables

Replication Catalog Views

Deferred Transaction Views

Snapshot and Snapshot Refresh Group Views

SNAPSHOTS View

REGISTERED_SNAPSHOTS View

SNAPSHOTS_LOGS View

REFRESH View

REFRESH_CHILDREN View

Performance Enhancements

Parallel Propagation of Deferred Transactions

Internalized Replication Triggers

Reduced Data Propagation

Data Subsetting Based on Subqueries

Large Object Datatypes (LOBs) Support

Improved Management and Ease of Use

Fine grained Quiesce

Primary Key Snapshots

Snapshot Registration at Master Sites

Reorganizing Tables With Capability of Fast Refresh

Support for Offline Instantiation

Deferred Constraints for Updatable Snapshots

Validate Procedure

Partitioned Tables and Indexes

Enhanced, System-Based Security Model

New Replication Manager Features

Migration Overview

Migration of Master Sites

Migration of All Master Sites at One Time

Incremental Migration of Master Sites

Migration of Snapshot Sites

Migration Using Export/ Import

Upgrading to Primary Key Snapshots

Primary Key Snapshots Conversion at Master Site(s)

Primary Key Snapshot Conversion at Snapshot Site(s)

Features Requiring Migration to Oracle8

Obsolete procedures

Contents

Title and Copyright Information

Preface

Chapter 1 Understanding Replication

Chapter 2 Using Basic Replication

Chapter 3 Using Multimaster Replication

Chapter 4 Using Snapshot Site Replication

Chapter 5 Conflict Resolution

Chapter 6 Administering a Replicated Environment

Chapter 7 Advanced Techniques

Chapter 8 Troubleshooting

Chapter 9 Using Deferred Transactions

Chapter 10 Replication Management API Reference

Chapter 11 Data Dictionary Views

Appendix A New Features

Appendix B Migration and Compatibility