Oracle Database Replication: 6 Easy Methods

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. Oracle’s stability and short recovery times make it a great choice for running critical enterprise transactional loads.

A common use case in the ETL scenario is the need for Oracle database replication to a separate data warehouse. This is done so that the data from Oracle can be combined with the data coming from other sources, to extract deeper analysis results. In this post, you will learn about the different methods available for Oracle database replication to other data warehouses and also how to replicate Oracle database.

What is Oracle Database Replication?

Oracle provides its own set of tools to replicate Oracle and integrate it with other databases. In this post, you will explore the tools provided by Oracle as well as open-source tools that can be used for Oracle database replication by implementing custom code.

Why Do We Replicate an Oracle Database?

Replicating an Oracle Database makes it easier for companies, businesses, and organizations to share, distribute, protect and use data efficiently. Oracle Database Replication also helps in creating backup and recovery points of data to avoid any data loss. Business runs their operation in different regions and Oracle Database Replication allows companies to synchronize their data across different locations, share data with vendors and partners, and aggregate data from their branches, both international and local. Oracle replication also enhances the availability of applications by providing alternative data access options, ensuring continuity even if one site becomes unavailable.

Companies also use Oracle Database Replication for testing and business reporting purposes. Some of the reasons why the Oracle Database Replication is a good practice are listed below:

• It enhances server performance and helps system administrators minimize the processing cycles on primary servers so that they can be used for writing data.
• It enhances network performance by reducing data access latency, as data can be accessed from multiple Databases.
• Database replication ensures data is copied across multiple machines, facilitating uninterrupted access to your application’s data. This redundancy means that even in the event of a machine being compromised by malware, hardware failure, or other issues, the availability of your application data is maintained around the clock.

Note: Understanding the distinction between Oracle database mirroring vs replication is crucial for crafting a robust disaster recovery strategy.

Methods to Setup Oracle Database Replication

You can easily set up the Oracle Database Replication using the following methods:

• Method 1: Oracle Database Replication Using Hevo Data
• Method 2: Oracle Database Replication using Incremental Approach
• Method 3: Oracle Database Replication using Full Dump And Load
• Method 4: Oracle Database Replication Using A Trigger-Based Approach
• Method 5: Oracle Database Replication Using Oracle Golden Gate CDC
• Method 6: Oracle Database Replication Using Custom Script-Based On Binary Log 

In the following sections, we will be discussing Oracle replication step by step for all the above methods.

Method 1: Oracle Database Replication Using Hevo Data

Hevo Data, an Automated No Code Data Pipeline, helps you replicate data from Oracle to Data Warehouses, Business Intelligence Tools, or any other destination of your choice in a completely hassle-free & automated manner. Hevo’s end-to-end Data Management offers both full and incremental Data Replication options for your Oracle account. Moreover, it serves as the one-stop solution for both the Generic Oracle Database and Amazon RDS Oracle Databases.

To learn more, check out Hevo’s documentation for Oracle Database Replication.

Check out what makes Hevo amazing:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data from Oracle Databases and replicates it to the destination schema.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use for aggregation.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Use Hevo’s no-code data pipeline to replicate data from oracle to a destination of your choice in a seamless and automated way. Try our 14-day full feature access free trial.

Get Started with Hevo for Free

Method 2: Oracle Database Replication using Incremental Approach

The incremental approach to Oracle database replication focuses on capturing and replicating only the changes made to the data since the last replication cycle rather than copying the entire database or table contents each time. This method is particularly useful for databases with large volumes of data where full replication would be time-consuming and resource-intensive.

To use this method, you must possess a copy of the source table as well as an older version of the same table. You can obtain the difference between the two tables, which are named new_version and old_version by running the command provided below.

SELECT * FROM new_version
MINUS
SELECT * FROM old_version;

This above command returns the inserts and updates in the new version of the table. However, you also need to delete the rows that are present in the old_version but not in the new_version.  However, you also need to delete the rows that are present in the old_version but not in the new_version. You can use the following command to do that.

After you get the difference between the two tables, you can load the data into the target database. You also need to replace the old_version table with the new_version table so you can compare it with a future new_version of the table.

Drawbacks 

• It can be computationally expensive, as it requires comparing two tables and performing set operations.
• It can be slow, as it depends on the replication interval and the amount of changed data.
• It can be difficult to restore data, as it requires applying the incremental changes in the correct order.

Method 3: Oracle Database Replication using Full Dump And Load

