Expert Oracle GoldenGate

Expert Oracle GoldenGate

by Ben Prusinski, Steve Phillips, Richard Chung
Released July 2011
Publisher(s): Apress
ISBN: 9781430235668

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Expert Oracle GoldenGate is a hands-on guide to creating and managing complex data replication environments using the latest in database replication technology from Oracle. GoldenGate is the future in replication technology from Oracle, and aims to be best-of-breed. GoldenGate supports homogeneous replication between Oracle databases. It supports heterogeneous replication involving other brands such as Microsoft SQL Server and IBM DB2 Universal Server. GoldenGate is high-speed, bidirectional, highly-parallelized, and makes only a light impact on the performance of databases involved in replication.

The authors share their experience in the form of tutorials on designing and implementing all types of Oracle GoldenGate environments. You'll learn methods for tuning Oracle GoldenGate performance. You'll discover GoldenGate's utility as a migration and ETL tool. You'll learn to configure highly-available environments involving GoldenGate, RAC, and Data Guard. From installation to design to implementation and troubleshooting, Expert Oracle GoldenGate helps you master all aspects of using and applying Oracle GoldenGate as the replication tool of choice in your environment.

  • Explains all aspects of using GoldenGate for replication

  • Covers homogeneous, heterogeneous, and bidirectional replication

  • Shows the use of GoldenGate for data migration and extract, transform, load (ETL)

What you'll learn

  • Install Oracle GoldenGate quickly and painlessly

  • Design single- and bi-directional replication solutions

  • Apply GoldenGate to high-availability solutions

  • Troubleshoot and tune GoldenGate for performance

  • Verify data synchronization using GoldenGate Veridata

  • Manage and monitor GoldenGate environments using GoldenGate Director

Who this book is for

