About Oracle GoldenGate Fundamentals

Introduction to GoldenGate

Replication 

 GoldenGate

(Source Database : Oracle)

Environments : heterogeneous

(Target Database: MySQL,SQL Server, DB2)

Data Guard

GoldenGate is a tool primarily used for replicating data between databases.

Its major advantage is the ability to replicate across heterogeneous databases

Key Capabilities of Oracle GoldenGate

• Real-Time replication
• Replication between heterogeneous(异构) databases
• Maintains Transaction integrity(事务完整性)

Benefits of Oracle GoldenGate

• Implement High Availability Solution

• Perform Zero Downtime Upgrades/Migration

• Provide for a Reporting Instance

•  Data integration(Transformations, Scalable Solutions) -Transformation/ETL

GoldenGate Deployment Topologies

  

Deployment Topologies

• Unidirectional replication
• Bidirectional replication
• Broadcast to multiple targets
• Integration of multiple Sources
• Cascaded replication

How does GoldenGate get the Transactions from Source Databases

• Database Redo / Transaction Logs
•  Maintains transactions in the order they were executed in the database
• Source for GoldenGate Extract

GoldenGate Processes Overview

 

GoldenGate Processes

• Extract - Reads from the Source DB Redo logs and writes to a local or Remote Trail
• Pump - Reads from a local trail and sends the trails file contents to a remote host
• Replicat - Reads a trail and performs / applies changes in the target database

Overview of Oracle GoldenGate Initial Load and Change Capture and Apply

Initial Load required to SETUP Target Database

Can be done using database utilities when source and target are homogeneous

- Can use GoldenGate if source and target are 

 

 

 Oracle GoldenGate Trails and Files 

• Trail files are created by Extract / Extract Pump
• Can create output as 

1.  Flat File
2. DB Load Utility (SQL Loader, BCP etc)
3. -GoldenGate trail file

           - Only format that a Replicat can use

Checkpointing in Oracle GoldenGate

 GoldenGate Extract

Always writes only COMMITED transactions to the Trail

Checkpoint Table - only for Replicat

As transactions are populated in the Target database, the Checkpoint Information is also in the Database and commited along with the transaction data.

Checkpoint is an activity used to synchronize

Checkpointing is used to ensure

- Data Consistency

- Transaction ordering

- Recovery of processes

Oracle GoldenGate Configuration Steps

• Setup Environment
• Identify Changes to be Captured (configure CAPTURE/EXTRACT)
• How to do Initial Load(Initial SYNC/LOAD)
• How to deliver the changes captured (Configure DELIVERY/REPLICAT)

Setup GoldenGate Environment

• Database User for GoldenGate processes

1.  For Extract on Source DB to get Metadata
2. For Replicat on Target DB  to do DMLs            

• To be able to identifiy the row in the Target DB

1. Enable Supplmental Logging in Source DB
2. TRANDATA for tables to be extracted from

• Configure Manager process
• Create a SourceDef/TargetDef file

Setup GoldenGate for Replication

Setup GoldenGate environment 

• A Database User for GoldenGate processes

1. For Extract on Source DB to get Metadata
2. For Replicat on Target DB to do DMLs

• To be able to identify the row in the Target DB

1. Enable Supplmental Logging in Source DB
2. TRANDATA for tables to be extracted from

• Configure Manager process
• Create a SourceDef/TargetDef file

Oracle GoldenGate Parameter Files and Manager Process

manager Process is part of every GoldenGate instance /Installation

It manages(Start/ Stop etc) the GoldenGate processes

GLOBALS parameter file - Default settings for all processes

Parameter files for every process

- Extract / Pump / Replicat

Processes create Logfiles and Discard Files

Initial Configuration on GoldenGate for Replication

Change Capture Flow

Extract reads from Redo Logs and can write to

- Local Trail

- Remote Trail(Over network to target host)

Best Practice use Local Trail with Pump for Remote Trail

Extract Trail has only Committed Transactions

Pump can send same trail to Multiple destinations

- Best practice is to create separate pump for each destination

GoldenGate Change Capture Flow (Extract Deployment Options)

Extract reads from Redo Logs and can write to 

- Local Trail

- Remote Trail(Over network to target host)

Best Practice use Local Trail with Pump for Remote Trail

Extract Trail has only Committed Transactions

Pump can send same trail to Multiple destinations 

- Best practice is to create separate pump for each destination

Step to Create an Extract in Oracle GoldenGate 

Steps to Create an Extract

- Create Parameter file for Extract

- Add Extract using GGSCI

- Add Extract trail(Local or Remote)

- Start the Extract

Oracle GoldenGate Extract and Pump Extract Configuration - Demonstration

Oracle GoldenGate Initial Load Methods

1. Source ->Extract -> Trail -> Replicat -> Target

2. Source -> Extract -> DBUtilFmt ->DBUtil ->Target

3.Source -> Extract -> Replicat -> Target

4.Source -> Extract ->BulkLoad/DbUtil->Target

Use SOURCEISTABLE in Extract definition for Initial Load

 

 Pre-requisites for Initial Load with Oracle GoldenGate

Hanlde Collisions if Source database is active(DMLs occur) when initial load is being run

Steps to Configure Replicat in Oracle GoldenGate

Configure Change Delivery

• prepared the Environment
• Configure Capture Delivery
• Replicat reads from a GoldenGate Trail
• Creating a Replicat

        - Parameter File for Replicat

        - Add the Replicat(specify the Trail source)

         - Start the Replicat

Parallelise Replication(Extract/Replicat) in Oracle GoldenGate

 Parallelise Replication

- Create separate Extract-Replicat for different set of Tables

- Create multiple Extract-Replicat for the same set of Tables(using RANGE function)

 

Collisions in Oracle GoldenGate

 

 

 Collisions happen when Initial Load runs along with a Live Source Database

Avoid Collisions by stopping the Transactions on the Source.

If you can't avoid Collisions, then you need to Handle them.

if downtime is not possible , Transactions continue to happen on Source during Initial Load

Use HANDLECOLLISIONS in Replicat to manage Collision Transactions (No need to apply a transaction which is already in the Initial Load)

It is set only for the duration of Initial Load

Can eb dynamically set to NOHANDLECOLLISIONS in Replicat.

Oview of Bidirectional Replication using Oracle GoldenGate

 

 In Bidirectional Replication

- DMLs occur on both databases

- Replication happens in both directions

Need to take care of 

- Loop Detection(EXCLUDEUSER)

- Conflict Avoidance or 

- Conclict Detection and Resolution

- Cant replicate Sequences

- Need special handling for Truncate statements(EXCLUDEUSER cannot exclude TRUNCATE)