Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Streams?

• Oracle Database 11g Release 2 (11.2) New Features in Oracle Streams
• Oracle Database 11g Release 1 (11.1) New Features in Oracle Streams

Part I Essential Oracle Streams Concepts

1 Introduction to Oracle Streams

• Overview of Oracle Streams
• What Can Oracle Streams Do?
  • Capture Messages at a Database
  • Stage Messages in a Queue
  • Propagate Messages from One Queue to Another
  • Consume Messages
  • Detect and Resolve Conflicts
  • Transform Messages
  • Track Messages with Oracle Streams Tags
  • Share Information with Non-Oracle Databases
• What Are the Uses of Oracle Streams?
  • Data Replication
  • Data Warehouse Loading
  • Database Availability During Upgrade and Maintenance Operations
  • Message Queuing
  • Event Management and Notification
  • Data Protection
• Sample Oracle Streams Configurations
  • Sample Hub-and-Spoke Replication Configuration
  • Sample Replication Configuration with Downstream Capture
  • Sample Replication Configuration That Uses Synchronous Captures
  • Sample N-Way Replication Configuration
  • Sample Configuration That Performs Capture and Apply in a Single Database
  • Sample Messaging Configuration
• Oracle Streams Documentation Roadmap
  • Documentation for Learning About Oracle Streams
  • Documentation About Setting Up or Extending an Oracle Streams Environment
  • Documentation About Managing an Oracle Streams Environment
  • Documentation About Monitoring an Oracle Streams Environment
  • Documentation About Using Oracle Streams for Upgrade and Maintenance

2 Oracle Streams Information Capture

• Ways to Capture Information with Oracle Streams
  • Implicit Capture
    • Capture Processes
    • Synchronous Captures
  • Explicit Capture
• Types of Information Captured with Oracle Streams
  • Logical Change Records (LCRs)
    • Row LCRs
    • DDL LCRs
    • Extra Information in LCRs
  • User Messages
• Summary of Information Capture Options with Oracle Streams
• Instantiation in an Oracle Streams Environment
• Implicit Capture with an Oracle Streams Capture Process
  • Introduction to Capture Processes
  • Capture Process Rules
  • Data Types Captured by Capture Processes
  • Types of DML Changes Captured by Capture Processes
  • Supplemental Logging in an Oracle Streams Environment
  • Local Capture and Downstream Capture
    • Local Capture
    • Downstream Capture
  • SCN Values Related to a Capture Process
    • Captured SCN and Applied SCN
    • First SCN and Start SCN
  • Oracle Streams Capture Processes and RESTRICTED SESSION
  • Capture Process Subcomponents
  • Capture User
  • Capture Process States
  • Capture Process Parameters
  • Persistent Capture Process Status Upon Database Restart
• Implicit Capture with Synchronous Capture
  • Introduction to Synchronous Capture
  • Synchronous Capture and Queues
  • Synchronous Capture Rules
  • Data Types Captured by Synchronous Capture
  • Types of DML Changes Captured by Synchronous Capture
  • Capture User for Synchronous Capture
  • Multiple Synchronous Captures in a Single Database
• Explicit Capture by Applications
  • Types of Messages That Can Be Enqueued Explicitly
    • User Messages
    • Logical Change Records (LCRs) and Messaging
  • Enqueue Features

3 Oracle Streams Staging and Propagation

• Introduction to Message Staging and Propagation
• Queues
  • ANYDATA Queues and Typed Queues
  • Persistent Queues and Buffered Queues
    • Queues and Oracle Streams Clients
• Message Propagation Between Queues
  • Propagation Rules
  • Queue-to-Queue Propagations
  • Ensured Message Delivery
  • Directed Networks
    • Queue Forwarding and Apply Forwarding

4 Oracle Streams Information Consumption

• Overview of Information Consumption with Oracle Streams
  • Ways to Consume Information with Oracle Streams
    • Implicit Consumption
    • Explicit Consumption
  • Types of Information Consumed with Oracle Streams
    • Captured LCRs
    • Persistent LCRs
    • Buffered LCRs
    • Persistent User Messages
    • Buffered User Messages
  • Summary of Information Consumption Options
• Implicit Consumption with an Apply Process
  • Introduction to the Apply Process
  • Apply Process Rules
  • Types of Messages That Can Be Processed with an Apply Process
  • Message Processing Options for an Apply Process
    • DML Handlers
    • Error Handlers
    • DDL Handlers
    • Message Handlers
    • Precommit Handlers
    • Considerations for Apply Handlers
    • Summary of Message Processing Options
  • The Source of Messages Applied by an Apply Process
  • Data Types Applied
  • Automatic Data Type Conversion During Apply
    • Automatic Trimming of Character Data Types During Apply
    • Automatic Conversion and LOB Data Types
  • SQL Generation
    • Interfaces for Performing SQL Generation
    • SQL Generation Formats
    • SQL Generation and Data Types
    • SQL Generation and Character Sets
    • Sample Generated SQL Statements
  • Oracle Streams Apply Processes and RESTRICTED SESSION
  • Apply Process Subcomponents
    • Reader Server States
    • Coordinator Process States
    • Apply Server States
  • Apply User
  • Apply Process Parameters
  • Persistent Apply Process Status Upon Database Restart
  • The Error Queue
• Explicit Consumption with a Messaging Client
• Explicit Consumption with Manual Dequeue

5 How Rules Are Used in Oracle Streams