The Full Dump And Load is a replication method that operates by taking a complete snapshot of a table at specified intervals and transferring it to another database or system.

SPOOL "/data/emp.csv"
SELECT /csv/ * FROM EMP;
SPOOL OFF

This command generates a CSV file containing all the data from the table, which can then be read and loaded into the target database.  This method is particularly efficient for small tables with less than 100 million rows due to its simplicity and the absence of the need for triggers or log readers.

Drawbacks

• Slow and inefficient: This method takes a long time to perform the dump and load, especially for large tables. It also wastes a lot of resources by copying the entire table every time, even if there are only a few changes.
• Not real-time: This method relies on periodic intervals to perform the replication, which means the target database is not always up to date with the source database. This can cause data inconsistency and stale results for queries.
• Difficult to restore: This method does not keep track of the previous versions of the data, so it is hard to restore the data to a previous state if needed. The only option is to load a full backup file, which can be time-consuming and error-prone.

Method 4: Oracle Database Replication Using A Trigger-Based Approach

The trigger-based approach is a synchronous process because the transactions in the source database will not get complete unless the triggers are successfully executed. It means that there is a performance hit in this case also. But using a multi-master replicated instance of Oracle as the source database can avoid this problem. 

Oracle provides a built-in stored procedure to configure the trigger and watch the source table for updates. The crux of this approach is the following command that configures a sync_source from where a ‘change table’ can read changes.

BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(

   owner             => 'cdcpub',
   change_table_name => 'employee_change_set', 
   change_set_name   => 'EMP_DAILY',
   source_schema     => 'SH',
   source_table      => 'EMPLOYEE',
   column_type_list  => 'EMP_ID NUMBER(6), 
                         EMP_NAME VARCHAR2(50),
                         EMP_AGE NUMBER(8,2)', 
   capture_values    => 'both',
   rs_id             => 'y', 
   row_id            => 'n',
   user_id           => 'n',
   timestamp         => 'n',
   object_id         => 'n',
   source_colmap     => 'y',
   target_colmap     => 'y',
   options_string    => 'TABLESPACE TS_EMP_DAILY');
END;

The above command creates a changeset called ‘employee_change_set’ based on the table EMPLOYEE. More details on this approach can be found on this page.

Drawbacks

Even though it solves the lack of real-time support problems in the first approach, this method still has some drawbacks. 

1. There is a performance hit in the source database since triggers can delay the completion of a transaction.
2. For inserting into a target database, there is still significant work involved in building a custom script to subscribe to the changeset table and write accordingly. 

Method 5: Oracle Database Replication Using Oracle Golden Gate CDC

Golden Gate is a paid product from Oracle that can connect to several target databases through built-in handlers. Golden gate works based on binary log-based replication. Oracle binary logs are a historical record of all the changes that have happened in a database since its creation. Watching the binary logs can provide you with events related to all the changes in an Oracle database. Golden Gate’s biggest strength is its true real-time support and a large number of target databases through its built-in handlers. It also supports the transformation of data while loading it to the target database.

Oracle GoldenGate is a software tool that enables real-time data replication using Change Data Capture (CDC) technology. CDC is a technique that captures and replicates only the changes made to the data rather than the entire data set, thus reducing the overhead and latency of data movement.

Oracle GoldenGate consists of several components and processes that work together to extract, transform, and apply data across different databases and platforms. To set up Oracle GoldenGate replication, the following steps are required:

Enable logging and supplemental logging on the source database to capture the data changes.

• First, you should log in to your database as a system user.

sys as sysdba

• Enabling force logging mode, supplemental log data, and archive log mode can be achieved using the command below. Even if end-users specify logging for other operations and transactions will still be captured using the force logging mode.

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 failed due to incorrect setup or errors, use the commands below to reset it.

SHUTDOWN IMMEDIATE
startup force

• It is essential to use supplemental log data to extract committed data from redo logs, which 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;

Create a user and grant permissions for GoldenGate on both the source and target databases.

• Create a new user called ggtest, assign it a secure password such as ggtest (or any other password of your choice), and ensure it has all the required permissions.

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

• Connect to the ggtest user that you have created and create a new table according to the instructions below.

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

• Execute the below command.

lsnrctl status
lsnrctl start

Setting Up Oracle GoldenGate and Assigning Roles

The steps below will guide you through the installation of Oracle GoldenGate and the assignment of roles for Oracle GoldenGate Replication:

