1/37

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Information
• Conventions

Part I What is Oracle GoldenGate for Heterogeneous Databases?

Part II Using Oracle GoldenGate with DB2 LUW

1 Understanding What's Supported for DB2 LUW

• 1.1 Supported DB2 LUW Data Types
• 1.2 Non-Supported DB2 LUW Data Types
• 1.3 Supported Objects and Operations for DB2 LUW
• 1.4 Non-Supported Objects and Operations for DB2 LUW
• 1.5 Supported Object Names

2 Preparing the System for Oracle GoldenGate

• 2.1 Configuring the Transaction Logs for Oracle GoldenGate
  • 2.1.1 Retaining the Transaction Logs
  • 2.1.2 Specifying the Archive Path
• 2.2 Preparing Tables for Processing
  • 2.2.1 Disabling Triggers and Cascade Constraints
  • 2.2.2 Assigning Row Identifiers
    • 2.2.2.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use
    • 2.2.2.2 Using KEYCOLS to Specify a Custom Key
  • 2.2.3 Preventing Key Changes
  • 2.2.4 Enabling Change Capture
  • 2.2.5 Maintaining Materialized Query Tables
• 2.3 Setting the Session Character Set
• 2.4 Preparing for Initial Extraction
• 2.5 Specifying the DB2 LUW Database in Parameter Files

3 Configuring Oracle GoldenGate for DB2 LUW

• 3.1 What to Expect from these Instructions
• 3.2 Where to Get More Information
• 3.3 Configuring the Primary Extract
• 3.4 Configuring the Data Pump Extract
• 3.5 Configuring Replicat
  • 3.5.1 Creating a Temporal Table
    • 3.5.1.1 Support for Temporal Tables
    • 3.5.1.2 Replicating with Temporal Tables
    • 3.5.1.3 Converting
  • 3.5.2 Creating a Checkpoint Table
  • 3.5.3 Configuring the Replicat Parameter File
• 3.6 Next Steps in the Deployment
• 3.7 When to Start Replicating Transactional Changes
• 3.8 Testing Your Configuration

Part III Using Oracle GoldenGate with IBM DB2 for i

4 Understanding What's Supported for IBM DB2 for i

• 4.1 Supported DB2 for i Data Types
• 4.2 Non-Supported DB2 for i Data Types
• 4.3 Supported Objects and Operations for DB2 for i
• 4.4 Non-Supported Objects and Operations for DB2 for i
• 4.5 Oracle GoldenGate Parameters Not Supported for DB2 for i
• 4.6 Supported Object Naming Conventions
• 4.7 Supported Character Sets

5 Preparing the System for Oracle GoldenGate

• 5.1 Preparing the Journals for Data Capture by Extract
  • 5.1.1 Allocating Journals to an Extract Group
  • 5.1.2 Setting Journal Parameters
  • 5.1.3 Deleting Old Journal Receivers
• 5.2 Specifying Object Names
• 5.3 Preparing Tables for Processing
  • 5.3.1 Assigning Row Identifiers
    • 5.3.1.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use
    • 5.3.1.2 Using KEYCOLS to Specify a Custom Key
  • 5.3.2 Preventing Key Changes
  • 5.3.3 Disabling Constraints on the Target
  • 5.3.4 Enabling Change Capture
    • 5.3.4.1 Specifying a Default Journal
    • 5.3.4.2 Removing a Default Journal Specification
  • 5.3.5 Maintaining Materialized Query Tables
  • 5.3.6 Specifying the Oracle GoldenGate Library
• 5.4 Adjusting the System Clock
• 5.5 Configuring the ODBC Driver
  • 5.5.1 Configuring ODBC on Linux
  • 5.5.2 Configuring ODBC on Windows

6 Configuring Oracle GoldenGate for DB2 for i

• 6.1 What to Expect from this Procedure
• 6.2 Getting Started with Oracle GoldenGate
• 6.3 Creating the Oracle GoldenGate Instance
• 6.4 Creating a GLOBALS File
• 6.5 Creating a Data Definitions File
• 6.6 Encrypting the Extract and Replicat Passwords
• 6.7 Configuring Extract for Change Capture from DB2 for i
  • 6.7.1 Configuring the Primary Extract
  • 6.7.2 Configuring the Data Pump
• 6.8 Configuring Replicat for Change Delivery to DB2 for i
  • 6.8.1 Creating a Checkpoint Table
  • 6.8.2 Configuring Replicat
• 6.9 Next Steps in the Deployment
• 6.10 When to Start Replicating Transactional Changes
  • 6.10.1 Starting Extract During Instantiation
  • 6.10.2 Changing the Position of Extract to a Later Time
• 6.11 Testing Your Configuration

7 Instantiating and Starting Oracle GoldenGate Replication

• 7.1 About the Instantiation Process
• 7.2 Overview of Basic Oracle GoldenGate Instantiation Steps
• 7.3 Satisfying Prerequisites for Instantiation
  • 7.3.1 Configure Change Capture and Delivery
  • 7.3.2 Add Collision Handling
  • 7.3.3 Prepare the Target Tables
