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 1 (11.1) New Features in Oracle Streams

Part I 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
  • Other Capabilities of Oracle Streams
• 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
• Overview of Implicit Capture
  • Overview of the Capture Process
  • Overview of Synchronous Capture
• 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 Oracle Streams Tags
• Overview of Heterogeneous Information Sharing
  • Overview of Oracle to Non-Oracle Data Sharing
  • Overview of Non-Oracle to Oracle Data Sharing
• Sample Oracle Streams Configurations
  • Sample Hub-and-Spoke Replication Configuration
  • Sample Hub-and-Spoke Replication Configuration With Downstream Capture
  • Sample Hub-and-Spoke 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
• Administration Tools for an Oracle Streams Environment
  • 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_MESSAGING Package
    • DBMS_STREAMS_TABLESPACE_ADM Package
    • UTL_SPADV Package
  • Oracle Streams Data Dictionary Views
  • Oracle Streams Tool in the Oracle Enterprise Manager Console

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 Changes Captured by Capture Processes
    • Types of DML Changes Captured by Capture Processes
    • DDL Changes and Capture Processes
    • Changes Ignored by a Capture Process
    • NOLOGGING and UNRECOVERABLE Keywords for SQL Operations
    • UNRECOVERABLE Clause for Direct Path Loads
  • Supplemental Logging in an Oracle Streams Environment
  • Local Capture and Downstream Capture
    • Local Capture
    • Downstream Capture
  • SCN Values Relating to a Capture Process
    • Captured SCN and Applied SCN
    • First SCN and Start SCN
  • Oracle Streams Capture Processes and RESTRICTED SESSION
  • Apply Process Interoperability with Oracle Streams Capture Processes
  • Oracle Streams Capture Processes and Oracle Real Application Clusters
  • Oracle Streams Capture Processes and Transparent Data Encryption
  • Capture Process Components
  • Capture User
  • 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 Oracle 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
• Implicit Capture with Synchronous Capture
  • Introduction to Synchronous Capture
  • Synchronous Capture and Queues
  • Synchronous Capture Rules
  • Data Types Captured by Synchronous Capture
  • Types of Changes Captured by Synchronous Capture
    • Types of DML Changes Captured by Synchronous Capture
    • Changes Ignored by Synchronous Capture
  • Synchronous Capture and Oracle Real Application Clusters
  • Synchronous Capture and Transparent Data Encryption
  • 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)
  • Enqueue Features
  • Explicit Capture and Transparent Data Encryption

3 Oracle Streams Staging and Propagation

• Introduction to Message Staging and Propagation
• Queues
  • ANYDATA Queues and User Messages
  • Queues and Oracle Real Application Clusters
  • Commit-Time Queues
    • When to Use Commit-Time Queues
    • How Commit-Time Queues Work
  • Buffered Queues
    • Buffered Queues and Oracle Streams Clients
    • Buffered Queues and Transparent Data Encryption
  • Secure Queues
    • Secure Queues and the SET_UP_QUEUE Procedure
    • Secure Queues and Oracle Streams Clients
  • Transactional and Nontransactional Queues
• Message Propagation Between Queues
  • Propagation Rules
  • Queue-to-Queue Propagations
  • Ensured Message Delivery
  • Directed Networks
    • Queue Forwarding and Apply Forwarding
  • Binary File Propagation
  • Propagation Jobs
    • Propagation Scheduling and Oracle Streams Propagations
    • Propagation Jobs and RESTRICTED SESSION
  • Oracle Streams Data Dictionary for Propagations
  • Propagations and Transparent Data Encryption
• 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
  • How to Manage the Flow of LCRs From the Capture Process to the Apply Process
  • Combined Capture and Apply and Point-in-Time Recovery
• Oracle Streams Pool
  • Oracle Streams Pool Size Set by Automatic Memory Management
  • Oracle Streams Pool Size Set by Automatic Shared Memory Management
  • Oracle Streams Pool Size Set Manually by a Database Administrator
  • Oracle Streams Pool Size Set by Default

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
    • Apply LCRs Directly
    • Process Messages with Apply Handlers
    • Summary of Message Processing Options
  • The Source of Messages Applied by an Apply Process
  • Data Types Applied
  • Oracle Streams Apply Processes and RESTRICTED SESSION
  • Oracle Streams Apply Processes and Oracle Real Application Clusters
  • Apply Processes and Transparent Data Encryption
  • Apply Process Components
    • Reader Server States
    • Coordinator Process States
    • Apply Server States
  • Apply User
  • Apply Process Creation
  • Oracle 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
• Explicit Consumption with a Messaging Client
• Explicit Consumption with Manual Dequeue

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 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
    • 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 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

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

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

9 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
    • Greater than Ten Copies of Data
    • 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
    • Configuring Oracle Real Application Clusters with Oracle Streams
    • 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 II Oracle Streams Configuration

