Seamless Data Replication from Oracle to YugabyteDB using Goldengate

YugabyteDB is a PostgreSQL-compatible, multi-master RDBMS that supports global data distribution, resilience, scalability, and enterprise features.  Customer can migrate, deploy, and run their applications with YugabyteDB across regions, clouds, and edges.

In this blog, we will see how data can be replicated between Oracle Database and YugabyteDB using Oracle’s Goldengate classic. 



Environment Setup : 

At Source : 

  1. Create goldengate replication user c##ggadmin (common user in case of Container DB) 

  2. Grant required permission to goldengate extract user 

  3. Set/enable required parameter in database

  4. Install Oracle Goldengate for Oracle database

  5. Start Manager process

  6. Create defgen file for target database for the table having different definition

  7. Transfer defgen file to target server 

  8. Create Credentialstore and add credentials

  9. Configure Extract process

  10. Start extract 



At Target : 

  1. Create goldengate user in target

  2. Grant required permission to goldengate replication user

  3. Install required packages

  4. Install Oracle client 

  5. Install Oracle Goldengate for PostgreSQL

  6. Setup ODBC config file

  7. Connect goldengate and start manager process

  8. Configure replicate process

  9. Start replicat


I have a table T1 in Oracle database, Following set of steps will demonstrate on how Goldengate can seamless connect to target YugabyteDB using PostgreSQL compatible ODBC driver and replicate data from Oracle Database. 


Source Configuration : 


1.    Create goldengate replication user c##ggadmin (common user in case of Container DB) 


create user C##GGADMIN identified by ggadmin;


2.    Grant required permission to user: 


GRANT RESOURCE to c##ggadmin; GRANT CREATE SESSION to c##ggadmin;

GRANT CREATE VIEW to c##ggadmin; GRANT CREATE TABLE to c##ggadmin;

GRANT CONNECT to c##ggadmin CONTAINER=all;

GRANT ALTER SYSTEM to c##ggadmin;

GRANT ALTER USER to c##ggadmin;

ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;

ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;

GRANT SELECT ANY DICTIONARY to c##ggadmin;

GRANT SELECT ANY TRANSACTION to c##ggadmin; EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin’);

GRANT DV_GOLDENGATE_ADMIN; ——— for data vault user

GRANT DV_GOLDENGATE_REDO_ACCESS; —— for data vault user


3.    Enable parameters in database :


alter database add supplemental log data;

alter system set enable_goldengate_replication=TRUE;


4.    Install Oracle Goldengate binaries for Oracle Database :

        

In my case, I have downloaded and install Oracle Goldengate 21c for Oracle Database.  https://www.oracle.com/in/middleware/technologies/goldengate-downloads.html


5.    Connect to Goldengate console and start manager process on port 7809


./ggsci 

start manager


6.    Create defgen file which is required when source and target table structure is not same, mostly when configuring replication between heterogeneous databases.


edit params defgen 

DEFSFILE ./dirprm/t1.def

USERIDALIAS oracredcdb

TABLE ORCLPDB1.ggpdbuser.t1;


Generate a definition file “./dirprm/t1.def” from defgen console. . 


        ./defgen paramfile ./dirprm/defgen.prm


7.    Transfer t1.def file to target server.


8.    Create credntialstore for oracle database login in goldengate


ADD CREDENTIALSTORE

ALTER CREDENTIALSTORE ADD USER c##ggadmin@ORCLCDB, PASSWORD ggadmin, ALIAS oracredcdb

ALTER CREDENTIALSTORE ADD USER c##ggadmin@ORCLPDB1, PASSWORD ggadmin, ALIAS oracredalias


9.     Configure Extract process : 


EXTRACT extora

USERIDALIAS oracredcdb

SETENV (LD_LIBRARY_PATH='opt/oracle/product/19c/dbhome_1/lib')

SETENV (TNS_ADMIN = '/opt/oracle/product/19c/dbhome_1/network/admin')

RMTHOST 10.98.41.29, MGRPORT 7810

RMTTRAIL ./dirdat/rt

DDL INCLUDE MAPPED

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

TABLE ORCLPDB1.ggpdbuser.t1;


10.     Start Extract 


add extract extora, integrated tranlog, begin now

add rmttrail ./dirdat/rt, extract extora, megabytes 10

Start extract extora

Target Configuration : 

1.    Create Goldengate user in target database :

create user ggpguser with superuser password 'ggpguser';

2.    Grant required permission to user :

GRANT CONNECT ON DATABASE dbname TO gguser;

ALTER USER gguser WITH SUPERUSER; < Required to enable table level supplemental logging (ADD TRANDATA) but can be revoked after TRANDATA is enabled for the table(s).>

GRANT USAGE ON SCHEMA tableschema TO gguser;

GRANT SELECT ON ALL TABLES IN SCHEMA tableschema TO gguser;

GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tablename TO gguser;

GRANT CREATE ON DATABASE dbname TO gguser;

