1/24

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database XStream Guide

• Changes in Oracle Database 12c Release 1 (12.1)

Part I XStream General Concepts and Use Cases

1 Introduction to XStream

• About XStream
• Purpose of XStream
• XStream Use Cases
  • Replicating Data Changes with Non-Oracle Databases
  • Using Files to Store Data Changes
    • XStream Demo That Replicates Database Changes Using Files
  • Sharing Data Changes with a Client-Side Memory Cache
• Prerequisites for XStream
• XStream Security Models
• Tasks and Tools for XStream
  • XStream Tasks
  • XStream Tools

2 General XStream Concepts

• Logical Change Records (LCRs)
  • Row LCRs
    • Row LCR Subtypes
  • DDL LCRs
  • Extra Information in Row LCRs and DDL LCRs
  • Sequence LCRs
  • Position Order in an LCR Stream
• Rules and Rule Sets
  • Rules and Rule Sets Defined
  • Rule Sets and XStream Components
  • System-Created Rules and XStream
    • XStream System-Created Rule Procedures
    • Global Rules
    • Schema Rules
    • Table Rules
    • Subset Rules
    • System-Created Rules and a Multitenant Environment
• Rule-Based Transformations
  • Declarative Rule-Based Transformations
  • Declarative Rule-Based Transformation Ordering
  • Evaluating Transformation Ordering
    • Row Migration Transformation Ordering
    • User-Specified Declarative Transformation Ordering
    • Considerations for Rule-Based Transformations
• XStream and the Oracle Streams Performance Advisor
  • XStream Components
    • XStream Out Apply Subcomponents
    • XStream In Apply Subcomponents
  • Topology and Stream Paths
  • XStream and Component-Level Statistics
  • The UTL_SPADV Package
    • Collecting XStream Statistics Using the UTL_SPADV Package
    • Showing XStream Statistics on the Command Line
    • Interpreting SHOW_STATS Output
    • Showing XStream Statistics in an HTML Report
    • Interpreting the HTML Report From SHOW_STATS_HTML
• XStream and SQL Generation
  • Interfaces for Performing SQL Generation
  • SQL Generation Formats
  • SQL Generation and Data Types
    • SQL Generation and Automatic Data Type Conversion
  • SQL Generation and Character Sets
  • Sample Generated SQL Statements
    • Sample Generated SQL Statements for the hr.employees Table
    • Sample Generated SQL Statements for a Table With LOB Columns
  • SQL Generation Demo

Part II XStream Out

3 XStream Out Concepts

• Introduction to XStream Out
• Capture Processes
  • Capture Process Overview
  • Data Types Captured by a Capture Process
    • ID Key LCRs
  • Types of DML Changes Captured by Capture Processes
  • Local Capture and Downstream Capture
    • Local Capture
    • Downstream Capture
  • Capture Processes and RESTRICTED SESSION
  • Capture Process Subcomponents
  • Capture Process States
  • Capture Process Parameters
  • Capture Process Checkpoints and XStream Out
    • Required Checkpoint SCN
    • Maximum Checkpoint SCN
    • Checkpoint Retention Time
  • SCN Values Related to a Capture Process
    • Captured SCN and Applied SCN
    • First SCN and Start SCN
• Outbound Servers
  • Overview of Outbound Servers
  • Data Types Supported by Outbound Servers
  • Apply User for an Outbound Server
  • Outbound Servers and RESTRICTED SESSION
  • Outbound Server Components
  • Considerations for Outbound Servers
  • Outbound Servers and Apply Parameters
• Position of LCRs and XStream Out
  • Additional LCR Attributes Related to Position in XStream Out
  • The Processed Low Position and Restartability for XStream Out
  • Streaming Network Transmission
• XStream Out and Distributed Transactions
• XStream Out and Security
  • The XStream Out Client Application and Security
  • XStream Out Component-Level Security
  • Privileges Required by the Capture User for a Capture Process
  • Privileges Required by the Connect User for an Outbound Server
• XStream Out and Other Oracle Database Components
  • XStream Out and Oracle Real Application Clusters
    • Capture Processes and Oracle Real Application Clusters
    • Queues and Oracle Real Application Clusters
    • Propagations and Oracle Real Application Clusters
    • Outbound Servers and Oracle Real Application Clusters
  • XStream Out and Transparent Data Encryption
    • Capture Processes and Transparent Data Encryption
    • Propagations and Transparent Data Encryption
    • Outbound Servers and Transparent Data Encryption
  • XStream Out and Flashback Data Archive
  • XStream Out and Recovery Manager
    • RMAN and Local Capture Processes
    • RMAN and Downstream Capture Processes
  • XStream and Distributed Transactions
  • XStream Out and a Multitenant Environment

4 Configuring XStream Out

