Oracle GoldenGate Replication: A Step-by-Step Guide

Moving data across databases from different locations in real-time for analysis has become a necessary process for modern organizations. Being able to store, process, and make the most out of data produced daily has become a useful tool. This ability helps to drive efficient decision-making for the business and improves overall productivity across the enterprise.

Oracle Database is one of the most popular databases around to collect, organize, and store data. It is an efficient product used by many companies to manage data at scale. One of the main drawbacks of the platform is the case where you need to replicate your data, which is when another tool is required to make this happen.

The tool of choice for many within the Oracle ecosystem is Oracle GoldenGate. GoldenGate is an extension of Oracle's data offering that enables you to replicate data from one database to another. Its modular architecture gives you the flexibility to extract and replicate selected data records, transactional changes, and changes to data definition language (DDL) across a variety of topologies.

In this article, we will discuss the steps in which you can take to set up Oracle GoldenGate replication to help solve your data replication needs.

What is Oracle?

Oracle is a popular Relational Database Management System (RDBMS) produced and marketed by Oracle Corporation. Oracle is a multi-model database system and is one of the most used RDBMS to store, organize, and retrieve information.  Many large organizations heavily depend on Oracle’s database offerings to handle the data needs of projects big and small.

Oracle was the first database to use Enterprise Grid Computing (EGC) and warehousing to perform tasks. Using EGC, information can be retrieved from anywhere in the world through a group of computers connected to a network, thereby making it a flexible and cost-effective way to manage information and applications.

Oracle databases are cross-platform and can easily run on multiple types of servers across different operating systems. Wide support is available for many operating systems including Windows, UNIX, Linux, and macOS. Standard SQL queries are used to interact with the database for defining schemas, managing database instances, and reading and writing from the database. Oracle databases can be deployed on-premise or in a fully-managed environment through Oracle Cloud.

Oracle has five editions of its database to choose from, the editions include:

• Standard Edition One: This edition has all the facilities needed to build business applications. It is easy to use and is suited for workgroups and departments to create web applications. It can be used in a single-server environment to highly distributed branch environments within small businesses.
• Standard Edition: It is similar to the Standard Edition One but it offers support for larger machines and Oracle Real Application Clusters (Oracle RAC). It is ideal for users that do not require the robust offerings of the Enterprise edition.
• Enterprise Edition: This is the most robust and secure edition as it contains all the features of the Oracle Database. It harbors features for applications such as high-volume Online Transaction Processing (OLTP) applications, query-intensive data warehouses, and demanding Internet applications.
• Express Edition (XE): This is a lightweight entry-level edition of the Oracle Database. It is easy to download, install, and manage. It is free to develop, deploy, and distribute by anyone. It can also be upgraded to other editions with minimal cost and fewer migration headaches.
• Personal Edition: The Personal Edition supports single-user development and deployment environments that need full compatibility with the Oracle Database Enterprise Edition, Oracle Database Standard Edition, and Oracle Database Standard Edition One. It is comparable with the Enterprise Edition but it is without the Oracle Real Application Clusters (RAC) feature available.

Features of Oracle

Oracle has many features that can be leveraged to create robust and scalable database instances. Let’s explore some of the key features that apply to organizations that are using Oracle databases for applications and services.

• Availability: Oracle provides highly-available databases that can ensure an instance is never out of service, offline, or unavailable. This is extremely important for ensuring availability during planned or unplanned outages and downtimes. It does this through technologies like RAC, Data Guard, and Golden Gate to make sure data is accessible at all times.
• Scalability: Oracle provides features such as Real Application Clusters (RAC) and advanced portability to enable an Oracle database to be highly scalable.
• Performance: Oracle provides performance optimization tools such as Oracle Advanced Compression, Oracle Database In-Memory, and Oracle Real Application Testing to improve the system's performance. These performance optimization tools can be used to decrease query execution time and operations allowing you to retrieve and alter data faster.
• Portability: An Oracle DB can be easily ported to work on various platforms therefore, applications developed on Oracle can be ported without having to make any changes. This is made possible through Oracle’s cross-platform approach which supports Windows server, macOS, Unix, and various distributions of GNU/Linux.
• Backup and Recovery: On Oracle DB, data can be recovered from almost all kinds of data failures. Oracle has recovery features such as Recovery Manager (RMAN) that can cater to this need by performing cold, hot, and incremental database backups and point-in-time recovery.
• Security: Oracle has advanced built-in mechanisms that control and monitor data access and usage at all times. It provides fail-safe security features to implement authorization policies to limit and monitor data access. These features can help to prevent data fraud.
• Concurrent Processing: With Oracle, it is possible to have multiple users execute a variety of applications on the database concurrently. Several applications can run at the same time using Oracle.
• Database Integrity: Consistency of data is ensured in Oracle DB as it enforces data integrity. As a result of its data integrity features, the costs of coding and managing checks on the data are removed as they can be handled directly in Oracle.

