GG Installation
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.
Â
Â
Discussion ¬