1/32

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Part I Oracle Streams Replication Concepts

1 Understanding Oracle Streams Replication

• Overview of Oracle Streams Replication
  • Rules in an Oracle Streams Replication Environment
  • Nonidentical Replicas with Oracle Streams
  • Subsetting with Oracle Streams
• Capture and Oracle Streams Replication
  • Change Capture Using a Capture Process
    • Capture Process Overview
    • Supplemental Logging for Oracle Streams Replication
  • Change Capture Using a Synchronous Capture
  • Change Capture Using a Custom Application
• Propagation and Oracle Streams Replication
  • LCR Staging
  • LCR Propagation
  • Combined Capture and Apply Optimization
• Apply and Oracle Streams Replication
  • Overview of the Apply Process
  • Apply Processing Options for LCRs
    • Captured LCRs and Persistent LCRs
    • Direct and Custom Apply of LCRs
  • Apply Processes and Dependencies
    • How Dependent Transactions Are Applied
    • Row LCR Ordering During Apply
    • Dependencies and Constraints
    • Dependency Detection, Rule-Based Transformations, and Apply Handlers
    • Virtual Dependency Definitions
    • Barrier Transactions
  • Considerations for Applying DML Changes to Tables
    • Constraints and Applying DML Changes to Tables
    • Substitute Key Columns
    • Apply Process Behavior for Column Discrepancies
    • Conflict Resolution and an Apply Process
    • Handlers and Row LCR Processing
  • Considerations for Applying DDL Changes
    • System-Generated Names
    • CREATE TABLE AS SELECT Statements
  • Instantiation SCN and Ignore SCN for an Apply Process
  • The Oldest SCN for an Apply Process
  • Low-Watermark and High-Watermark for an Apply Process
  • Trigger Firing Property

2 Instantiation and Oracle Streams Replication

• Overview of Instantiation and Oracle Streams Replication
• Capture Rules and Preparation for Instantiation
  • DBMS_STREAMS_ADM Package Procedures Automatically Prepare Objects
  • When Preparing for Instantiation Is Required
  • Supplemental Logging Options During Preparation for Instantiation
• Oracle Data Pump and Oracle Streams Instantiation
  • Data Pump Export and Object Consistency
  • Oracle Data Pump Import and Oracle Streams Instantiation
    • Instantiation SCNs and Data Pump Imports
    • Instantiation SCNs and Oracle Streams Tags Resulting from Data Pump Imports
    • The STREAMS_CONFIGURATION Data Pump Import Utility Parameter
• Recovery Manager (RMAN) and Oracle Streams Instantiation
  • The RMAN DUPLICATE and CONVERT DATABASE Commands and Instantiation
  • The RMAN TRANSPORT TABLESPACE Command and Instantiation

3 Oracle Streams Conflict Resolution

• About DML Conflicts in an Oracle Streams Environment
• Conflict Types in an Oracle Streams Environment
  • Update Conflicts in an Oracle Streams Environment
  • Uniqueness Conflicts in an Oracle Streams Environment
  • Delete Conflicts in an Oracle Streams Environment
  • Foreign Key Conflicts in an Oracle Streams Environment
• Conflicts and Transaction Ordering in an Oracle Streams Environment
• Conflict Detection in an Oracle Streams Environment
  • Control Over Conflict Detection for Nonkey Columns
  • Rows Identification During Conflict Detection in an Oracle Streams Environment
• Conflict Avoidance in an Oracle Streams Environment
  • Use a Primary Database Ownership Model
  • Avoid Specific Types of Conflicts
    • Avoid Uniqueness Conflicts in an Oracle Streams Environment
    • Avoid Delete Conflicts in an Oracle Streams Environment
    • Avoid Update Conflicts in an Oracle Streams Environment
• Conflict Resolution in an Oracle Streams Environment
  • Prebuilt Update Conflict Handlers
    • Types of Prebuilt Update Conflict Handlers
    • Column Lists
    • Resolution Columns
    • Data Convergence
  • Custom Conflict Handlers

4 Oracle Streams Tags

• Introduction to Tags
• Tags and Rules Created by the DBMS_STREAMS_ADM Package
• Tags and Online Backup Statements
• Tags and an Apply Process
• Oracle Streams Tags in a Replication Environment
  • N-Way Replication Environments
  • Hub-and-Spoke Replication Environments
  • Hub-and-Spoke Replication Environment with Several Extended Secondary Databases

