VooServers

How To: Install & Configure Oracle Golden Gate 12.3 for Live Replication between Oracle Linux 7 (12c Database) and Windows Server 2016 (Microsoft SQL Server 2014) – Full SCHEMA


Reliable Hardware – Trained Staff
You are here:  Service / Technical Blog
By    -   Head of Technical Projects

Overview & Version Information:

I will be showing how to install and configure Oracle Fusion Middleware Golden Gate 12.3 for the purposes of data replication from a 12c instance on Oracle Linux 7, into an MSSQL Server 2014 Std instance on Windows Server 2016 of a full Oracle SCHEMA.

  • Oracle Golden Gate v12.3.0.1.4 (For Oracle Linux 7)
  • Oracle Golden Gate v12.3.0.1.6 (For Windows Server 2016)
  • Oracle Linux 7.4 (Kernel 4.1.12-112.14.2.el7uek.x86_64)
  • Oracle 12c (v12.1.0.2.0)
  • Windows Server 2016 (x64 Datacentre)
  • Microsoft SQL Server 2014 (Standard v12.0.5207.0)

We will be making use of EXTRACT and REPLICAT processes for the initial data load, and also utilising TRAIL’s, CDC and CDD to handle the live change data replication.

Throughout this article, Oracle Golden Gate will be referred to as OGG.


Installing OGG into Oracle Linux 7 (12c DB):


Head to https://edelivery.oracle.com and download the relevant OGG 12.3 DLP, at time of writing, 12.3.0.1.4 is “V975837-01.zip”. Transfer this zip file onto a convenient location on your OL7 server.

<<< On the SOURCE SERVER >>>

On OL7, create the staging directory, and prepare by installing readline wrapper:

[root@shell]# mkdir /stage
[root@shell]# mv /path/to/zipfile.zip /stage/
[root@shell]# yum –y install readline readline-devel
[root@shell]# cd /stage
[root@shell]# wget ftp://ftp.pbone.net/mirror/download.fedora.redhat.com/pub/fedora/epel/7/x86_64/Packages/r/rlwrap-0.42-1.el7.x86_64.rpm
[root@shell]# unzip V975837-01.zip
[root@shell]# yum install rlwrap-0.42-1.el7.x86_64.rpm


Setup aliases in OL7 for GGSCI and SQLPLUS:

[root@shell]# su -l oracle
[oracle@shell]# nano ~/.bashrc

# Aliases for GoldenGate
alias sqlplus="rlwrap sqlplus"
alias ggsci="rlwrap ./ggsci"

[oracle@shell]# . .bashrc %% alias
[oracle@shell]# mkdir /u01/app/oracle/product/ogg_src


NOTE: You may change the directory name created above, it must be within your oracle installations product directory, but you may name it whatever you wish. On later installations, I suffixed the directory with the version number (ogg_src_12-3).

Run the OGG installer:

Connect to the console of the server, VM Console if virtualised, or physical KVM console if using a dedicated system. You need to run the next steps in a graphical environment. This guide assumes you have a functioning X server or other compatible desktop environment to use.

Log on as your Oracle user, open a Terminal window:

[oracle@shell]# cd /stage/fbo_ggs_Linux_x64_shiphome/Disk1
[oralce@shell]# ./runInstaller




The graphical OGG installer will now start. Follow the on screen instructions.




Select 12c when prompted.




Your details here may differ to the screenshot shown.


Software Location: The full working path to the ogg product folder that you created earlier
Start Manager: Checked (starts manager as automatic Linux server)
Database Location: The oracle DB Home location of your instance
Manager Port: I’ve used a slightly different port, you are welcome to use whatever you wish, but be sure to substitute it in later steps of the install.




Let the installer complete.




Done, installation is complete. We will now work on installing OGG into Windows Server 2016.


Installing OGG into Microsoft Windows Server 2016 Datacentre:


<<< On the TARGET SERVER >>>

Head over to https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html and download the relevant version of OGG for Windows Server MSSQL. At time of writing it should be “Oracle GoldenGate 12.3.0.1.6 for SQL Server (CDC Capture) on Windows (64bit) – 75Mb. Transfer the downloaded Zip to your MSSQL Server.