• Install Oracle GoldenGate.
• After installing Oracle GoldenGate, create a GoldenGate admin user on the source and target databases, providing them with the required permissions. You can follow the sample code below to do this, using ggowner as the username and password.

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

Making GoldenGate Tablespace and using Interpreter 

The next step is to make a GoldenGate Tablespace. The Interpreter is used to configure the Oracle GoldenGate Replication as shown below:

• Use the following command to make a GoldenGate tablespace

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

• On the Oracle GoldenGate source, we will use the Manager, Pump, and Extract processes. These are configured with the GoldenGate command-line interpreter ggsci.exe. Run the following command in the ggsci.exe

edit params ./GLOBALS

• Enter the following commands in the text file that opens after running the above command and save it.

GGSCHEMA ggowner

• Open the command prompt in the folder where GoldenGate is installed and run the command below. Make sure to exit from the ggsci.exe application first.

exit
sqlplus / as sysdba
@role_setup.sql

Extracting the file

After setting up the managers, we will now set up the capture/extract process.Here are the steps: 

• To set up extract, run the command.

edit params extract

• Put 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.*;

• Then, add a data pump. The GoldenGate data pump process helps to separate the primary extract process from network latency.

edit params pumpora

• Put the following parameters in the data pump configuration file:

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

• Make a local trail file for the extract. The Data pump has to be linked to the extract before starting the capture process. Use the script below for 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

Configuring Target and Setting Up

After making sure that both extracts are running, the next step is to set up the apply/replicat process on the target system.

• Use the following command to create a checkpoint for ggowner

add checkpointtable ggowner.checkpointtable

• Use the command below to set up the replicate file.

edit params repora

• Put the following parameters in the text file.

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

• Use the command below to register the process on the target database. This is the same as what we did with the capture process on the source database. 

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

• Use the command below to see the status of the systems.

info all

Adding Data and Committing Changes

The last step for setting up Oracle GoldenGate Replication is adding and committing changes. Here are the steps.

• Use the below command to add data to the test table. 

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

• Once this is done, check server two for the data update.

You have successfully set up Oracle GoldenGate to enable real-time replication when the replication is confirmed.

Drawbacks

Drawbacks of this approach are listed below:

1. Golden Gate configuration is a complex process and needs an expert Oracle admin to get it right. A detailed post on how to set this up can be found here.
2. The initial load of the target database is still a problem because Golden Gate is notorious for its slow first-time load. Most database administrators still prefer using the traditional dump and export method for the first load.
3. Oracle GoldenGate has a command-line interface that can be challenging for non-technical users.
4. Oracle GoldenGate consumes a lot of memory during the extract process, which can affect the source database performance.
5. Oracle GoldenGate may encounter character set problems when migrating data across different databases and platforms.
6. The process might cause data corruption when dumping data in XML, HDFS, or other formats, which requires custom scripts to detect and correct.

Method 6: Oracle Database Replication Using Custom Script-Based On Binary Log 

There are some open-source tools available that can read the binary log events and expose them for consumption. One such library is the kafka-connect-oracle hosted which can be found here. This utility reads binary logs and adds the event into a Kafka stream. To implement a custom script, you only need to process the Kafka stream events and write accordingly to the target database. The biggest advantage of using such a utility is that it spares you from spending money on the Golden Gate license. For the same reason, Oracle actively discourages you from using such utilities and may even remove the features that are used by such libraries in future releases.

Drawbacks

This method may seem like the perfect solution, but like everything else in life, there are certain drawbacks to this approach too.

1. It does not support DDL operations, and so events related to new table creation and deletion are not captured.
2. It is not an officially supported solution, so in case of a problem, while setting this up, you are at the mercy of the open-source community. It may be hard to digest for organizations that are used to Oracle’s enterprise-grade customer support. 

Conclusion

The article introduced you to Oracle Databases and listed virus methods for replicating your Oracle Data. As evident from the above discussion, no approach is perfect, and the decision depends a lot on the on-ground scenarios in an organization and the compromises that you are willing to make in your criteria. You will need to implement it manually, which will consume your time & resources and is error-prone. Moreover, you need a full working knowledge of the backend tools to successfully implement the in-house Data Replication mechanism. If you are tired of making compromises and want a simple, easy-to-use, and completely managed Oracle database replication experience, you should have a look at what Hevo offers.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 150+ data sources (including 40+ free sources) and can seamlessly perform Oracle Database Replication in real-time. Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your Oracle data. It will make your life easier and make data replication hassle-free.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Share your experience on working with Oracle Database Replication. Get in touch with us in the comment section below.