Expert Oracle GoldenGate is for database professionals needing to master the latest in replication technology from Oracle. It is for the Oracle database administrator, technical manager, or Oracle developer requiring a solid base of expertise on to implement or manage replication environments with GoldenGate technology. Expert Oracle GoldenGate is an appropriate and timely reference for all users of the GoldenGate software.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewer
  4. Acknowledgments
  5. 1. Introduction
    1. 1.1. Distributed Processing and Replication
    2. 1.2. Oracle Basic Replication
    3. 1.3. Oracle Advanced Replication
    4. 1.4. Oracle Streams Replication
    5. 1.5. Evolution and Oracle GoldenGate
    6. 1.6. Summary
  6. 2. Installation
    1. 2.1. Downloading the Software
      1. 2.1.1. Downloading from Oracle E-Delivery
      2. 2.1.2. Downloading from OTN
    2. 2.2. Understanding Your Environment
    3. 2.3. Reviewing the Install Instructions
    4. 2.4. Installing Goldengate
      1. 2.4.1. General System Requirements
        1. 2.4.1.1. Database Server Versions
      2. 2.4.2. Memory Requirements
      3. 2.4.3. Disk Space Requirements
      4. 2.4.4. Network Requirements
      5. 2.4.5. Operating System Requirements
      6. 2.4.6. Requirements for Microsoft Windows Clustered Environments
      7. 2.4.7. Installing Goldengate on Windows
      8. 2.4.8. Installing GoldenGate 11g on Linux and UNIX
      9. 2.4.9. Environment Settings for Oracle and Goldengate on Linux and UNIX
      10. 2.4.10. GoldenGate and Oracle RAC Considerations
      11. 2.4.11. Installing Goldengate for Microsoft SQL Server on Windows
      12. 2.4.12. Installing Goldengate for Teradata on Windows and UNIX
        1. 2.4.12.1. System Requirements for Teradata with Oracle GoldenGate
          1. 2.4.12.1.1. Oracle GoldenGate Replication Server
          2. 2.4.12.1.2. Disk Requirements
      13. 2.4.13. Installing Goldengate for Sybase on Windows and UNIX
      14. 2.4.14. Installing GoldenGate for IBM DB2 UDB on Windows and UNIX
    5. 2.5. Installing Oracle GoldenGate Director 11g
      1. 2.5.1. System Requirements
        1. 2.5.1.1. Supported Platforms
        2. 2.5.1.2. Software Requirements
        3. 2.5.1.3. GoldenGate Director Client Installation Requirements
        4. 2.5.1.4. GoldenGate Director Web Client Installation Guidelines
    6. 2.6. Installing Oracle GoldenGate Director Server
      1. 2.6.1. Grant Database Privileges and Credentials to Oracle GoldenGate Director Server Schema
      2. 2.6.2. Install Oracle GoldenGate Director
    7. 2.7. Installing Oracle GoldenGate Veridata
      1. 2.7.1. GoldenGate Veridata Agent System Requirements
      2. 2.7.2. GoldenGate Veridata Agent Disk Requirements
      3. 2.7.3. GoldenGate Veridata Agent Memory Requirements
      4. 2.7.4. GoldenGate Veridata Agent Database Privileges
      5. 2.7.5. GoldenGate Veridata Server System Requirements
        1. 2.7.5.1. Disk Requirements
        2. 2.7.5.2. Memory Requirements
        3. 2.7.5.3. GoldenGate Veridata Server Software Requirements
        4. 2.7.5.4. Database Privileges for GoldenGate Veridata Server
        5. 2.7.5.5. GoldenGate Veridata Web Requirements for Installation
      6. 2.7.6. Install Oracle Goldengate Veridata
    8. 2.8. Summary
  7. 3. Architecture
    1. 3.1. Typical GoldenGate Flow
    2. 3.2. GoldenGate Components
      1. 3.2.1. Source Database
      2. 3.2.2. Capture (Local Extract) Process
      3. 3.2.3. Source Trail
      4. 3.2.4. Data Pump
      5. 3.2.5. Network
      6. 3.2.6. Collector
      7. 3.2.7. Remote Trail
      8. 3.2.8. Delivery (Replicat)
      9. 3.2.9. Target Database
      10. 3.2.10. Manager
    3. 3.3. Topologies and Use Cases
      1. 3.3.1. One-Way Replication
      2. 3.3.2. Bidirectional Replication
      3. 3.3.3. Broadcast Replication
      4. 3.3.4. Integration Replication
    4. 3.4. Tools and Utilities
      1. 3.4.1. GGSCI
      2. 3.4.2. DEFGEN
      3. 3.4.3. Logdump
      4. 3.4.4. Reverse
      5. 3.4.5. Veridata
      6. 3.4.6. Director
    5. 3.5. Summary
  8. 4. Basic Replication
    1. 4.1. Overview
      1. 4.1.1. Prerequisites for Setting Up Replication
      2. 4.1.2. Requirements
      3. 4.1.3. One-Way Replication Topology
      4. 4.1.4. Basic Replication Steps
    2. 4.2. Starting the Extract
      1. 4.2.1. Verifying Database-Level Supplemental Logging
      2. 4.2.2. Enabling Database-Level Supplemental Logging
      3. 4.2.3. Enabling Table-Level Supplemental Logging
      4. 4.2.4. Disabling Triggers and Cascade-Delete Constraints
      5. 4.2.5. Verifying the Manager Status
      6. 4.2.6. Configuring the Local Extract
        1. 4.2.6.1. EXTRACT
        2. 4.2.6.2. SETENV
        3. 4.2.6.3. USERID
        4. 4.2.6.4. EXTTRAIL
        5. 4.2.6.5. TABLE
      7. 4.2.7. Adding the Extract
      8. 4.2.8. Starting and Stopping the Extract
      9. 4.2.9. Verifying the Extract
    3. 4.3. Starting the Data Pump
      1. 4.3.1. Configuring the Data Pump
        1. 4.3.1.1. EXTRACT
        2. 4.3.1.2. PASSTHRU
        3. 4.3.1.3. RMTHOST
        4. 4.3.1.4. RMTTRAIL
        5. 4.3.1.5. TABLE
      2. 4.3.2. Adding the Data Pump
      3. 4.3.3. Starting and Stopping the Data Pump
      4. 4.3.4. Verifying the Data Pump
    4. 4.4. Loading with GoldenGate
      1. 4.4.1. Prerequisites for the GoldenGate Initial Load
      2. 4.4.2. Configuring the Initial-Load Extract
      3. 4.4.3. Adding the Initial-Load Extract
      4. 4.4.4. Configuring the Initial-Load Replicat
        1. 4.4.4.1. REPLICAT
        2. 4.4.4.2. SETENV
        3. 4.4.4.3. USERID
        4. 4.4.4.4. ASSUMETARGETDEFS
        5. 4.4.4.5. MAP
      5. 4.4.5. Adding the Initial-Load Replicat
      6. 4.4.6. Starting the GoldenGate Initial Load
      7. 4.4.7. Verifying the Initial Load
    5. 4.5. Loading with DBMS Utilities
      1. 4.5.1. Prerequisites for Loading with DBMS Utilities
      2. 4.5.2. Steps for Loading with DBMS Utilities
    6. 4.6. Starting the Replicat
      1. 4.6.1. Configuring the Replicat
        1. 4.6.1.1. Replicat
        2. 4.6.1.2. SETENV
        3. 4.6.1.3. USERID
        4. 4.6.1.4. HANDLECOLLISIONS
        5. 4.6.1.5. ASSUMETARGETDEFS
        6. 4.6.1.6. MAP
      2. 4.6.2. Adding the Replicat
      3. 4.6.3. Starting and Stopping the Replicat
      4. 4.6.4. Verifying the Replicat
    7. 4.7. Summary
  9. 5. Advanced Features
    1. 5.1. Enhancing the Replication Configuration
      1. 5.1.1. Enhancing Extract and Replicat Reporting
        1. 5.1.1.1. REPORTCOUNT
        2. 5.1.1.2. REPORT
        3. 5.1.1.3. REPORTROLLOVER
      2. 5.1.2. Reporting Discarded Records
      3. 5.1.3. Purging Old Trail Files
      4. 5.1.4. Adding Automatic Process Startup and Restart
      5. 5.1.5. Adding a Checkpoint Table
    2. 5.2. Making the Replication More Secure
      1. 5.2.1. Encrypting Passwords
      2. 5.2.2. Encrypting the Trail Files
    3. 5.3. Adding Data Filtering and Mapping
      1. 5.3.1. Filtering Tables
      2. 5.3.2. Filtering Columns
      3. 5.3.3. Filtering Rows
      4. 5.3.4. Mapping Columns
        1. 5.3.4.1. Generating a Data Definitions File
      5. 5.3.5. Transforming Columns
    4. 5.4. Oracle-Specific DBMS Configuration Options
      1. 5.4.1. Configuring for Oracle RAC
        1. 5.4.1.1. Synchronizing the Nodes
        2. 5.4.1.2. Connecting to the Database
        3. 5.4.1.3. Defining Threads
      2. 5.4.2. Configuring for Oracle ASM
        1. 5.4.2.1. Specifying the ASM User
        2. 5.4.2.2. Updating the Listener
        3. 5.4.2.3. Updating the TNSNAMES.ORA
      3. 5.4.3. Adding Oracle DDL Replication
    5. 5.5. Adding Bidirectional Replication
      1. 5.5.1. Excluding Transactions for Bidirectional Replication
      2. 5.5.2. Handling Conflict Resolution for Bidirectional Replication
    6. 5.6. Summary
  10. 6. Heterogeneous Replication
    1. 6.1. Microsoft SQL Server to Oracle Replication
      1. 6.1.1. Preparing the Oracle GoldenGate Environments
      2. 6.1.2. Initial Data Load Completion for Oracle GoldenGate Environments
      3. 6.1.3. Source Oracle Database Configuration
        1. 6.1.3.1. Configure Source Definition on Source Oracle Database System
        2. 6.1.3.2. Execute the Source Definition Generator on the Oracle Source System
        3. 6.1.3.3. Transfer the Source Definition File to the Target Microsoft SQL Server System
      4. 6.1.4. Target Microsoft SQL Server Database Configuration
    2. 6.2. Creating Sample Microsoft SQL Server Database
      1. 6.2.1. Configure Change Data Capture on Source
        1. 6.2.1.1. MySQL to Oracle Replication
        2. 6.2.1.2. Teradata to Oracle Replication
        3. 6.2.1.3. Sybase to Oracle Replication
        4. 6.2.1.4. IBM DB2 UDB to Oracle Replication
    3. 6.3. Verifying Operational Readiness
    4. 6.4. Summary
  11. 7. Tuning
    1. 7.1. Tuning Methodology
    2. 7.2. Defining the Performance Requirements
    3. 7.3. Creating a Performance Baseline
    4. 7.4. Evaluating the Current Performance
    5. 7.5. Determining the Problem
    6. 7.6. Designing and Implementing a Solution
      1. 7.6.1. Using Parallel Extracts and Replicats
      2. 7.6.2. Implementing Parallel Extracts and Replicats with Table Filtering
      3. 7.6.3. Implementing Parallel Extracts and Replicats Using Key Ranges
      4. 7.6.4. Using BATCHSQL
      5. 7.6.5. Using GROUPTRANSOPS
      6. 7.6.6. Tuning Disk Storage
      7. 7.6.7. Tuning the Network
      8. 7.6.8. Tuning the RMTHOST Parameter
      9. 7.6.9. Tuning the Database
    7. 7.7. Summary
  12. 8. Monitoring Oracle GoldenGate
    1. 8.1. Designing a Monitoring Strategy
      1. 8.1.1. Why Monitoring the Extract Is Important
      2. 8.1.2. Getting the Maximum Threshold
    2. 8.2. Which Processes to Monitor in the GoldenGate Environment
      1. 8.2.1. Monitoring All Running Processes
      2. 8.2.2. Monitoring the Detail Extract
      3. 8.2.3. Checking the Current Oracle Online Redo Log
      4. 8.2.4. Checking the Archived Log
      5. 8.2.5. Monitoring the GoldenGate Rate and Redo Log Consumption Rate
      6. 8.2.6. Monitoring Lags for Each Group
        1. 8.2.6.1. Using getlag
        2. 8.2.6.2. Using Write Checkpoint
        3. 8.2.6.3. Using the Trail File Generation Rate
        4. 8.2.6.4. Recording the Lag Status in the ggserr.log File
      7. 8.2.7. Viewing Event and Error Logs
    3. 8.3. Automating Monitoring
      1. 8.3.1. Checking GoldenGate Process Scripts
      2. 8.3.2. Monitoring Lag Scripts
      3. 8.3.3. Checking Memory and CPU Scripts
      4. 8.3.4. Checking Disk Space
    4. 8.4. Summary
  13. 9. Oracle GoldenGate Veridata
    1. 9.1. Veridata Components
      1. 9.1.1. GoldenGate Veridata Server
      2. 9.1.2. GoldenGate Veridata Web
      3. 9.1.3. GoldenGate Veridata Repository
      4. 9.1.4. GoldenGate Veridata Agent, Java, and C-Code
      5. 9.1.5. GoldenGate Veridata CLI (Vericom)
    2. 9.2. How Veridata Comparison Works
    3. 9.3. How Veridata Can Help You
    4. 9.4. Setting Up the Veridata Compares
      1. 9.4.1. Creating Database Connections
      2. 9.4.2. Setting Up Tables and Data Scripts.
      3. 9.4.3. Creating a Group
      4. 9.4.4. Creating Compare Pairs
      5. 9.4.5. Creating a Job
      6. 9.4.6. Creating a Profile
      7. 9.4.7. Running the Veridata Job
    5. 9.5. Improving Performance and Reducing Overhead
      1. 9.5.1. Excluding Columns
      2. 9.5.2. Tuning Profiles Settings
      3. 9.5.3. Disabling the Confirm Out of Sync Step
      4. 9.5.4. Increasing the Number of Threads
      5. 9.5.5. Compares Methods
      6. 9.5.6. Right Trim on Character Fields
      7. 9.5.7. Comparing Incremental Data for Large Tables
    6. 9.6. Comparing GoldenGate Real-Time Replication Data
    7. 9.7. Comparing Different Column Types and Compare Formats
    8. 9.8. Using Performance Statistics
    9. 9.9. Using Vericom Command Line
    10. 9.10. Setting Up Role-Based Security
    11. 9.11. Summary
  14. 10. GoldenGate Director
    1. 10.1. Director Components
      1. 10.1.1. GoldenGate GGSCI Instances
      2. 10.1.2. Director Administrator
      3. 10.1.3. Director Server and Database
      4. 10.1.4. Director Web
      5. 10.1.5. Director Client
    2. 10.2. Setting Up the Data Source
    3. 10.3. Modifying the Manager Parameter file
      1. 10.3.1. Modifying the Parameter File Using the Built-in Editor
      2. 10.3.2. Modifying the Parameter File Using the GUI
    4. 10.4. Setting Up the Initial Load
      1. 10.4.1. Adding an Initial Load Task
    5. 10.5. Creating a One-Way Replication
    6. 10.6. Adding a Data Pump Process
    7. 10.7. Additional Director Features and Tricks
      1. 10.7.1. Alter Extract or Replicat RUN Options
      2. 10.7.2. Changing the Trail-File Size
      3. 10.7.3. Extracting Tranlogoptions
      4. 10.7.4. Generating Definition Files
      5. 10.7.5. Finding Parameters or Attributes in Director
      6. 10.7.6. Advanced Mapping
      7. 10.7.7. Alerts
    8. 10.8. Summary
  15. 11. Troubleshooting Oracle GoldenGate
    1. 11.1. Common Problems and Solutions
    2. 11.2. Oracle GoldenGate Process Failures
      1. 11.2.1. Oracle GoldenGate Extract Process Failures
      2. 11.2.2. Oracle GoldenGate Process Failures Without Report Diagnostics
    3. 11.3. Oracle GoldenGate Trail File Problems
      1. 11.3.1. Trail Files that Don't Empty
      2. 11.3.2. Trail Files that Don't Roll Over
      3. 11.3.3. Trail File Purging Issues
      4. 11.3.4. Trail Files that Purge Too Soon
    4. 11.4. Oracle GoldenGate Error Log Analysis
    5. 11.5. Understanding the Oracle GoldenGate Discard File
      1. 11.5.1. Discard File Not Created
      2. 11.5.2. Discard File Is Too Big
      3. 11.5.3. Can't Open the Discard File
    6. 11.6. Using Trace Commands with Oracle GoldenGate
      1. 11.6.1. Oracle GoldenGate Process Tracing with TLTRACE
      2. 11.6.2. Using TRACE Parameters with Oracle GoldenGate
      3. 11.6.3. Troubleshooting Case Study with Oracle GoldenGate
    7. 11.7. Oracle GoldenGate Configuration Issues
      1. 11.7.1. Incorrect Software Versions with Oracle GoldenGate
      2. 11.7.2. Database Availability Issues
      3. 11.7.3. Missing Oracle GoldenGate Process Groups
      4. 11.7.4. Missing Oracle GoldenGate Trail Files
      5. 11.7.5. Oracle GoldenGate Parameter File Configuration Issues
      6. 11.7.6. Operating System Configuration Issues with Oracle GoldenGate
      7. 11.7.7. Network Configuration Issues with Oracle GoldenGate
      8. 11.7.8. Network Data-Transfer Issues
    8. 11.8. Oracle Database Issues with GoldenGate
      1. 11.8.1. Extract Can't Access Oracle Database Archive and Redo Logs
      2. 11.8.2. Extract Failure Conditions Due to Oracle Source Database Issues
      3. 11.8.3. Data-Pump Errors
      4. 11.8.4. Replicat Errors on the Oracle Database Target System
      5. 11.8.5. Replicat Hangs on the Target System
      6. 11.8.6. Replicat Experiences an Abend Failure on the Target System
      7. 11.8.7. Replicat Fails on Large Transactions
      8. 11.8.8. Incompatible Record Errors with a Replicat
      9. 11.8.9. Data-Synchronization Issues
      10. 11.8.10. Tables Missing Key Constraints
      11. 11.8.11. Character Set Configuration Issues
      12. 11.8.12. Missing-Column Errors
      13. 11.8.13. Fetch Failures
    9. 11.9. Summary
  16. 12. Disaster Recovery Replication
    1. 12.1. Prerequisites
    2. 12.2. Requirements
    3. 12.3. Disaster Recovery Replication Topology
    4. 12.4. Setup
      1. 12.4.1. Configuring the Local Extract for Disaster Recovery
      2. 12.4.2. Configuring the Data Pump for Disaster Recovery
      3. 12.4.3. Configuring the Replicat for Disaster Recovery
      4. 12.4.4. Configuring the Standby Extract
      5. 12.4.5. Configuring the Standby Data Pump
      6. 12.4.6. Configuring the Standby Replicat
    5. 12.5. Performing a Planned Switchover
    6. 12.6. Performing an Unplanned Failover
    7. 12.7. Summary
  17. 13. Zero-Downtime Migration Replication
    1. 13.1. Prerequisites
    2. 13.2. Requirements
    3. 13.3. Zero-Downtime Migration Topology
    4. 13.4. Setup
      1. 13.4.1. Configuring the Local Extract for Zero-Downtime Migration
      2. 13.4.2. Configuring the Data Pump for Zero-Downtime Migration
      3. 13.4.3. Configuring the Replicat for Zero-Downtime Migration
      4. 13.4.4. Configuring the Fallback Local Extract for Zero-Downtime Migration
      5. 13.4.5. Configuring the Fallback Data-Pump for Zero-Downtime Migration
      6. 13.4.6. Configuring the Fallback Replicat for Zero-Downtime Migration
    5. 13.5. Performing the Migration Cutover
    6. 13.6. Performing the Migration Fallback
    7. 13.7. Summary
  18. 14. Tips and Tricks
    1. 14.1. Requirements and Planning
      1. 14.1.1. Knowing the Business Objectives
      2. 14.1.2. Understanding the Requirements
      3. 14.1.3. Determining the Topology
    2. 14.2. Installation and Setup
      1. 14.2.1. Creating Dedicated Users
      2. 14.2.2. Encrypting Passwords
      3. 14.2.3. Creating a Dedicated Installation Directory
      4. 14.2.4. Using a Checkpoint Table
      5. 14.2.5. Verifying the Character Sets
      6. 14.2.6. Developing Naming Standards
      7. 14.2.7. Using a Data Pump
    3. 14.3. Management and Monitoring
      1. 14.3.1. Using GGSCI Command Shortcuts
      2. 14.3.2. Using OBEY Files
      3. 14.3.3. Generating Interim Statistics
      4. 14.3.4. Using a Discard File
      5. 14.3.5. Reporting Regularly on Process Health
      6. 14.3.6. Purging Old Trail Files Regularly
      7. 14.3.7. Automatically Starting Processes
    4. 14.4. Performance
      1. 14.4.1. Running Performance Tests
      2. 14.4.2. Limiting the Number of Extracts
      3. 14.4.3. Using Passthru Mode for Data Pumps
      4. 14.4.4. Using Parallel Replicats
      5. 14.4.5. Using the Fastest Available Storage
      6. 14.4.6. Tuning the Database
    5. 14.5. Summary
  19. A. Additional Technical Resources for the Oracle GoldenGate Administrator
    1. A.1. References for Further Reading
    2. A.2. Quick Guide to Oracle GoldenGate Commands
      1. A.2.1. ADD
      2. A.2.2. GGSCI
      3. A.2.3. HELP
      4. A.2.4. INFO
      5. A.2.5. SEND
      6. A.2.6. STATUS
    3. A.3. Logdump Commands and Syntax for Troubleshooting
      1. A.3.1. Accessing the Logdump Utility
      2. A.3.2. Getting Help with Logdump Syntax
      3. A.3.3. HISTORY
      4. A.3.4. Opening GoldenGate Trail Files with Logdump

Product information

  • Title: Expert Oracle GoldenGate
  • Author(s): Ben Prusinski, Steve Phillips, Richard Chung
  • Release date: July 2011
  • Publisher(s): Apress
  • ISBN: 9781430235668