Gateway to the Golden Gate

For Oracle Golden Gate; the gold standard is the Oracle Golden Gate Studio.

The setup will be:

• JDK and Golden Gate Studio installed in a Windows workstation.
• Repository for Golden Gate Studio installed in a Linux VM (note — can’t connect to an Autonomous Database).

However there are a few curve balls in the installation and setup, that the Oracle Golden Gate Studio documentation doesn’t cover.

With this article, you will be put back on the yellow-brick road again.

Setup a repository

Golden Gate Studio does need a repository for metadata, but it doesn’t even need to be an conventional Oracle product!

It is possible to use Oracle MySQL, IBM DB2, or Microsoft SQL Server for the repository for Golden Gate Studio.

However for this exercise, for the Golden Gate Studio repository, we’ll use an Oracle 19c database — as it is a long term release.

In addition, for this exercise we’ll use a Vagrant Oracle 19c database, to leaver the ease of setup. The Oracle Vagrant project will be used for this purpose.

GitHub - oracle/vagrant-projects: Vagrant projects for Oracle products and other examples

After cloning the project, we will need to download the database for Oracle 19c. ie LINUX.X64_193000_db_home.zip.

The database file will also need to be placed in the appropriate directory. ie vagrant-projects\OracleDatabase\19.3.0\.

Oracle Database Software Downloads

Before you are permitted to download, you’ll need to accept the Oracle License Agreement.

After the download, verify that the 19c database file is present in the correct directory. Use the Git Bash terminal (ie Mintty), or your favourite.

ls -alh

So now we are ready to start building.

vagrant up

Note that the local port forward includes 1521, so we’ll be able to connect to the database instance from any tool from the Windows host. eg SQL Developer, Golden Gate Studio, sqlplus, etc.

At the tail of the install log you’ll see the password for the database administrator accounts.

So in this case the password is 2iJkGrK6naE=1

Ensure to retain the password, as we’ll need it for all login activity.

Also open SQL Developer just to confirm connectivity to the database.

Now open the DBA module within SQL Developer, and verify that the 19c database is operating.

This database is open, and will be used as a repository for the Golden Gate Studio.

Download and install the JDK8

The Java SE Development Kit is a prerequisite for Golden Gate Studio.

As we will install Golden Gate Studio 12.2.1.4.0 for Windows, then the latest JDK8 for Windows is what we need to install:

jdk-8u311-windows-x64.exe

Download it from here:

https://www.oracle.com/java/technologies/downloads/#java8-windows

Note — don’t download and install Java 17, as from experience it prevented Golden Gate Studio 12.2.1.4.0 being installed.

For reference the JDK8 will be installed in the following directory:

“C:\Program Files\Java\jdk1.8.0_311”

The installation of the latest JDK8 is straight forward, and the version returned from the following will be 1.8.0_311 (or later):

java -version

Download and install Oracle Golden Gate Studio

As the repository for Oracle Golden Gate Studio is ready, we can now download and install Oracle Golden Gate Studio.

Oracle GoldenGate Downloads

Now unzip the file using your favourite unzip utility. You should then see a jar file when you list the contents of the directory.

ls

To install Oracle Golden Gate from the jar file, we need to switch to the Windows command prompt.

In addition, we need to open the Windows command prompt as “run as administrator” to get the heightened administrator privileges.

Now install the jar file like this in the Windows command prompt:

"C:\Program Files\Java\jdk1.8.0_311\bin\java" -jar fmw_12.2.1.4.0_oggstudio.jar

If all goes well, then you’ll see the Studio installation application open up.

Just acknowledge all the defaults, as you go through each screen.

The Oracle Home Location will be located here: C:\Oracle\Middleware\Oracle_Home

The next stage is to create the schema for the repository.

Create the schema for the repository

Before we can use Oracle Golden Gate Studio, the repository schema needs to be created.

You must first create the required schemas by using the Repository Creation Utility (RCU) for Oracle Golden Gate Studio (Windows).

Unfortunately, the RCU script for Windows (C:\Oracle\Middleware\Oracle_Home\oracle_common\bin\rcu.bat) has some bugs, so change this line in the script :

IF EXIST %WLS_ORACLE_HOME% (
        SET INTERNAL_SCRIPTPATH="%WLS_ORACLE_HOME%\oracle_common\bin"

to the following:

IF EXIST %WLS_ORACLE_HOME%. (
        SET INTERNAL_SCRIPTPATH=%WLS_ORACLE_HOME%\oracle_common\bin

Also change this line :

CALL %ORACLE_HOME%%INTERNAL_SCRIPTPATH%\%INTERNAL_SCRIPT% %*

to the following:

CALL %INTERNAL_SCRIPTPATH%\%INTERNAL_SCRIPT% %*

With that fixed, we also first need some prerequisite environment variables setup in Windows as follows:

set JAVA_HOME=C:\Program Files\Java\jdk1.8.0_311
set ORACLE_HOME=C:\Oracle\Middleware\Oracle_Home

Now the RCU script can be run:

cd %ORACLE_HOME%
oracle_common\bin\rcu.bat

If all is well then acknowledge the defaults.

Then enter the connection details for the Oracle 19c database that was setup before.

Note1 — need to connect to the PDB (not CDB) which is named ORCLPDB1.

Note2 — also need to specify a user with sysdba (eg sys) as the RCU requires an execute privilege on the DBMS_LOCK package.

Next is to create the schemas for the repository.

The schema for the Oracle Golden Gate repository is ‘dev_ogsrepo’.

In the next screen you’ll get to choose the passwords for the main and auxiliary schema users, which will include ‘dev_ogsrepo’.

Now we need to choose the password for the supervisor user.

The password for the supervisor user must have a maximum length of no more than 12 characters.

The encryption algorithm will default to AES-128.

The next screen will display the default tablespaces (permanent and temporary) that will be used for the repository.

Again choose the defaults.

And the last screen will show the summary of what will be created.

After the creation, another summary screen will show the result.

From here the Golden Gate Studio should be ready for use.

But — I experienced the following issue when I tried to start Oracle Golden Gate Studio.

The solution was to copy the C:\Program Files\system32\msvcr100.dll file to the C:\Program Files\Java\jdk1.8.0_311\jre\bin\msvcr100.dll file.

Now Golden Gate Studio will start.

Next is the connect to the repository.

Note — the Studio Connection user must be upper-case. ie SUPERVISOR.

Test the connection, then ok.

Here you can choose a password for a wallet.

Or just for the exercise, choose not to secure the wallet.

Now you can login as the SUPERVISOR user.

And a successful login presents us with the Start Page.

More information on the end-to-end installation of Golden Gate Studio is here:

Contents

Paul Guerin has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.