Oracle9i Streams -- Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Part I Streams Concepts

1 Introduction to Streams

Part II Streams Administration

11 Configuring a Streams Environment

Configuring a Streams Administrator

Setting Initialization Parameters Relevant to Streams

Setting Export and Import Parameters Relevant to Streams

Export Utility Parameters Relevant to Streams

Import Utility Parameters Relevant to Streams

Configuring a Database to Run a Streams Capture Process

Configuring the Database to Run in ARCHIVELOG Mode

Specifying an Alternate Tablespace for LogMiner

Configuring Network Connectivity and Database Links

Configuring a Capture-Based Streams Environment

Creating a New Streams Single Source Environment

Adding Shared Objects to an Existing Single Source Environment

Adding a New Destination Database to an Existing Single Source Environment

Creating a New Multiple Source Environment

Adding Shared Objects to an Existing Multiple Source Environment

Adding a New Database to an Existing Multiple Source Environment

12 Managing a Capture Process

Creating a Capture Process

Example of Creating a Capture Process Using DBMS_STREAMS_ADM

Example of Creating a Capture Process Using DBMS_CAPTURE_ADM

Starting a Capture Process

Specifying the Rule Set for a Capture Process

Adding Rules to the Rule Set for a Capture Process

Removing a Rule from the Rule Set for a Capture Process

Removing the Rule Set for a Capture Process

Setting a Capture Process Parameter

Specifying Supplemental Logging at a Source Database

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

Setting the Start SCN for a Capture Process

Preparing Database Objects for Instantiation at a Source Database

Aborting Preparation for Instantiation at a Source Database

Changing the DBID of a Database Where Changes Are Captured

Resetting the Log Sequence Number Where Changes Are Captured

Stopping a Capture Process

Dropping a Capture Process

13 Managing Staging and Propagation

Managing Streams Queues

Creating a Streams Queue

Enabling a User to Perform Operations on a Secure Queue

Disabling a User from Performing Operations on a Secure Queue

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

Adding Rules to the Rule Set for a Propagation

Removing a Rule from the Rule Set for a Propagation

Removing the 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

Propagating Messages Between a SYS.AnyData Queue and a Typed Queue

14 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 the 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 a DML Handler

Creating a DML Handler

Setting a DML Handler

Removing a DML Handler

Managing the DDL Handler for an Apply Process

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 an Error Handler

Creating an Error Handler

Setting an Error Handler

Removing an Error Handler

Managing the Substitute Key Columns for a Table

Setting Substitute Key Columns for a Table

Removing the Substitute Key Columns for a Table

Managing Streams Conflict Resolution

Setting an Update Conflict Handler

Modifying an Existing Update Conflict Handler

Removing an Existing Update Conflict Handler

Managing Apply Errors

Retrying Apply Error Transactions

Deleting Apply Error Transactions

Setting Instantiation SCNs at a Destination Database

Setting Instantiation SCNs Using Export/Import

Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package

15 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

16 Other Streams Management Tasks

Managing Logical Change Records (LCRs)

Constructing and Enqueuing LCRs

The use_old Parameter in Some Row LCR Member Functions

Constructing and Processing LCRs Containing LOB Columns

Managing Streams Tags

Managing Streams Tags for the Current Session

Managing Streams Tags for an Apply Process

Performing Database 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

Performing Full Database Export/Import on a Database Using Streams

17 Monitoring a Streams Environment

Summary of Streams Static Data Dictionary Views

Summary of Streams Dynamic Performance Views

Monitoring a Streams Capture Process

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

Displaying General Information About a Capture Process

Listing the Parameter Settings for a Capture Process

Determining the Applied SCN for All Capture Processes in a Database

Determining Redo Log Scanning Latency for a Capture Process

Determining Event Enqueuing Latency for a Capture Process

Determining Which Database Objects Are Prepared for Instantiation

Displaying Supplemental Log Groups at a Source Database

Monitoring a Streams Queue

Displaying the Streams Queues in a Database

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

Determining the Rule Set for a Propagation

Displaying the Schedule for a Propagation Job

Determining the Total Number of Events and Bytes Propagated

Monitoring a Streams Apply Process

Displaying General Information About Each Apply Process

Listing the Parameter Settings for an Apply Process

Displaying Information About Apply Handlers

Displaying the Substitute Key Columns Specified at a Destination Database

Displaying Information About Update Conflict Handlers for a Destination Database

Determining the Tables for Which an Instantiation SCN Has Been Set

Displaying Information About the Reader Server for an Apply Process

Determining Capture to Dequeue Latency for an Event

Displaying Information About the Coordinator Process

Determining the Capture to Apply Latency for an Event

Displaying Information About the Apply Servers for an Apply Process

Displaying Effective Apply Parallelism for an Apply Process

Checking for Apply Errors

Displaying Detailed Information About Apply Errors

Monitoring Rules and Rule-Based Transformations

Displaying the Streams Rules Used by a Streams Process or Propagation

Displaying the Condition for a Streams Rule

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 the Rule-Based Transformations in a Rule Set

Monitoring Streams Tags

Displaying the Tag Value for the Current Session

Displaying the Tag Value for an Apply Process

18 Troubleshooting a Streams Environment

Troubleshooting Capture Problems

Is the Capture Process Enabled?

Is the Capture Process Current?

Is LOG_PARALLELISM Set to 1?

Is LOGMNR_MAX_PERSISTENT_SESSIONS Set High Enough?

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 Streams 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 a Custom Apply Handler Specified?

Is the Apply Process Waiting for a Dependent Transaction?

Are There Any Apply Errors in the Exception Queue?

Troubleshooting Problems with Rules and Rule-Based Transformations

Are Rules Configured Properly for the Streams Process or Propagation?

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

19 Streams Messaging Example

Overview of Messaging Example

Prerequisites

Set Up Users and Create a Streams Queue

Create the Enqueue Procedures

Configure an Apply Process

Configure Explicit Dequeue

Enqueue Events

Dequeue Events Explicitly and Query for Applied Events

Enqueue and Dequeue Events Using JMS

20 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

21 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

22 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

23 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

24 Rule-Based Application Example

Overview of the Rule-Based Application

Using Rules on Non-Table Data Stored 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

Part IV Appendixes

A XML Schema for LCRs

Definition of the XML Schema for LCRs