Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Streams?

• Oracle Database 10g Release 2 (10.2) New Features in Streams
• Oracle Database 10g Release 1 (10.1) New Features in Streams

Part I Streams Concepts

1 Introduction to Streams

• Overview of Streams
• What Can Streams Do?
• • Capture Messages at a Database
  • Stage Messages in a Queue
  • Propagate Messages from One Queue to Another
  • Consume Messages
  • Other Capabilities of Streams
• What Are the Uses of Streams?
• • Message Queuing
  • Data Replication
  • Event Management and Notification
  • Data Warehouse Loading
  • Data Protection
  • Database Availability During Upgrade and Maintenance Operations
• Overview of the Capture Process
• Overview of Message Staging and Propagation
• • Overview of Directed Networks
  • Explicit Enqueue and Dequeue of Messages
• Overview of the Apply Process
• Overview of the Messaging Client
• Overview of Automatic Conflict Detection and Resolution
• Overview of Rules
• Overview of Rule-Based Transformations
• Overview of Streams Tags
• Overview of Heterogeneous Information Sharing
• • Overview of Oracle to Non-Oracle Data Sharing
  • Overview of Non-Oracle to Oracle Data Sharing
• Example Streams Configurations
• Administration Tools for a Streams Environment
• • Oracle-Supplied PL/SQL Packages
  • • DBMS_STREAMS_ADM Package
    • DBMS_CAPTURE_ADM Package
    • DBMS_PROPAGATION_ADM Package
    • DBMS_APPLY_ADM Package
    • DBMS_STREAMS_MESSAGING Package
    • DBMS_RULE_ADM Package
    • DBMS_RULE Package
    • DBMS_STREAMS Package
    • DBMS_STREAMS_TABLESPACE_ADM
    • DBMS_STREAMS_AUTH Package
  • Streams Data Dictionary Views
  • Streams Tool in the Oracle Enterprise Manager Console

2 Streams Capture Process

• The Redo Log and a Capture Process
• Logical Change Records (LCRs)
• • Row LCRs
  • DDL LCRs
  • Extra Information in LCRs
• Capture Process Rules
• Datatypes Captured
• Types of Changes Captured
• • Types of DML Changes Captured
  • DDL Changes and Capture Processes
  • 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
• • Local Capture
  • • The Source Database Performs All Change Capture Actions
    • Advantages of Local Capture
  • Downstream Capture
  • • Real-Time Downstream Capture
    • Archived-Log Downstream Capture
    • The Downstream Database Performs Most Change Capture Actions
    • Advantages of Downstream Capture
    • Optional Database Link from the Downstream Database to the Source Database
    • Operational Requirements for Downstream Capture
• SCN Values Relating to a Capture Process
• • Captured SCN and Applied SCN
  • First SCN and Start SCN
  • • First SCN
    • Start SCN
    • Start SCN Must Be Greater than or Equal to First SCN
    • A Start SCN Setting that Is Prior to Preparation for Instantiation
• 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
  • • Required Checkpoint SCN
    • Maximum Checkpoint SCN
    • Checkpoint Retention Time
  • Capture Process Creation
  • • The LogMiner Data Dictionary for a Capture Process
    • First SCN and Start SCN Specifications During Capture Process Creation
  • A New First SCN Value and Purged LogMiner Data Dictionary Information
  • The Streams Data Dictionary
  • ARCHIVELOG Mode and a Capture Process
  • • RMAN and Archived Redo Log Files Required by a Capture Process
  • Capture Process Parameters
  • • Capture Process Parallelism
    • Automatic Restart of a Capture Process
  • Capture Process Rule Evaluation
  • Persistent Capture Process Status Upon Database Restart

3 Streams Staging and Propagation

• Introduction to Message Staging and Propagation
• Captured and User-Enqueued Messages in an ANYDATA Queue
• Message Propagation Between Queues
• • Propagation Rules
  • Queue-to-Queue Propagations
  • Ensured Message Delivery
  • Directed Networks
  • • Queue Forwarding and Apply Forwarding
  • Binary File Propagation
• Messaging Clients
• ANYDATA Queues and User Messages
• Buffered Messaging and Streams Clients
• • Buffered Messages and Capture Processes
  • Buffered Messages and Propagations
  • Buffered Messages and Apply Processes
  • Buffered Messages and Messaging Clients
• Queues and Oracle Real Application Clusters
• 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
• Streams Staging and Propagation Architecture
• • Streams Pool
  • • Streams Pool Size Set by Automatic Shared Memory Management
    • Streams Pool Size Set Manually by a Database Administrator
    • Streams Pool Size Set by Default
  • Buffered Queues
  • Propagation Jobs
  • • Propagation Scheduling and Streams Propagations
    • Propagation Jobs and RESTRICTED SESSION
  • Secure Queues
  • • Secure Queues and the SET_UP_QUEUE Procedure
    • Secure Queues and Streams Clients
  • Transactional and Nontransactional Queues
  • Streams Data Dictionary for Propagations