Create a new directory, for this example, we are using “C:/GoldenGate”, copy the contents of the extracted ZIP into the new directory.

Open an Administrator level, elevated command prompt, and change directory to the GoldenGate directory you created.

Run GGSCI and create the OGG subdirectories:

C:/Users/oggdba> cd C:/GoldenGate
C:/GoldenGate> ggsci.exe

GGSCI> CREATE SUBDIRS


Give the MGR process a custom name:

GGSCI> EDIT PARAM ./GLOBALS

MGRSERVNAME name-here

GGSCI> EXIT


Install the OGG Manager as a service, with some options:

C:/GoldenGate> install.exe ADDEVENTS
C:/GoldenGate> install.exe ADDSERVICE
C:/GoldenGate> install.exe AUTOSTART
C:/GoldenGate> install.exe ADDEVENTS


Restart your windows system and verify the OGG MGR starts on boot, verify this with:

GGSCI> INFO MGR



Create MSSQL Target Database, Schema, User and DSN:


This section will outline the basics of setting up the OGG Target DB and DSN, although this should be taken with some interpretation, use your own settings, permissions, naming schemes etc. as appropriate.

<<< On the TARGET SERVER >>>

Open SQL Server Management Studio, and create a new database to be used for storing your OGG replicated data set:



Create the new DB.



Name it something sensible.



In my experience, you MUST change the Collation (default character set) to “Latin1_General_BIN2”. Without this set, I usually run into issues trying to replicate certain Unicode characters in fields in the source DB.

Create SCHEMA within new DB:

Right click on your new DB, and select “New Query”, type:

CREATE SCHEMA “SCHEMA1”;


NOTE: “SCHEMA1” must be the name of your source SCHEMA that you are replicating.

Create the new User, and give SCHEMA ownership to user:

Right click “Security” in the SQL Instance branch (not within the Database), and select New Login.



Ensure SQL Server Authentication is used, and set a secure password. Select your recently created DB as the users default DB, and choose “British English” as the users default language.



Within “User Mapping”, check the DB you just created, and ensure “db_owner” is selected. Take this opporunity to ser the default SCHEMA to the SCHEMA you created earlier.



Create System DSN for use by OGG:

Open Control Panel, Administrative Tools, and open “ODBC Data Sources (64bit)”. Change tab to “System DSN” and click the ADD button.



Select “ODBC Driver 11 for SQL Server”, name your DSN something logical and simple, in this example “oggrepldsn”, select the local SQL Server instance from the drop down. Ensure you select to use SQL Server Authentication. Check the box to connect to SQL to obtain additional settings, use the user you created earlier.

On the next screen, change the default DB to the DB created earlier. Leave everything else untouched. And finish the DSN Wizard.


Configuring GGSCI and Preparing for Initial Data Load


<<< On the SOURCE SERVER >>>

Verify the manager is running OK:

[oracle@shell]# cd /u01/app/oracle/product/ogg_src
[oracle@shell]# ggsci

GGSCI> EDIT PARAM MGR

[Here you may add any additional manager options you want, by default, you only need the PORT parameter]

GGSCI> INFO MGR

[Verify the manager is running, you may also use START or STOP MGR]

Create Schema TRANDATA

GGSCI> DBLOGIN USERID <schema-user-here>
Password: <user-pass-here>
GGSCI> ADD TRANDATA SCHEMA1.*


Substitute “SCHEMA1” for your schema you wish to replicate.

NOTE: Use of “ADD TRANDATA” only adds TRANDATA for the tables specified by your selection after it. If you add new tables after this is generated, new tables will have no TRANDATA, and therefore will not be able to be replicated until TRANDATA has been added. This is fine for me and this example, however a more robust solution would be to use ADD SCHEMATRANDATA, which adds at schema level, rather than table level, and new tables within the schema, are automatically included in the TRANDATA.

Verify that the TRANDATA is added OK:

GGSCI> INFO TRANDATA SCHEMA1.*


Create source table definition parameters:

GGSCI> EDIT PARAM DEFGEN