• Overview of How Rules Are Used in Oracle Streams
• Rule Sets and Rule Evaluation of Messages
  • Oracle Streams Client with No Rule Set
  • Oracle Streams Client with a Positive Rule Set Only
  • Oracle Streams Client with a Negative Rule Set Only
  • Oracle Streams Client with Both a Positive and a Negative Rule Set
  • Oracle Streams Client with One or More Empty Rule Sets
  • Summary of Rule Sets and Oracle Streams Client Behavior
• System-Created Rules
  • Global Rules
    • Global Rules Example
    • System-Created Global Rules Avoid Empty Rule Conditions Automatically
  • Schema Rules
    • Schema Rule Example
  • Table Rules
    • Table Rules Example
  • Subset Rules
    • Subset Rules Example
    • Row Migration and Subset Rules
    • Subset Rules and Supplemental Logging
    • Guidelines for Using Subset Rules
  • Message Rules
    • Message Rule Example
  • System-Created Rules and Negative Rule Sets
    • Negative Rule Set Example
  • System-Created Rules with Added User-Defined Conditions

6 Rule-Based Transformations

• Declarative Rule-Based Transformations
• Custom Rule-Based Transformations
  • Custom Rule-Based Transformations and Action Contexts
  • Required Privileges for Custom Rule-Based Transformations
• Rule-Based Transformations and Oracle Streams Clients
  • Rule-Based Transformations and Capture Processes
    • Rule-Based Transformation Errors During Capture by a Capture Process
  • Rule-Based Transformations and Synchronous Captures
    • Rule-Based Transformations and Errors During Capture by a Synchronous Capture
  • Rule-Based Transformations and Propagations
    • Rule-Based Transformation Errors During Propagation
  • Rule-Based Transformations and an Apply Process
    • Rule-Based Transformation Errors During Apply Process Dequeue
    • Apply Errors on Transformed Messages
  • Rule-Based Transformations and a Messaging Client
    • Rule-Based Transformation Errors During Messaging Client Dequeue
  • Multiple Rule-Based Transformations
• Transformation Ordering
  • Declarative Rule-Based Transformation Ordering
    • Default Declarative Transformation Ordering
    • User-Specified Declarative Transformation Ordering
• Considerations for Rule-Based Transformations

Part II Advanced Oracle Streams Concepts

7 Advanced Capture Process Concepts

• Multiple Capture Processes in a Single Database
• Capture Process Checkpoints
  • Required Checkpoint SCN
  • Maximum Checkpoint SCN
  • Checkpoint Retention Time
• A New First SCN Value and Purged LogMiner Data Dictionary Information
• ARCHIVELOG Mode and a Capture Process
• Capture Process Creation
  • The LogMiner Data Dictionary for a Capture Process
    • Scenario Illustrating Why a Capture Process Needs a LogMiner Data Dictionary
    • Multiple Capture Processes for the Same Source Database
• The Oracle Streams Data Dictionary
• Capture Process Rule Evaluation

8 Advanced Queue Concepts

• Secure Queues
  • Secure Queues and the SET_UP_QUEUE Procedure
  • Secure Queues and Oracle Streams Clients
• Transactional and Nontransactional Queues
• Commit-Time Queues
  • When to Use Commit-Time Queues
    • Transactional Dependency Ordering During Dequeue
    • Consistent Browse of Messages in a Queue
  • How Commit-Time Queues Work

9 Advanced Propagation Concepts

• Propagation Jobs
  • Propagation Scheduling and Oracle Streams Propagations
  • Propagation Jobs and RESTRICTED SESSION
• Oracle Streams Data Dictionary for Propagations
• Binary File Propagation

10 Advanced Apply Process Concepts

• Apply Process Creation
• 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
    • Value Dependency
    • Object Dependency
  • 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
    • Missing Columns at the Destination Database
    • Extra Columns at the Destination Database
    • Column Data Type Mismatch
  • Conflict Resolution and an Apply Process
  • Handlers and Row LCR Processing
    • No Relevant Handlers
    • Relevant Update Conflict Handler
    • DML Handler But No Relevant Update Conflict Handler
    • DML Handler And a Relevant Update Conflict Handler
    • Statement DML Handler and Procedure DML Handler
    • Error Handler But No Relevant Update Conflict Handler
    • Error Handler And a Relevant Update Conflict Handler
    • Statement DML Handler and Relevant Error Handler
    • Statement DML Handler, Error Handler, and Relevant Update Conflict Handler
• Considerations for Applying DDL Changes
  • System-Generated Names
  • CREATE TABLE AS SELECT Statements
  • DML Statements within DDL Statements
    • The DDL Statement Contains Derived Values
    • The DDL Statement Fires DML Triggers
• 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
• Apply Processes and Triggers
  • Trigger Firing Property
  • Apply Processes and Triggers Created with the ON SCHEMA Clause
• Oracle Streams Data Dictionary for an Apply Process
• Multiple Apply Processes in a Single Database

11 Advanced Rule Concepts

• The Components of a Rule
  • Rule Condition
    • Variables in Rule Conditions
    • Simple Rule Conditions
  • Rule Evaluation Context
    • Explicit and Implicit Variables
    • Evaluation Context Association with Rule Sets and Rules
    • Evaluation Function
  • Rule Action Context
• Rule Set Evaluation
  • Rule Set Evaluation Process
  • Partial Evaluation
• Database Objects and Privileges Related to Rules
  • Privileges for Creating Database Objects Related to Rules
  • Privileges for Altering Database Objects Related to Rules
  • Privileges for Dropping Database Objects Related to Rules
  • Privileges for Placing Rules in a Rule Set
  • Privileges for Evaluating a Rule Set
  • Privileges for Using an Evaluation Context
