Background
Sometimes, we have heterogeneous database services in our system, we expect to transparently access data residing in a non-Oracle system from an Oracle environment. In this post, we will discuss how to use the Oracle Database Gateway to provide the ability to make this done.
Exactly speaking, we will install Oracle Database Gateway 19c on an OCI Database System 19c and make a connection to an existing Microsoft SQL Server 2016.
Prerequisites
Assuming we already have the following service/server.
-
OCI Database System 19c
-
MS SQL Server 2016
Setup VNC server in OCI Database System
Pre-check
We are going to use Oracle Universal Installer to install the Oracle Database Gateway into the OCI Database System, so we need to setup VNC server on the OCI Database System first.
Login to the OCI Database System with default opc user and check the Linux version.
Since the base image of OCI Database System 19c is Oracle Linux 7.5, and Server with GUI package is already installed. So we do not need to install it.
Configure VNC server
Switch to oracle user and setup password for the VNC server.
su - oracle
vncpasswd
Start the VNC server and check the port number which VNC server is using.
vncserver
vncserver -list
netstat -ltp4 | grep 590
Configure iptables and VCN security list
iptables
By default, VNC server port 590X is not allowed by the OCI Database System iptables, so we need to perform following commands to open the port 5901.
# check the iptables
iptables -L
# allow the inbound of port 5901
iptables -I INPUT 6 -p tcp -m state --state NEW -m tcp --dport 5901 -j ACCEPT
VCN Security List
We also need to ensure the port number 5901 is not blocked by the security list of the VCN(Virtual Cloud Network) which the OCI Database System resides in.
For simplicity, I allow all the ingress traffic to this VCN.
Connect from VNC Viewer
On your desktop, try to connect to the OCI Database System from the TigerVNC Viewer
Input the password and click OK
You should be able to see the OCI Database System terminal in the VNC Viewer as below.
Install Oracle Database Gateway
Download
You can download Oracle Database Gateway 19c from here.
https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html
Then transfer the Oracle Database Gateway 19c installation package to the OCI Database System.
Install
Open VNC Viewer, move to where you placed the Oracle Database Gateway installation package and unzip it.
Go the unzipped directory gateway and execute the ./runInstaller file
Oracle Universal Installer interface will pop up and we can click Next to install gateway to the default Oracle home directory
Select Oralce Database Gateway for Microsoft SQL Server and click Next.
Now we need to input some critical information of the target SQL Server.
- Host Name: public IP address of the SQL Server or the hostname
- Port Number: 1433 is the default port number of SQL Server, if yours is using any other port, just change it
- Server Name: the instance name of the SQL Server, you can get it from SQL Server Configuration Manager as shown below
- Database Name: the database name you want to connect to
SQL Server instance name
Review the summary and click Install
Execute the script as root user
Once you executed the script, back to VNC Viewer and click OK, you will see the successful message.
Until now, the Oracle Database Gateway 19c is installed.
Configure the Oracle Database Gateway
Pre-check
Login to the OCI Database System and switch to oracle user.
Go to following directory and start to configure the Oracle Database Gateway.
# go to the directory
cd $ORACLE_HOME/dg4msql/admin
# have a look at the gateway initialization file
cat initdg4msql.ora
Everything looks good, all the required SQL Server information are reflected here.
Open file tnsnames.ora.sample and listener.ora.sample, we will use the contents to make the configuration.
Configure the tnsnames.ora
Make sure you are working as user oracle. Go to the directory $ORACLE_HOME/network/admin and update the tnsnames.ora file. Copy the content from tnsnames.ora.sample and paste it into tnsnames.ora, remember to change the HOST to be your hostanme.
Configure the listener.ora
OCI Database System is built as a single instance RAC, user grid is the owner of the listener, so we need to switch to user grid. Then copy the content(only the SID_LIST_LISTENER part) from listener.ora.sample and paste it into listener.ora.
Please check below screenshot.
# switch to user grid
sudo su - grid
# move to the listener directory
cd $ORACLE_HOME/network/admin
vim listener.ora
Reload the listeners and check the status. We can see that the listener dg4msql is registered and working well.
lsnrctl reload
lsnrctl status
Until now, the configuration of the Oracle Database Gateway to connect to a specific SQL Server is completed. Next, let's verify how it works.
Verify the Oracle Database Gateway
Login to the OCI Database System as user oracle and connect to the database as sys.
sudo su - oracle
sqlplus / as sysdba
-- change to pdb
alter session set container=pdb1;
-- create database link to the SQL Server
create public database link xucheng connect to "SQL_SERVER_USER" identified by "SQL_SERVER_PASSWORD" using 'dg4msql';
-- query table [trex] through the database link [xucheng]
select * from trex@xucheng;
The database link is created as an object in the database, so you can use it any time unless you drop it.
In the sample query above, trex is a table in SQL Server database xucheng. And the xucheng in the sample query is the database link we just created. We use this database link to connect to the database in SQL Server. So the basic usage is select * from <TARGET_TABLE_NAME>@<DB_LINK>
In OCI Database System the global_names parameter is set to TURE by default, so you will get the ORA-02085 error.
You can set the global_names to FALSE to overcome the ORA-02085 issue at session level, or you can also alter this parameter at the system level.
ALTER SESSION SET global_names = FALSE;
select * from trex@xucheng;
As you can see in below screenshot, we can get the same query result as in SQL Server.
Now, we can query the SQL Server database from OCI Database System 19c instance.