DEFSFILE /u01/app/oracle/product/ogg_src/dirdef/<filename-here>.def, PURGE 
USERID <oracle-user> PASSWORD <oracle-user-password>
TABLEEXCLUDE SCHEMA1.TABLEA;
TABLEEXCLUDE SCHEMA1.TABLEB;
TABLE SCHEMA1.*;


Substitute a relevant .def file name into DEFSFILE parameter, you’ll need to use this later.

NOTE: In my example, I exclude some tables that I know I am not going to need in my replication. You may or may not want to do this. Be aware that you cannot generate definitions for externally organized tables (if you’re using them).

Generate the source table definitions using DEFGEN:

[oracle@shell]# cd /u01/app/oracle/product/ogg_src
[oracle@shell]# ./defgen paramfile dirprm/defgen.prm


This creates the .def file within ./dirdef/

The generated *.def file now needs to be transferred to the TARGET SERVER, and placed within $INSTALL_DIR/dirdef/


Configure Initial Data Load EXTRACT


These steps configure the initial load groups that will copy source data and apply it to the target tables.

<<< On the SOURCE SERVER >>>

Add the initial data load EXTRACT batch task group:

[oracle@shell]# cd /u01/app/oracle/product/ogg_src
[oracle@shell]# ggsci

GGSCI> ADD EXTRACT EINI9001, SOURCEISTABLE


NOTE: EINI9001 is created from the following format EINI<unique ID, max 4 digits>

Verify the EXTRACT created with the following:

GGSCI> INFO EXTRACT *, TASKS


Configure the initial data load EXTRACT PARAM file:

GGSCI> EDIT PARAMS EINI9001

--
-- GoldenGate Initial Data Capture
--
EXTRACT EINI9001
USERID <oracle schema user here>, PASSWORD <oracle schema password here>
RMTHOST <IP of TARGET SERVER here>, MGRPORT 7890
RMTTASK REPLICAT, GROUP RINI9001
TABLEEXCLUDE SCHEMA1.CAP_*;
TABLEEXCLUDE SCHEMA1.DR$*;
TABLE SCHEMA1.*;


<<< On the TARGET SERVER >>>

Add the initial data load REPLICAT batch task group:

GGSCI> ADD REPLICAT RINI9001, SPECIALRUN
GGSCI> INFO RINI9001*, TASKS
GGSCI> EDIT PARAMS RINI9001

-- 
-- GoldenGate Initial Data Load Delivery 
-- 
REPLICAT RINI9001 
TARGETDB oggrepldsn, USERID oggrepluser, PASSWORD <SQL user password here>
DISCARDFILE ./dirrpt/RINI9001.txt, PURGE 
SOURCEDEFS ./dirdef/<definition-file-name-from-earlier>.def OVERRIDE
SOURCECHARSET PASSTHRU
MAP SCHMEA1.*, TARGET SCHEMA1.*;


INTERLUDE – Getting to this point in the guide assumes you have created the relevant tables/DDL in your target MSSQL database. OGG EXTRACT and REPLICAT processes will not create tables for you within MSSQL, it expects them to be there to insert into on REPLCAT. There is no agreed method of how best to do this. Me personally, I export DDL from SQL Developer, and then spend a lot of time pruning that output for JUST the CREATE TABLE and KEY statements. Of course, you’re then left with a lot of DDL statements that are only good for use within Oracle. You’ll need to convert them into SQL that MSSQL understands. There are many ways to do this, there are premium paid for 3rd party tools, and there are also free online tools such as SQLLines. You could also do it manually if you didn’t have many tables, although I wouldn’t recommend that.

<<< On the SOURCE SERVER >>>

Start the initial data load EXTRAC process:

GGSCI> START EXTRACT EINI9001


View its progress with:

GGSCI> VIEW REPORT EINI9001


NOTE: There may be many errors to resolve on your first EXTRACT RUN, table names not existing, data type mismatches, column names not existing, permissions, network level restrictions such as firewalls etc.

Assuming the EXTRACT runs, REPLICAT will start on the TARGET SERVER, verify this, and its results, with the following on the TARGET SERVER:

GGSCI> VIEW REPORT RINI9001


If you have made it this far, you now have a DB in MSSQL with your Oracle data set in it, congrats! If that’s all you wanted, you can stop here, but most of the time, you will be aiming for live change data replication from Oracle. For this, we need to make use of a few more components of OGG.