• 7.4 Making the Instantiation Procedure More Efficient
  • 7.4.1 Share Parameters Between Process Groups
  • 7.4.2 Use Parallel Processes
• 7.5 Configuring the Initial Load
  • 7.5.1 Configuring an Initial Load from File to Replicat
  • 7.5.2 Configuring an initial load with a database utility
• 7.6 Adding Change-Capture and Change-Delivery processes
  • 7.6.1 Add the Primary Extract
    • 7.6.1.1 Understanding the Primary Extract Start Point
    • 7.6.1.2 Establishing the Required and Optional Extract Start Points
  • 7.6.2 Add the Local Trail
  • 7.6.3 Add the Data Pump Extract Group
  • 7.6.4 Add the Remote Trail
  • 7.6.5 Add the Replicat Group
• 7.7 Performing the Target Instantiation
  • 7.7.1 To Perform Instantiation from File to Replicat
  • 7.7.2 To Perform Instantiation with a Database Utility
• 7.8 Monitoring Processing after the Instantiation
• 7.9 Backing up Your Oracle GoldenGate Environment
• 7.10 Positioning Extract After Startup

8 Using Remote Journal

• 8.1 Preparing to Use Remote Journals
• 8.2 Adding a Remote Journal
  • 8.2.1 What Happens During Add Remote Journal Processing?
  • 8.2.2 Guidelines For Adding a Remote Journal

Part IV Using Oracle GoldenGate with DB2 for z/OS

9 Understanding What's Supported for DB2 for z/OS

• 9.1 Supported DB2 for z/OS Data Types
• 9.2 Non-Supported DB2 for z/OS Data Types
• 9.3 Supported Objects and Operations for DB2 for z/OS
• 9.4 Non-Supported Objects and Operations for DB2 for z/OS

10 Preparing the DB2 for z/OS Database for Oracle GoldenGate

• 10.1 Preparing Tables for Processing
  • 10.1.1 Disabling Triggers and Cascade Constraints
  • 10.1.2 Assigning Row Identifiers
    • 10.1.2.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use
    • 10.1.2.2 Using KEYCOLS to Specify a Custom Key
  • 10.1.3 Handling ROWID Columns
• 10.2 Configuring a Database Connection
  • 10.2.1 Setting Initialization Parameters
  • 10.2.2 Specifying the Path to the Initialization File
  • 10.2.3 Ensuring ODBC Connection Compatibility
  • 10.2.4 Specifying the Number of Connection Threads
• 10.3 Accessing Load Modules
• 10.4 Specifying Job Names and Owners
• 10.5 Assigning WLM Velocity Goals
• 10.6 Monitoring Processes
  • 10.6.1 Viewing Oracle GoldenGate Messages
  • 10.6.2 Identifying Oracle GoldenGate Processes
  • 10.6.3 Interpreting Statistics for Update Operations
• 10.7 Supporting Globalization Functions
  • 10.7.1 Replicating From a Source that Contains Both ASCII and EBCDIC
  • 10.7.2 Specifying Multi-Byte Characters in Object Names

11 Preparing the DB2 for z/OS Transaction Logs for Oracle GoldenGate

• 11.1 Making Transaction Data Available
  • 11.1.1 Enabling Change Capture
  • 11.1.2 Enabling Access to Log Records
  • 11.1.3 Sizing and Retaining the Logs
  • 11.1.4 Using Archive Logs on Tape
  • 11.1.5 Controlling Log Flushes

Part V Using Oracle GoldenGate with MySQL

12 Understanding What's Supported for MySQL

• 12.1 Character Sets in MySQL
• 12.2 Supported MySQL Data Types
  • 12.2.1 Limitations and Clarifications
• 12.3 Supported Objects and Operations for MySQL
• 12.4 Non-Supported MySQL Data Types

13 Preparing and Configuring Your System for Oracle GoldenGate

• 13.1 Ensuring Data Availability
• 13.2 Setting Logging Parameters
• 13.3 Adding Host Names
• 13.4 Setting the Session Character Set
• 13.5 Preparing Tables for Processing
  • 13.5.1 Assigning Row Identifiers
    • 13.5.1.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use
    • 13.5.1.2 Tables with a Primary Key Derived from a Unique Index
    • 13.5.1.3 How to Specify Your Own Key for Oracle GoldenGate to Use
  • 13.5.2 Limiting Row Changes in Tables That Do Not Have a Key
  • 13.5.3 Disabling Triggers and Cascade Constraints
• 13.6 Changing the Log-Bin Location
• 13.7 Configuring Bi-Directional Replication
• 13.8 Capturing using a MySQL Replication Slave
• 13.9 Establishing a Secure Database Connection to AWS Aurora MySQL
• 13.10 Other Oracle GoldenGate Parameters for MySQL
• 13.11 Positioning Extract to a Specific Start Point