• Evaluation Contexts Used in Oracle Streams
  • Evaluation Context for Global, Schema, Table, and Subset Rules
  • Evaluation Contexts for Message Rules
• Oracle Streams and Event Contexts
• Oracle Streams and Action Contexts
  • Purposes of Action Contexts in Oracle Streams
    • Internal LCR Transformations in Subset Rules
    • Information About Declarative Rule-Based Transformations
    • Custom Rule-Based Transformations
    • Execution Directives for Messages During Apply
    • Enqueue Destinations for Messages During Apply
  • Ensure That Only One Rule Can Evaluate to TRUE for a Particular Rule Condition
  • Action Context Considerations for Schema and Global Rules
• User-Created Rules, Rule Sets, and Evaluation Contexts
  • User-Created Rules and Rule Sets
    • Rule Conditions for Specific Types of Operations
    • Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs
    • Complex Rule Conditions
    • Rule Conditions with Undefined Variables that Evaluate to NULL
    • Variables as Function Parameters in Rule Conditions
  • User-Created Evaluation Contexts

12 Combined Capture and Apply Optimization

• About Combined Capture and Apply Optimization
• Combined Capture and Apply Requirements
• How to Use Combined Capture and Apply
• How to Determine Whether Combined Capture and Apply Is Being Used
• Combined Capture and Apply and Point-in-Time Recovery

13 Oracle Streams High Availability Environments

• Overview of Oracle Streams High Availability Environments
• Protection from Failures
  • Oracle Streams Replica Database
    • Updates at the Replica Database
    • Heterogeneous Platform Support
    • Multiple Character Sets
    • Mining the Online Redo Logs to Minimize Latency
    • Fast Failover
    • Single Capture for Multiple Destinations
  • When Not to Use Oracle Streams
  • Application-Maintained Copies
• Best Practices for Oracle Streams High Availability Environments
  • Configuring Oracle Streams for High Availability
    • Directly Connecting Every Database to Every Other Database
    • Creating Hub-and-Spoke Configurations
    • Local or Downstream Capture with Oracle Streams Capture Processes
  • Recovering from Failures
    • Automatic Capture Process Restart After a Failover
    • Database Links Reestablishment After a Failover
    • Propagation Job Restart After a Failover
    • Automatic Apply Process Restart After a Failover

Part III Oracle Streams Administration

14 Introduction to Oracle Streams Administration

• Oracle-Supplied PL/SQL Packages
  • DBMS_APPLY_ADM Package
  • DBMS_CAPTURE_ADM Package
  • DBMS_COMPARISON Package
  • DBMS_PROPAGATION_ADM Package
  • DBMS_RULE Package
  • DBMS_RULE_ADM Package
  • DBMS_STREAMS Package
  • DBMS_STREAMS_ADM Package
  • DBMS_STREAMS_ADVISOR_ADM Package
  • DBMS_STREAMS_AUTH Package
  • DBMS_STREAMS_HANDLER_ADM Package
  • DBMS_STREAMS_MESSAGING Package
  • DBMS_STREAMS_TABLESPACE_ADM Package
  • UTL_SPADV Package
• Oracle Streams Data Dictionary Views
• Oracle Streams Tool in Oracle Enterprise Manager

15 Managing Oracle Streams Implicit Capture

• Managing a Capture Process
  • Starting a Capture Process
  • Stopping a Capture Process
  • Managing the Rule Set for a Capture Process
    • Specifying a Rule Set for a Capture Process
    • Adding Rules to a Rule Set for a Capture Process
    • Removing a Rule from a Rule Set for a Capture Process
    • Removing a Rule Set for a Capture Process
  • Setting a Capture Process Parameter
  • Setting the Capture User for a Capture Process
  • Managing the Checkpoint Retention Time for a Capture Process
    • Setting the Checkpoint Retention Time for a Capture Process to a New Value
    • Setting the Checkpoint Retention Time for a Capture Process to Infinite
  • Adding an Archived Redo Log File to a Capture Process Explicitly
  • Setting the First SCN for an Existing Capture Process
  • Setting the Start SCN for an Existing Capture Process
  • Specifying Whether Downstream Capture Uses a Database Link
  • Dropping a Capture Process
• Managing a Synchronous Capture
  • Managing the Rule Set for a Synchronous Capture
    • Specifying a Rule Set for a Synchronous Capture
    • Adding Rules to a Rule Set for a Synchronous Capture
    • Removing a Rule from a Rule Set for a Synchronous Capture
  • Setting the Capture User for a Synchronous Capture
  • Dropping a Synchronous Capture
• Managing Extra Attributes in Captured LCRs
  • Including Extra Attributes in Implicitly Captured LCRs
  • Excluding Extra Attributes from Implicitly Captured LCRs
• Switching From a Capture Process to a Synchronous Capture
• Switching from a Synchronous Capture to a Capture Process

16 Managing Staging and Propagation

• Managing Queues
  • Enabling a User to Perform Operations on a Secure Queue
  • Disabling a User from Performing Operations on a Secure Queue
  • Removing a Queue
