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 create backup and recovery points for 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 in real time, 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.

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

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

Method 1: 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.
• 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 incremental changes must be applied in the correct order.

Method 2: 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. Also, check the other types of data replication methods.

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.

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

Drawbacks

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

• There is a performance hit in the source database since triggers can delay the completion of a transaction.
• 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 4: Oracle Database Replication Using Oracle Golden Gate CDC

Oracle GoldenGate is a software tool that enables real-time data replication using Oracle 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

• Log into your database as a system user and enable force logging, supplemental logging, and archive log mode. Use these commands:

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;

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

• After installation, create an admin user (e.g., ggowner) and set up a GoldenGate tablespace.

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

• Set up extract and pump processes using the GoldenGate command-line tool ggsci.exe.

edit params extract
edit params pumpora

• On the target system, create the replicat process to apply data changes.

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

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

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

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

Drawbacks

The drawbacks of this approach are listed below:

• Golden Gate configuration is a complex process and needs an expert Oracle admin to get it right. 
• 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.
• Oracle GoldenGate has a command-line interface that can be challenging for non-technical users.
• Oracle GoldenGate consumes a lot of memory during the extract process, which can affect the source database performance.
• Oracle GoldenGate may encounter character set problems when migrating data across different databases and platforms.
• The process might cause data corruption when dumping data in XML, HDFS, or other formats, which requires custom scripts to detect and correct.

How Hevo Simplifies Oracle Database Replication

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

Conclusion

The article introduced you to Oracle Databases and listed virus methods for replicating your Oracle Data. 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.

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.
Share your experience working with Oracle Database Replication. Get in touch with us in the comment section below.

Frequently Asked Questions