Oracle Real Time Replication: Simple Steps to Set Up

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. 

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 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;

2. 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.

3. 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’. 

4. You can now start the extract process using the below command

START EXTRACT EMP_EXT

5. 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

6. 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. 

7. 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. 

8. You will now start the replication process in the destination database using the below command.

start REPLICAT cust_rep

9. 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.

10. 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. 

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. 

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.

Sign up here for a 14 day free trial with 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.