5 Oracle Streams Heterogeneous Information Sharing

• Oracle to Non-Oracle Data Sharing with Oracle Streams
  • Change Capture and Staging in an Oracle to Non-Oracle Environment
  • Change Apply in an Oracle to Non-Oracle Environment
    • Apply Process Configuration in an Oracle to Non-Oracle Environment
    • Data Types Applied at Non-Oracle Databases
    • Types of DML Changes Applied at Non-Oracle Databases
    • Instantiation in an Oracle to Non-Oracle Environment
  • Transformations in an Oracle to Non-Oracle Environment
  • Messaging Gateway and Oracle Streams
  • Error Handling in an Oracle to Non-Oracle Environment
  • Example Oracle to Non-Oracle Streams Environment
• Non-Oracle to Oracle Data Sharing with Oracle Streams
  • Change Capture in a Non-Oracle to Oracle Environment
  • Staging in a Non-Oracle to Oracle Environment
  • Change Apply in a Non-Oracle to Oracle Environment
  • Instantiation from a Non-Oracle Database to an Oracle Database
• Non-Oracle to Non-Oracle Data Sharing with Oracle Streams

Part II Configuring Oracle Streams Replication

6 Simple Oracle Streams Replication Configuration

• Configuring Replication Using an Oracle Streams Wizard in Enterprise Manager
  • Oracle Streams Global, Schema, Table, and Subset Replication Wizard
  • Oracle Streams Tablespace Replication Wizard
  • Opening an Oracle Streams Replication Configuration Wizard
• Configuring Replication Using the DBMS_STREAMS_ADM Package
  • Preparing to Configure Oracle Streams Replication Using the DBMS_STREAMS_ADM Package
    • Decisions to Make Before Configuring Oracle Streams Replication
    • Tasks to Complete Before Configuring Oracle Streams Replication
  • Configuring Database Replication Using the DBMS_STREAMS_ADM Package
  • Configuring Tablespace Replication Using the DBMS_STREAMS_ADM Package
  • Configuring Schema Replication Using the DBMS_STREAMS_ADM Package
  • Configuring Table Replication Using the DBMS_STREAMS_ADM Package

7 Flexible Oracle Streams Replication Configuration

• Creating a New Oracle Streams Single-Source Environment
• Creating a New Oracle Streams Multiple-Source Environment
  • Configuring Populated Databases When Creating a Multiple-Source Environment
  • Adding Shared Objects to Import Databases When Creating a New Environment
  • Complete the Multiple-Source Environment Configuration

8 Adding to an Oracle Streams Replication Environment

• Adding Shared Objects to an Existing Single-Source Environment
• Adding a New Destination Database to a Single-Source Environment
• Adding Shared Objects to an Existing Multiple-Source Environment
  • Configuring Populated Databases When Adding Shared Objects
  • Adding Shared Objects to Import Databases in an Existing Environment
  • Finish Adding Objects to a Multiple-Source Environment Configuration
• Adding a New Database to an Existing Multiple-Source Environment
  • Configuring Databases If the Shared Objects Already Exist at the New Database
  • Adding Shared Objects to a New Database

Part III Administering Oracle Streams Replication

9 Managing Capture, Propagation, and Apply

• Managing Capture for Oracle Streams Replication
  • Creating a Capture Process
  • Creating a Synchronous Capture
  • Managing Supplemental Logging in an Oracle Streams Replication Environment
    • Specifying Table Supplemental Logging Using Unconditional Log Groups
    • Specifying Table Supplemental Logging Using Conditional Log Groups
    • Dropping a Supplemental Log Group
    • Specifying Database Supplemental Logging of Key Columns
    • Dropping Database Supplemental Logging of Key Columns
• Managing Staging and Propagation for Oracle Streams Replication
  • Creating an ANYDATA Queue to Stage LCRs
  • Creating a Propagation that Propagates LCRs
• Managing Apply for Oracle Streams Replication
  • Creating an Apply Process That Applies Captured LCRs
  • Creating an Apply Process That Applies Persistent LCRs and User Messages
  • Managing the Substitute Key Columns for a Table
    • Setting Substitute Key Columns for a Table
    • Removing the Substitute Key Columns for a Table
  • Managing a DML Handler
    • Creating a DML Handler
    • Setting a DML Handler
    • Unsetting a DML Handler
  • Managing a DDL Handler
    • Creating a DDL Handler for an Apply Process
    • Setting the DDL Handler for an Apply Process
    • Removing the DDL Handler for an Apply Process
  • Using Virtual Dependency Definitions
    • Setting and Unsetting Value Dependencies
    • Creating and Dropping Object Dependencies
  • Managing Oracle Streams Conflict Detection and Resolution
    • Setting an Update Conflict Handler
    • Modifying an Existing Update Conflict Handler
    • Removing an Existing Update Conflict Handler
    • Stopping Conflict Detection for Nonkey Columns