Specifically, CDC and CDD. Change Data capture (via EXTRACT on SOURCE), and Change Data Delivery (via REPLICAT on TARGET). The next section explains how to do this.


Configuring Change Data Capture via EXTRACT


Through the use of trail files being shipped from SOURCE to TARGET, OGG can replicate changes in data detected at source (and written to the trail files). Here’s how to do that.

<<< On the SOURCE SERVER >>>

Add the EXTRACT group for CDC:

GGSCI> ADD EXTRACT EORA9001, TRANLOG, BEGIN NOW, THREADS 1


NOTE: “THREADS” is an integer of how many EXTRACT threads are maintained to read the differe4nt redo logs on the different Oracle Instance Nodes. If you are not running an Oracle Cluster, or RAC, then set this to 1, setting a higher value does not improve single instance performance.

Verify it created OK with:

GGSCI> INFO EXTRACT EORA9001


Configure the EXTRACT group for CDC:

GGSCI> EDIT PARAM EORA9001

--
-- Change Capture parameter file to capture
--
EXTRACT EORA9001
USERID <sql-user-name>, PASSWORD <oracle-user-password>
RMTHOST <target-server-IP-address>, MGRPORT 7890
RMTTRAIL ./dirdat/1p
TABLEEXCLUDE SCHEMA1.CAP_*;
TABLEEXCLUDE SCHEMA1.DR$*;
TABLE SCHEMA1.*;


NOTE: The 2 character (max) identifier at the end of RMTTRAIL is important, make it unique, and remember it for later.

Create the GoldenGate Trail:

GGSCI> ADD RMTTRAIL ./dirdat/1p EXTRACT EORA9001, MEGABYTES 5


Verify that it created OK:

GGSCI> INFO RMTTRAIL *


And verify the results:

GGSCI> INFO EXTRACT EORA9001, DETAIL 
GGSCI> VIEW REPORT EORA9001



Configuring Change Data Delivery via REPLICAT


The trail files defined earlier will be present on the TARGET server now, and they can be used by a CDD REPLICAT process to live replicate changed data from the TARGET.

On the TARGET SERVER

Edit Global PARAMs and create the checkpoint table:



Create REPLICAT checkpoint group:

GGSCI> ADD REPLICAT RMSS9001, EXTTRAIL ./dirdat/1p


NOTE: The two letter prefix for EXTTRAIL is the same as earlier.

Configure REPLICAT PARAM file for CDD:

GGSCI> EDIT PARAM RMSS9001

REPLICAT RMSS9001
TARGETDB oggrepldsn, USERID oggrepluser, PASSWORD <sql-user-password>
HANDLECOLLISIONS 
SOURCEDEFS ./dirdef/1pmoracle.def
DISCARDFILE ./dirrpt/RMSS9001.DSC, PURGE 
MAP SCHEMA1.*, TARGET SCHEMA1.*;


Start the REPLICAT process:

GGSCI> START REPLICAT RMSS9001


Verify it is running with:

GGSCI> INFO REPLICAT RMSS9001



Summary:


Providing everything is running without issue, you are now finished, and you have a love replication scenario shipping data from Oracle 12c in Oracle Linux 7, into MSSQL 2014, in Windows Server 2016. This will continue to run all the time that you have the EORA and RMSS processes running. The initial data load EXTRACT and REPLICAT of EINI and RINI are redundant, unless you happen to ever want to drop your whole data set from MSSQL and have it replicated from scratch again.

Some of the above processes may seem simple, however documentation on a lot of it is few and far between, and when it can be found within Oracle Documentation, it is not often easy to interpret. In our testing, I was able to see change data appear in TARGET after altering it in SOURCE around 1second after committing in SOURCE.

Please feel free to reach out to me with any questions you may have. I can’t promise I can answer them all, but I will do my best to assist if I can.


Got Something To Say:

Your email address will not be published. Required fields are marked *

*


Authors

Categories

Recent Posts

Review Us

Are you a VooServers customer? We would love to hear what you say!

Review us
Back to top

© VooServers Ltd 2016 - All Rights Reserved
Company No. 05598156