• Managing Oracle Streams Propagations and Propagation Jobs
  • Starting a Propagation
  • Stopping a Propagation
  • Altering the Schedule of a Propagation Job
    • Altering the Schedule of a Propagation Job for a Queue-to-Queue Propagation
    • Altering the Schedule of a Propagation Job for a Queue-to-Dblink Propagation
  • Specifying the Rule Set for a Propagation
    • Specifying a Positive Rule Set for a Propagation
    • Specifying a Negative Rule Set for a Propagation
  • Adding Rules to the Rule Set for a Propagation
    • Adding Rules to the Positive Rule Set for a Propagation
    • Adding Rules to the Negative Rule Set for a Propagation
  • Removing a Rule from the Rule Set for a Propagation
  • Removing a Rule Set for a Propagation
  • Dropping a Propagation

17 Managing Oracle Streams Information Consumption

• Starting an Apply Process
• Stopping an Apply Process
• Managing the Rule Set for an Apply Process
  • Specifying the Rule Set for an Apply Process
    • Specifying a Positive Rule Set for an Apply Process
    • Specifying a Negative Rule Set for an Apply Process
  • Adding Rules to the Rule Set for an Apply Process
    • Adding Rules to the Positive Rule Set for an Apply Process
    • Adding Rules to the Negative Rule Set for an Apply Process
  • Removing a Rule from the Rule Set for an Apply Process
  • Removing a Rule Set for an Apply Process
• Setting an Apply Process Parameter
• Setting the Apply User for an Apply Process
• Managing a DML Handler
  • Managing a Statement DML Handler
    • Creating a Statement DML Handler and Adding It to an Apply Process
    • Adding Statements to a Statement DML Handler
    • Modifying a Statement in a Statement DML Handler
    • Removing Statements from a Statement DML Handler
    • Removing a Statement DML Handler from an Apply Process
    • Dropping a Statement DML Handler
  • Managing a Procedure DML Handler
    • Creating a Procedure DML Handler
    • Setting a Procedure DML Handler
    • Unsetting a Procedure 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
• Managing the Message Handler for an Apply Process
  • Setting the Message Handler for an Apply Process
  • Unsetting the Message Handler for an Apply Process
• Managing the Precommit Handler for an Apply Process
  • Creating a Precommit Handler for an Apply Process
  • Setting the Precommit Handler for an Apply Process
  • Unsetting the Precommit Handler for an Apply Process
• Specifying That Apply Processes Enqueue Messages
  • Setting the Destination Queue for Messages that Satisfy a Rule
  • Removing the Destination Queue Setting for a Rule
• Specifying Execute Directives for Apply Processes
  • Specifying that Messages that Satisfy a Rule Are Not Executed
  • Specifying that Messages that Satisfy a Rule Are Executed
• Managing an Error Handler
  • Creating an Error Handler
  • Setting an Error Handler
  • Unsetting an Error Handler
• Managing Apply Errors
  • Retrying Apply Error Transactions
    • Retrying a Specific Apply Error Transaction
    • Retrying All Error Transactions for an Apply Process
  • Deleting Apply Error Transactions
    • Deleting a Specific Apply Error Transaction
    • Deleting All Error Transactions for an Apply Process
• Managing the Substitute Key Columns for a Table
  • Setting Substitute Key Columns for a Table
  • Removing the Substitute Key Columns for a Table
• Using Virtual Dependency Definitions
  • Setting and Unsetting Value Dependencies
    • Schema Differences and Value Dependencies
    • Undefined Constraints at the Destination Database and Value Dependencies
  • Creating and Dropping Object Dependencies
    • Creating an Object Dependency
    • Dropping an Object Dependency
• Dropping an Apply Process

18 Managing Rules

• Managing Rule Sets
  • Creating a Rule Set
  • Adding a Rule to a Rule Set
  • Removing a Rule from a Rule Set
  • Dropping a Rule Set
• Managing Rules
  • Creating a Rule
    • Creating a Rule without an Action Context
    • Creating a Rule with an Action Context
  • Altering a Rule
    • Changing a Rule Condition
    • Modifying a Name-Value Pair in a Rule Action Context
    • Adding a Name-Value Pair to a Rule Action Context
    • Removing a Name-Value Pair from a Rule Action Context
  • Modifying System-Created Rules
  • Dropping a Rule
• Managing Privileges on Evaluation Contexts, Rule Sets, and Rules
  • Granting System Privileges on Evaluation Contexts, Rule Sets, and Rules
  • Granting Object Privileges on an Evaluation Context, Rule Set, or Rule
  • Revoking System Privileges on Evaluation Contexts, Rule Sets, and Rules
  • Revoking Object Privileges on an Evaluation Context, Rule Set, or Rule

19 Managing Rule-Based Transformations

• Managing Declarative Rule-Based Transformations
  • Adding Declarative Rule-Based Transformations
    • Adding a Declarative Rule-Based Transformation that Renames a Table
    • Adding a Declarative Rule-Based Transformation that Adds a Column
  • Overwriting an Existing Declarative Rule-Based Transformation
  • Removing Declarative Rule-Based Transformations
• Managing Custom Rule-Based Transformations
  • Creating a Custom Rule-Based Transformation
  • Altering a Custom Rule-Based Transformation
  • Unsetting a Custom Rule-Based Transformation

20 Using Oracle Streams to Record Table Changes