4 Streams Apply Process

• Introduction to the Apply Process
• Apply Process Rules
• Message Processing with an Apply Process
• • Processing Captured or User-Enqueued Messages with an Apply Process
  • Message Processing Options for an Apply Process
  • • LCR Processing
    • Non-LCR User Message Processing
    • Audit Commit Information for Messages Using Precommit Handlers
    • Considerations for Apply Handlers
    • Summary of Message Processing Options
• Datatypes Applied
• Streams Apply Processes and RESTRICTED SESSION
• Streams Apply Processes and Oracle Real Application Clusters
• Apply Process Architecture
• • Apply Process Components
  • • Reader Server States
    • Coordinator Process States
    • Apply Server States
  • Apply Process Creation
  • Streams Data Dictionary for an Apply Process
  • Apply Process Parameters
  • • Apply Process Parallelism
    • Commit Serialization
    • Automatic Restart of an Apply Process
    • Stop or Continue on Error
  • Multiple Apply Processes in a Single Database
  • Persistent Apply Process Status upon Database Restart
  • The Error Queue

5 Rules

• 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

6 How Rules Are Used in Streams

• Overview of How Rules Are Used in Streams
• Rule Sets and Rule Evaluation of Messages
• • 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
• • 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
    • Restrictions for 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
• 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
  • • 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
  • 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
  • • Rule Conditions for Specific Types of Operations
    • Rule Conditions that Instruct 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

7 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 Streams Clients
• • Rule-Based Transformations and Capture Processes
  • • Rule-Based Transformation Errors During 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

8 Information Provisioning

• 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 Streams
• On-Demand Information Access

9 Streams High Availability Environments

• Overview of Streams High Availability Environments
• Protection from Failures
• • Streams Replica Database
  • • Updates at the Replica Database
    • Heterogeneous Platform Support
    • Multiple Character Sets
    • Mining the Online Redo Logs to Minimize Latency
    • Greater than Ten Copies of Data
    • Fast Failover
    • Single Capture for Multiple Destinations
  • When Not to Use Streams
  • Application-maintained Copies
• Best Practices for Streams High Availability Environments
• • Configuring Streams for High Availability
  • • Directly Connecting Every Database to Every Other Database
    • Creating Hub-and-Spoke Configurations
    • Configuring Oracle Real Application Clusters with Streams
    • Local or Downstream Capture with Streams
  • 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 II Streams Administration

10 Preparing a Streams Environment

• Configuring a Streams Administrator
• Setting Initialization Parameters Relevant to Streams
• Configuring Network Connectivity and Database Links

11 Managing a Capture Process

• Creating a Capture Process
• • Preparing to Create a Capture Process
  • Creating a Local Capture Process
  • • Example of Creating a Local Capture Process Using DBMS_STREAMS_ADM
    • Example of Creating a Local Capture Process Using DBMS_CAPTURE_ADM
  • Preparing for and Creating a Real-Time Downstream Capture Process
  • • Preparing to Copy Redo Data for Real-Time Downstream Capture
    • Creating a Real-Time Downstream Capture Process
  • Creating an Archived-Log Downstream Capture Process that Assigns Logs Implicitly
  • • Preparing to Copy Redo Log Files for Archived-Log Downstream Capture
    • Creating an Archived-Log Downstream Capture Process
  • Creating an Archived-Log Downstream Capture Process that Assigns Logs Explicitly
  • Creating a Local Capture Process with Non-NULL Start SCN
• Starting a Capture Process
• Stopping a Capture Process
• Managing the Rule Set for a Capture Process
• • Specifying a Rule Set for a Capture Process
  • • Specifying a Positive Rule Set for a Capture Process
    • Specifying a Negative Rule Set for a Capture Process
  • Adding Rules to a Rule Set for a Capture Process
  • • Adding Rules to the Positive Rule Set for a Capture Process
    • Adding Rules to the Negative 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
• Specifying Supplemental Logging at a Source Database
• 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
• Managing Extra Attributes in Captured Messages
• • Including Extra Attributes in Captured Messages
  • Excluding Extra Attributes from Captured Messages
• Dropping a Capture Process

12 Managing Staging and Propagation

• Managing ANYDATA Queues
• • Creating an ANYDATA Queue
  • Enabling a User to Perform Operations on a Secure Queue
  • Disabling a User from Performing Operations on a Secure Queue
  • Removing an ANYDATA Queue
