Before the database system was invented, data was commonly stored in flat files like text files or CSV files. Database technology is evolving rapidly and new innovations in this field are becoming quite common. Nowadays, many databases have come up that have changed the field of data storage to a great extent. One such popular database system is Oracle.
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. One such application of Oracle replication is Oracle GoldenGate Replication.
In this article, you will gain information about Oracle GoldenGate Replication, but not by using Hevo. Hevo currently doesn’t connect with GoldenGate to sync data. You will also gain a holistic understanding of Oracle Database, its key features, GoldenGate, its key features, steps to set up Oracle GoldenGate Replication, and Limitations of Oracle GoldenGate Replication. Read along to find out in-depth information about Oracle GoldenGate Replication.
Table of Contents
The prerequisites for setting up Oracle GoldenGate Replication are as follows:
- Oracle Database.
- Basic knowledge of Oracle.
- Requirement of two different servers on the Oracle database.
- Oracle GoldenGate Installation and configuration step by step.
What is Oracle?
Oracle is one of the most popularly used on-premise relational databases that works exceptionally well with a wide range of workloads. It can easily handle huge volumes of data, large data transactions, and a lot more. It’s one of the most stable and fault-tolerant software with minimal recovery time. Oracle’s robust querying layer, makes it one of the best analysis tools available in the market and an absolute favorite for deploying ETL pipelines.
Oracle provides a complete suite of applications that help organizations integrate data from various sources, and perform data replication, and transformation. Automated Data Pipelines like Hevo Data help to perform Oracle Database Replication in a very smooth and efficient manner.
For further information on Oracle, you can check the official site here.
Key Features of Oracle
Here a some of the key features responsible for the immense popularity of Oracle.
- Cross-Platform Integration: Oracle supports and works on all the available Operating Systems (OS) including Windows, macOS, Linux, etc.
- Analytics Solutions: You can implement OLAP (Oracle Analytic Processing) and Oracle Advanced Analytics to easily solve analytical calculations on business data.
- Compliant to ACID Properties: Oracle DB supports ACID (Atomicity, Consistency, Isolation, and Durability) properties in order to maintain the integrity of the Database during the transaction processes.
- Easy communication with applications across different platforms: You can seamlessly integrate your Oracle DB with applications across various platforms via its native networking stack. For instance, you can easily integrate and interact with an application (running on Unix) to your Oracle (running on Windows).
- Backup and Recovery: Oracle is capable of recovering data from any kind of crash or technical failure. Designed as RAC, Oracle creates a backup for all data and processes.
What is GoldenGate?
GoldenGate was acquired by Oracle in 2009. It is software that can replicate data from one database to another. It is capable of exchanging and manipulating data at the transaction level among several heterogeneous platforms. It runs on the operating system and is independent of the database.
The target database acts as your backup. GoldenGate is also used for live reporting and offers sub-second latency. Some of the supported databases are Oracle, MySQL, DB2, Microsoft SQL Server, etc. The modular architecture of GoldenGate provides flexibility to replicate data with ease. Some of the topologies supported by GoldenGate are unidirectional, bidirectional, peer-to-peer, etc.
To learn more about GoldenGate, visit here.
Key Features of GoldenGate
- Latency Reduction: In GoldenGate, Data movement across multiple systems or databases is in real-time which helps in reducing latency.
- Consistency & Performance: Oracle GoldenGate Replication only moves committed transactions. It keeps the operations of each transaction in a controlled virtual-memory pool called a cache until the transaction is committed or rolled back. The Oracle GoldenGate cache manager makes use of the operating system’s memory management functions to ensure consistency and improve performance.
- Support of Databases: Oracle Database versions and releases are supported, as well as a wide range of heterogeneous databases running on a variety of operating systems. Data from an Oracle database can be replicated in a heterogeneous database.
- Simplicity: GoldenGate has a simple architecture and easy configuration.
- Minimal Overhead: GoldenGate has a low overhead on the underlying databases and infrastructure, resulting in high performance.
Hevo is a No-code Data Replication Pipeline that provides you with a hassle-free solution to perform the Oracle Replication for a variety of use cases with an easy-to-use no-code interface. Hevo’s end-to-end Data Management offers both full and incremental Data Replication options for your Oracle account. Hevo 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.
Get Started with Hevo for Free
Hevo’s fault-tolerant Data Pipeline offers you a secure option to unify data from 150+ data sources (including 40+ free data sources) and store it in a destination of your choice. 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.
Experience an entirely automated hassle-free setup of Oracle Replication. Try our 14-day full access free trial today!
Importance of Oracle GoldenGate Replication
To understand Oracle GoldenGate Replication, you need to understand the various components involved in it. The components in this process of Oracle GoldenGate Replication are manager, extract, data pump, replicate, and trail/extract files.
The Manager starts the GoldenGate process. It must be running on both servers. It also manages old disk space by purging old trail files. The Extract captures all the DML and DDL changes from redo logs. It writes all the changes in the trail file. The Data Pump process is optional. The extracted data in the trail file is copied to the target system.
The Replicat works in the target database. It reads the trail files and applies the changes to the target. The Trail/Extract files were created in the Extract process for consumption by the Data Pump. All the DML and DDL commands in the source database are copied to trail files using Extract. The Data Pump copies these trail files to the target system. Replicat then reads these files and applies changes to the target.
Steps to Set up Oracle GoldenGate Replication
To achieve Oracle GoldenGate replication, you need to have two servers ready.
You can follow these steps for setting up Oracle GoldenGate Replication:
Step 1: Login and Enable Log Mode
The steps involved in Logging and enabling Log Mode for setting up Oracle GoldenGate Replication are as follows:
- Log in to your database as sys user.
sys as sysdba
- Execute the following command to enable force logging mode, archive log mode, and supplemental log data. You need supplemental log data to extract committed data from redo logs. The Oracle database consists of redo logs, which are files in a proprietary format. Redo logs contain the logs of all the changes made in the database.
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;
Step 2: Creating User & Granting Permissions
The steps involved in creating user & granting permissions for setting up Oracle GoldenGate Replication are as follows:
- Create a new user and grant necessary permissions to it.
create user username identified by password;
grant resource, dba, connect to username;
- Connect to your newly created user, and then create a new table as shown below:
create table tablename(column1 data type,..);
insert into student values(value1, value2,..);
- Make sure that the following command executes on your command prompt.
Note: Run all commands on both the servers.
Step 3: Installing Oracle GoldenGate & Granting Permissions
The steps involved in installing Oracle GoldenGate & granting permissions for setting up Oracle GoldenGate Replication are as follows:
- Install Oracle GoldenGate.
- After successful installation of Oracle GoldenGate, you will now create a GoldenGate admin user for both the databases and grant the necessary permissions.
Create user ogguser identified by ogguser;
Grant resource, dba, connect to ogguser;
Replicating data from Oracle can be a tedious task if the right set of tools is not leveraged. Hevo’s No-Code Automated Data Pipeline offers you a fully-managed solution for all your Oracle CDC and Real-time Data Replication needs. Hevo’s native integration with Oracle empowers you to stream data in real-time from Oracle straight to a Data Warehouse such as Redshift, Snowflake, BigQuery & more!
This Oracle Replication tool lets you effortlessly connect to 150+ Data Sources (including 40+ free sources) and leverage Hevo’s blazing-fast Data Pipelines to help you seamlessly extract, transform, and load data to your desired destination such as a Data Warehouse.
- Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the format of incoming Oracle data and replicates it to the destination schema. You can also choose between Full & Incremental Mappings to suit your Data Replication requirements.
- 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 to make your incoming data from Oracle fit for the chosen destination. You can also use drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few.
- One-Stop Solution: Hevo offers a robust all-in-one solution for replicating data from Generic Oracle and Amazon RDS Oracle. This way, you don’t have to seek multiple solutions for your Oracle Data and can rely on Hevo’s Pipeline to ensure a smooth Data Replication from Oracle sources. To learn more, check out Hevo’s documentation for Oracle Replication.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Want to take Hevo for a spin? Sign up here for a 14-day free trial and experience the feature-rich Hevo.
Step 4: Creating GoldenGate Tablespace & using Interpreter
The steps involved in creating GoldenGate Tablespace & using Interpreter for setting up Oracle GoldenGate Replication are as follows:
- Create a GoldenGate tablespace using the following command:
Create tablespace goldengate
alter user ogguser default tablespace goldengate;
- For the Oracle GoldenGate source, use the GoldenGate command-line interpreter. Execute the following command in the ‘ggsci.exe’:
Edit params ./GLOBALS
Write the following commands in the text file that opens up after executing the above command:
- Open the command prompt in the folder where GoldenGate is installed and execute the following commands:
sqlplus / as sysdba
Your schema name is ‘ogguser’ while setting up Oracle GoldenGate Replication.
grant GGS_GGSUSER_ROLE to ogguser;
Step 5: Extracting the File
The steps involved in extracting the file for setting up Oracle GoldenGate Replication are as follows:
- To configure the extract file, you need to execute the following command:
edit params extract
Write the following lines in the text file that opens up:
USERID ogguser, PASSWORD ogguser
- Now, you will do the data pump process to isolate the primary extract process from network latency.
edit params pumpora
Add the following lines in the data pump configuration file:
RMTHOST 192.168.104.34, MGRPORT 7809
- Now, you will create a local trail file for the extract. You need to assign a data pump to the extract before the capture process.
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
Step 6: Setting Target & Configuring
The steps involved in setting target & configuring for setting up Oracle GoldenGate Replication are as follows:
- You need to set the target system for setting up Oracle GoldenGate Replication. Add a checkpoint using the following command:
add checkpointtable ogguser.checkpointtable
To config the replicate file, use the following command:
edit params repora
Add the following lines in the text file:
USERID ogguser, PASSWORD ogguser
DISCARDFILE ./dirdsc/replcat1.dsc, PURGE
MAP ggtest.*, TARGET ggtest.*;
- Register your process on the target database using the following command:
add replicat repora, EXTTRAIL ./dirdat/rt, checkpointtable ggowner.checkpointtable
Step 7: Adding Data & Commiting Changes
The steps involved in adding & commiting changes for setting up Oracle GoldenGate Replication are as follows:
- Add data in the student table of the test database in server 1 and commit changes as shown below:
insert into student values(3,'xyz');
Data will be updated on server 2.
Limitations of Oracle GoldenGate replication
Some of the limitations of using Oracle GoldenGate Replication are as follows:
- The memory consumption during the extract process of Oracle GoldenGate Replication is exponentially high.
- You can face the character set problem while migrating data in Oracle GoldenGate Replication.
- There are numerous bugs in the data dumping process of XML, HDFs, etc. Data corruptions are very common, and so you should write your script for the detection and correction of XML corruption while setting up Oracle GoldenGate Replication.
- Oracle GoldenGate replication lacks a simple user interface for non-technical users.
In this blog, you learned about Oracle GoldenGate replication. It introduces you to the Oracle database, GoldenGate, GoldenGate configuration steps in Oracle, and the Oracle GoldenGate Replication process. It also provides information about the steps involved in setting up Oracle GoldenGate Replication and the limitations of Oracle GoldenGate Replication. This replication provides a backup of your data in case of data loss. Such a solution will require skilled engineers and regular data updates. Furthermore, you will have to deal with huge memory overhead and work with a complex user interface.
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.
Visit our Website to Explore Hevo
Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) such as Oracle allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.
Share your experience of Oracle GoldenGate Replication in the comment section below.