Contents

Title and Copyright Information

Preface

Audience

Documentation Accessibility

Related Documents

Conventions

Part I Streams Replication Concepts

1 Understanding Streams Replication

Overview of Streams Replication

Rules in a Streams Replication Environment

Nonidentical Replicas with Streams

Subsetting with Streams

Capture and Streams Replication

Change Capture Using a Capture Process

Capture Process Overview

Supplemental Logging for Streams Replication

Change Capture Using a Custom Application

Propagation and Streams Replication

LCR Staging

LCR Propagation

Apply and Streams Replication

Overview of the Apply Process

Apply Processing Options for LCRs

Captured and User-Enqueued LCRs

Direct and Custom Apply of LCRs

Apply Processes and Dependencies

Virtual Dependency Definitions

How Dependent Transactions Are Applied

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

Index-Organized Tables and an Apply Process

Conflict Resolution and an Apply Process

Handlers and Row LCR Processing

Considerations for Applying DDL Changes

Types of DDL Changes Ignored by an Apply Process

Database Structures in a Streams Environment

Current Schema User Must Exist at Destination Database

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 Streams Replication

Overview of Instantiation and Streams Replication

Capture Process 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 Streams Instantiation

Data Pump Export and Object Consistency

Oracle Data Pump Import and Streams Instantiation

Instantiation SCNs and Data Pump Imports

Instantiation SCNs and Streams Tags Resulting from Data Pump Imports

The STREAMS_CONFIGURATION Data Pump Import Utility Parameter

Recovery Manager (RMAN) and Streams Instantiation

The RMAN DUPLICATE and CONVERT DATABASE Commands and Instantiation

The RMAN TRANSPORT TABLESPACE Command and Instantiation

Original Export/Import and Streams Instantiation

The OBJECT_CONSISTENT Export Utility Parameter and Streams

Original Import Utility Parameters Relevant to Streams

The STREAMS_INSTANTIATION Import Utility Parameter and Streams

The STREAMS_CONFIGURATION Import Utility Parameter and Streams

3 Streams Conflict Resolution

About DML Conflicts in a Streams Environment

Conflict Types in a Streams Environment

Update Conflicts in a Streams Environment

Uniqueness Conflicts in a Streams Environment

Delete Conflicts in a Streams Environment

Foreign Key Conflicts in a Streams Environment

Conflicts and Transaction Ordering in a Streams Environment

Conflict Detection in a Streams Environment

Control Over Conflict Detection for Nonkey Columns

Rows Identification During Conflict Detection in a Streams Environment

Conflict Avoidance in a Streams Environment

Use a Primary Database Ownership Model

Avoid Specific Types of Conflicts

Avoid Uniqueness Conflicts in a Streams Environment

Avoid Delete Conflicts in a Streams Environment

Avoid Update Conflicts in a Streams Environment

Conflict Resolution in a Streams Environment

Prebuilt Update Conflict Handlers

Types of Prebuilt Update Conflict Handlers

Column Lists

Resolution Columns

Data Convergence

Custom Conflict Handlers

4 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

Streams Tags in a Replication Environment

Each Databases Is a Source and Destination Database for Shared Data

Primary Database Sharing Data with Several Secondary Databases

Primary Database Sharing Data with Several Extended Secondary Databases

5 Streams Heterogeneous Information Sharing

Oracle to Non-Oracle Data Sharing with 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

Datatypes 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 Streams

Error Handling in an Oracle to Non-Oracle Environment

Example Oracle to Non-Oracle Streams Environment

Non-Oracle to Oracle Data Sharing with 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 Streams

Part II Configuring Streams Replication

6 Simple Streams Replication Configuration

Configuring Replication Using a Streams Wizard in Enterprise Manager

Streams Global, Schema, Table, and Subset Replication Wizard

Streams Tablespace Replication Wizard

Opening a Streams Replication Configuration Wizard

Configuring Replication Using the DBMS_STREAMS_ADM Package

Preparing to Configure Streams Replication Using the DBMS_STREAMS_ADM Package

Decisions to Make Before Configuring Streams Replication

Tasks to Complete Before Configuring 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 Streams Replication Configuration

Creating a New Streams Single-Source Environment

Creating a New 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 a 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

Complete the 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 Streams Replication

9 Managing Capture, Propagation, and Apply

Managing Capture for Streams Replication

Creating a Capture Process

Managing Supplemental Logging in a 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 Streams Replication

Creating an ANYDATA Queue to Stage LCRs

Creating a Propagation that Propagates LCRs

Managing Apply for Streams Replication

Creating an Apply Process that Applies LCRs

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 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 Streams Tags

Managing Streams Tags for the Current Session

Setting the Tag Values Generated by the Current Session

Getting the Tag Value for the Current Session

Managing Streams Tags for an Apply Process

Setting the Tag Values Generated by an Apply Process

Removing the Apply Tag for an Apply Process

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 a 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 a Table for Instantiation

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 a 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 Objects Using Original Export/Import

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 Monitoring Streams Replication

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 a 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 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

Running Flashback Queries in a Streams Replication Environment

13 Troubleshooting Streams Replication

Recovering from Configuration Errors

Recovery Scenario

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 Sample Replication Environments

14 Simple Single-Source Replication Example

Overview of the Simple Single-Source Replication Example

Prerequisites

Set Up Users and 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

15 Single-Source Heterogeneous Replication Example

Overview of the Single-Source Heterogeneous Replication Example

Prerequisites

Set Up Users and 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 Streams Replication Environment

Make a DML Change to the hr.employees Table

Add a Database to an Existing Streams Replication Environment

Make a DML Change to the hr.departments Table

16 Multiple-Source Replication Example

Overview of the Multiple Source Databases Example

Prerequisites

Set Up Users and Create Queues and Database Links

Example Script for Sharing Data from Multiple Databases

Make DML and DDL Changes to Tables in the hr Schema

Part V Appendixes

A Migrating Advanced Replication to 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 Streams

Preparing to Generate the Migration Script

Generating and Modifying the Migration Script

Example Advanced Replication Environment to be Migrated to Streams

Performing the Migration for Advanced Replication to Streams

Before Executing the Migration Script

Executing the Migration Script

After Executing the Script

Recreating Master Sites to Retain Materialized View Groups

Example Advanced Replication to Streams Migration Script

Index