1/27

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 2 (12.2.0.1)
  • New Features
• Changes in Oracle Database 12c Release 1 (12.1)
  • New Features

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
  • LCRIDs and the Position of LCRs
• 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
      • System-Created Rules in a CDB and XStream Out
      • System-Created Rules in a CDB and XStream In
• 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
      • Sample Output When an Outbound Server Is the Last Component in a Path
      • Sample Output When an Inbound Server Is the Last Component in a Path
    • 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 LOB, LONG, LONG RAW, and XMLType Data Types
  • 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
    • ID Key LCRs Demo
  • Types of DML Changes Captured by Capture Processes
  • 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 with XStream Out
  • 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
      • First SCN
      • Start SCN
      • Start SCN Must Be Greater Than or Equal to First 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 Subcomponents
  • 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
      • Required Supplemental Logging in an XStream Environment
      • 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
      • Procedures That Automatically Specify 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 the History of Events for XStream Out Components
• Monitoring an Outbound Server
  • 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
      • Statement DML Handlers
      • Procedure 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 XStream In Conflict Detection and Resolution
  • About DML Conflicts in an XStream Environment
  • Conflict Types in an XStream Environment
    • Update Conflicts in an XStream Environment
    • Uniqueness Conflicts in an XStream Environment
    • Delete Conflicts in an XStream Environment
    • Foreign Key Conflicts in an XStream Environment
  • Conflicts and Transaction Ordering in an XStream Environment
  • Conflict Detection in an XStream Environment
    • About Conflict Detection in an XStream Environment
    • Control Over Conflict Detection for Non-Key Columns
    • Rows Identification During Conflict Detection in an XStream Environment
  • Conflict Avoidance in an XStream Environment
    • Use a Primary Database Ownership Model
    • Avoid Specific Types of Conflicts
      • Avoid Uniqueness Conflicts in an XStream Environment
      • Avoid Delete Conflicts in an Oracle Streams Environment
      • Avoid Update Conflicts in an XStream Environment
  • Conflict Resolution in an XStream Environment
    • About Conflict Resolution in an XStream Environment
    • Prebuilt DML Conflict Handlers
    • Types of Prebuilt DML Conflict Handlers
    • Column Lists
    • Resolution Columns
    • Data Convergence
    • Collision Handling Without a DML Conflict Handler
    • Custom Conflict Handlers
  • Managing DML Conflict Handlers
    • Setting a DML Conflict Handler
    • Removing a DML Conflict Handler
  • Stopping Conflict Detection for Non-Key Columns
• Managing Apply Errors
  • Inbound Server Error Handling
    • About Error Handlers
    • Setting and Unsetting an Error Handler
  • Retrying Apply Error Transactions
    • Retrying a Specific Apply Error Transaction
      • Retrying a Specific Apply Error Transaction Without a User Procedure
      • Retrying a Specific Apply Error Transaction With a User Procedure
    • 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
• Conflict and Error Handling Precedence
• Dropping Components in an XStream In Configuration

11 Monitoring XStream In

• Displaying Session Information for Inbound Servers
• Displaying General Information About an Inbound Server
• Monitoring the History of Events for XStream In Components
• 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
• Displaying Information About DML Conflict Handlers
• Displaying Information About Error Handlers
• 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
  • Changes Are Failing to Reach the Client Application in XStream In
• How to Get More Help with XStream In

Part IV Oracle GoldenGate Capabilities in Oracle Database

13 Oracle GoldenGate Automatic Conflict Detection and Resolution

• About Oracle GoldenGate
• About Automatic Conflict Detection and Resolution
  • Automatic Conflict Detection and Resolution
  • Latest Timestamp Conflict Detection and Resolution
  • Delta Conflict Detection and Resolution
  • Column Groups
• Configuring Automatic Conflict Detection and Resolution
  • Configuring Latest Timestamp Conflict Detection and Resolution
  • Configuring Delta Conflict Detection and Resolution
• Managing Automatic Conflict Detection and Resolution
  • Altering Conflict Detection and Resolution for a Table
  • Altering a Column Group
  • Purging Tombstone Rows
  • Removing Conflict Detection and Resolution From a Table
  • Removing a Column Group
  • Removing Delta Conflict Detection and Resolution
• Monitoring Automatic Conflict Detection and Resolution
  • Displaying Information About the Tables Configured for Conflicts
  • Displaying Information About Conflict Resolution Columns
  • Displaying Information About Column Groups

14 Oracle GoldenGate Procedural Replication

• About Oracle GoldenGate Procedural Replication
• Determining Whether Procedural Replication Is On
• Excluding Objects from Procedural Replication
• Monitoring Oracle GoldenGate Procedural Replication
  • Displaying the Packages Supported for Oracle GoldenGate Procedural Replication
  • Listing the Procedures Supported for Oracle GoldenGate Procedural Replication
  • Displaying Information About Database Objects Excluded from Oracle GoldenGate Procedural Replication

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