• Managing Streams Propagations and Propagation Jobs
• • Creating a Propagation Between Two ANYDATA Queues
  • • Example of Creating a Propagation Using DBMS_STREAMS_ADM
    • Example of Creating a Propagation Using DBMS_PROPAGATION_ADM
  • 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
• Managing a Streams Messaging Environment
• • Wrapping User Message Payloads in an ANYDATA Wrapper and Enqueuing Them
  • Dequeuing a Payload that Is Wrapped in an ANYDATA Payload
  • Configuring a Messaging Client and Message Notification

13 Managing an Apply Process

• Creating an Apply Process
• • Examples of Creating an Apply Process Using DBMS_STREAMS_ADM
  • • Creating an Apply Process for Captured Messages
    • Creating an Apply Process for User-Enqueued Messages
  • Examples of Creating an Apply Process Using DBMS_APPLY_ADM
  • • Creating an Apply Process for Captured Messages with DBMS_APPLY_ADM
    • Creating an Apply Process for User-Enqueued Messages with DBMS_APPLY_ADM
• 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 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 Message Enqueues by Apply Processes
• • 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
• Dropping an Apply Process

14 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

15 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

16 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

17 Other Streams Management Tasks

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

18 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 Data?
  • Are You Trying to Configure Downstream Capture without DBMS_CAPTURE_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 Enabled?
  • Are There Enough Job Queue Processes?
  • 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
• Troubleshooting Apply Problems
• • Is the Apply Process Enabled?
  • Is the Apply Process Current?
  • Does the Apply Process Apply Captured Messages or User-Enqueued Messages?
  • Is the Apply Process 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?
  • Are Any Apply Errors in the Error Queue?
• Troubleshooting Problems with Rules and Rule-Based Transformations
• • Are Rules Configured Properly for the 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?
• 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 Monitoring Streams

19 Monitoring a Streams Environment

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

20 Monitoring Streams Capture Processes

• Displaying the Queue, Rule Sets, and Status of 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
• 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 Message Enqueuing Latency for Each Capture Process
• Displaying Information About Rule Evaluations for Each Capture Process

21 Monitoring Streams Queues and Propagations

• Monitoring ANYDATA 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 User-Enqueued Message in a Queue
  • Viewing the Contents of User-Enqueued Messages in a 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 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 the Schedule for a Propagation Job
  • Determining the Total Number of Messages and Bytes Propagated

22 Monitoring 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 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 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
• Checking for Apply Errors
• Displaying Detailed Information About Apply Errors

23 Monitoring Rules

• Displaying All Rules Used by All Streams Clients
• Displaying the Streams Rules Used by a Specific Streams Client
• • Displaying the Rules in the Positive Rule Set for a Streams Client
  • Displaying the Rules in the Negative Rule Set for a Streams Client
• Displaying the Current Condition for a Rule
• Displaying Modified Rule Conditions for 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

24 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

25 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

26 Monitoring Other Streams Components

• Monitoring Streams Administrators and Other Streams Users
• • Listing Local Streams Administrators
  • Listing Users Who Allow Access to Remote Streams Administrators
• Monitoring the Streams Pool
• • Query Result that Advises Increasing the Streams Pool Size
  • Query Result that Advises Retaining the Current Streams Pool Size
  • Query Result that Advises Decreasing the Streams Pool Size
• 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 AWR and Statspack

Part IV Sample Environments and Applications

27 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 Messages

28 Rule-Based Application Example

• Overview of the Rule-Based Application
• Using Rules on Nontable 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 V Appendixes

A XML Schema for LCRs

• Definition of the XML Schema for LCRs

B Online Database Upgrade with Streams

• Overview of Using 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 Streams
• • Preparing to Upgrade a Database with User-defined Types
  • Deciding Which Utility to Use for Instantiation
• Performing a Database Upgrade Using Streams
• • Task 1: Beginning the Upgrade
  • Task 2: Setting Up Streams Prior to 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 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 Streams

C Online Database Maintenance with Streams

• Overview of Using Streams for Database Maintenance Operations
• • The Capture Database During the Maintenance Operation
  • Assumptions for the Database Being Maintained
  • Considerations for Job Queue Processes and PL/SQL Package Subprograms
  • Unsupported Database Objects Are Excluded
• Preparing for a Database Maintenance Operation
• • Preparing for Downstream Capture
  • Preparing for 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 Streams Directly or Generate a Script
  • Deciding Which Utility to Use for Instantiation
• Performing a Database Maintenance Operation Using Streams
• • Task 1: Beginning the Maintenance Operation
  • Task 2: Setting Up Streams Prior to 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 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 Maintenance Operation and Removing Streams

Glossary

Index