• About Using Oracle Streams to Record Changes to Tables
• Preparing for an Oracle Streams Environment That Records Table Changes
  • Decisions to Make Before Running the MAINTAIN_CHANGE_TABLE Procedure
    • Decide Which Type of Environment to Configure
    • Decide Which Columns to Track
    • Decide Which Metadata to Record
    • Decide Which Values to Track for Update Operations
    • Decide Whether to Configure a KEEP_COLUMNS Transformation
    • Decide Whether to Specify CREATE TABLE Options for the Change Table
    • Decide Whether to Perform the Configuration Actions Directly or with a Script
    • Decide Whether to Replicate the Source Table
  • Prerequisites for the MAINTAIN_CHANGE _TABLE Procedure
    • Configure an Oracle Streams Administrator on All Databases
    • Configure Network Connectivity and Database Links
    • Ensure That the Source Database Is in ARCHIVELOG Mode
    • Set Initialization Parameters That Are Relevant to Oracle Streams
    • Configure the Oracle Streams Pool
    • Configure Log File Transfer to a Downstream Capture Database
    • Configure Standby Redo Logs for Real-Time Downstream Capture
    • Configure the Required Directory Object If You Are Using a Script
    • Instantiate the Source Table at the Destination Database
• Configuring an Oracle Streams Environment That Records Table Changes
  • Recording Table Changes Using Local Capture and Apply on One Database
  • Recording Table Changes Using Local Capture and Remote Apply with Replication
  • Recording Table Changes Using Downstream Capture and Local Apply
  • Recording Table Changes Using Downstream Capture and Remote Apply
• Managing an Oracle Streams Environment That Records Table Changes
  • Unsetting and Setting a Change Handler
  • Recording Changes to a Table Using Existing Oracle Streams Components
  • Maintaining Change Tables
  • Managing the Oracle Streams Environment
• Monitoring an Oracle Streams Environment That Records Table Changes
  • Monitoring a Change Table
  • Monitoring Change Handlers
    • Displaying General Information About Change Handlers
    • Displaying the Change Table and Source Table for Change Handlers
  • Monitoring the Oracle Streams Environment

21 Other Oracle Streams Management Tasks

• Performing Full Database Export/Import in an Oracle Streams Environment
• Removing an Oracle Streams Configuration

Part IV Monitoring Oracle Streams

22 Monitoring an Oracle Streams Environment

• Summary of Oracle Streams Static Data Dictionary Views
• Summary of Oracle Streams Dynamic Performance Views

23 Monitoring the Oracle Streams Topology and Performance

• About the Oracle Streams Topology
• About the Oracle Streams Performance Advisor
  • Oracle Streams Performance Advisor Data Dictionary Views
  • Oracle Streams Components and Statistics
• About Stream Paths in an Oracle Streams Topology
  • Separate Stream Paths in an Oracle Streams Environment
  • Shared Stream Paths in an Oracle Streams Replication Environment
• About the Information Gathered by the Oracle Streams Performance Advisor
• Gathering Information About the Oracle Streams Topology and Performance
• Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance
  • Viewing the Oracle Streams Topology
    • Viewing the Databases in the Oracle Streams Environment
    • Viewing the Oracle Streams Components at Each Database
    • Viewing Each Stream Path in an Oracle Streams Topology
  • Viewing Performance Statistics for Oracle Streams Components
    • Checking for Bottleneck Components in the Oracle Streams Topology
    • Viewing Component-Level Statistics
    • Viewing Session-Level Statistics
    • Viewing Statistics for the Stream Paths in an Oracle Streams Environment
• Using the UTL_SPADV Package
  • Collecting Oracle Streams Statistics Using the UTL_SPADV Package
  • Checking Whether an Oracle Streams Monitoring Job Is Currently Running
  • Altering an Oracle Streams Monitoring Job
  • Stopping an Oracle Streams Monitoring Job
  • Showing Oracle Streams Statistics Using the UTL_SPADV Package

24 Monitoring Oracle Streams Implicit Capture

• Monitoring a Capture Process
  • Displaying the Queue, Rule Sets, and Status of Each Capture Process
  • Displaying Session Information About Each Capture Process
  • Displaying Change Capture Information About Each Capture Process
  • Displaying State Change and Message Creation Time for Each Capture Process
  • Displaying Elapsed Time Performing Capture Operations for Each Capture Process
  • Displaying Information About Each Downstream Capture Process
  • Displaying the Registered Redo Log Files for Each Capture Process
  • Displaying the Redo Log Files that Are Required by Each Capture Process
  • Displaying SCN Values for Each Redo Log File Used by Each Capture Process
  • Displaying the Last Archived Redo Entry Available to Each Capture Process
  • Listing the Parameter Settings for Each Capture Process
  • Determining the Applied SCN for All Capture Processes in a Database
  • Determining Redo Log Scanning Latency for Each Capture Process
  • Determining Message Enqueuing Latency for Each Capture Process
  • Displaying Information About Rule Evaluations for Each Capture Process
  • Determining Which Capture Processes Use Combined Capture and Apply
  • Displaying Information About Split and Merge Operations
    • Displaying the Names of the Original and Cloned Oracle Streams Components
    • Displaying the Actions and Thresholds for Split and Merge Operations
    • Displaying the Lag Time of the Cloned Capture Process
    • Displaying Information About the Split and Merge Jobs
    • Displaying Information About Past Split and Merge Operations
  • Monitoring Supplemental Logging
    • Displaying Supplemental Log Groups at a Source Database
    • Displaying Database Supplemental Logging Specifications
    • Displaying Supplemental Logging Specified During Preparation for Instantiation
• Monitoring a Synchronous Capture
  • Displaying the Queue and Rule Set of Each Synchronous Capture
  • Displaying the Tables For Which Synchronous Capture Captures Changes
• Viewing the Extra Attributes Captured by Implicit Capture

25 Monitoring Oracle Streams Queues and Propagations

