Setting Up Oracle GoldenGate Replication: Made Easy 101

Before databases, data was stored in flat files like text or CSV files. The evolution of database technology has significantly transformed data storage.

Oracle is a versatile On-premise Relational Database System capable of handling transactions, data warehouse loads, and mixed loads, with recent offerings including a fully managed service.

Oracle’s querying capabilities make it an excellent analysis tool and a key component in ETL pipelines, supported by applications for data integration, replication, and transformation.

This article covers Oracle GoldenGate Replication, providing insights into Oracle Database, its features, GoldenGate’s features, setup steps, and its limitations.

Prerequisites

The prerequisites for setting up Oracle GoldenGate Replication are as follows:

• Oracle Database.
• Basic knowledge of Oracle.
• Requirement of two different servers on the Oracle database.
• Oracle GoldenGate Installation and configuration step by step.

What is Oracle?

Oracle is one of the most popularly used on-premise relational databases that works exceptionally well with a wide range of workloads. It can easily handle huge volumes of data, large data transactions, and a lot more. It’s one of the most stable and fault-tolerant software with minimal recovery time. Oracle’s robust querying layer, makes it one of the best analysis tools available in the market and an absolute favorite for deploying ETL pipelines.

Oracle provides a complete suite of applications that help organizations integrate data from various sources, and perform data replication, and transformation. Automated Data Pipelines like Hevo Data help to perform Oracle Database Replication in a very smooth and efficient manner.

For further information on Oracle, you can check the official site here.

Key Features of Oracle

Here a some of the key features responsible for the immense popularity of Oracle.

• Cross-Platform Integration: Oracle supports and works on all the available Operating Systems (OS) including Windows, macOS, Linux, etc.
• Analytics Solutions: You can implement OLAP (Oracle Analytic Processing) and Oracle Advanced Analytics to easily solve analytical calculations on business data.
• Compliant to ACID Properties: Oracle DB supports ACID (Atomicity, Consistency, Isolation, and Durability) properties in order to maintain the integrity of the Database during the transaction processes.
• Easy communication with applications across different platforms: You can seamlessly integrate your Oracle DB with applications across various platforms via its native networking stack. For instance, you can easily integrate and interact with an application (running on Unix) to your Oracle (running on Windows).
• Backup and Recovery: Oracle is capable of recovering data from any kind of crash or technical failure. Designed as RAC, Oracle creates a backup for all data and processes.

What is GoldenGate?

GoldenGate was acquired by Oracle in 2009. It is software that can replicate data from one database to another. It is capable of exchanging and manipulating data at the transaction level among several heterogeneous platforms. It runs on the operating system and is independent of the database.

The target database acts as your backup. GoldenGate is also used for live reporting and offers sub-second latency. Some of the supported databases are Oracle, MySQL, DB2, Microsoft SQL Server, etc. The modular architecture of GoldenGate provides flexibility to replicate data with ease. Some of the topologies supported by GoldenGate are unidirectional, bidirectional, peer-to-peer, etc. 

To learn more about GoldenGate, visit here.

Key Features of GoldenGate

• Latency Reduction: In GoldenGate, Data movement across multiple systems or databases is in real-time which helps in reducing latency.
• Support of Databases: Oracle Database versions and releases are supported, as well as a wide range of heterogeneous databases running on a variety of operating systems. Data from an Oracle database can be replicated in a heterogeneous database.
• Simplicity: GoldenGate has a simple architecture and easy configuration.
• Minimal Overhead: GoldenGate has a low overhead on the underlying databases and infrastructure, resulting in high performance.

Importance of Oracle GoldenGate Replication

The components in this process of Oracle GoldenGate Replication are manager, extract, data pump, replicate, and trail/extract files.

The Manager starts the GoldenGate process. It must be running on both servers. It also manages old disk space by purging old trail files. The Extract captures all the DML and DDL changes from redo logs. It writes all the changes in the trail file. The Data Pump process is optional. The extracted data in the trail file is copied to the target system.

The Replicat works in the target database. It reads the trail files and applies the changes to the target. The Trail/Extract files were created in the Extract process for consumption by the Data Pump. All the DML and DDL commands in the source database are copied to trail files using Extract. The Data Pump copies these trail files to the target system. Replicat then reads these files and applies changes to the target. 

Steps to Set up Oracle GoldenGate Replication

To achieve Oracle GoldenGate replication, you need to have two servers ready.

Step 1: Login and Enable Log Mode

The steps involved in Logging and enabling Log Mode for setting up the Replication are as follows:

• Log in to your database as sys user.

sys as sysdba

• Execute the following command to enable force logging mode, archive log mode, and supplemental log data. You need supplemental log data to extract committed data from redo logs. The Oracle database consists of redo logs, which are files in a proprietary format. Redo logs contain the logs of all the changes made in the database. 

shutdown
startup mount
alter database archivelog;
alter database open;
select log_mode from v$database;
alter database add supplemental log data(all) columns;
select SUPPLEMENTAL_LOG_DATA_ALL from v$database;
alter database force logging;
alter system switch logfile;
alter database add supplemental log data;

