Introduction

This document will detail the Oracle GoldenGate (GGS) installation and configuration for Linux with an Oracle database as the source and Microsoft windows with a SQL Server database as the destination.

Windows installation and Configuration

Requirements

Oracle GoldenGate for Microsoft windows SQL Server requires

A full Oracle client installation

An SQL Server database user and schema

An odbc dsn setup for the MS SqlServer database.

Oracle Client Installation

Go to oracle technet -> downloads -> Oracle Database

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html .

Select “See all” next to the Microsoft Windows x64 (64-bit).

Select the Oracle Database Client for Microsoft Windows (x64) download.

Once the download has completed unzip the file and run setup.exe.

Select “Administrartor” and “Next”.

Select English and next.

Select “Use Windows Build-in Account”

Enter the Base and Software location directories.

For this installation we used “D:\Oracle\” and “D:\Oracle\Client”

Select Install

SQLServer Login and Schema

Login

Connect to the database as an administrator.

Right click Security and select New -> Login.

Add GUSER and select the relevant database as the default database; do not select OK.

Select User Mapping from the left hand menu and then:

Select the database from the top screen and db_owner from the bottom screen.

Select OK to exit.

Schema

With the database selected, choose New Query and execute the command:

create schema gguser;

Right click the Security option under the database and select new -> user

Enter a name for the user and select the “…” next to the Login name field.

Select Browse from the Select Login page, select the user we created and select OK.

Enter the schema name we created in the Default Schema box.

Select OK

ODBC Setup

Select Start > Settings > Control Panel.

Double-click Administrative Tools.

Double-click Data Sources (ODBC)

Select the System DSN tab and then select the Add button.

The Create New Data Source dialog box appears.

Select SQL Server and then Finish

Enter a name for the source, e.g. “GGToSQL”

Enter a description, e.g. “Oracle GG to MS SQL Server”

Enter the server name.

Select “Next”

Normally we would use a windows authenticated user but for this example we have added the sql server user GGUSER.

Use the drop down list to change the to the relevant database.

Select Next and then Finish.

GoldenGate Installation

Install

Unzip the download file provided by oracle into

D:\oracle\GoldenGate,

please note the directory name cannot include spaces.

Change to the windows directory

D:\oracle\GoldenGate

Enter

ggsci

At the ggsci prompt enter the commands

create subdirs

exit

Windows Service

The GoldenGate manager process is not installed as a windows service by default and must be manually added.

As a system administrator:

start -> run -> cmd

cd

instll ADDEVENTS ADDSERVICE AUTOSTART

This will start the service on system boot under the local system account and add events to the windows event manager.

GoldenGate Directory Setup

The create subdirs GoldenGate command has created the relevant directories under the GoldenGate home.

As the trail and discard files can grow quite large and we do not want to endanger other processes running of the D drive a separate volume was created for these files. We need to create the relevant directories on this volume.

cd G:

mkdir goldengatefiles

cd goldengatefiles

mkdir dirdat

mkdir dirrpt

GoldenGate Configuration

All commands will be run from the D:\oracle\GoldenGate directory

Credentialstore

Use a credential store to remove the requirement for passwords to be typed on the command line.

ggsci> add credentialstore

ggsci> alter credentialstore add user gguser password gguser alias gguser

This is the sql server database login chosen earlier.

 

Checkpoint table

$ ggsci

ggsci> edit params ./GLOBALS

CHECKPOINTTABLE gguser.chkptab

ggsci> exit

The exit and restart is required to reload the GLOBALS file.

Note the GL:OBALS file must be uppercase, in the top level directory (goldengate) and have no file extension.

$ ggsci

ggsci> dblogin sourcedb gguser useridalias gguser

ggsci> add checkpointtable

Manager service.

$ggsci

ggsci> edit params mgr

PORT 7908

DYNAMICPORTLIST 7908-7915

ACCESSRULE, PROG *, IPADDR ip1.ip1.ip1.ip1, ALLOW

SOURCEDB gguser

USERIDALIAS gguser

PURGEOLDEXTRACTS G:\goldengatefiles\dirdat\TP*, minkeephours 12

Start and stop the manager service using the windows services panel


Linux Installation and Configuration

GoldenGate Linux user

Create the oracle goldengate user as a member of the oracle database installation group; this will enable Classic capture mode should it ever be required.

