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
  • • 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
    • 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 Streams Replication Best Practices

14 Best Practices for Streams Replication Databases

• Best Practices for Streams Database Configuration
• • Set Initialization Parameters That Are Relevant to Streams
  • Configure Database Storage in a Streams Database
  • • Configure a Separate Tablespace for the Streams Administrator
    • Use a Separate Queue for Each Capture Process and Apply Process
  • Grant User Privileges to the Streams Administrator
  • Automate the Streams Replication Configuration
• Best Practices for Streams Database Operation
• • Follow the Best Practices for the Global Name of a Streams Database
  • Follow the Best Practices for Replicating DDL Changes
  • Monitor Capture Process Queues for Growth
  • Follow the Streams Best Practices for Backups
  • • Best Practices for Backups of a Streams Source Database
    • Best Practices for Backups of a Streams Destination Database
  • Adjust the Automatic Collection of Optimizer Statistics
  • Use the STRMMON Tool for Monitoring Streams
  • Check the Alert Log for Streams Information
  • Follow the Best Practices for Removing a Streams Configuration at a Database
• Best Practices for RAC Databases in Streams Replication Environments
• • Make Archive Log Files of All Threads Available to Capture Processes
  • Follow the Best Practices for the Global Name of a Streams RAC Database
  • Follow the Best Practices for Configuring and Managing Propagations
  • Follow the Best Practices for Queue Ownership

15 Best Practices for Capture

• Best Practices for Source Database Configuration
• • Enable Archive Logging at Each Source Database in a Streams Environment
  • Add Supplemental Logging at Each Source Database in a Streams Environment
  • Configure a Heartbeat Table at Each Source Database in a 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

16 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
• Best Practices for Propagation Operation
• • Restart Broken Propagations

17 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

18 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

19 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

20 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 VI 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