Contents

Title and Copyright Information

Send Us Your Comments

Preface

Related Documentation

Documentation Accessibility

What's New in Oracle Streams?

Streams Performance Improvements

Streams Configuration and Manageability Enhancements

Streams Replication Enhancements

Streams Messaging Enhancements

Rules Interface Enhancements

Part I Streams Concepts

1 Introduction to Streams

2 Streams Capture Process

The Redo Log and a Capture Process

Logical Change Records (LCRs)

Extra Information in LCRs

Capture Process Rules

Datatypes Captured

Types of Changes Captured

Types of DML Changes Captured

Types of DDL Changes Ignored by a Capture Process

Other Types of Changes Ignored by a Capture Process

NOLOGGING and UNRECOVERABLE Keywords for SQL Operations

UNRECOVERABLE Clause for Direct Path Loads

Supplemental Logging in a Streams Environment

Instantiation in a Streams Environment

Local Capture and Downstream Capture

Downstream Capture

SCN Values Relating to a Capture Process

Captured SCN and Applied SCN

First SCN and Start SCN

Streams Capture Processes and RESTRICTED SESSION

Streams Capture Processes and Oracle Real Application Clusters

Capture Process Architecture

Capture Process Components

Capture Process States

Multiple Capture Processes in a Single Database

Capture Process Checkpoints

Capture Process Creation

A New First SCN Value and Purged LogMiner Dictionary Information

The Streams Data Dictionary

ARCHIVELOG Mode and a Capture Process

Capture Process Parameters

Capture Process Rule Evaluation

Persistent Capture Process Status Upon Database Restart

3 Streams Staging and Propagation

Introduction to Event Staging and Propagation

Captured and User-Enqueued Events

Event Propagation Between Queues

Propagation Rules

Ensured Event Delivery

Directed Networks

Binary File Propagation

Messaging Clients

SYS.AnyData Queues and User Messages

SYS.AnyData Queues and Oracle Real Application Clusters

Streams Staging and Propagation Architecture

Buffered Queues

Propagation Jobs

Transactional and Nontransactional Queues

Streams Data Dictionary for Propagations

4 Streams Apply Process

Introduction to the Apply Process

Apply Process Rules

Event Processing with an Apply Process

Processing Captured and User-Enqueued Events with an Apply Process

Event Processing Options with an Apply Process

Datatypes Applied

Streams Apply Processes and RESTRICTED SESSION

Streams Apply Processes and Oracle Real Application Clusters

Apply Process Architecture

Apply Process Components

Apply Process Creation

Streams Data Dictionary for an Apply Process

Apply Process Parameters

Persistent Apply Process Status Upon Database Restart

The Error Queue

5 Rules

The Components of a Rule

Rule Evaluation Context

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

6 How Rules Are Used In Streams

Overview of How Rules Are Used In Streams

Rule Sets and Rule Evaluation of Events

Streams Client With No Rule Set

Streams Client With a Positive Rule Set Only

Streams Client With a Negative Rule Set Only

Streams Client With Both a Positive and a Negative Rule Set

Streams Client With One or More Empty Rule Sets

Summary of Rule Sets and Streams Client Behavior

System-Created Rules

System-Created Rules and Negative Rule Sets

System-Created Rules with Added User-Defined Conditions

Evaluation Contexts Used in Streams

Evaluation Context for Global, Schema, Table, and Subset Rules

Evaluation Contexts for Message Rules

Streams and Event Contexts

Streams and Action Contexts

Purposes of Action Contexts in Streams

Make Sure 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

User-Created Evaluation Contexts

Rule-Based Transformations

Rule-Based Transformations and a Capture Process

Rule-Based Transformations and a Propagation

Rule-Based Transformations and an Apply Process

Rule-Based Transformations and a Messaging Client

Multiple Rule-Based Transformations

7 Streams High Availability Environments

Overview of Streams High Availability Environments

Protection from Failures

Streams Replica Database

When Not to Use Streams

Application Maintained Copies

Best Practices for Streams High Availability Environments

Configuring Streams for High Availability

Recovering from Failures

Part II Streams Administration

8 Preparing a Streams Environment

Configuring a Streams Administrator

Setting Initialization Parameters Relevant to Streams