GRANT CREATE, USAGE ON SCHEMA ggschema TO gguser;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ggschema TO gguser;

GRANT SELECT, INSERT, UPDATE, DELETE, ON ALL TABLES IN SCHEMA ggschema TO gguser;

3.    Install following packages as pre-requisites : 

sudo yum install unixODBC* libaio* psqlodbc* libnsl* 

4.    Install Oracle Client 

yum install oracle-instantclient-basic-23.6.0.24.10-1.el8.x86_64.rpm

5.    Install Oracle GoldenGate binaries for PostgreSQL 

Download and unzip the 213000_ggs_Linux_x64_PostgreSQL_64bit.zip

6.     Next, we’ll do ODBC configuration 

# cat /etc/odbc.ini

#Sample DSN entries

[ODBC Data Sources]

YB_tgt=DataDirect 7.1 PostgreSQL Wire Protocol

[ODBC]

IANAAppCodePage=4

#InstallDir=/home/postgres/gg_pg_21c/

InstallDir=/home/yugabyte/gg_yb_21c

[YB_tgt]

Driver=/home/postgres/gg_pg_21c/lib/GGpsql25.so

Description=DataDirect 7.1 PostgreSQL Wire Protocol

Database=yugadb

HostName=localhost

PortNumber=5433

7.    Connect Goldengate console for postgreSQL :

./ggsci

start mgr.  >> manager is configure on 7810 port

create SUBDUERS

8.  Add target YugabyteDB login credential to Goldengate credntialstore and configure replicate process


    set environment variable :


export PATH=/usr/lib/oracle/23/client64/bin:$PATH

export LD_LIBRARY_PATH=/usr/lib/oracle/23/client64/lib

export LD_LIBRARY_PATH=/home/yugabyte/gg_yb_21c/lib:$LD_LIBRARY_PATH

export PG_HOME=/home/yugabyte/yb-software/yugabyte-2024.1.3.0-b105-centos-x86_64

export ODBCINI=/etc/odbc.ini

    Login to ggsci  

DBLOGIN SOURCEDB YB_tgt userid yugabyte password yourpassword

alter credentialstore add user yugabyte, password yourpassword, alias yugacredalias

list tables

    Edit replicat param file 

REPLICAT repyuga

SOURCEDEFS dirdef/t1.def

SETENV (ODBCINI="/etc/odbc.ini")

SETENV (PGCLIENTENCODING="UTF8")

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

TARGETDB YB_tgt USERIDALIAS yugacredalias

BATCHSQL

GETTRUNCATES

DISCARDFILE ./dirrpt/diskg.dsc, purge

MAP ORCLPDB1.ggpdbuser.t1, TARGET public.t1, COLMAP (T1_ID=t1_id, T1_NAME=t1_name);

9.    Start replicat

add replicat repyuga, NODBCHECKPOINT, exttrail ./dirdat/yb

start repyuga 

 

Awesome! Here we have done with initial setup for data replication for table T1 from Oracle to Yugabyte. Let’s test by performing DML on source T1 table. 


At Source >>


SQL> insert into t1 values (2024,'yugabyte');

1 row created.


SQL> commit;

Commit complete.


At Target  >>


yugadb=# select * from t1;

 t1_id | t1_name  

-------+----------

  2024 | yugabyte

(1 row)













 


Comments

Post a Comment

Popular posts from this blog

Simplified Database migration from RDS PostgreSQL to YugabyteDB using voyager

Image
" YugabyteDB Voyager ”  is a migration tool from Yugabyte that powers you to perform offline/online migration with fall-forward and fall-back options from your on-premise/public cloud to YugabyteDB.  I have recently tested one of the scenario in my local environment, where source database is RDS PostgreSQL and target database is YugabyteDB which is running on ec2 cluster.  sharing step-by-step procedures which I have followed referring below document :  https://docs.yugabyte.com/preview/yugabyte-voyager/migrate/live-fall-back/ .  Step -1. Install yugabytedb voyager on intermediate server which have connectivity from/to source and target databases Step -2. Prepare Source and Target database for online migration Step -3. Run Voyager Assessment to validate the configuration Step -4. Run export schema from source database Step -5. Analyze the schema Step -6. Import the schema to target database Step -7. Export the data from source with snapshot and changes enabled ...
Read more

Perform Initial data load using from Oracle to YugabyteDB using Goldengate

In this blog, we’ll see initial data load process from Oracle database to YugabyteDB using Goldengate.  Source -  Oracle Database 19c Goldengate 21c for Oracle database 1.    Get the DDL of source table and convert into PostgreSQL format to create required tables and indexes into Yugabyte database. 2.     create the source table definition files using Goldengate defgen utility.   3.     create initialload extract params file and add extract process with “sourceistable ” mentioned 4.     start the initial load extract.  Target -  YugabyteDB v2024.2.0.0 Goldengate 21c for PostgreSQL database 1.     Create the required tables and indexes structure into YugabyteDB 2.     Create the  checkpoint table  3.     add replicate and start replicat process
Read more