What is GoldenGate?

Oracle GoldenGate is a premium software tool used to replicate, filter, and transform data from one database to another. It is available as an add-on license option for Oracle Enterprise Edition Database and provides real-time data mesh. GoldenGate works by replicating data between source and target databases to keep it highly available for real-time analysis using CDC.

Oracle GoldenGate can be used to implement log-based CDC to enable real-time replication. Log-based CDC with Oracle GoldenGate allows for incremental updates on the destination continuously with a low impact on the source database. The logs are monitored to identify changes in the records of the database. Once detected, these changes are replicated to create, update, and delete committed transactions on the target database(s). Data movement happens in real-time to reduce latency and only committed transactions from a primary database are moved to target databases making GoldenGate highly efficient. This ensures data consistency and improves the overall performance of the data ecosystem.

Oracle Goldengate is comprised of a family of products, including:

• Oracle GoldenGate Veridata: Used to verify and reconcile data from the source by comparing one set of data to another to determine if data is out of sync.
• Oracle GoldenGate Studio: Graphical user interface (GUI) that generates configurations from templates using a drag-and-drop process. It also enables the design and deployment of high-volume, real-time replication by handling table and column mappings.
• Oracle GoldenGate for Big Data: Support layer for writing records into big data formats like HDFS, MongoDB, HBase, Cassandra, Kafka, etc.
• Oracle GoldenGate Monitor: Web-based monitoring console that provides a graphical view of all the GoldenGate instances and associated databases in real time.
• Oracle GoldenGate Plug-in for EMCC: Provides visual support for monitoring and managing Oracle GoldenGate processes extending the Oracle Enterprise Manager Cloud Control.
• Oracle GoldenGate Application Adapters: Supports Java Message Service (JMS) information by integrating with installations of the Oracle GoldenGate product.
• Oracle GoldenGate for HP NonStop (Guardian): Used to manage business data at a transactional level by extracting and replicating selected data records and transactional changes.

Key Features of GoldenGate

Although GoldenGate has many features and functionality spread across the family of products, here are a few key features to consider when looking at Oracle GoldenGate.

• Data Consistency: Oracle GoldenGate ensures data consistency and provides improved performance by replicating only committed transactions.
• Reduced Latency: Data is moved in real-time, therefore, reducing latency.
• Simplicity: It has a simple architecture that is easy to configure.
• High Performance: It puts a minimal overhead load on the database and infrastructure.
• Support Systems: It has support for various Oracle Database versions.

Types of Golden Gate Replication

Oracle GoldenGate supports two types of architecture: Classic Architecture and Microservices Architecture. Let’s take a look at how each of these can be defined.

Classic Architecture

Oracle GoldenGate Classic Architecture effectively moves data across a variety of topologies by providing the processes and files required. These processes and files make up the main components of classic architecture. It is used for the static extraction of data records from a source to a target database. The source and target databases are kept consistent through the continuous extraction and replication of transaction DML operations and DDL changes.

Microservices Architecture

Oracle GoldenGate Microservices Architecture uses REST-enabled services to provide remote configuration, administration, and monitoring through HTM5 web pages, command lines, and APIs. It is used for streaming data on a large scale and cloud deployments with fully-secure HTTPS interfaces and secured Websockets. The Microservices Architecture also allows for simpler management of multiple implementations of Oracle Goldengate environments and for controlling user access for different aspects of Oracle GoldenGate setup and monitoring.

Importance of Oracle GoldenGate Replication

With the flexibility and processing features of Oracle GoldenGate, its replication is vital for numerous business requirements, such as:

• Initial Load and Database Migration: Oracle GoldenGate replication is important when performing the initial load of your data. The initial load is a data migration process of extracting data records from the source database and loading them to the target database. This process, when performed correctly, is only performed once.
• Data Integration: Oracle GoldenGate provides real-time data integration by combining data from different sources to provide a unified view of the data.
• Business Continuance and High Availability: Oracle GoldenGate ensures that businesses provide functions and services without any lapse in operations if there is an outage or disaster. GoldenGate provides high availability of data through continuous integration of data across multiple servers, storage solutions, and data centers.

Steps to Set up Oracle GoldenGate Replication/Oracle Golden Gate Replication Example

To create an efficient replication process, Oracle GoldenGate will require the creation of tables on the target that is based on table definitions in the source database. The process to set up Oracle GoldenGate replication, including each step involved, is explained below.

Login and Enable Log Mode

The following steps are used to log in and enable Log Mode when setting up Oracle GoldenGate Replication:

• The first step is to log in to your database as a sys user.

sys as sysdba

• The command below should be executed to enable force logging mode, supplemental log data, and archive log mode. Transactions are captured using the force logging mode even if the logging is specified for other operations by the end user.

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;

• If the shutdown did not work due to a bad setup or another type of error, the commands below can be used to reset it.

SHUTDOWN IMMEDIATE
startup force

• Supplemental log data is necessary to extract committed data from redo logs. The redo logs contain all the changes made in the 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;

Creating User and Granting Permissions

To create a user and grant permissions for setting up Oracle GoldenGate Replication, do the following:

• Create a new user called ggtest with a password ggtest (or another more secure password) and grant necessary permissions to it.

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

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

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

• Execute the following command on the command prompt.

lsnrctl status
lsnrctl start

Remember that all the commands stated above must be executed on both servers.

Installing Oracle GoldenGate and Granting Permissions

The steps detailed below will install Oracle GoldenGate and grant permissions for setting up Oracle GoldenGate Replication:

• Install Oracle GoldenGate.
• After the installation of Oracle GoldenGate, create a GoldenGate admin user for both the source and target databases and grant the necessary permissions. You can use the example code below to achieve this, the example has a username and password as ggowner.

create user ggowner identified by ggowner;
Grant resource, dba, connect to ggowner;

Creating GoldenGate Tablespace and using Interpreter

The next process is to create GoldenGate Tablespace. The Interpreter is used to set up the Oracle GoldenGate Replication as shown below:

• The following command is used to create a GoldenGate tablespace:

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

• In the Oracle GoldenGate source, we will have Manager, Pump, and Extract. This is done directly with the GoldenGate command-line interpreter ggsci.exe. 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 and save it:

GGSCHEMA ggowner

• Open the command prompt in the folder where GoldenGate is installed and execute the command below. First, be sure to exit from the ggsci.exe application.

exit
sqlplus / as sysdba
@role_setup.sql

• In this example, the schema name used while setting up Oracle GoldenGate Replication is ggowner.

grant GGS_GGSUSER_ROLE to ggowner;

Extracting the File

After configuring the managers, we will now configure the capture/extract process. The steps involved in the extract process for setting up Oracle GoldenGate Replication are as follows:

• To configure extract, execute the command:

edit params extract

• Add the following parameters in the extract configuration file that is opened by the previous command:

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

• Next, add a data pump. The GoldenGate data pump process is used to isolate the primary extract process from network latency.

edit params pumpora

• Add the following parameters to the data pump configuration file:

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

• Create a local trail file for the extract. The Data pump needs to be assigned to the extract before starting the capture process. Use the script below to do this.

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

Setting Target and Configuring

After ensuring that both extracts are running, the next step involved is to configure the apply/replicat process on the target system.

• Add a checkpoint for ggowner using the following command:

add checkpointtable ggowner.checkpointtable

• Configure the replicate file, using the command:

edit params repora

• Add the following parameters in the text file:

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

• Register the process on the target database using the command below. This is similar to what we did with the capture process on the source database:

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

• Check the status of the systems by typing the command below.

info all

Adding Data and Committing Changes

The final step involved in setting up Oracle GoldenGate Replication is adding and committing changes  To do this, follow the steps below.