Preparing a Database to Run a Streams Capture Process

Configuring Network Connectivity and Database Links

9 Managing a Capture Process

Creating a Capture Process

Creating a Local Capture Process

Creating a Downstream Capture Process That Assigns Log Files Implicitly

Creating a Downstream Capture Process That Assigns Log Files Explicitly

Creating a Local Capture Process with Non-NULL Start SCN

Starting, Stopping, and Dropping a Capture Process

Starting a Capture Process

Stopping a Capture Process

Dropping 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

Specifying Supplemental Logging at a Source Database

Adding an Archived Redo Log File to a Capture Process Explicitly

Setting SCN Values for an Existing Capture Process

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

Managing Extra Attributes in Captured LCRs

Including Extra Attributes in Captured LCRs

Excluding Extra Attributes from Captured LCRs

10 Managing Staging and Propagation

Managing SYS.AnyData Queues

Creating a SYS.AnyData Queue

Enabling a User to Perform Operations on a Secure Queue

Disabling a User from Performing Operations on a Secure Queue

Removing a SYS.AnyData Queue

Managing Streams Propagations and Propagation Jobs

Creating a Propagation

Enabling a Propagation Job

Scheduling a Propagation Job

Altering the Schedule of a Propagation Job

Unscheduling a Propagation Job

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

Removing a Rule from the Rule Set for a Propagation

Removing a Rule Set for a Propagation

Disabling a Propagation Job

Dropping a Propagation

Managing a Streams Messaging Environment

Wrapping User Message Payloads in a SYS.AnyData Wrapper and Enqueuing Them

Dequeuing a Payload That Is Wrapped in a SYS.AnyData Payload

Configuring a Messaging Client and Message Notification

11 Managing an Apply Process

Creating, Starting, Stopping, and Dropping an Apply Process

Creating an Apply Process

Starting an Apply Process

Stopping an Apply Process

Dropping an Apply Process

Managing the Rule Set for an Apply Process

Specifying the Rule Set for an Apply Process

Adding Rules to the 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 the Message Handler for an Apply Process

Setting the Message Handler for an Apply Process

Removing 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

Removing the Precommit Handler for an Apply Process

Specifying Event Enqueues by Apply Processes

Setting the Destination Queue for Events That Satisfy a Rule

Removing the Destination Queue Setting for a Rule

Specifying Execute Directives for Apply Processes

Specifying That Events That Satisfy a Rule Are Not Executed

Specifying That Events 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

Deleting Apply Error Transactions

12 Managing Rules and Rule-Based Transformations

Managing Rule Sets and Rules

Creating a Rule Set

Creating a Rule

Adding a Rule to a Rule Set

Altering a Rule

Modifying System-Created Rules

Removing a Rule from a Rule Set

Dropping a Rule

Dropping a Rule Set

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

Managing Rule-Based Transformations

Creating a Rule-Based Transformation

Altering a Rule-Based Transformation

Removing a Rule-Based Transformation

13 Other Streams Management Tasks

Performing Full Database Export/Import in a Streams Environment

Removing a Streams Configuration

14 Monitoring a Streams Environment

Summary of Streams Static Data Dictionary Views

Summary of Streams Dynamic Performance Views

Monitoring Streams Administrators and Other Streams Users

Listing Local Streams Administrators

Listing Users Who Allow Access to Remote Streams Administrators

Monitoring a Streams Capture Process

Displaying the Queue, Rule Sets, and Status of Each Capture Process

Displaying General Information About 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 Will Never Be Needed by Any Capture Process

Displaying SCN Values for Each Redo Log File Used by a Capture Process

Displaying the Last Archived Redo Entry Available to Each Capture Process

Listing the Parameter Settings for Each Capture Process

Viewing the Extra Attributes Captured by Each Capture Process

Determining the Applied SCN for All Capture Processes in a Database

Determining Redo Log Scanning Latency for Each Capture Process

Determining Event Enqueuing Latency for Each Capture Process

Displaying Information About Rule Evaluations for Each Capture Process

Monitoring a SYS.AnyData Queue and Messaging

Displaying the SYS.AnyData Queues in a Database

Viewing the Messaging Clients in a Database

Viewing Message Notifications

