How to replicate tables from SQL Server to Oracle Database using GoldenGate

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

In previous article we have seen how we can replicate tables from Oracle Database to SQL Server using GoldenGate. In this article we will see how we can do the reverse from SQL Server to Oracle Database and our replication to be from unidirectional with direction to a target to bidirectional with two-way synchronization.

The process for sending the data is quite similar, with the central difference being that GoldenGate on SQL Server makes use of it Change Data Capture (CDC) for the supplemental logging required.

Before looking at the process, let's first remember what GoldenGate is.

What is GoldenGate

Oracle GoldenGate is a tool that allows us to copy and format data between databases, without affecting the operating system or RDBMS being used. The updating of the tables can be either in one direction with a target (unidirectional), or to several targets (broadcast), or bidirectional.

For example, we can transfer table data using GoldenGate from Oracle to Oracle, from Oracle to SQL Server, from SQL Server to SQL Server, or from Oracle simultaneously to SQL Server, MySQL, DB2 and PostgreSQL.

How GoldenGate works

GoldenGate uses three processes: Extract, Data Pump (in case we use a remote server) and Replicat.

The Extract is responsible for recording the committed transactions from the tables we have defined and storing them in a file called local trail. Then the Data Pump sends the data from the local trail file to the remote server via TCP/IP and writes the data to the file named remote trail. In the end the Replicat reads the remote trail and applies the changes to the target databases.

The prerequisites

For starters we should have set it up Oracle Database Software on the primary and on the standby server and Oracle Database in primary to archivelog mode. How we do these installations can be seen step by step in the article here.

We should download the version Oracle GoldenGate separately for each system from here. The Oracle on Linux x86-64 edition for Oracle Server and the SQL Server on Windows x86-64 edition for SQL Server.

On the Windows Server that has SQL Server we will need to download and install it Oracle Database Client from here.

Having done all the steps of installing GoldenGate on both servers as detailed in previous article.

Installation / configuration in source

On Windows SQL Server (source)

For example, we have the following table in SQL Server, which we brought from the Oracle Database:

USE StackOverflow2013
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CUSTOMERS](
	[ID] [numeric](38, 0) NOT NULL,
	[NAME] [varchar](50) NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.customers ADD PRIMARY KEY (id);

INSERT INTO CUSTOMERS VALUES (1,'Stratos');
INSERT INTO CUSTOMERS VALUES (2,'Nikos');
INSERT INTO CUSTOMERS VALUES (3,'George');

To begin with, we should create in the database the schema where the objects belonging to GoldenGate will be stored:

USE StackOverflow2013
GO

create schema ggs;

Next we need to define the user SA as owner of the database, to the user who administers GoldenGate (ggadmin) his role db_owner and as long as the installation process lasts his role sysadmin:

EXEC sys.sp_changedbowner 'sa';
GO

EXEC sp_addrolemember 'db_owner', 'ggadmin'
GO

EXEC sp_addsrvrolemember 'ggadmin', 'sysadmin';  
GO

Then we go to the folder where we have installed GoldenGate and connect to the interface:

cd c:\goldengate

ggsci.exe

Once connected, modify the GoldenGate Manager parameter file and add the following:

edit params mgr

PURGEOLDEXTRACTS ./dirdat/*, MINKEEPHOURS 2
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 10, WAITMINUTES 5, RESETMINUTES 60
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 10, WAITMINUTES 5, RESETMINUTES 60

• PORT: It is the minimum variable that the file must have and defines the port of the Manager.
• PURGEOLDEXTRACT: It is the location where the trail file with the data is stored and we define it to keep at least 2 hours.
• AUTOSTART : We define which processes to start automatically when the Manager starts.
• AUTORESTART: We define which processes should be restarted after an error.

We are modifying it GLOBALS parameter file so we can add it schema which we made before:

edit param ./GLOBALS

MGRSERVNAME GOLDENGATEsrv
CHECKPOINTTABLE dbo.CHECKPOINTTABLE
GGSCHEMA ggs

Now start the GoldenGate manager, if it is not already open:

start manager

We connect to the database through GoldenGate using it Credentialstore which we had made in the previous installation (ggadmin). Its job is to keep the GoldenGate database administrator username and password locally encrypted:

dblogin sourceDB sqlserverdb useridalias ggadmin

In case we had not created it and we want to create it, we run the following:

add credentialstore
alter credentialstore add user ggadmin alias ggadmin

After connecting with the user, we set supplemental logging to be enabled for each table we want to replicate:

add trandata dbo.CUSTOMERS

Once we enable supplemental logging it will enable it Change Data Capture in the SQL Server table. Along with the table it will also create one cleanup job of SQL Server Agent which deletes information that is no longer needed. But this cleanup job does not check if the Extract process of GoldenGate managed to record the information, so we will have to delete it and make a new one in its place.

To delete the original cleanup job, connect to the instance of SQL Server with the following query:

execute sys.sp_cdc_drop_job 'cleanup';

Then to create the compatible cleanup job, we return to the GoldenGate interface and run the following command putting in order username, password, dbname, hostname and schema:

shell ogg_cdc_cleanup_setup.bat createJob ggadmin Kwdikos StackOverflow2013 localhost ggs

If we connect from SQL Server Management Studio to SQL Server we will see that it has created a new job with a name OracleGGCleanup:

Creating the Extract process in the source

Now we create the Extract process that will extract the data to the local trail file:

edit param sql_ext

EXTRACT sql_ext
SOURCEDB sqlserverdb USERIDALIAS ggadmin
RMTHOST oracledev2, MGRPORT 7809
RMTTRAIL ./dirdat/bb
TABLE dbo.CUSTOMERS;

• EXTRACT: We set the name of the process.
• USERIDALIAS: Set the name of the alias we made for the base user in the Credentialstore.
• RMTHOST: We define the hostname of the remote destination server. If we do not use a DNS server, we put the record we passed in /etc/hosts.
• MGRPORT: We define the port that the GoldenGate Manager has on the remote destination server.
• RMTTRAIL: We define the path and the name that the trail files will have.
• TABLE: We define the tables we want to transfer, in this case we put the user's schema and * since the schema contains only the table we want.

To add the extract process we should run the following with the name of:

add extract sql_ext,tranlog, begin now

add rmttrail ./dirdat/bb, extract sql_ext

We connect again to the GoldenGate interface:

ggsci.exe

dblogin sourceDB sqlserverdb useridalias ggadmin

We start the Extract process we have created and see the status of the processes:

start sql_ext

info all

We don't forget to make a check that the files have gone to the directory of the destination server.

Optional step for translation of definitions between RDBMS

Optionally in case the replicat show errors when converting to compatible data types, we need the translation of the definition of the tables from the source to the target. So to have this we run and add the corresponding tables to the following:

edit params sql_objects

sourcedb sqlserverdb useridalias ggadmin
defsfile ./dirsql/sql_objects.sql purge
table dbo.customers;

We get out of it ggsci and run the following generator:

defgen paramfile ./dirprm/sql_objects.prm

After the file is generated, we copy it to the target:

copy
 C:\goldengate\dirsql\sql_objects.sql
 /oracle/app/oracle/goldengate/dirdef/

Installation / configuration on the target

On the Oracle Database server (target)

To begin with, we connect to the database and give the GoldenGate administrator user permission to make changes to the table with the following query:

grant insert,update,delete on stratos.customers to ggadmin;

While creating the user for the reverse process, we have also given the following permissions that we also need to have:

-- Grant/Revoke role privileges 
grant connect to ggadmin;
grant resource to ggadmin;
-- Grant/Revoke system privileges 
grant select any table to ggadmin;
grant select any dictionary to ggadmin;
grant flashback any table to ggadmin;
grant create session to ggadmin;
grant alter session to ggadmin;
exec dbms_goldengate_auth.grant_admin_privilege('ggadmin');

Then we go to the folder where we have installed GoldenGate and connect to the interface:

cd /oracle/app/oracle/goldengate
./ggsci

As soon as we connect, as a first step we modify the parameter file of the GoldenGate Manager and add the following:

edit param mgr

PORT 7809
PURGEOLDEXTRACTS ./dirdat/*, MINKEEPHOURS 2
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 10, WAITMINUTES 5, RESETMINUTES 60
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 10, WAITMINUTES 5, RESETMINUTES 60

Now start the GoldenGate manager, if it is not already open:

start manager

We connect to the database through GoldenGate using it Credentialstore which we had made in the previous installation (ggadmin). Its job is to keep the GoldenGate database administrator username and password locally encrypted:

dblogin useridalias ggadmin

In case we had not created it and we want to create it, we run the following:

add credentialstore
alter credentialstore add user ggadmin alias ggadmin

We define the table checkpoint which will contain the information with the apply of replication in the Oracle Database:

add checkpointtable ggadmin.chkpt

What do we do in case we have not transferred the table from SQL Server to Oracle Database

In this particular example we had transferred (as we had in a previous article) the table from Oracle Database to SQL Server through the tool SQL Server 2019 Import and Export Data (64bit). But if we hadn't done that and wanted to migrate back now from SQL Server to Oracle Database, we would have to follow the procedure below.

On Windows SQL Server (source)

Go to the GoldenGate interface and create a process with the following parameters:

edit params FIRST_EXP

SOURCEISTABLE
SOURCEDB sqlserverdb USERIDALIAS ggadmin
RMTHOST oracledev2, MGRPORT 7809
RMTFILE ./dirdat/fe, PURGE
TABLE dbo.CUSTOMERS;

• SOURCEISTABLE : We set it to extract the entire information not from the logs but directly from the table, with what is needed to load it into another table.
• SOURCEDB: We define the ODBC we have made for the source database.
• USERIDALIAS: Set the name of the alias we made for the base user in the Credentialstore.
• RMTHOST: We set the hostname of the remote SQL Server. If we do not use a DNS server, we put the record we passed in /etc/hosts.
• MGRPORT: We define the port that the GoldenGate Manager has on the remote SQL Server.
• RMTFILE: We define the path and the name that the data files will have.
• TABLE: We define the tables we want to transfer.

* In the event that we want to save the trailfiles of the inital load on a disk other than Goldengate subdirs, then we must modify it from above to RMTFILE D:\\trails/fe , PURGE ,as also in ./GLOBALS of the target to add the line ALLOWOUTPUTDIR D:\\trails\ and make the corresponding change to the parameter EXTFILE / RMTFILE of the replicat.

We exit the GoldenGate interface and run the following from the GoldenGate folder to start the export:

extract paramfile dirprm\first_exp.prm reportfile dirrpt\first_exp.rpt

On the Oracle Database server (target)

Go to the GoldenGate interface and create a process with the following parameters:

edit params FIRST_IMP

SPECIALRUN
USERIDALIAS ggadmin
SOURCECHARSET PASSTHRU
BATCHSQL
EXTFILE ./dirdat/fe
DISCARDFILE ./dirrpt/sql_discard.txt, APPEND, MEGABYTES 50
HANDLECOLLISIONS
MAP DBO.CUSTOMERS, TARGET STRATOS.CUSTOMERS;
END RUNTIME

• SPECIALRUN : We define that it will run without checkpoints and only once for the initial loading of the table.
• USERIDALIAS: Set the name of the alias we made for the base user in the Credentialstore.
• SOURCECHARSET: We define the type of character conversion, with the value PASSTHRU it does not convert the data but only works with ASCII.
• BATCHSQL: We set it to group the requests for better performance.
• EXTFILE: We define the path and the name of the files to be loaded.
• DISCARDFILE: We define the file containing the table records that failed to pass
• HANDLECOLLISIONS : Parameter that handles duplicate and wrong records.
• MAP: We define the relationship between the source and target tables. In this case we want the tables to go to the DBO schema.

We exit the GoldenGate interface and run the following from the GoldenGate folder to start the import:

./replicat paramfile dirprm/first_imp.prm

Creating the Replicat process on the target

At this point we make it Replicat process which will import the data from the trail file adding the following parameters:

edit param ora_rep

REPLICAT ora_rep
USERIDALIAS ggadmin
SOURCEDEFS ./dirdef/sql_objects.sql
DISCARDFILE ./dirrpt/sql_discard.txt, APPEND, MEGABYTES 50
HANDLECOLLISIONS
MAP DBO.CUSTOMERS, TARGET STRATOS.CUSTOMERS;

• REPLICAT: We set the name of the process.
• TARGETDB: We define the ODBC we created with the target database.
• USERIDALIAS: Set the name of the alias we made for the base user in the Credentialstore.
• SOURCEDEFS: We define the traslation of table definitions between target and source. (optional parameter, we saw how to create this file in previous step)
• DISCARDFILE: We define the file containing the table records that failed to pass
• HANDLECOLLISIONS – NOHANDLECOLLISIONS: Parameter that handles duplicate and wrong records. We return it to HANDLECOLLISIONS in case the Replicat Process fails to start due to the above case.
• MAP: We define the relationship between the source and target tables. In this case we want the tables to go to the DBO schema.

To make it Replicat process we run the following:

add replicat ora_rep, exttrail ./dirdat/bb, checkpointtable ggadmin.chkpt 

Finally we are ready to start it Replicat process by running the following:

start ora_rep

info all

The test

To test if it works, all we have to do is change one record in the table in SQL Server:

INSERT INTO DBO.CUSTOMERS VALUES (13,'Jason');

Now if we SELECT the table in the Oracle Database we will see that the write has passed:

select * from stratos.customers;

So now we have a bidirectional active-active update between the Oracle Database table and the SQL Server table.

Monitoring

We can see his evolution Replicat process in the target Oracle Database by SELECTing the table checkpoint:

select * from ggadmin.chkpt;

We can find information about it Extract process for its status, when it ran and the delay it has running through the GoldenGate interface of the source server the following:

info replicat ora_rep, detail

We can also generate a detailed report for a process that contains the log by running through the GoldenGate interface the following:

view report ora_rep

Sources:

• Oracle GoldenGate for Windows and UNIX (Choosing Capture and Apply Modes)
• Configuring Capture in Integrated Mode
• Oracle GoldenGate Parameters
• Configuring Oracle GoldenGate for Active-Active High Availability
• Replicating Transactions Between Microsoft SQL Server and Oracle Database Using Oracle GoldenGate
• Preparing the Database for Oracle GoldenGate — CDC Capture
• Using the Oracle GoldenGate for SQL Server CDC Capture Replication
• Preparing the Database for Oracle GoldenGate
• INFO EXTRACT
• VIEW REPORT
• HANDLECOLLISIONS | NOHANDLECOLLISIONS
• SOURCEISTABLE
• SPECIALRUN
• SOURCECHARSET
• SOURCEDEFS