Oracle Golden Gate Initial Load Example Step by Step Guide

YouTube video

Introduction

In this article, we will discuss the step-by-step process of performing an initial load using Oracle Golden Gate. We will configure the initial load for a single table and set up chain synchronization. This guide is aimed at beginners who want to understand the process of configuring replication using Golden Gate. Let’s dive in!

Table Setup

To begin with, we have two databases – prod and FD. For demonstration purposes, we will use different schemas: Fox schema on the source (prod) and Tom schema on the target (FD). The source table is called “EMP” in the “Scott” schema, and the target table is also named “EMP”.

Step 1: Create Blank Table on Target

The first step is to create a blank table on the target database. For this, we execute the following command on the target server:

sql
CREATE TABLE Tom.EMP;

Step 2: Configure Golden Gate Initial Load

Next, we need to configure the Golden Gate initial load. This involves populating the target table with data from the source table. To do this, follow these steps:

  1. Connect to the source database.
  2. Create the “EMP” table in the Fox schema, using the data from the “Scott.EMP” table:

sql
CREATE TABLE Fox.EMP AS SELECT * FROM Scott.EMP;

  1. Add a primary key to the newly created “EMP” table:

sql
ALTER TABLE Fox.EMP ADD PRIMARY KEY (EMP_NUMBER);

  1. Verify the data in the “EMP” table:

sql
SELECT * FROM Fox.EMP;

  1. Generate the DDL command for creating the “EMP” table on the target:

sql
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'Fox') FROM dual;

  1. Copy the generated DDL command and replace the schema name from “Fox” to “Tom” in the command.

  2. Execute the modified DDL command on the target database to create the table.

Step 3: Configure Change Synchronization

Now that we have the target table ready, we can configure the change synchronization using a normal extract and replicate. Here are the steps:

  1. Connect to the Golden Gate home directory on the source server.
  2. Start the Golden Gate Manager process:

shell
./mgr

  1. Connect to the Manager process:

shell
./ggsci

  1. Configure the extract process for the source table:

shell
DBLOGIN USERID source_user PASSWD source_password
ADD EXTRACT ext1, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/lt, EXTRACT ext1
ADD RMTTRAIL ./dirdat/rt, EXTRACT ext1
ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW, SQLPRED "TABLE_NAME = 'EMP'"

  1. Configure the replicate process for the target table:

shell
DBLOGIN USERID target_user PASSWD target_password
ADD REPLICAT rep1, EXTTRAIL ./dirdat/lt, BEGIN NOW

  1. Start the extract and replicate processes:

shell
START EXTRACT ext1
START REPLICAT rep1

Step 4: Sync and Delete Initial Load

Once the initial load is complete, we can sync and delete the initial load files. Here’s how:

  1. Wait until the initial load is completed.

  2. Check the progress of the initial load:

shell
./ggsci
INFO EXTRACT ext1, SHOWTRANS

  1. Once the initial load is complete, start the replicate process:

shell
START REPLICAT rep1

  1. Validate the data on the target table:

sql
SELECT * FROM Tom.EMP;

  1. Delete the initial load files:

shell
DELETE EXTRACT ext1
DELETE REPLICAT rep1

Conclusion

In this article, we have discussed how to perform an initial load using Oracle Golden Gate. We covered the step-by-step process of configuring the initial load and chain synchronization for a single table. Following these steps will help you configure your own replication setup using Golden Gate. Remember, this guide provides a simplified example, and real-world scenarios may involve more complex configurations. Happy replicating!