Determining the Consumer of Each User-Enqueued Event in a Queue

Viewing the Contents of User-Enqueued Events in a Queue

Monitoring Streams Propagations and Propagation Jobs

Determining the Source Queue and Destination Queue for Each Propagation

Determining the Rule Sets for Each Propagation

Displaying the Schedule for a Propagation Job

Determining the Total Number of Events and Bytes Propagated by Each Propagation

Monitoring a Streams Apply Process

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 the Reader Server for Each Apply Process

Determining Capture to Dequeue Latency for an Event

Displaying General Information About Each Coordinator Process

Displaying Information About Transactions Received and Applied

Determining the Capture to Apply Latency for an Event for Each Apply Process

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

Checking for Apply Errors

Displaying Detailed Information About Apply Errors

Monitoring Rules and Rule-Based Transformations

Displaying All Rules Used by All Streams Clients

Displaying the Streams Rules Used by a Specific Streams Client

Displaying the Current Condition for a Rule

Displaying Rule Conditions for Streams Rules That Have Been Modified

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 Rule-Based Transformations

Displaying Aggregate Statistics for All Rule Set Evaluations

Displaying General Information About Rule Set Evaluations

Determining the Resources Used by Evaluation of Each Rule Set

Displaying Evaluation Statistics for a Rule

Monitoring Compatibility in a Streams Environment

Listing the Database Objects That Are Not Compatible With Streams

Listing the Database Objects That Have Become Compatible With Streams Recently

Monitoring Streams Performance Using Statspack

15 Troubleshooting a Streams Environment

Troubleshooting Capture Problems

Is the Capture Process Enabled?

Is the Capture Process Current?

Are Required Redo Log Files Missing?

Is a Downstream Capture Process Waiting for Redo Log Files?

Are You Trying to Configure Downstream Capture Using DBMS_STREAMS_ADM?

Are More Actions Required for Downstream Capture without a Database Link?

Troubleshooting Propagation Problems

Does the Propagation Use the Correct Source and Destination Queue?

Is the Propagation Job Used by a Propagation Enabled?

Are There Enough Job Queue Processes?

Is Security Configured Properly for the SYS.AnyData Queue?

Troubleshooting Apply Problems

Is the Apply Process Enabled?

Is the Apply Process Current?

Does the Apply Process Apply Captured Events or User-Enqueued Events?

Is the Apply Process Queue Receiving the Events to Apply?

Is a Custom Apply Handler Specified?

Is the AQ_TM_PROCESSES Initialization Parameter Set to Zero?

Are There Any Apply Errors in the Error Queue?

Troubleshooting Problems with Rules and Rule-Based Transformations

Are Rules Configured Properly for the Streams Client?

Are the Rule-Based Transformations Configured Properly?

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?

Part III Example Environments and Applications

16 Single Database Capture and Apply Example

Overview of the Single Database Capture and Apply Example

Prerequisites

Set Up the Environment

Configure Capture and Apply

Make DML Changes, Query for Results, and Dequeue Events

17 Rule-Based Application Example

Overview of the Rule-Based Application

Using Rules on Non-Table Data Stored in Explicit Variables

Using Rules on Data in Explicit Variables with Iterative Results

Using Partial Evaluation of Rules on Data in Explicit Variables

Using Rules on Data Stored in a Table

Using Rules on Both Explicit Variables and Table Data

Using Rules on Implicit Variables and Table Data

Using Event Contexts and Implicit Variables with Rules

Dispatching Problems and Checking Results for the Table Examples

Part IV Appendixes

A XML Schema for LCRs

Definition of the XML Schema for LCRs

B Online Database Upgrade and Maintenance With Streams

Overview of Using Streams in the Database Maintenance Process

Performing a Database Version Upgrade Using Streams

Performing a Database Version Upgrade Using Streams and Original Export/Import

Performing a Database Version Upgrade Using Streams and RMAN

Performing a Database Maintenance Operation Using Streams

Preparing for Upgrades to User-Created Applications

Deciding Which Utility to Use for Instantiation

Performing the Maintenance Operation Using Export/Import and Streams

Performing the Maintenance Operation Using RMAN and Streams

Finishing the Database Maintenance Operation

Index