Oracle9i Advanced Replication

360 Pages·1996·2.88 MB·English

by  Oracle, Inc.

Oracle9i
Advanced Replication
Release 2 (9.2)
March 2002
Part No. A96567-01
Oracle9i Advanced Replication, Release 2 (9.2)
Part No. A96567-01
Copyright © 1996, 2002 Oracle Corporation. All rights reserved.
Primary Author: Ted Burroughs
Contributing Authors: Randy Urbano
Graphic Artist: Valarie Moore
Contributors: Nimar Arora, Sukanya Balaraman, Ruth Baylis, Yuen Chan, Al Demers, Alan Downing,
Curt Elsbernd, Yong Feng, Jairaj Galagali, Lewis Kaplan, Jonathan Klein, Anand Lakshminath, Jing Liu,
Edwina Lu, Pat McElroy, Maria Pratt, Arvind Rajaram, Neeraj Shodhan, Wayne Smith, Jim Stamos, Janet
Stern, Mahesh Subramaniam, Lik Wong, David Zhang
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent and other intellectual and industrial property
laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required
to obtain interoperability with other independently created software or as specified by law, is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle9i, Oracle8i, Oracle8, Oracle7, SQL*Net, SQL*Plus, PL/SQL,
and Oracle Storeare trademarks or registered trademarks of Oracle Corporation. Other names may be
trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................... ix
Preface............................................................................................................................................................ xi
What’s New in Advanced Replication?.................................................................................... xix
1 Introduction to Advanced Replication
Replication Overview........................................................................................................................ 1-2
Applications That Use Replication................................................................................................. 1-3
Replication Objects, Groups, and Sites.......................................................................................... 1-4
Replication Objects....................................................................................................................... 1-4
Replication Groups....................................................................................................................... 1-5
Replication Sites............................................................................................................................ 1-5
Types of Replication Environments................................................................................................ 1-6
Multimaster Replication.............................................................................................................. 1-6
Materialized View Replication................................................................................................... 1-8
Multimaster and Materialized View Hybrid Configurations.............................................. 1-14
Administration Tools for a Replication Environment............................................................... 1-15
Replication Management Tool in Oracle Enterprise Manager............................................ 1-16
Replication Management API................................................................................................... 1-17
Replication Catalog.................................................................................................................... 1-17
Distributed Schema Management............................................................................................ 1-17
Replication Conflicts........................................................................................................................ 1-18
Other Options for Multimaster Replication................................................................................ 1-19
iii
Synchronous Replication........................................................................................................... 1-19
Procedural Replication............................................................................................................... 1-19
2 Master Replication Concepts and Architecture
Master Replication Concepts............................................................................................................ 2-2
What is Master Replication?........................................................................................................ 2-2
Why Use Multimaster Replication?........................................................................................... 2-4
Multimaster Replication Process................................................................................................ 2-7
Conflict Resolution Concepts.................................................................................................... 2-10
Replication of User-Defined Types.......................................................................................... 2-12
Master Replication Architecture.................................................................................................... 2-18
Master Site Mechanisms............................................................................................................ 2-18
Administrative Mechanisms..................................................................................................... 2-31
Organizational Mechanisms..................................................................................................... 2-37
Propagation Mechanism............................................................................................................ 2-40
Performance Mechanisms.......................................................................................................... 2-47
Replication Protection Mechanisms......................................................................................... 2-53
Conflict Resolution Mechanisms.............................................................................................. 2-57
3 Materialized View Concepts and Architecture
Materialized View Concepts............................................................................................................. 3-2
What is a Materialized View?..................................................................................................... 3-2
Why Use Materialized Views?.................................................................................................... 3-3
Read-Only, Updatable, and Writeable Materialized Views................................................... 3-4
Available Materialized Views..................................................................................................... 3-7
Required Privileges for Materialized View Operations........................................................ 3-14
Data Subsetting with Materialized Views............................................................................... 3-17
Determining the Fast Refresh Capabilities of a Materialized View.................................... 3-29
Multitier Materialized Views.................................................................................................... 3-30
Materialized Views with User-Defined Types....................................................................... 3-36
Materialized View Registration at a Master Site or Master Materialized View Site......... 3-47
Materialized View Architecture..................................................................................................... 3-49
Master Site and Master Materialized View Site Mechanisms.............................................. 3-51
Materialized View Site Mechanisms........................................................................................ 3-55
Organizational Mechanisms..................................................................................................... 3-58
iv
Refresh Process........................................................................................................................... 3-64
4 Deployment Templates Concepts and Architecture
Mass Deployment Challenge........................................................................................................... 4-2
Deployment Templates and the Mass Deployment Goal....................................................... 4-2
Oracle Deployment Templates Concepts....................................................................................... 4-3
Deployment Template Elements................................................................................................ 4-4
Deployment Template Packaging and Instantiation............................................................... 4-9
Deployment Template Architecture.............................................................................................. 4-13
Template Definitions Stored in System Tables....................................................................... 4-14
Packaging and Instantiation Process....................................................................................... 4-15
After Instantiation...................................................................................................................... 4-18
Deployment Template Design....................................................................................................... 4-20
Column Subsetting with Deployment Templates................................................................. 4-20
Row Subsetting........................................................................................................................... 4-22
Data Sets....................................................................................................................................... 4-26
Additional Design Considerations........................................................................................... 4-27
Local Control of Materialized View Creation............................................................................. 4-28
5 Conflict Resolution Concepts and Architecture
Conflict Resolution Concepts........................................................................................................... 5-2
Understanding Your Data and Application Requirements.................................................... 5-2
Types of Replication Conflicts.................................................................................................... 5-3
Conflict Detection......................................................................................................................... 5-5
Conflict Resolution....................................................................................................................... 5-7
Techniques for Avoiding Conflicts.......................................................................................... 5-17
Conflict Resolution Architecture................................................................................................... 5-22
Support Mechanisms................................................................................................................. 5-22
Common Update Conflict Resolution Methods..................................................................... 5-24
Additional Update Conflicts Resolution Methods................................................................ 5-28
Uniqueness Conflicts Resolution Methods............................................................................. 5-37
Delete Conflict Resolution Methods........................................................................................ 5-39
Performance Mechanisms and Conflict Resolution.............................................................. 5-40
v
6 Planning Your Replication Environment
Considerations for Replicated Tables............................................................................................. 6-2
Primary Keys................................................................................................................................. 6-2
Foreign Keys.................................................................................................................................. 6-2
Datatype Considerations............................................................................................................. 6-2
Row-Level Dependency Tracking.............................................................................................. 6-4
Initialization Parameters................................................................................................................... 6-4
Master Sites and Materialized View Sites..................................................................................... 6-9
Advantages of Master Sites....................................................................................................... 6-10
Advantages of Materialized View Sites................................................................................... 6-10
Preparing for Materialized Views............................................................................................ 6-11
Creating a Materialized View Log........................................................................................... 6-17
Creating a Materialized View Environment........................................................................... 6-19
Avoiding Problems When Adding a New Materialized View Site..................................... 6-21
Guidelines for Scheduled Links.................................................................................................... 6-22
Scheduling Periodic Pushes...................................................................................................... 6-23
Scheduling Continuous Pushes................................................................................................ 6-24
Guidelines for Scheduled Purges of a Deferred Transaction Queue..................................... 6-25
Scheduling Periodic Purges....................................................................................................... 6-26
Scheduling Continuous Purges................................................................................................ 6-27
Serial and Parallel Propagation...................................................................................................... 6-28
Deployment Templates.................................................................................................................... 6-29
Preparing Materialized View Sites for Instantiation of Deployment Templates.............. 6-29
Conflict Resolution........................................................................................................................... 6-32
Security............................................................................................................................................... 6-32
Designing for Survivability............................................................................................................ 6-33
Oracle Real Application Clusters versus Replication............................................................ 6-34
Designing a Replication Environment for Survivability....................................................... 6-35
Implementing a Survivable System......................................................................................... 6-36
7 Replication Management Tool Introduction
Usage Scenarios for the Replication Management Tool.............................................................. 7-2
Logging in to the Replication Management Tool......................................................................... 7-3
The Replication Management Tool Interface................................................................................ 7-4
Navigator Pane.............................................................................................................................. 7-5
vi
Right Pane...................................................................................................................................... 7-9
The Replication Management Tool Wizards............................................................................... 7-15
Setup Wizard............................................................................................................................... 7-16
Materialized View Group Wizard............................................................................................ 7-19
Deployment Template Wizard................................................................................................. 7-20
Template Script Generation Wizard........................................................................................ 7-22
Copy Template Wizard.............................................................................................................. 7-23
Flowchart for Creating a Replication Environment................................................................... 7-25
A Troubleshooting Replication Problems
Diagnosing Problems with Database Links.................................................................................. A-2
Diagnosing Problems with Master Sites........................................................................................ A-2
Replicated Objects Not Created at New Master Site............................................................... A-3
DDL Changes Not Propagated to Master Site.......................................................................... A-3
DML Changes Not Asynchronously Propagated to Other Sites........................................... A-4
DML Cannot be Applied to Replicated Table.......................................................................... A-4
Bulk Updates and Constraint Violations.................................................................................. A-4
Recreating a Replicated Object................................................................................................... A-5
Unable to Generate Replication Support for a Table............................................................... A-5
Problems with Replicated Procedures or Triggers.................................................................. A-5
Problems With ON DELETE CASCADE and Integrity Constraints..................................... A-6
Diagnosing Problems with the Deferred Transaction Queue.................................................... A-7
Check Jobs for Scheduled Links................................................................................................. A-7
Distributed Transaction Problems with Synchronous Replication....................................... A-7
Incomplete Database Link Specifications.................................................................................. A-7
Incorrect Replication Catalog Views.......................................................................................... A-7
Diagnosing Problems with Materialized Views........................................................................... A-8
Problems Creating Replicated Objects at Materialized View Site......................................... A-8
Problems Performing Offline Instantiation of a Deployment Template.............................. A-9
Refresh Problems.......................................................................................................................... A-9
Advanced Troubleshooting of Refresh Problems.................................................................. A-11
B Column Length Semantics and Unicode
Column Length Semantics for Replication Sites and Table Columns..................................... B-2
Multimaster Support for Column Length Semantics.................................................................. B-3
vii
Column Length Semantics Support for Tables Generated by Advanced Replication....... B-3
Column Length Semantics Support for Precreated Tables..................................................... B-4
Materialized View Support for Column Length Semantics....................................................... B-5
Materialized Views with Prebuilt Container Tables............................................................... B-5
Column Length Semantics Support for Updatable Materialized Views.............................. B-6
DDL Propagation and Column Length Semantics....................................................................... B-7
Replication Support for Unicode..................................................................................................... B-8
Replication of NCLOB Datatype Columns............................................................................... B-9
Index
viii
Send Us Your Comments
Oracle9i Advanced Replication, Release 2 (9.2)
Part No. A96567-01
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
document. Your input is an important part of the information used for revision.
n Did you find any errors?
n Is the information clearly presented?
n Do you need more information? If so, where?
n Are the examples correct? Do you need more examples?
n What features did you like most?
If you find any errors or have any other suggestions for improvement, please indicate the document
title and part number, and the chapter, section, and page number (if available). You can send com-
ments to us in the following ways:
n Electronic mail: [email protected]
n FAX: [phone] Attn: Server Technologies Documentation Manager
n Postal service:
Oracle Corporation
Server Technologies Documentation
500 Oracle Parkway, Mailstop 4op11
Redwood Shores, CA 94065
USA
If you would like a reply, please give your name, address, telephone number, and (optionally) elec-
tronic mail address.
If you have problems with the software, please contact your local Oracle Support Services.
ix
x