Oracle DB Gateway 19c for SQL Server

I recently worked on a cross platform migration project to move financial Oracle db systems to SQL Server. Last minute dilemma in this project required some quick thinking. The environment leveraged an in-house application built on Oracle APEX (Application Express), which meant that in-house application cannot run on SQL SERVER. This application was essential to certain functionalities within the finance department and could not be retired, neither could we afford (development time + cost) to build an entirely new in-house application. I came up with the solution to implement Oracle Database Gateways to address the needs of such disparate data access.

What is Oracle Database Gateway ?

Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment.

Pre-requisite Tasks

Here’s some pre-requisite tasks/checks before deploying Oracle Gateway 19c for SQl Server

• Ensure you have X11 or some form of GUI access to the Oracle database host
• Check system requirements
• Download Oracle Database Gateway binaries from Oracle website. You’ll find the Gateway binaries on the same download page as the DB binaries…just scroll down. In using Oracle Database Gateway 19c for Solaris SPARC (64-bit) in this example

Install and Configuration Steps

Copy the Gateway zip file to oracle database host.

scp /tmp/SOLARIS.SPARC64_193000_gateways.zip TestOraHost:/tmp

SSH to Oracle database host as the oracle software owner, then change permissions on the Gateway software zip

chmod 777 /tmp/SOLARIS.SPARC64_193000_gateways.zip

Unzip the Gateway zip file in /tmp. It will create a directory called ‘gateways’.

unzip /tmp/SOLARIS.SPARC64_193000_gateways.zip -d /tmp/

Launch the Oracle Universal Installer (OUI) for Oracle 19c Database Gateway

/tmp/gateways/runInstaller

Specify the ‘Installation Location’, then click next. To comply with Oracle’s Optimal Flexible Architecture (OFA), ensure that the Gateway software location is within an existing Oracle base.

Select ‘Product Component’, then click next. In this case you’d want to select ‘Oracle Database Gateway for Microsoft SQL Server’.

Enter ‘Oracle Gateway to SQL Server’ details, then click next. Here’s code from my Github to retrieve the info needed for this page.

Wait as OUI wizard performs pre-requisite checks. It shouldn’t take long.

Check to make sure everything looks good on the ‘Summary’ screen, then click install.

Wait as OUI wizard performs installation. It shouldn’t take long.

The OUI wizard will invoke Oracle Net Configuration Assistant (NetCA), click next

Enter a name for the gateway listener, then click next

Select the network connection protocol, then click next. TCP should already be the selected protocol

Assign a connection port for the gateway listener. Port 1521 (default for oracle) may already be in use by your db listener, in which case Check the radio button to ‘Use another port number’ to enter the desired port number, then click next. Ensure the port being used is open/available on the database host.

Select ‘No’ radio button, then click next

Gateway listener creation is complete, click next

NetCa configuration is complete. Click finish and this should return you to OUI wizard

OUI wizard will prompt you to run root script. Open another ssh terminal, then connect as root user on the Oracle db host and run the ‘root.sh’ script. Click ok on the OUI wizard once the ‘root.sh’ script completes.

Gateway install is complete. Click close

Append your gateway listener.ora with to include SID_LIST. See the example below

GTWYLSNR =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = TestOraHost)(PORT = 1522))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
     )
   )
 SID_LIST_GTWYLSNR =
   (SID_LIST =
     (SID_DESC =
       (PROGRAM = dg4msql)
       (ORACLE_HOME = /usr/local/app/oracle/product/19.3/gateway_1)
       (SID_NAME = dg4msql)
       (ENVS = "LD_LIBRARY = /usr/local/app/oracle/product/19.3/gateway_1/dg4msql/driver/lib:/usr/local/app/oracle/product/19.3/gateway_1/lib")
     )

Reload the gateway listener after adding Heterogenous Service (HS) configs

lsnrctl reload GTWYLSNR

Append your tnsnames.ora (in the Oracle database home) with the following. Change the HOST and PORT details to fit your environment. See the example below

dg4msql =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = TestOraHost)(PORT = 1522 ))
     (CONNECT_DATA =
       (SID = dg4msql)
      )
     (HS = OK)
   )

Test the newly configured Transparent Network Substrate

tnsping dg4msql

Connect as to your desired Oracle database instance. In this example I’m connecting to a database called ‘testoradb’ as a user called ‘richard’

setenv ORACLE_SID testoradb
echo $ORACLE_SID
sqlplus richard

Create Heterogeneous Service (HS) db link to connect Oracle to SQL Server. In the example below, I’m creating an HS db link called ‘GTWY_LINK’ from Oracle db to my target SQL Server schema is called ‘APPUSER’ and it’s password is ‘welcome1’. Change the details ass needed for your environment.

create database link GTWY_LINK connect to "APPUSER" identified by "welcome1" using 'dg4msql';

Test the HS db link. You can now use Oracle to directly query tables in SQL Server.

select * from dual@GTWY_LINK