• Preparing for XStream Out
  • Decide How to Configure XStream Out
  • Prerequisites for Configuring XStream Out
    • Configure an XStream Administrator on All Databases
    • Granting Additional Privileges to the XStream Administrator
    • If Required, Configure Network Connectivity and Database Links
    • Ensure That Each Source Database Is in ARCHIVELOG Mode
    • Set the Relevant Initialization Parameters
    • Configure the Streams pool
    • If Required, Configure Supplemental Logging
    • If Required, Configure Log File Transfer to a Downstream Database
    • If Required, Add Standby Redo Logs for Real-Time Downstream Capture
• Configuring XStream Out
  • Configuring an Outbound Server Using CREATE_OUTBOUND
  • Adding an Additional Outbound Server to a Capture Process Stream
  • Configuring an Outbound Server Using ADD_OUTBOUND
  • Configuring XStream Out in a CDB
    • Configuring XStream Out with Local Capture in a CDB
    • Configuring XStream Out with Downstream Capture in CDBs

5 Managing XStream Out

• About Managing XStream Out
• Managing an Outbound Server
  • Starting an Outbound Server
  • Stopping an Outbound Server
  • Setting an Apply Parameter for an Outbound Server
  • Changing the Connect User for an Outbound Server
• Managing the Capture Process for an Outbound Server
  • Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process
  • Starting a Capture Process
  • Stopping a Capture Process
  • Setting a Capture Process Parameter
  • Changing the Capture User of an Outbound Server's Capture Process
  • Changing the Start SCN or Start Time of an Outbound Server's Capture Process
    • Changing the Start SCN of an Outbound Server's Capture Process
    • Changing the Start Time of an Outbound Server's Capture Process
  • Setting the First SCN for a Capture Process
• Managing Rules for an XStream Out Configuration
  • Adding Rules to an XStream Out Configuration
    • Adding Schema Rules and Table Rules to an XStream Out Configuration
    • Adding Subset Rules to an Outbound Server's Positive Rule Set
    • Adding Rules With Custom Conditions to XStream Out Components
  • Removing Rules from an XStream Out Configuration
    • Removing Schema Rules and Table Rules From an XStream Out Configuration
    • Removing Subset Rules from an Outbound Server's Positive Rule Set
    • Removing Rules Using the REMOVE_RULE Procedure
• 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 Existing Declarative Rule-Based Transformations
  • Removing Declarative Rule-Based Transformations
• Dropping Components in an XStream Out Configuration
• Removing an XStream Out Configuration

6 Monitoring XStream Out

• About Monitoring XStream Out
• Monitoring Session Information About XStream Out Components
• Monitoring XStream Out
  • Displaying General Information About an Outbound Server
  • Displaying Status and Error Information for an Outbound Server
  • Displaying Information About an Outbound Server's Current Transaction
  • Displaying Statistics for an Outbound Server
  • Displaying the Processed Low Position for an Outbound Server
  • Determining the Process Information for an Outbound Server
  • Displaying the Apply Parameter Settings for an Outbound Server
• Monitoring the Capture Process for an Outbound Server
  • Displaying Change Capture Information About Each Capture Process
  • Displaying the Registered Redo Log Files for Each Capture Process
  • Displaying Redo Log Files That Are Required by Each Capture Process
  • Displaying SCN Values for Each Redo Log File Used by Each Capture Process
  • Listing the Parameter Settings for Each Capture Process
  • Determining the Applied SCN for Each Capture Process
  • Displaying the Redo Log Scanning Latency for Each Capture Process
  • Displaying the Extra Attributes Captured by a Capture Process
• Monitoring XStream Rules
• Monitoring Declarative Rule-Based Transformations
  • Displaying Information About ADD COLUMN Transformations
  • Displaying Information About RENAME TABLE Transformations

7 Troubleshooting XStream Out

• Diagnosing Problems with XStream Out
  • Viewing Alerts
  • Using the Streams Configuration Report and Health Check Script
  • Checking the Trace File and Alert Log for Problems
    • Capture Process Trace Files
    • Logminer Trace Files
    • Outbound Server Trace File
    • Client Application Trace Files
• Problems and Solutions for XStream Out
  • An OCI Client Application Cannot Attach to the Outbound Server
  • Changes Are Failing to Reach the Client Application in XStream Out
  • The Capture Process Is Missing Required Redo Log Files
  • LCRs Streaming from an Outbound Server Are Missing Extra Attributes
  • The XStream Out Client Application Is Unresponsive
• How to Get More Help with XStream Out

Part III XStream In

8 XStream In Concepts

• Introduction to XStream In
• The Inbound Server
  • Overview of Inbound Servers
  • Data Types Applied by Inbound Servers
  • LCR Processing Options for Inbound Servers
    • DML Handlers
    • Error Handlers
    • DDL Handlers
    • Precommit Handlers
  • Inbound Servers and RESTRICTED SESSION
  • Inbound Server Components
  • Considerations for Inbound Servers
  • The Error Queue for an Inbound Server