• Monitoring Queues and Messaging
  • Displaying the ANYDATA Queues in a Database
  • Viewing the Messaging Clients in a Database
  • Viewing Message Notifications
  • Determining the Consumer of Each Message in a Persistent Queue
  • Viewing the Contents of Messages in a Persistent Queue
• Monitoring Buffered Queues
  • Determining the Number of Messages in Each Buffered Queue
  • Viewing the Capture Processes for the LCRs in Each Buffered Queue
  • Displaying Information About Propagations that Send Buffered Messages
  • Displaying the Number of Messages and Bytes Sent By Propagations
  • Displaying Performance Statistics for Propagations that Send Buffered Messages
  • Viewing the Propagations Dequeuing Messages from Each Buffered Queue
  • Displaying Performance Statistics for Propagations That Receive Buffered Messages
  • Viewing the Apply Processes Dequeuing Messages from Each Buffered Queue
• Monitoring Oracle Streams Propagations and Propagation Jobs
  • Displaying the Queues and Database Link for Each Propagation
  • Determining the Source Queue and Destination Queue for Each Propagation
  • Determining the Rule Sets for Each Propagation
  • Displaying Information About the Schedules for Propagation Jobs
  • Determining the Total Number of Messages and Bytes Propagated
  • Displaying Information About Propagation Senders
  • Displaying Information About Propagation Receivers
  • Displaying Session Information About Each Propagation

26 Monitoring Oracle Streams Apply Processes

• Determining the Queue, Rule Sets, and Status for Each Apply Process
• Displaying General Information About Each Apply Process
• Listing the Parameter Settings for Each Apply Process
• Displaying Information About Apply Handlers
  • Displaying Information About DML Handlers
    • Displaying Information About All DML Handlers
    • Displaying Information About Statement DML Handlers
    • Displaying Information About Procedure DML Handlers
  • Displaying the DDL Handler for Each Apply Process
  • Displaying All of the Error Handlers for Local Apply Processes
  • Displaying the Message Handler for Each Apply Process
  • Displaying the Precommit Handler for Each Apply Process
• Displaying Session Information About Each Apply Process
• Displaying Information About the Reader Server for Each Apply Process
• Monitoring Transactions and Messages Spilled by Each Apply Process
• Determining Capture to Dequeue Latency for a Message
• Displaying General Information About Each Coordinator Process
• Displaying Information About Transactions Received and Applied
• Determining the Capture to Apply Latency for a Message for Each Apply Process
  • Example V$STREAMS_APPLY_COORDINATOR Query for Latency
  • Example DBA_APPLY_PROGRESS Query for Latency
• Displaying Information About the Apply Servers for Each Apply Process
• Displaying Effective Apply Parallelism for an Apply Process
• Viewing Rules that Specify a Destination Queue on Apply
• Viewing Rules that Specify No Execution on Apply
• Determining Which Apply Processes Use Combined Capture and Apply
• Displaying the Substitute Key Columns Specified at a Destination Database
• Monitoring Virtual Dependency Definitions
  • Displaying Value Dependencies
  • Displaying Object Dependencies
• Checking for Apply Errors
• Displaying Detailed Information About Apply Errors

27 Monitoring Rules

• Displaying All Rules Used by All Oracle Streams Clients
• Displaying the Oracle Streams Rules Used by a Specific Oracle Streams Client
  • Displaying the Rules in the Positive Rule Set for an Oracle Streams Client
  • Displaying the Rules in the Negative Rule Set for an Oracle Streams Client
• Displaying the Current Condition for a Rule
• Displaying Modified Rule Conditions for Oracle Streams Rules
• Displaying the Evaluation Context for Each Rule Set
• Displaying Information About the Tables Used by an Evaluation Context
• Displaying Information About the Variables Used in an Evaluation Context
• Displaying All of the Rules in a Rule Set
• Displaying the Condition for Each Rule in a Rule Set
• Listing Each Rule that Contains a Specified Pattern in Its Condition
• Displaying Aggregate Statistics for All Rule Set Evaluations
• Displaying Information About Evaluations for Each Rule Set
• Determining the Resources Used by Evaluation of Each Rule Set
• Displaying Evaluation Statistics for a Rule

28 Monitoring Rule-Based Transformations

• Displaying Information About All Rule-Based Transformations
• Displaying Declarative Rule-Based Transformations
  • Displaying Information About ADD COLUMN Transformations
  • Displaying Information About RENAME TABLE Transformations
• Displaying Custom Rule-Based Transformations

29 Monitoring Other Oracle Streams Components

• Monitoring Oracle Streams Administrators and Other Oracle Streams Users
  • Listing Local Oracle Streams Administrators
  • Listing Users Who Allow Access to Remote Oracle Streams Administrators
• Monitoring the Oracle Streams Pool
  • Query Result that Advises Increasing the Oracle Streams Pool Size
  • Query Result that Advises Retaining the Current Oracle Streams Pool Size
  • Query Result that Advises Decreasing the Oracle Streams Pool Size
• Monitoring Compatibility in an Oracle Streams Environment
  • Monitoring Compatibility for Capture Processes
    • Listing the Database Objects That Are Not Compatible with Capture Processes
    • Listing the Database Objects Recently Compatible with Capture Processes
  • Listing Database Objects and Columns Not Compatible with Synchronous Captures
  • Monitoring Compatibility for Apply Processes
    • Listing Database Objects and Columns Not Compatible with Apply Processes
    • Listing Columns That Have Become Compatible with Apply Processes Recently
• Monitoring Oracle Streams Performance Using AWR and Statspack

