Oracle Database Advanced Replication

342 Pages·2003·3.28 MB·English

by  Oracle Corporation

Oracle® Database
Advanced Replication
10g Release 1 (10.1)
Part No. B10732-01
December 2003
Oracle Database Advanced Replication, 10g Release 1 (10.1)
Part No. B10732-01
Copyright © 1996, 2003 Oracle Corporation. All rights reserved.
Primary Author: 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.
The information contained in this document is subject to change without notice. If you find any problems
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, Oracle8, SQL*Net, SQL*Plus, PL/SQL, and Oracle Store
are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their
respective owners.
Contents
Send Us Your Comments................................................................................................................... ix
Preface............................................................................................................................................................ xi
1 Introduction to Advanced Replication
Overview of Replication................................................................................................................... 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-13
Administration Tools for a Replication Environment............................................................... 1-15
Replication Management Tool in the Oracle Enterprise Manager Console....................... 1-15
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
Synchronous Replication........................................................................................................... 1-19
Procedural Replication............................................................................................................... 1-19
iii
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
How Replication Works with Object Types and Collections............................................... 2-12
Master Replication Architecture.................................................................................................... 2-18
Master Site Mechanisms............................................................................................................ 2-18
Administrative Mechanisms..................................................................................................... 2-32
Organizational Mechanisms..................................................................................................... 2-37
Propagation Mechanism............................................................................................................ 2-40
Performance Mechanisms.......................................................................................................... 2-47
Replication Protection Mechanisms......................................................................................... 2-52
Conflict Resolution Mechanisms.............................................................................................. 2-56
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-5
Available Materialized Views..................................................................................................... 3-8
Required Privileges for Materialized View Operations........................................................ 3-15
Data Subsetting with Materialized Views............................................................................... 3-18
Determining the Fast Refresh Capabilities of a Materialized View.................................... 3-30
Multitier Materialized Views.................................................................................................... 3-31
How Materialized Views Work with Object Types and Collections.................................. 3-37
Materialized View Registration at a Master Site or Master Materialized View Site......... 3-49
Materialized View Architecture..................................................................................................... 3-50
Master Site and Master Materialized View Site Mechanisms.............................................. 3-52
Materialized View Site Mechanisms........................................................................................ 3-56
Organizational Mechanisms..................................................................................................... 3-58
Refresh Process............................................................................................................................ 3-63
iv
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-10
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-28
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-18
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-40
Send and Compare Old Values................................................................................................ 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-5
Master Sites and Materialized View Sites..................................................................................... 6-8
Advantages of Master Sites......................................................................................................... 6-8
Advantages of Materialized View Sites..................................................................................... 6-9
Preparing for Materialized Views.............................................................................................. 6-9
Creating a Materialized View Log........................................................................................... 6-14
Creating a Materialized View Environment........................................................................... 6-16
Avoiding Problems When Adding a New Materialized View Site..................................... 6-18
Interoperability in an Advanced Replication Environment..................................................... 6-20
Guidelines for Scheduled Links.................................................................................................... 6-20
Scheduling Periodic Pushes...................................................................................................... 6-21
Scheduling Continuous Pushes................................................................................................ 6-22
Guidelines for Scheduled Purges of a Deferred Transaction Queue..................................... 6-23
Scheduling Periodic Purges....................................................................................................... 6-24
Scheduling Continuous Purges................................................................................................ 6-25
Serial and Parallel Propagation...................................................................................................... 6-26
Deployment Templates.................................................................................................................... 6-27
Preparing Materialized View Sites for Instantiation of Deployment Templates.............. 6-27
Conflict Resolution........................................................................................................................... 6-30
Security............................................................................................................................................... 6-30
Designing for Survivability............................................................................................................ 6-31
Oracle Real Application Clusters versus Replication............................................................ 6-32
Designing a Replication Environment for Survivability....................................................... 6-33
Implementing a Survivable System......................................................................................... 6-34
7 Introduction to the Replication Management Tool
Usage Scenarios for the Replication Management Tool.............................................................. 7-2
Logging into the Replication Management Tool.......................................................................... 7-3
vi
The Replication Management Tool Interface................................................................................ 7-4
Navigator Pane............................................................................................................................. 7-5
Right Pane...................................................................................................................................... 7-9
The Replication Management Tool Wizards............................................................................... 7-15
Setup Wizard............................................................................................................................... 7-15
Materialized View Group Wizard............................................................................................ 7-18
Deployment Template Wizard................................................................................................. 7-20
Template Script Generation Wizard........................................................................................ 7-22
Copy Template Wizard.............................................................................................................. 7-24
Flowchart for Creating a Replication Environment................................................................... 7-26
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
Re-creating a Replicated Object.................................................................................................. A-5
Unable to Generate Replication Support for a Table............................................................... A-5
Problems with Replicated Procedures or Triggers.................................................................. A-5
Diagnosing Problems with the Deferred Transaction Queue.................................................... A-6
Check Jobs for Scheduled Links................................................................................................. A-6
Distributed Transaction Problems with Synchronous Replication....................................... A-6
Incomplete Database Link Specifications.................................................................................. A-6
Incorrect Replication Catalog Views......................................................................................... A-7
Diagnosing Problems with Materialized Views........................................................................... A-7
Problems Creating Replicated Objects at Materialized View Site......................................... A-7
Problems Performing Offline Instantiation of a Deployment Template.............................. A-8
Refresh Problems.......................................................................................................................... A-8
Advanced Troubleshooting of Refresh Problems.................................................................. A-10
vii
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
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-7
Replication of NCLOB Datatype Columns............................................................................... B-9
Index
viii
Send Us Your Comments
Oracle Database Advanced Replication, 10g Release 1 (10.1)
Part No. B10732-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.
(cid:1) Did you find any errors?
(cid:1) Is the information clearly presented?
(cid:1) Do you need more information? If so, where?
(cid:1) Are the examples correct? Do you need more examples?
(cid:1) 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:
(cid:1) Electronic mail: [email protected]
(cid:1) FAX: [phone] Attn: Server Technologies Documentation Manager
(cid:1) 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 proble ms with the software, please contact your local Oracle Support Services.
ix
x