useradd –g oinstall oraggs

mkdir /u01/app/oracle/ggs_12201

chown oracle:oinstall /u01/app/oracle/ggs_12201

chmod 775 /u01/app/oracle/ggs_12201

passwd oraggs

Log in as oraggs

Edit ~/.bash_profile and add the following:

export PATH=/u01/app/oracle/ggs_12201:$PATH

export LD_LIBRARY_PATH=/u01/app/oracle/ggs_12201:/lib:$LD_LIBRARY_PATH

alias ggs=”cd /u01/app/oracle/ggs_12201

Install goldenGate

Download to /tmp and then expand  the oracle GoldenGate zip file.

This unzips as /tmp/fbo_ggs_Linux_x64_shiphome.

Change directory to Disk1 and run the installer.

cd Disk1

./runInstaller

Select the 12c installation

Enter the correct locations.

 

 

Select Install

Select Close

GoldenGate Directory Setup

The GoldenGate directories have been created under the GoldenGate home.

As for the windows installation we should create directories on a separate volume for the GoldenGate trail and log files.

mkdir /u01/oracle/

mkdir GG_DIRECTORY

mkdir GG_DIRECTORY/dirdat

mkdir GG_DIRECTORY/dirrpt

Database Modifications

The oracle database should be in archivelog mode, logging and with flashback on.

SQL> select log_mode from v$database;

SQL> select flashback_on from v$database;

SQL> select force_logging from v$database;

If any are not in force then make the necessary changes.

Enable GoldenGate

SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true

Create the GoldenGate user

Sql> create user oraggs identified by oraggs default tablespace users temporary tablespace temp;

Sql> grant create session, connect, resource, dba to oraggs;

Sql> alter user oraggs quota unlimited on users;

Sql> grant execute on dbms_goldengate_auth to oraggs;

Sql> grant execute on dbms_flashback to oraggs;

Sql> exec dbms_goldengate_auth.grant_admin_privilege(‘ORAGGS’);

Sql> alter pluggable database add supplemental log data;

Sql> alter system switch logfile;

GoldenGate Configuration

All commands will be run from the GG_DIRECTORY directory as oraggs.

The commands all assume the oraggs user profile has been executed to define the oracle database home and sid environment variables.

Credentialstore

Use a credential store to remove the requirement for passwords to be typed on the command line.

ggsci> add credentialstore

ggsci> alter credentialstore add user oraggs password oraggs alias oraggs

Checkpoint table

$ggsci

ggsci> edit params ./GLOBALS

CHECKPOINTTABLE oraggs.chkptab

ggsci> exit

The exit and restart is required to reload the GLOBALS file.

Note the GLOBALS file must be uppercase, in the top level directory (goldengate) and have no file extension.

$ ggsci

ggsci> dblogin useridalias oraggs

ggsci> add checkpointtable

Manager service.

$ggsci

ggsci> edit params mgr

PORT 7908

DYNAMICPORTLIST 7909-7915

ACCESSRULE, PROG *, IPADDR ip2.ip2.ip2.ip2, ALLOW

USERIDALIAS oraggs

PURGEOLDEXTRACTS GG_DIRECTORY/dirdat/TP*, minkeephours 12

Start and stop the manager service

ggsci> stop mgr

ggsci> start mgr

Trandata

On the source oracle goldengate server

ggsci> dblogin useridalias oraggs

ggsci> ADD TRANDATA .*;

ggsci> exit

Oracle to SQLServer initial load

Mapping tables by name

SQLServer setup

$ ggsci

ggsci> add replicat RnameINI, SPECIALRUN

ggsci> edit params RnameINI

REPLICAT RnameINI

TARGETDB gguser, USERIDALIAS gguser

discardfile G:\goldengatefiles\INIFAIL.dsc, APPEND

MAP ., TARGET .;

: : :

MAP ., TARGET .;

ggsci> start replicat RnameINI

N.B. In the above command we are naming all the tables to be processed

Oracle Setup

As oraggs

$ggsci

ggsci> add extract EnameINI, SOURCEISTABLE

ggsci> edit params enameINI

EXTRACT EnameINI

USERIDALIAS oraggs

RMTHOST ip2.ip2.ip2.ip2, mgrport 7908

RMTTASK REPLICAT, GROUP RnameINI