• Managing Oracle Streams Tags
  • Managing Oracle Streams Tags for the Current Session
    • Setting the Tag Values Generated by the Current Session
    • Getting the Tag Value for the Current Session
  • Managing Oracle Streams Tags for an Apply Process
    • Setting the Tag Values Generated by an Apply Process
    • Removing the Apply Tag for an Apply Process
• Splitting and Merging an Oracle Streams Destination
  • About Splitting and Merging Oracle Streams
    • Automatic Merge of Oracle Streams
    • Split and Merge With Generated Scripts
  • Examples That Split and Merge Oracle Streams
    • Splitting and Merging an Oracle Streams Destination Directly and Automatically
    • Splitting and Merging an Oracle Streams Destination Manually With Scripts
• Changing the DBID or Global Name of a Source Database
• Resynchronizing a Source Database in a Multiple-Source Environment
• Performing Database Point-in-Time Recovery in an Oracle Streams Environment
  • Performing Point-in-Time Recovery on the Source in a Single-Source Environment
  • Performing Point-in-Time Recovery in a Multiple-Source Environment
  • Performing Point-in-Time Recovery on a Destination Database
    • Resetting the Start SCN for the Existing Capture Process to Perform Recovery
    • Creating a New Capture Process to Perform Recovery

10 Performing Instantiations

• Preparing Database Objects for Instantiation at a Source Database
  • Preparing Tables for Instantiation
    • Preparing a Table for Instantiation When a Capture Process Is Used
    • Preparing Tables for Instantiation When a Synchronous Capture Is Used
  • Preparing the Database Objects in a Schema for Instantiation
  • Preparing All of the Database Objects in a Database for Instantiation
• Aborting Preparation for Instantiation at a Source Database
• Instantiating Objects in an Oracle Streams Replication Environment
  • Instantiating Objects Using Data Pump Export/Import
  • Instantiating Objects in a Tablespace Using Transportable Tablespace or RMAN
    • Instantiating Objects Using Transportable Tablespace
    • Instantiating Objects Using Transportable Tablespace from Backup with RMAN
  • Instantiating an Entire Database Using RMAN
    • Instantiating an Entire Database on the Same Platform Using RMAN
    • Instantiating an Entire Database on Different Platforms Using RMAN
• Setting Instantiation SCNs at a Destination Database
  • Setting Instantiation SCNs Using Export/Import
    • Full Database Export and Full Database Import
    • Full Database or User Export and User Import
    • Full Database, User, or Table Export and Table Import
  • Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package
    • Setting the Instantiation SCN While Connected to the Source Database
    • Setting the Instantiation SCN While Connected to the Destination Database

11 Managing Logical Change Records (LCRs)

• Requirements for Managing LCRs
• Constructing and Enqueuing LCRs
• Executing LCRs
  • Executing Row LCRs
    • Example of Constructing and Executing Row LCRs
  • Executing DDL LCRs
• Managing LCRs Containing LOB Columns
  • Apply Process Behavior for Direct Apply of LCRs Containing LOBs
  • LOB Assembly and Custom Apply of LCRs Containing LOB Columns
    • LOB Assembly Considerations
    • LOB Assembly Example
  • Requirements for Constructing and Processing LCRs Containing LOB Columns
    • Requirements for Constructing and Processing LCRs Without LOB Assembly
    • Requirements for Apply Handler Processing of LCRs with LOB Assembly
    • Requirements for Rule-Based Transformation Processing of LCRs with LOBs
  • Example Script for Constructing and Enqueuing LCRs Containing LOBs
• Managing LCRs Containing LONG or LONG RAW Columns

12 Comparing and Converging Data

• About Comparing and Converging Data
  • Scans
  • Buckets
  • Parent Scans and Root Scans
  • How Scans and Buckets Identify Differences