10 Preparing and Configuring an Oracle Streams Environment

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

11 Configuring Implicit Capture

• Configuring a Capture Process
  • Preparing to Configure a Capture Process
  • Configuring a Local Capture Process
    • Example of Configuring a Local Capture Process Using DBMS_STREAMS_ADM
    • Example of Configuring a Local Capture Process Using DBMS_CAPTURE_ADM
  • Preparing for and Configuring a Real-Time Downstream Capture Process
    • Preparing to Copy Redo Data for Real-Time Downstream Capture
    • Configuring a Real-Time Downstream Capture Process
  • Configuring an Archived-Log Downstream Capture Process that Assigns Logs Implicitly
    • Preparing to Copy Redo Log Files for Archived-Log Downstream Capture
    • Configuring an Archived-Log Downstream Capture Process
  • Configuring an Archived-Log Downstream Capture Process that Assigns Logs Explicitly
  • Configuring a Local Capture Process with Non-NULL Start SCN
  • After Configuring a Capture Process
• Configuring Synchronous Capture
  • Preparing to Configure a Synchronous Capture
  • Configuring a Synchronous Capture Using the DBMS_STREAMS_ADM Package
  • Configuring a Synchronous Capture Using the DBMS_CAPTURE_ADM Package
  • After Configuring a Synchronous Capture

12 Configuring Queues and Propagations

• Configuring Queues
  • Configuring an ANYDATA Queue
• Creating Oracle Streams Propagations Between ANYDATA Queues
  • Example of Configuring a Propagation Using DBMS_STREAMS_ADM
  • Example of Configuring a Propagation Using DBMS_PROPAGATION_ADM

13 Configuring Implicit Apply

• Overview of Apply Process Creation
• Creating an Apply Process Using the DBMS_STREAMS_ADM Package
  • Creating an Apply Process for Captured LCRs
  • Creating an Apply Process for User Messages
• Creating an Apply Process Using the DBMS_APPLY_ADM Package
  • Creating an Apply Process for Captured LCRs with DBMS_APPLY_ADM
  • Creating an Apply Process for Persistent LCRs with DBMS_APPLY_ADM
  • Creating an Apply Process for Persistent User Messages with DBMS_APPLY_ADM

14 Configuring Oracle Streams Messaging Environments

• 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

Part III Oracle Streams Administration

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 LCRs
  • Excluding Extra Attributes from Implicitly 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 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
• 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 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

21 Other Oracle Streams Management Tasks

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

22 Troubleshooting an Oracle Streams Environment

• Viewing Oracle Streams Alerts
• 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 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 Synchronous Capture Problems
  • Is a Synchronous Capture Failing to Capture Changes to Tables?
• Troubleshooting Propagation Problems
  • 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
• Troubleshooting Apply Process Problems
  • Is the Apply Process Enabled?
  • Is the Apply Process Current?
  • Does the Apply Process Apply Captured LCRs?
  • 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 There Any Apply Errors in the Error Queue?
• Troubleshooting Problems with 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?
• 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 IV Monitoring Oracle Streams

23 Monitoring an Oracle Streams Environment

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

24 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
  • Stream Paths in a Sample Combined Capture and Apply Replication Environment
  • Stream Paths in a Sample Replication Environment That Propagates Messages
• About the Information Gathered With 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

25 Monitoring Oracle Streams Implicit Capture

• Monitoring a Capture Process
  • 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
  • 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
• 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

26 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 the Schedule for a Propagation Job
  • Determining the Total Number of Messages and Bytes Propagated

27 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 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
• Determining Which Apply Processes Use Combined Capture and Apply
• Checking for Apply Errors
• Displaying Detailed Information About Apply Errors

28 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

29 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

30 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

31 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 That Have Become Compatible With Capture Processes Recently
  • Listing Database Objects and Columns That Are Not Compatible With Synchronous Captures
  • Monitoring Compatibility for Apply Processes
    • Listing Database Objects and Columns That Are 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 Sample Environments and Applications

32 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

33 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 VI Appendixes

A XML Schema for LCRs

• Definition of the XML Schema for LCRs

B Online Database Upgrade 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 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 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

C Online Database Maintenance with Oracle Streams

• Overview of Using Oracle Streams for Database Maintenance Operations
  • The Capture Database During the Maintenance Operation
  • Assumptions for the Database Being Maintained
  • Considerations for Job Slaves 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 Oracle Streams Directly or Generate a Script
  • Deciding Which Utility to Use for Instantiation
• Performing a Database Maintenance Operation Using Oracle Streams
  • Task 1: Beginning the Maintenance Operation
  • Task 2: Setting Up Oracle 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 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 Maintenance Operation and Removing Oracle Streams

Glossary

Index