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.
Let’s see how this blog is structured:
Table of Contents
What is Oracle Database Replication?
Image Source: DBaaS
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.
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 the server performance of the server, and it helps system administrators minimize the processing cycles on primary servers so that they can be used for writing data.
- It enhances the network performance because it reduces the data access latency as data can be accessed from multiple Databases.
Method 1: Oracle Database Replication Using Hevo Data
Hevo Data, an Automated No-code Data Pipeline provides you with a hassle-free solution to perform the Oracle Database Replication with an easy-to-use no-code interface. Hevo is fully managed and completely automates the process of not only replicating data from Oracle but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.
Hevo’s fault-tolerant Data Pipeline offers a faster way to move your data from Oracle and 100+ other data sources(including 40+ free data sources) into Data Warehouses, Databases, BI Tools, or any other destination of your choice. Hevo will take full charge of the data replication process, allowing you to focus on key business activities.
Method 2: Oracle Database Replication Using A Full Backup And Load Approach
This method takes a full dump of the table that you want to replicate in specific intervals of time to generate a snapshot in the preferred format. You can then use this dump file to load another table in a different data warehouse or execute some custom transformations. With Oracle, this can be done easily by using /*csv*/ annotation with the query.
Method 3: Oracle Database Replication Using A Trigger-Based Approach
A trigger is a function that can be configured to execute when specific events occur. Triggers can be configured, such that when a source table is modified, a record is inserted into another table. This table can then be used to replicate Oracle data to a target database by using some custom code.
Method 4: Oracle Database Replication Using Oracle Golden Gate CDC
Oracle Golden Gate is Oracle’s solution for all things related to ETL and replication with Oracle as the source database. Golden Gate’s Kafka handler makes it easy for organizations to set up custom pipelines in case their preferred target database handler is not present in the Golden Gate.
Method 5: Oracle Database Replication Using Custom Script-Based On Binary Log
Oracle binary log is a complete record of all the changes that have taken place in an Oracle table from the beginning. It means any custom code that can read this binary log can aid in creating a replication script. You can use an in-built Oracle function to apply this approach.
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
Image Source
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 A Full Backup And Load Approach
An example of such a dump file generation can be done in SQL Plus as follows:
SPOOL "/data/emp.csv"
SELECT /*csv*/ * FROM EMP;
SPOOL OFF
The above command will generate a CSV file, which can then be loaded into the target database.
As you may have guessed already, the problem with this approach is that it can only be used with smaller tables. But this is a very reliable method for smaller dimension tables in an ETL.
An alternative to this approach is to load the target database incrementally with only the changes from the last load. To execute such a maneuver, you need to have access to a second Oracle table. Oracle provides a very convenient method to take the difference between two tables by using the following command.
SELECT * FROM new_version
MINUS SELECT * FROM old_version;
The above command will provide you with all the inserts and updates in the new version of the table. To execute this correctly, an old version of the table must be replaced with the new version every time data is loaded into the target database.
As already evident, even with the incremental load approach, the load process is inefficient. It is because, at some point in the pipeline, a table is being completely replaced by the new version of the table. This is usually done at predefined intervals.
Drawbacks
In summarizing, the above method has the following drawbacks:
- It can only be used with smaller dimension tables.
- It is very inefficient because of the complete overwrite of the involved tables.
- Real-time Oracle Database replication is not feasible with this approach. Only a predefined interval-based load is possible.
- There is a significant performance hit to the source database while the data file generation is in progress. This can be worked around by configuring a multi-master replication though.
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. 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.
- 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
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.
Drawbacks
Drawbacks of this approach are listed below:
- 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.
- 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.
Method 5: 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.
- It does not support DDL operations, and so events related to new table creation and deletion are not captured.
- 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 100+ 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.
With around a decade of experience, Sarad has designed and developed fundamental components of Hevo. His expertise lies in building lean solutions for various software problems, mentoring fellow engineers and exploring new technologies"