Oracle Real Time Replication: Simple Steps to Set Up

on Data Integration, Tutorials • June 10th, 2020 • Write for Hevo

Introduction

Are you looking for a simple method to set up real time replication for data in your Oracle database? If yes, you are in the right place. Real time replication is a typical requirement while using Oracle as a transactional system so that ETL workloads can run based on this replicated instance without putting pressure on the performance of the primary transactional database. Oracle real time replication is also required in case a staging database has to be maintained for development and testing use cases. 

What is Oracle database?

Oracle is a popular relational database that can be used to run transactional as well as data warehousing loads. Besides the database, Oracle also provides a complete suite of products to help the customers with data integration, transformation, and loading and bundles them with enterprise-grade security and compliance support. Even though it was designed as an on-premise database, the recent trend towards managed cloud services has prompted Oracle to offer the database in the cloud as well. All this flexibility and the big list of legacy oracle clients mean that Oracle is still among the top preferred database systems.

Methods to Set Up Oracle Real Time Replication

Method 1: Oracle Real Time Replication using GoldenGate

Oracle’s GoldenGate is an easy to use service that utilizes redo logs from your database to set up replication in real time. This method requires a thorough understanding of redo logs and command line to do this.

Method 2: Oracle Real Time Replication using Hevo Data

Hevo Data, a No-code Data Pipeline, is a fully automated solution that you can use to set up real time replication for your Oracle database within a matter of few clicks. It supports robust data transformation and you can also integrate data from many other sources using Hevo.

Get started with hevo for free

Prerequisites

  • An Oracle instance with Oracle GoldenGate installed and two databases.
  • Oracle administrator user account – A SYDBA account.
  • A basic understanding of relational databases.

Method 1: Oracle Real Time Replication using GoldenGate

Oracle 19c Oracle recommends the implementation of real-time replication through Oracle GoldenGate rather than going through the complex CDC configuration.

Oracle Real Time Replication: GoldenGate Architecture
Oracle GoldenGate Architecture

Image source: databaseinternalmechanism.com

Oracle GoldenGate uses the redo logs to enable replication.  Redo logs are a snapshot of all changes happening in an Oracle table. Redo logs based replication ensure that the applications that depend on the source database do not experience any connection problems. GoldenGate works through extract and replicate processes that need to be configured in source and destination instances respectively. You will now start setting up the replication by configuring Oracle GoldenGate.

  1. Enable supplemental logging. This is done so that update statements are also part of the redo logs.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER SYSTEM SWITCH LOGFILE;
  1. You will now add an extract group in the source database. This is to be executed in the GG command interface. 
ADD EXTRACT emp_ext, TRANLOG, BEGIN NOW

ADD EXTTRAIL C:OGG10Gdirdatet, EXTRACT cust_ext

The above command creates an extract group called cust_ext that tracks the transaction log immediately.

  1. The next step is to create a parameter file to add the configurations for the extract.
EDIT PARAM cust_ext

  The file should have the below lines.

EXTRACT cust_ext
USERID system@localhost:1521/TD1, PASSWORD password
EXTTRAIL C:OGG10Gdirdatet
TABLE tr.customers;

In the above configuration, TD1 is the source database with userid as ‘system’. 

  1. You can now start the extract process using the below command
START EXTRACT EMP_EXT
  1. The next step is to set up the replication process in the target database. Login into the target database from the GoldenGate command interface.
DBLOGIN USERID system@localhost:1521/TD2 PASSWORD password
  1. Add a checkpoint table using the below command
ADD CHECKPOINTTABLE TR.CUSTOMERS_CHKPT

The purpose of checkpoint table is to keep track of the status of the extract and replicate the process. This is a very critical step in the case of continuous replication. 

  1. You will now edit the configuration parameters for the replicant process.
EDIT PARAM cust_rep

The emp_rep file should have the below entries.

REPLICAT emp_rep
 USERID system@localhost:1521/TD2, PASSWORD password ASSUMETARGETDEFS
 MAP tr.customers, TARGET tr.customers;

In the above steps, ASSUMETARGETDEFS is for denoting that the same data types will be followed for the target database. 

  1. You will now start the replication process in the destination database using the below command.
start REPLICAT cust_rep
  1. You can check the status of the replication process using the below command.
status REPLICAT cust_rep

If the status is showing as STOPPED, it means there is something wrong with the configuration, and replication is not running currently.

  1. To test replication is running, try inserting a row to your source table and verify that the row is reflected in the target table. 

Challenges of using GoldenGate

You have now learned to configure real-time replication between two Oracle databases using the Oracle GoldenGate product. Even though Oracle GoldenGate is a very dependable product that is used by thousands of organizations, it is not the best when it comes to user-friendliness. The above example for a very basic replication setup between two databases in the same Oracle instance. In reality, while implementing this for a production environment, there will be numerous challenges associated with it. Some of the typical challenges are as below.

  1. GoldenGate is a complex product to set up with very confusing configurations. Oracle itself maintains that this is the job of a database administrator and not for developers. So you will need an expert Oracle database administrator in your team.
  2. The process will become even more complex in case data is to be transformed before inserting it into the target database. This is a very common requirement.
  3. GoldenGate replication is notorious for its slow initial load to the target database. Most administrators prefer to do this by taking a dump of the data and loading it before configuring the replication to save time. This will be an additional step in the database that is already in use. 

Method 2: Oracle Real Time Replication using Hevo Data

If you are not in a mood to go through such a learning curve or solve the above challenges, it may be worthwhile to explore a cloud-based completely managed service like Hevo. Hevo can execute such real-time replications across a variety of source and destination databases. Configuring the replication can be done in a few clicks using an intuitive UI, offering a very short time to production. Hevo also comes with comprehensive transformation support in flight. 

Sign up here for a 14-day free trial!

The following video with take you through how to get started with Hevo platform.

Some important features of Hevo Data include:

  • Ease of Use: The platform is easy to set up and implement with no coding or extensive period of time required to start the process.
  • Complete Monitoring and Management: It is completely monitored and managed independently without the need for any manual tracking.
  • Variety of Integrations: It fosters integrations with different data sources from where easy and efficient migration can be carried out.
  • Real-time Data Replication: The data can be streamed in real-time to provide fast and easy access.
  • Robust Data Transformations: The data transformations are robust and compatible over a variety of platforms. 

sign up for a 14-day free trial with Hevo to experience seamless data replication in real time for Oracle.

Conclusion

This article showcased two methods to set up Oracle real time replication. While GoldenGate provides a way to do this if you are planning to set up replication between multiple databases in Oracle, Hevo Data provides a plug-and-play solution that can be used by anyone to set this up in no time. It also includes an array of other robust features to augment your data integration experience.

visit our website to explore hevo

How do you replicate your Oracle database in real time? Do you use any of the methods shared in this post? Please let us know in the comments section.

No-code Data Pipeline for your data warehouse