Setting Up Oracle GoldenGate Replication: Made Easy 101

Before the database system was invented, data was commonly stored in flat files like text files or CSV files. Database technology is evolving rapidly and new innovations in this field are becoming quite common. Nowadays, many databases have come up that have changed the field of data storage to a great extent. One such popular database system is Oracle.

Oracle is a popular On-premise Relational Database System suited for a wide range of workloads. It can handle transactions, data warehouse loads, and mixed loads. Even though it is designed as an on-premise solution, a recent offering from Oracle also includes a completely managed service. Oracle’s comprehensive querying layer makes it a great analysis tool and a favorite in ETL pipelines. It also comes with a suite of applications to help organizations in data integration, replication, and data transformation. One such application of Oracle replication is Oracle GoldenGate Replication.

In this article, you will gain information about Oracle GoldenGate Replication, but not by using Hevo. Hevo currently doesn’t connect with GoldenGate to sync data. You will also gain a holistic understanding of Oracle Database, its key features, GoldenGate, its key features, steps to set up Oracle GoldenGate Replication, and Limitations of Oracle GoldenGate Replication. Read along to find out in-depth information about Oracle GoldenGate Replication.

Table of Contents

• What is Oracle?
• What is GoldenGate?
• Importance of Oracle GoldenGate Replication
• Steps to Set Up Oracle GoldenGate Replication
  • Step 1: Log in and Enable Log Mode
  • Step 2: Creating User & Granting Permissions
  • Step 3: Installing Oracle GoldenGate & Granting Permissions
  • Step 4: Creating GoldenGate Tablespace & using Interpreter
  • Step 5: Extracting the File
  • Step 6: Setting Target & Configuring
  • Step 7: Adding Data & Commiting Changes
• Limitations of Oracle GoldenGate Replication
• Conclusion

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.
• Consistency & Performance: Oracle GoldenGate Replication only moves committed transactions. It keeps the operations of each transaction in a controlled virtual-memory pool called a cache until the transaction is committed or rolled back. The Oracle GoldenGate cache manager makes use of the operating system’s memory management functions to ensure consistency and improve performance.
• 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

To understand Oracle GoldenGate Replication, you need to understand the various components involved in it. 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.

You can follow these steps for setting up Oracle GoldenGate Replication:

• Step 1: Log in and Enable Log Mode
• Step 2: Creating User & Granting Permissions
• Step 3: Installing Oracle GoldenGate & Granting Permissions
• Step 4: Creating GoldenGate Tablespace & using Interpreter
• Step 5: Extracting the File
• Step 6: Setting Target & Configuring
• Step 7: Adding Data & Commiting Changes

Step 1: Login and Enable Log Mode

The steps involved in Logging and enabling Log Mode for setting up Oracle GoldenGate 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 Oracle GoldenGate Replication 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 Oracle GoldenGate Replication are 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 Oracle GoldenGate Replication are 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

Some of the limitations of using Oracle GoldenGate Replication are as follows:

• 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.
• Oracle GoldenGate replication 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 Oracle GoldenGate Replication process. It also provides information about the steps involved in setting up Oracle GoldenGate 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.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 150+ Data Sources (including 40+ Free Sources) such as Oracle allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing Hevo Price, which will assist you in selecting the best plan for your requirements.

Share your experience of Oracle GoldenGate Replication in the comment section below.