TABLE .;

:

TABLE .;

The tables listed are the same as those in the RnameINI parameter file used on the Windows server.

 

ggsci> start extract EnameINI

ggsci> info all

ggsci> send extract EnameINI, report

 

n.b. If the extract completes quickly then the info all and reports will both show that the extract is not currently running, this is normal but you should check the ggserr.log file.

Oracle to SQLServer Change Delivery

Oracle Setup

As oraggs

$ggsci

ggsci> add extract Ename1, SOURCEISTABLE

ggsci> edit params Ename1

EXTRACT Ename1

USERIDALIAS oraggs

RMTHOST ip2.ip2.ip2.ip2, mgrport 7908

RMTTASK REPLICAT, GROUP Rname1

TABLE .;

:

TABLE .;

 

The tables listed are the same as those in the Rname1 parameter file used on the Windows server.

 

ggsci> start extract Ename1

ggsci> info all

ggsci> send extract Ename1, report

SQLServer setup

$ ggsci

ggsci> add replicat Rname1, exttrail G:\GoldenGatefiles\dirdat\PP

ggsci> edit params Rname1

REPLICAT Rname1

TARGETDB gguser, USERIDALIAS gguser

discardfile G:\goldengatefiles\INIFAIL.dsc, APPEND

MAP ., TARGET .;

: : :

MAP ., TARGET .;

Oracle Setup

As oraggs

$ggsci

ggsci> add extract Ename2, integrated tranlog, begin now

ggsci> register extract Ename2

ggsci> edit params Ename2

EXTRACT Ename2

USERIDALIAS oraggs

EXTTRAIL ./dirdat/TP

TABLE .;

:

TABLE .;

 

ggsci> add exttrail GG_DIRECTORY/dirdat/TP, extract Ename2

ggsci> add extract Pname1, exttrailsource GG_DIRECTORY/dirdat/TP

ggsci> register extract Pname1

ggsci> edit params Pname1

EXTRACT Pname1

USERIDALIAS oraggs

RMTHOST ip2.ip2.ip2.ip2, mgrport 7908

RMTTRAIL G:\goldengatefiles\dirdat\PP

TABLE .;

:

TABLE .;

 

ggsci> add rmttrail G:\GoldenGatefiles\dirdat\PP, extract Pname1,  megabytes 10

 

ggsci> start extract Ename2

ggsci> start extract Pname1

SQLServer

ggsci> start replicat Rname1

 

Changes made to the source tables should now be replicated to the destination tables.

Report Commands

Here are some of the commands to generate reports.

ggsci> info all

 

ggsci> send extract Ename1, report

ggsci> view report Ename1

 

ggsci> send extract Pname1, report

ggsci> view report Pname1

 

ggsci> send replicat Rname1, report

ggsci> view report rname1

 

ggsci> info extract Ename1

ggsci> info exttrail *

 

n.b. For initial load extracts if it completes quickly then the info all and reports will both show that the extract is not currently running, this is normal but you should check the ggserr.log file.


 

Security

For encryption of the trail files both on the system and in transit between the systems we will use wallets.

Master Key and Wallet

Create the master key and wallet.

$ ggsci

ggsci> create wallet

Created wallet at location ‘dirwlt’.

Opened wallet at location ‘dirwlt’.

ggsci> add masterkey

Master key ‘OGG_DEFAULT_MASTERKEY’ added to wallet at location ‘dirwlt’.

ggsci> info masterkey

Masterkey Name:                 OGG_DEFAULT_MASTERKEY

Creation Date:

Version:        Creation Date:                  Status:

1 Current

ggsci> info masterkey version 1

Masterkey Name:                 OGG_DEFAULT_MASTERKEY

Creation Date:

Version:                        1

Renew Date:

Status:                         Current

Key Hash (SHA1):                0…6

 

Copy the wallet “dirwlt/cwallet.sso’ to the windows server.

Parameter files

For the extract group parameter file (e.g. ESRCDST1) add the following line:

ENCRYPTTRAIL AES256

For the pump group parameter file (e.g. PSRCDST1) add the following line:

RMTHOSTOPTIONS ENCRYPT AES256

 

Restart all extracts and the files should now be encrypted. As long as the wallet has been copied to the dirwlt directory on the windows server decryption is automatic.

 

Â