14 Using DDL Replication

• 14.1 DDL Configuration Prerequisites and Considerations
• 14.2 Installing DDL Replication
• 14.3 Using the Metadata Server
• 14.4 Using DDL Filtering for Replication
• 14.5 Troubleshooting DDL Replication
• 14.6 Uninstalling DDL Replication

Part VI Using Oracle GoldenGate with SQL Server

15 Understanding What's Supported for SQL Server

• 15.1 Supported SQL Server Data Types
• 15.2 Non-Supported SQL Server Data Types and Features
• 15.3 Supported Objects and Operations for SQL Server
• 15.4 Non-Supported Objects and Operations for SQL Server

16 Preparing the System for Oracle GoldenGate

• 16.1 Configuring a Database Connection
  • 16.1.1 Configuring an Extract Database Connection
  • 16.1.2 Configuring a Replicat Database Connection
    • 16.1.2.1 Using ODBC or Default OLE DB
    • 16.1.2.2 Using OLE DB with USEREPLICATIONUSER
  • 16.1.3 Configuring an ODBC Connection
• 16.2 Preparing Tables for Processing
  • 16.2.1 Disabling Triggers and Cascade Constraints on the Target
  • 16.2.2 Assigning Row Identifiers
    • 16.2.2.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use
    • 16.2.2.2 Using KEYCOLS to Specify a Custom Key
  • 16.2.3 Improving IDENTITY Replication with Array Processing
• 16.3 Globalization Support

17 Preparing the Database for Oracle GoldenGate — Classic Capture

• 17.1 Setting the Database to Full Recovery Model
• 17.2 Backing Up the Transaction Log
• 17.3 Enabling Supplemental Logging
  • 17.3.1 To Enable Supplemental Logging
• 17.4 Managing the Secondary Truncation Point
  • 17.4.1 Oracle GoldenGate Manages the Secondary Truncation Point
  • 17.4.2 SQL Server Manages the Secondary Truncation Point
• 17.5 Retaining the Log Backups and Backup History

18 Preparing the Database for Oracle GoldenGate — CDC Capture

• 18.1 Enabling CDC Supplemental Logging
• 18.2 Retaining the CDC Table History Data
• 18.3 Enabling Bi-Directional Loop Detection

19 Requirements Summary for Classic Extract in Archived Log Only (ALO) Mode

• 19.1 Windows OS Requirements
• 19.2 Transaction Log Backups
• 19.3 ODBC Connection
• 19.4 Supplemental Logging
• 19.5 Operational Requirements and Considerations

20 Requirements Summary for Capture and Delivery of Databases in an AlwaysOn Availability Group

• 20.1 ODBC Connection
• 20.2 Supplemental Logging
• 20.3 Operational Requirements and Considerations

21 Oracle GoldenGate Classic Extract for SQL Server Standard Edition Capture

• 21.1 Overview
• 21.2 SQL Server Instance Requirements
• 21.3 Table Requirements
• 21.4 Supplemental Logging
• 21.5 Operational Requirements and Considerations

22 CDC Capture Method Operational Considerations

• 22.1 Tuning the SQL Server Change Data Capture Job
• 22.2 Valid and Invalid Parameters for SQL Server Change Data Capture
• 22.3 Details of the Oracle GoldenGate CDC Cleanup Process
• 22.4 Changing from Classic Extract to a CDC Extract

Part VII Using Oracle GoldenGate with Teradata

• Overview of Oracle GoldenGate for Teradata

• 23 Understanding What's Supported for Teradata

  • 23.1 Supported Teradata Data Types
    • 23.1.1 Limitations of Support for Numeric Data Types
    • 23.1.2 Limitations of Support for Single-byte Character Data Types
    • 23.1.3 Conditions and Limitations of Support for Multi-byte Character Data
    • 23.1.4 Limitations of Support for Binary Data Types
    • 23.1.5 Limitations of Support for Large Object Data Types
    • 23.1.6 Limitations of Support for Date Data Types
    • 23.1.7 Limitations of Support for IDENTITY Data Types
  • 23.2 Supported Objects and Operations for Teradata
  • 23.3 Non-Supported Operations for Teradata

  24 Preparing the System for Oracle GoldenGate

  • 24.1 Preparing Tables for Processing
    • 24.1.1 Disabling Triggers and Cascade Constraints
    • 24.1.2 Assigning Row Identifiers
      • 24.1.2.1 How Oracle GoldenGate Determines the Kind of Row Identifier to Use
      • 24.1.2.2 Using KEYCOLS to Specify a Custom Key

  25 Configuring Oracle GoldenGate

  • 25.1 Configuring Oracle GoldenGate Replicat
  • 25.2 Additional Oracle GoldenGate Configuration Guidelines
    • 25.2.1 Handling Massive Update and Delete Operations
    • 25.2.2 Preventing Multiple Connections
    • 25.2.3 Performing Initial Synchronization

  26 Common Maintenance Tasks

  • 26.1 Modifying Columns of a Table