• Add data in the test table of the ggtest user database in server one and commit changes as shown below.

insert into test values(2,'Madhuka');
commit;

• Check server two for the data update once this is done.

Once the replication is confirmed, you have successfully set up Oracle GoldenGate to enable real-time replication.

Limitations of Oracle GoldenGate Replication

Some limitations associated with Oracle GoldenGate Replication include:

• Oracle GoldenGate replication user interface can be a challenge for non-technical users.
• The memory consumption of Oracle GoldenGate Replication during the extract process is high.
• Character set problem is encountered while migrating data in Oracle GoldenGate Replication.
• Data corruptions are very common as there are numerous bugs in the data dumping process of XML, HDFs, etc. Writing your script for the detection and correction of XML corruption is advised when setting up Oracle GoldenGate Replication.

Although this is not an exhaustive list, the limitations mentioned above can be major blockers and concerns for implementing GoldenGate. Of course, there are other solutions, like Arcion, which offer all of the benefits of GoldenGate with more ease and scalability. Let’s explore this alternative approach to replicating data in Oracle using Arcion.

Using Arcion as an Oracle GoldenGate Alternative

Arcion is a zero-code, real-time Change Data Capture (CDC) platform designed for massive scalability, guaranteed data consistency, and low latency. Arcion is offered in two flavors, a self-hosted on-premise solution and Arcion Cloud. Arcion’s 25+ database connectors are 100% agentless, which means no Arcion agent needs to be installed in the database server or instance. Arcion data pipelines can be deployed without writing a single line of code (all you need is your database login info) and all deployment types can support high volume and throughput with easy configuration.

Key features of Arcion include:

• Available as an on-premise solution and fully managed cloud service
• 100% agentless CDC connectors for enterprise databases, including Mainframes
• Guaranteed data consistency
• Low latency
• Massive scalability

When comparing Arcion to Oracle GoldenGate, it’s important to look at a few of the differentiators. Below are some highlights of the features and benefits of using Arcion.

• Arcion offers real-time log-CDC-based data replication for OLTP as well as OLAP systems.
• Arcion is the only end-to-end multi-threaded CDC solution in the market, combining with Oracle Native Reader, Arcion is 10x faster than competitors in data replication speed when Oracle is the source. Also, Arcion has native integration with Snowflake & Databricks, Arcion’s ingestion speed can reach 10k ops/sec/table. Not to mention Arcion can support daily terabyte-scale data replication that requires high throughput (read Arcion helps publica.la migrating 400M rows in minutes).
• It supports automatic schema conversions across a wide variety of databases.
• It can enable zero-downtime migration from on-premise databases to a cloud database (read how Arcion works with Snowflake & Databricks).
• Arcion has a zero data loss architecture that guarantees data consistency.
• Arcion’s patent-pending end-to-end multithreaded architecture provides massive data replication speed as it is highly distributed and parallelized.
• It comes with replication storage to enable single source multiple target data pipelines.
• Arcion is SOC 2 Type 2, and HIPAA compliant. The enterprise-grade security and compliance standards ensure data governance.

When looking at the two solutions, it’s important to highlight the ease and scalability that comes with using Arcion instead of Oracle GoldenGate. By using Arcion, there is also added flexibility since it plays well within the Oracle ecosystem but does not limit future possibilities if other data sources and targets are used.

Conclusion

This article has covered a lot of ground concerning Oracle and Oracle's GoldenGate replication. We have taken a comprehensive look at the inner workings of Oracle Database products, stating its editions, features, and importance of using it. We also dug into Oracle GoldenGate, going over what it is, its key features, as well as its importance in enabling replication within the Oracle ecosystem. We then went into how to set up GoldenGate replication to enable you to move data from a source location to a destination with ease using a step-by-step approach.

Lastly, we looked into using Arcion as an alternative to Oracle GoldenGate. Arcion is one of the Oracle GoldenGate alternatives that meet the requirements for a flexible, intuitive, easy-to-manage data integration solution. It is available in both on-premises and cloud offerings and has connectors to the most popular database systems. Arcion is also incredibly quick to configure and delivers extremely performant data pipelines. To get started today, Download Arcion Self-hosted or sign up for Arcion Cloud for free and unlock the power of your data through zero data loss and zero downtime pipelines in minutes.