• Position of LCRs and XStream In
• XStream In and Performance Considerations
  • Optimizing XStream In Performance for Large Transactions
  • Avoiding Potential Bottlenecks in Transaction Tracking
  • Optimizing Transaction Apply Scheduling
• XStream In and Security
  • The XStream In Client Application and Security
  • XStream In Component-Level Security
  • Privileges Required by the Apply User for an Inbound Server
• XStream In and Other Oracle Database Components
  • XStream In and Oracle Real Application Clusters
  • XStream In and Flashback Data Archive
  • XStream In and Transportable Tablespaces
  • XStream In and a Multitenant Environment

9 Configuring XStream In

• Preparing for XStream In
  • Configure an XStream Administrator
    • Granting Additional Privileges to the XStream Administrator
  • Set the Relevant Initialization Parameters
  • Configure the Streams pool
  • If Required, Specify Supplemental Logging at the Source Database
• Configuring XStream In

10 Managing XStream In

• About Managing XStream In
• Starting an Inbound Server
• Stopping an Inbound Server
• Setting an Apply Parameter for an Inbound Server
• Changing the Apply User for an Inbound Server
• Managing Apply Errors
  • Retrying Apply Error Transactions
    • Retrying a Specific Apply Error Transaction
    • Retrying All Error Transactions for an Inbound Server
  • Deleting Apply Error Transactions
    • Deleting a Specific Apply Error Transaction
    • Deleting All Error Transactions for an Inbound Server
  • Managing Eager Errors Encountered by an Inbound Server
• Dropping Components in an XStream In Configuration

11 Monitoring XStream In

• Displaying Session Information for Inbound Servers
• Displaying General Information About an Inbound Server
• Displaying the Status and Error Information for an Inbound Server
• Displaying Apply Parameter Settings for an Inbound Server
• Displaying the Position Information for an Inbound Server
• Checking for Apply Errors
• Displaying Detailed Information About Apply Errors
  • Step 1: Grant Explicit SELECT Privilege on the ALL_APPLY_ERROR View
  • Step 2: Create a Procedure that Prints the Value in an ANYDATA Object
  • Step 3: Create a Procedure that Prints a Specified LCR
  • Step 4: Create a Procedure that Prints All the LCRs in the Error Queue
  • Step 5: Create a Procedure that Prints All the Error LCRs for a Transaction

12 Troubleshooting XStream In

• Diagnosing Problems with XStream In
  • Viewing Alerts
  • Checking the Trace File and Alert Log for Problems
• Problems and Solutions for XStream In
  • XStream In Cannot Identify an Inbound Server
  • Inbound Server Encounters an ORA-03135 Error
• How to Get More Help with XStream In

Part IV Appendixes

A Sample XStream Client Application

• About the Sample XStream Client Application
• Sample XStream Client Application for the Oracle Call Interface API
• Sample XStream Client Application for the Java API

B XStream Out Restrictions

• Capture Process Restrictions
  • Unsupported Data Types for Capture Processes
  • Unsupported Changes for Capture Processes
    • Unsupported Schemas for Capture Processes
    • Unsupported Table Types for Capture Processes
    • Unsupported DDL Changes for Capture Processes
    • Changes Ignored by a Capture Process
    • NOLOGGING and UNRECOVERABLE Keywords for SQL Operations
    • UNRECOVERABLE Clause for Direct Path Loads
  • Supplemental Logging Data Type Restrictions
  • Operational Requirements for Downstream Capture with XStream Out
  • Capture Processes Do Not Support Oracle Label Security
• Propagation Restrictions
  • Connection Qualifiers and Propagations
• Outbound Server Restrictions
  • Unsupported Data Types for Outbound Servers
  • Types of DDL Changes Ignored by an Outbound Server
  • Apply Process Features That Are Not Applicable to Outbound Servers
• XStream Out Rule Restrictions
  • Restrictions for Subset Rules
• XStream Out Rule-Based Transformation Restrictions
  • Unsupported Data Types for Declarative Rule-Based Transformations
• XStream Out Limitations for Extended Data Types

C XStream In Restrictions

• Inbound Server Restrictions
  • Unsupported Data Types for Inbound Servers
  • Unsupported Data Types for Apply Handlers
  • Types of DDL Changes Ignored by an Inbound Server
  • Current Schema User Must Exist at Destination Database
  • Inbound Servers Do Not Support Oracle Label Security
• XStream In Rule Restrictions
  • Restrictions for Subset Rules
• XStream In Rule-Based Transformation Restrictions
  • Unsupported Data Types for Declarative Rule-Based Transformations
• XStream In Limitations for Extended Data Types

Index