Part V Troubleshooting an Oracle Streams Environment

30 Identifying Problems in an Oracle Streams Environment

• Viewing Oracle Streams Alerts
• Using the Streams Configuration Report and Health Check Script
• Handling Performance Problems Because of an Unavailable Destination
• Checking the Trace Files and Alert Log for Problems
  • Does a Capture Process Trace File Contain Messages About Capture Problems?
  • Do the Trace Files Related to Propagation Jobs Contain Messages About Problems?
  • Does an Apply Process Trace File Contain Messages About Apply Problems?

31 Troubleshooting Implicit Capture

• Troubleshooting Capture Process Problems
  • Is Capture Process Creation or Data Dictionary Build Taking a Long Time?
  • Is the Capture Process Enabled?
  • Is the Capture Process Waiting for Redo?
  • Is the Capture Process Paused for Flow Control?
  • Is the Capture Process Current?
  • Are Required Redo Log Files Missing?
  • Is a Downstream Capture Process Waiting for Redo Data?
  • Are You Trying to Configure Downstream Capture Incorrectly?
  • Are You Trying to Configure Downstream Capture without Proper Authentication?
  • Are More Actions Required for Downstream Capture without a Database Link?
• Troubleshooting Synchronous Capture Problems
  • Is a Synchronous Capture Failing to Capture Changes to Tables?

32 Troubleshooting Propagation

• Does the Propagation Use the Correct Source and Destination Queue?
• Is the Propagation Enabled?
• Is Security Configured Properly for the ANYDATA Queue?
  • ORA-24093 AQ Agent not granted privileges of database user
  • ORA-25224 Sender name must be specified for enqueue into secure queues

33 Troubleshooting Apply

• Is the Apply Process Enabled?
• Is the Apply Process Current?
• Does the Apply Process Apply Captured LCRs?
• Is the Apply Process's Queue Receiving the Messages to be Applied?
• Is a Custom Apply Handler Specified?
• Is the AQ_TM_PROCESSES Initialization Parameter Set to Zero?
• Does the Apply User Have the Required Privileges?
• 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
    • ORA-01031 Insufficient Privileges
    • ORA-01403 No Data Found
    • ORA-23605 Invalid Value for Oracle Streams Parameter
    • ORA-23607 Invalid Column
    • ORA-24031 Invalid Value, parameter_name Should Be Non-NULL
    • ORA-26687 Instantiation SCN Not Set
    • ORA-26688 Missing Key in LCR
    • ORA-26689 Column Type Mismatch
    • ORA-26786 A row with key exists but has conflicting column(s) in table
    • ORA-26787 The row with key column_value does not exist in table table_name

34 Troubleshooting Rules and Rule-Based Transformations

• Are Rules Configured Properly for the Oracle Streams Client?
  • Checking Schema and Global Rules
  • Checking Table Rules
  • Checking Subset Rules
  • Checking for Message Rules
  • Resolving Problems with Rules
• Are Declarative Rule-Based Transformations Configured Properly?
• Are the Custom Rule-Based Transformations Configured Properly?
• Are Incorrectly Transformed LCRs in the Error Queue?

Part VI Oracle Streams Information Provisioning

35 Information Provisioning Concepts

• Overview of Information Provisioning
• Bulk Provisioning of Large Amounts of Information
  • Data Pump Export/Import
  • Transportable Tablespace from Backup with RMAN
  • DBMS_STREAMS_TABLESPACE_ADM Procedures
    • File Group Repository
    • Tablespace Repository
    • Read-Only Tablespaces Requirement During Export
    • Automatic Platform Conversion for Tablespaces
  • Options for Bulk Information Provisioning
• Incremental Information Provisioning with Oracle Streams
• On-Demand Information Access

36 Using Information Provisioning

• Using a Tablespace Repository
  • Creating and Populating a Tablespace Repository
  • Using a Tablespace Repository for Remote Reporting with a Shared File System
  • Using a Tablespace Repository for Remote Reporting without a Shared File System
• Using a File Group Repository

37 Monitoring File Group and Tablespace Repositories

• Monitoring a File Group Repository
  • Displaying General Information About the File Groups in a Database
  • Displaying Information About File Group Versions
  • Displaying Information About File Group Files
• Monitoring a Tablespace Repository
  • Displaying Information About the Tablespaces in a Tablespace Repository
  • Displaying Information About the Tables in a Tablespace Repository
  • Displaying Export Information About Versions in a Tablespace Repository

Part VII Appendixes

A How Oracle Streams Works with Other Database Components

• Oracle Streams and Oracle Real Application Clusters
  • Capture Processes and Oracle Real Application Clusters
  • Synchronous Capture and Oracle Real Application Clusters
  • Combined Capture and Apply and Oracle Real Application Clusters
  • Queues and Oracle Real Application Clusters
  • Propagations and Oracle Real Application Clusters
  • Apply Processes and Oracle Real Application Clusters
• Oracle Streams and Transparent Data Encryption
  • Capture Processes and Transparent Data Encryption
  • Synchronous Capture and Transparent Data Encryption
  • Explicit Capture and Transparent Data Encryption
  • Queues and Transparent Data Encryption
  • Propagations and Transparent Data Encryption
  • Apply Processes and Transparent Data Encryption
  • Messaging Clients and Transparent Data Encryption
  • Manual Dequeue and Transparent Data Encryption
• Oracle Streams and Flashback Data Archive
• Oracle Streams and Recovery Manager (RMAN)
  • RMAN and Instantiation
  • RMAN and Archived Redo Log Files Required by a Capture Process
    • RMAN and Local Capture Processes
    • RMAN and Downstream Capture Processes
  • The Recovery Catalog and Oracle Streams