Step 2: Creating User & Granting Permissions

The steps involved in creating user & granting permissions for setting up Oracle GoldenGate Replication are as follows:

• Create a new user and grant necessary permissions to it. 

create user username identified by password;
grant resource, dba, connect to username;

For example:

• Connect to your newly created user, and then create a new table as shown below:

connect username/password;
create table tablename(column1 data type,..);
insert into student values(value1, value2,..);

• Make sure that the following command executes on your command prompt.

lsnrctl status
lsnrctl start

Note: Run all commands on both the servers.

Step 3: Installing Oracle GoldenGate & Granting Permissions

The steps involved in installing Oracle GoldenGate & granting permissions for setting up are as follows:

• Install Oracle GoldenGate.
• After successful installation of Oracle GoldenGate, you will now create a GoldenGate admin user for both the databases and grant the necessary permissions.

Create user ogguser identified by ogguser;
Grant resource, dba, connect to ogguser;

Step 4: Creating GoldenGate Tablespace & using Interpreter

The steps involved in creating GoldenGate Tablespace & using Interpreter for setting up Oracle GoldenGate Replication are as follows:

• Create a GoldenGate tablespace using the following command:

Create tablespace goldengate
datafile ‘goldengate.dbf’
size 100m
autoextend on;
alter user ogguser default tablespace goldengate;

• For the Oracle GoldenGate source, use the GoldenGate command-line interpreter. Execute the following command in the ‘ggsci.exe’:

Edit params ./GLOBALS

Write the following commands in the text file that opens up after executing the above command:

GGSCHEMA ogguser

• Open the command prompt in the folder where GoldenGate is installed and execute the following commands:

sqlplus / as sysdba
@role_setup.sql

Your schema name is ‘ogguser’ while setting up Oracle GoldenGate Replication.

grant GGS_GGSUSER_ROLE to ogguser;

Step 5: Extracting the File

The steps involved in extracting the file for setting up as follows:

• To configure the extract file, you need to execute the following command:

edit params extract

Write the following lines in the text file that opens up:

EXTRACT extract
USERID ogguser, PASSWORD ogguser
EXTTRAIL ./dirdat/ex
CHECKPOINTSECS 1
TABLE ggtest.*;

• Now, you will do the data pump process to isolate the primary extract process from network latency.

edit params pumpora

Add the following lines in the data pump configuration file:

EXTRACT pumpora
PASSTHRU
RMTHOST 192.168.104.34, MGRPORT 7809
RMTTRAIL ./dirdat/RT
CHECKPOINTSECS 1
TABLE ggtest.*;

• Now, you will create a local trail file for the extract. You need to assign a data pump to the extract before the capture process.

ADD EXTRACT extract, TRANLOG, BEGIN now
ADD EXTTRAIL ./dirdat/ex, EXTRACT extract
ADD EXTRACT pumpora, EXTTRAILSOURCE ./dirdat/ex
Add RMTTRAIL ./dirdat/rt, EXTRACT pumpora
START EXTRACT EXTRACT
START EXTRACT PUMPORA

Step 6: Setting Target & Configuring

The steps involved in setting target & configuring for setting up as follows:

• You need to set the target system for setting up Oracle GoldenGate Replication. Add a checkpoint using the following command:

add checkpointtable ogguser.checkpointtable

To config the replicate file, use the following command:

edit params repora

Add the following lines in the text file:

REPLICAT repora
USERID ogguser, PASSWORD ogguser
DISCARDFILE ./dirdsc/replcat1.dsc, PURGE
ASSUMETARGETDEFS
MAP ggtest.*, TARGET ggtest.*;

• Register your process on the target database using the following command:

add replicat repora, EXTTRAIL ./dirdat/rt, checkpointtable ggowner.checkpointtable

Step 7: Adding Data & Commiting Changes

The steps involved in adding & commiting changes for setting up Oracle GoldenGate Replication are as follows:

• Add data in the student table of the test database in server 1 and commit changes as shown below:

insert into student values(3,'xyz');
commit;

Data will be updated on server 2. 

Limitations of Oracle GoldenGate replication

• The memory consumption during the extract process of Oracle GoldenGate Replication is exponentially high. 
• You can face the character set problem while migrating data in Oracle GoldenGate Replication.
• There are numerous bugs in the data dumping process of XML, HDFs, etc. Data corruptions are very common, and so you should write your script for the detection and correction of XML corruption while setting up Oracle GoldenGate Replication.
• It lacks a simple user interface for non-technical users. 

Conclusion

In this blog, you learned about Oracle GoldenGate replication.

It introduces you to the Oracle database, GoldenGate, GoldenGate configuration steps in Oracle, and the Replication process. It also provides information about the steps involved in setting up the Replication and the limitations of Oracle GoldenGate Replication.

This replication provides a backup of your data in case of data loss. Such a solution will require skilled engineers and regular data updates. Furthermore, you will have to deal with huge memory overhead and work with a complex user interface.