• Other Documentation About the DBMS_COMPARISON Package
• Preparing To Compare and Converge a Shared Database Object
• Diverging a Database Object at Two Databases to Complete Examples
• Comparing a Shared Database Object at Two Databases
  • Comparing a Subset of Columns in a Shared Database Object
  • Comparing a Shared Database Object Without Identifying Row Differences
  • Comparing a Random Portion of a Shared Database Object
  • Comparing a Shared Database Object Cyclically
  • Comparing a Custom Portion of a Shared Database Object
• Viewing Information About Comparisons and Comparison Results
  • Viewing General Information About the Comparisons in a Database
  • Viewing Information Specific to Random and Cyclic Comparisons
  • Viewing the Columns Compared by Each Comparison in a Database
  • Viewing General Information About Each Scan in a Database
  • Viewing the Parent Scan ID and Root Scan ID for Each Scan in a Database
  • Viewing Detailed Information About the Row Differences Found in a Scan
  • Viewing Information About the Rows Compared in Specific Scans
• Converging a Shared Database Object
  • Converging a Shared Database Object for Consistency With the Local Object
  • Converging a Shared Database Object for Consistency With the Remote Object
  • Converging a Shared Database Object With a Session Tag Set
• Rechecking the Comparison Results for a Comparison
• Purging Comparison Results
  • Purging All of the Comparison Results for a Comparison
  • Purging the Comparison Results for a Specific Scan ID of a Comparison
  • Purging the Comparison Results of a Comparison Before a Specified Time
• Dropping a Comparison
• Using DBMS_COMPARISON in an Oracle Streams Replication Environment
  • Checking for Consistency After Instantiation
  • Checking for Consistency in a Running Oracle Streams Replication Environment

13 Monitoring Oracle Streams Replication

• Monitoring the Oracle Streams Topology and Oracle Streams Performance
• Monitoring Supplemental Logging
  • Displaying Supplemental Log Groups at a Source Database
  • Displaying Database Supplemental Logging Specifications
  • Displaying Supplemental Logging Specified During Preparation for Instantiation
    • Displaying Supplemental Logging Enabled by PREPARE_TABLE_INSTANTIATION
    • Displaying Supplemental Logging Enabled by PREPARE_SCHEMA_INSTANTIATION
    • Displaying Supplemental Logging Enabled by PREPARE_GLOBAL_INSTANTIATION
• Monitoring an Apply Process in an Oracle Streams Replication Environment
  • Displaying the Substitute Key Columns Specified at a Destination Database
  • Displaying Information About DML and DDL Handlers
    • Displaying All of the DML Handlers for Local Apply
    • Displaying the DDL Handler for Each Apply Process
  • Monitoring Virtual Dependency Definitions
    • Displaying Value Dependencies
    • Displaying Object Dependencies
  • Displaying Information About Conflict Detection
  • Displaying Information About Update Conflict Handlers
• Monitoring Oracle Streams Tags
  • Displaying the Tag Value for the Current Session
  • Displaying the Default Tag Value for Each Apply Process
• Monitoring Instantiation
  • Determining Which Database Objects Are Prepared for Instantiation
  • Determining the Tables for Which an Instantiation SCN Has Been Set
• Tracking LCRs Through a Stream
• Running Flashback Queries in an Oracle Streams Replication Environment

14 Troubleshooting Oracle Streams Replication

• Responding to Steams Alerts
• Recovering from Configuration Errors
  • Recovery Scenario
• Using the Streams Configuration Report and Health Check Script
• Handling Performance Problems Because of an Unavailable Destination
• Troubleshooting a Capture Process in a Replication Environment
  • Is the Capture Process Waiting for Redo?
  • Is the Capture Process Paused for Flow Control?
• Troubleshooting an Apply Process in a Replication Environment
  • Is the Apply Process Encountering Contention?
  • Is the Apply Process Waiting for a Dependent Transaction?
  • Is an Apply Server Performing Poorly for Certain Transactions?
  • Are There Any Apply Errors in the Error Queue?
    • Using a DML Handler to Correct Error Transactions
    • Troubleshooting Specific Apply Errors

Part IV Oracle Streams Replication Best Practices

15 Best Practices for Oracle Streams Replication Databases

• Best Practices for Oracle Streams Database Configuration
  • Set Initialization Parameters That Are Relevant to Oracle Streams
  • Configure Database Storage in an Oracle Streams Database
    • Configure a Separate Tablespace for the Oracle Streams Administrator
    • Use a Separate Queue for Capture and Apply Oracle Streams Clients
  • Grant User Privileges to the Oracle Streams Administrator
  • Automate the Oracle Streams Replication Configuration