• Oracle Streams and Distributed Transactions
• Oracle Streams and Oracle Data Vault

B Oracle Streams Restrictions

• Capture Process Restrictions
  • Unsupported Data Types for Capture Processes
  • Unsupported Changes for Capture Processes
    • Unsupported Schemas for Capture Processes
    • Unsupported Table Types for Capture Processes
    • Unsupported DDL Changes for Capture Processes
    • Changes Ignored by a Capture Process
    • NOLOGGING and UNRECOVERABLE Keywords for SQL Operations
    • UNRECOVERABLE Clause for Direct Path Loads
  • Supplemental Logging Data Type Restrictions
  • Operational Requirements for Downstream Capture
  • Capture Processes Do Not Support Oracle Label Security
  • Capture Process Interoperability with Oracle Streams Apply Processes
• Synchronous Capture Restrictions
  • Synchronous Captures Only Use Table Rules
  • Unsupported Data Types for Synchronous Captures
  • Unsupported Changes for Synchronous Captures
    • Unsupported Schemas for Synchronous Captures
    • Unsupported Table Types for Synchronous Captures
    • Changes Ignored by Synchronous Capture
  • Synchronous Capture Rules and the DBMS_STREAMS_ADM Package
  • Synchronous Captures Do Not Support Oracle Label Security
• Queue Restrictions
  • Explicit Enqueue Restrictions for ANYDATA Queues
  • Restrictions for Buffered Messaging
  • Triggers and Queue Tables
• Propagation Restrictions
  • Connection Qualifiers and Propagations
  • Character Set Restrictions for Propagations
  • Compatibility Requirements for Queue-To-Queue Propagations
• Apply Process Restrictions
  • Unsupported Data Types for Apply Processes
  • Unsupported Data Types for Apply Handlers
  • Types of DDL Changes Ignored by an Apply Process
  • Database Structures in an Oracle Streams Environment
  • Current Schema User Must Exist at Destination Database
  • Apply Processes Do Not Support Oracle Label Security
  • Apply Process Interoperability with Oracle Streams Capture Components
• Messaging Client Restrictions
  • Messaging Clients and Buffered Messages
• Rule Restrictions
  • Restrictions for Subset Rules
  • Restrictions for Action Contexts
• Rule-Based Transformation Restrictions
  • Unsupported Data Types for Declarative Rule-Based Transformations
  • Unsupported Data Types for Custom Rule-Based Transformations
• Character Set Restrictions for Oracle Streams Replication

C XML Schema for LCRs

• Definition of the XML Schema for LCRs

D Online Database Upgrade and Maintenance with Oracle Streams

• Overview of Using Oracle Streams for Upgrade and Maintenance Operations
  • The Capture Database During the Upgrade or Maintenance Operation
  • Assumptions for the Database Being Upgraded or Maintained
  • Considerations for Job Slaves and PL/SQL Package Subprograms
  • Unsupported Database Objects Are Excluded
• Preparing for a Database Upgrade or Maintenance Operation
  • Preparing for Downstream Capture
  • Preparing for Upgrade or Maintenance of a Database with User-Defined Types
  • Preparing for Upgrades to User-Created Applications
    • Handling Modifications to Schema Objects
    • Handling Logical Dependencies
  • Deciding Whether to Configure Oracle Streams Directly or Generate a Script
  • Deciding Which Utility to Use for Instantiation
• Performing a Database Upgrade or Maintenance Operation Using Oracle Streams
  • Task 1: Beginning the Operation
  • Task 2: Setting Up Oracle Streams Before Instantiation
    • The Source Database Is the Capture Database
    • The Destination Database Is the Capture Database
    • A Third Database Is the Capture Database
  • Task 3: Instantiating the Database
    • Instantiating the Database Using Export/Import
    • Instantiating the Database Using the RMAN DUPLICATE Command
    • Instantiating the Database Using the RMAN CONVERT DATABASE Command
  • Task 4: Setting Up Oracle Streams After Instantiation
    • The Source Database Is the Capture Database
    • The Destination Database Is the Capture Database
    • A Third Database Is the Capture Database
  • Task 5: Finishing the Upgrade or Maintenance Operation and Removing Oracle Streams

E Online Upgrade of a 10.1 or Earlier Database with Oracle Streams

• Overview of Using Oracle Streams in the Database Upgrade Process
  • The Capture Database During the Upgrade Process
  • Assumptions for the Database Being Upgraded
  • Considerations for Job Queue Processes and PL/SQL Package Subprograms
• Preparing for a Database Upgrade Using Oracle Streams
  • Preparing to Upgrade a Database with User-Defined Types
  • Deciding Which Utility to Use for Instantiation
• Performing a Database Upgrade Using Oracle Streams
  • Task 1: Beginning the Upgrade
  • Task 2: Setting Up Oracle Streams Before Instantiation
    • The Source Database Is the Capture Database
    • The Destination Database Is the Capture Database
    • A Third Database Is the Capture Database
  • Task 3: Instantiating the Database
    • Instantiating the Database Using Export/Import
    • Instantiating the Database Using RMAN
  • Task 4: Setting Up Oracle Streams After Instantiation
    • The Source Database Is the Capture Database
    • The Destination Database Is the Capture Database
    • A Third Database Is the Capture Database
  • Task 5: Finishing the Upgrade and Removing Oracle Streams

Glossary

Index