• Best Practices for Oracle Streams Database Operation
  • Follow the Best Practices for the Global Name of an Oracle Streams Database
  • Follow the Best Practices for Replicating DDL Changes
  • Monitor Performance and Make Adjustments When Necessary
  • Monitor Capture Process and Synchronous Capture Queues for Size
  • Follow the Oracle Streams Best Practices for Backups
    • Best Practices for Backups of an Oracle Streams Source Database
    • Best Practices for Backups of an Oracle Streams Destination Database
  • Adjust the Automatic Collection of Optimizer Statistics
  • Check the Alert Log for Oracle Streams Information
  • Follow the Best Practices for Removing an Oracle Streams Configuration at a Database
• Best Practices for Oracle Real Application Clusters and Oracle Streams
  • Make Archive Log Files of All Threads Available to Capture Processes
  • Follow the Best Practices for the Global Name of an Oracle Real Application Clusters Database
  • Follow the Best Practices for Configuring and Managing Propagations
  • Follow the Best Practices for Queue Ownership

16 Best Practices for Capture

• Best Practices for Source Database Configuration for Capture Processes
  • Enable Archive Logging at Each Source Database in an Oracle Streams Environment
  • Add Supplemental Logging at Each Source Database in an Oracle Streams Environment
  • Configure a Heartbeat Table at Each Source Database in an Oracle Streams Environment
• Best Practices for Capture Process Configuration
  • Set Capture Process Parallelism
  • Set the Checkpoint Retention Time
• Best Practices for Capture Process Operation
  • Perform a Dictionary Build and Prepare Database Objects for Instantiation Periodically
  • Minimize the Performance Impact of Batch Processing
• Best Practices for Synchronous Capture Configuration

17 Best Practices for Propagation

• Best Practices for Propagation Configuration
  • Use Queue-to-Queue Propagations
  • Set the Propagation Latency for Each Propagation
  • Increase the SDU in a Wide Area Network for Better Network Performance
• Best Practices for Propagation Operation
  • Restart Broken Propagations

18 Best Practices for Apply

• Best Practices for Destination Database Configuration
  • Grant Required Privileges to the Apply User
  • Set Instantiation SCN Values
  • Configure Conflict Resolution
• Best Practices for Apply Process Configuration
  • Set Apply Process Parallelism
  • Consider Allowing Apply Processes to Continue When They Encounter Errors
• Best Practices for Apply Process Operation
  • Manage Apply Errors

Part V Sample Replication Environments

19 Simple Single-Source Replication Example

• Overview of the Simple Single-Source Replication Example
• Prerequisites
• Create Queues and Database Links
• Configure Capture, Propagation, and Apply for Changes to One Table
• Make Changes to the hr.jobs Table and View Results

20 Single-Source Heterogeneous Replication Example

• Overview of the Single-Source Heterogeneous Replication Example
• Prerequisites
• Create Queues and Database Links
• Example Scripts for Sharing Data from One Database
  • Simple Configuration for Sharing Data from a Single Database
  • Flexible Configuration for Sharing Data from a Single Database
• Make DML and DDL Changes to Tables in the hr Schema
• Add Objects to an Existing Oracle Streams Replication Environment
• Make a DML Change to the hr.employees Table
• Add a Database to an Existing Oracle Streams Replication Environment
• Make a DML Change to the hr.departments Table

21 N-Way Replication Example

• Overview of the N-Way Replication Example
• Prerequisites
• Create the hrmult Schema at the mult1.example.com Database
• Create Queues and Database Links
• Example Script for Configuring N-Way Replication
• Make DML and DDL Changes to Tables in the hrmult Schema

Part VI Appendixes

A Migrating Advanced Replication to Oracle Streams

• Overview of the Migration Process
  • Migration Script Generation and Use
  • Modification of the Migration Script
  • Actions Performed by the Generated Script
  • Migration Script Errors
  • Manual Migration of Updatable Materialized Views
  • Advanced Replication Elements that Cannot Be Migrated to Oracle Streams
• Preparing to Generate the Migration Script
• Generating and Modifying the Migration Script
  • Example Advanced Replication Environment to be Migrated to Oracle Streams
• Performing the Migration for Advanced Replication to Oracle Streams
  • Before Executing the Migration Script
  • Executing the Migration Script
  • After Executing the Script
• Re-creating Master Sites to Retain Materialized View Groups
• Example Advanced Replication to Oracle Streams Migration Script

Index