8 minute read

Oracle Gateway for MSSQL DG4MSQL configuration

Environment

  • MSSQL database on Docker 2022-latest
  • Oracle database 12.2
  • Oracle Database Gateway 19.3
  • VirtualBox

References

  • How to Configure DG4MSQL (Database Gateway for MS SQL Server) on a 64bit Windows post install (Doc ID 1086365.1)
  • https://rkkoranteng.com/2021/09/20/oracle-database-gateway-19c-deployment-for-sql-server/
  • http://oracle-help.com/oracle-database/oracle-database-gateway-microsoft-sql-server/
  • https://oracle-base.com/articles/misc/heterogeneous-services-generic-connectivity

Synaposis

The Oracle Database Gateway for MSSQL comes on a separate CD. It can be installed into an existing ORACLE_HOME(attention: if the ORACLE_HOME contains an already patched release of the database, you MUST apply the patchset agian.) Recommended installation is to install into a separate home under the same ORACLE_BASE

Overview installation and configuration steps:

  • MSSQL installation
  • Oracle database setup
  • Software installation
  • Software configuration
  • Listener configuration
  • tnsnames.ora file configuration
  • initdg4msql.ora file configuration
  • Database Link creation
  • Testing the connection

MSSQL server configuration

  • Get the Microsoft SQL Server docker image
root@wls:~# sudo docker pull mcr.microsoft.com/mssql/server:2022-latest
2022-latest: Pulling from mssql/server
9e92253e66cd: Pull complete
03f9a33f72ef: Pull complete
889f53a1308c: Pull complete
Digest: sha256:ea5e3a6dd0535fadeccfc2919a33d81bf9f48f1581681a1454399bce0dd88ba5
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2022-latest
mcr.microsoft.com/mssql/server:2022-latest
  • Create docker container instance
root@wls:~# sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=xxxxxxxx" \
  -p 143>    -p 1433:1433 --name mssql1 --hostname mssql1 \
>    -d \
>    mcr.microsoft.com/mssql/server:2022-latest
978dfb61e2439049e7edb7609b25168db5fac2db16452825667b916e1e7d0b73
root@wls:~#
root@wls:~#
root@wls:~#
root@wls:~# docker ps -a
CONTAINER ID   IMAGE                                        COMMAND                  CREATED         STATUS         PORTS                                                                 NAMES
978dfb61e243   mcr.microsoft.com/mssql/server:2022-latest   "/opt/mssql/bin/perm…"   4 seconds ago   Up 3 seconds   0.0.0.0:1433->1433/tcp                                                mssql1
  • Execute command in MSSQL docker container
root@wls:~# docker exec -t mssql1 cat /var/opt/mssql/log/errorlog | grep connection
2022-09-08 12:15:48.38 Server      The maximum number of dedicated administrator connections for this instance is '1'
2022-09-08 12:15:49.90 Server      Dedicated admin connection support was established for listening locally on port 1434.
2022-09-08 12:15:49.91 spid54s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2022-09-08 12:15:52.22 spid62s     Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
  • Query MSSQL database
root@wls:~# sudo docker exec -it mssql1 "bash"
mssql@mssql1:/$ bash
mssql@mssql1:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "xxxxxxxx"
1> CREATE DATABASE TestDB;
2>
3>
4>
5> SELECT Name from sys.databases;
6>
7>
8>
9> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

(5 rows affected)
  • Create demo table
1> USE TestDB;
2> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT);
3> INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
4> go
Changed database context to 'TestDB'.

(1 rows affected)

(1 rows affected)
1>
2>
3>
4>
5> SELECT * FROM Inventory WHERE quantity > 152;
6> GO
id          name                                               quantity
----------- -------------------------------------------------- -----------
          2 orange                                                     154

(1 rows affected)
1> SELECT * FROM Inventory WHERE quantity > 152;
2>
3> go
id          name                                               quantity
----------- -------------------------------------------------- -----------
          2 orange                                                     154

(1 rows affected)
1> go
1> SELECT * FROM Inventory WHERE quantity > 152;
2> go
id          name                                               quantity
----------- -------------------------------------------------- -----------
          2 orange                                                     154

(1 rows affected)
1> exit;
2> quit;
3>

Oracle Databaes configuration

Oracle VirtualBox Developer Day existing image

Step 1 Oracle Database Gateway installation

[oracle@localhost ~]$ cd Downloads/
[oracle@localhost Downloads]$ ls -ltr
total 983864
drwxr-xr-x. 5 oracle oinstall         85 Apr 17  2019 gateways
-rwxrwx---. 1 oracle oinstall 1007154302 Sep  8 12:27 LINUX.X64_193000_gateways.zip
-rwxrwx---. 1 oracle oinstall     317816 Sep  8 14:10 jtds-1.3.1.jar
[oracle@localhost Downloads]$ cd gateways
[oracle@localhost gateways]$ ls -ltr
total 24
-rwxrwxr-x.  1 oracle oinstall  500 Feb  6  2013 welcome.html
-rwxr-xr-x.  1 oracle oinstall 8850 Apr 17  2019 runInstaller
drwxr-xr-x.  4 oracle oinstall 4096 Apr 17  2019 install
drwxrwxr-x.  2 oracle oinstall   35 Apr 17  2019 response
drwxr-xr-x. 16 oracle oinstall 4096 Apr 17  2019 stage
[oracle@localhost gateways]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB.   Actual 14481 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-09-08_02-14-08PM. Please wait ...[oracle@localhost gateways]$ The response file for this session can be found at:
 /u01/app/oracle/product/12.2/gw_mssql/install/response/tg_2022-09-08_02-14-08PM.rsp

You can find the log of this install session at:
 /u01/installervb/logs/installActions2022-09-08_02-14-08PM.log

[oracle@localhost gateways]$
[oracle@localhost gateways]$

Step 2 Oracle Database Gateway software configuration

TO-DO

Step 3 Listener Configuration

[oracle@localhost gateways]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 08-SEP-2022 14:34:01

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                08-SEP-2022 13:59:26
Uptime                    0 days 0 hr. 34 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully
  • Update listener.ora file
[oracle@localhost admin]$ pwd
/u01/app/oracle/product/12.2/db_1/network/admin
[oracle@localhost admin]$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 1441 Aug 28  2015 shrept.lst
drwxr-xr-x. 2 oracle oinstall   61 Jun 12  2017 samples
-rw-r--r--. 1 oracle oinstall   53 Jun 12  2017 sqlnet.ora
-rw-r--r--. 1 oracle oinstall  734 Sep  8 15:42 tnsnames.ora
-rw-r--r--. 1 oracle oinstall  820 Sep  8 15:43 listener.ora
[oracle@localhost admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orcl12c)

SID_LIST_LISTENER_GW =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4msql)
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/gw_mssql)
    )
  )

LISTENER_GW =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1531))
    )
  )

[oracle@localhost admin]$
  • Start LISTENER_GW
[oracle@localhost admin]$ ps -ef | grep tns
root        23     2  0 13:58 ?        00:00:00 [netns]
oracle   21907     1  0 17:55 ?        00:00:00 /u01/app/oracle/product/12.2/db_1/bin/tnslsnr LISTENER -inherit
oracle   22033 13994  0 17:58 pts/1    00:00:00 grep --color=auto tns
[oracle@localhost admin]$ env | grep ORA
ORACLE_UNQNAME=orcl12c
ORACLE_SID=orcl12c
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2/gw_mssql
[oracle@localhost admin]$ env | grep TNS
[oracle@localhost admin]$ export TNS_ADMIN=/u01/app/oracle/product/12.2/gw_mssql/dg4msql/admin
[oracle@localhost admin]$ lsnrctl start LISTENER_GW

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-SEP-2022 18:00:32

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

TNS-01106: Listener using listener name LISTENER has already been started
  • Reload listener
[oracle@localhost admin]$ lsnrctl reload LISTENER_GW

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-SEP-2022 18:29:50

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
The command completed successfully
[oracle@localhost admin]$ ps -ef | grep tns
root        23     2  0 13:58 ?        00:00:00 [netns]
oracle   21907     1  0 17:55 ?        00:00:00 /u01/app/oracle/product/12.2/db_1/bin/tnslsnr LISTENER -inherit
oracle   22385     1  0 18:08 ?        00:00:00 /u01/app/oracle/product/12.2/gw_mssql/bin/tnslsnr LISTENER_GW -inherit
oracle   22957 13994  0 18:29 pts/1    00:00:00 grep --color=auto tns

Step 4 tnsnames.ora file configuration

[oracle@localhost admin]$ pwd
/u01/app/oracle/product/12.2/db_1/network/admin
[oracle@localhost admin]$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 1441 Aug 28  2015 shrept.lst
drwxr-xr-x. 2 oracle oinstall   61 Jun 12  2017 samples
-rw-r--r--. 1 oracle oinstall   53 Jun 12  2017 sqlnet.ora
-rw-r--r--. 1 oracle oinstall  734 Sep  8 15:42 tnsnames.ora
-rw-r--r--. 1 oracle oinstall  820 Sep  8 15:43 listener.ora
[oracle@localhost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

LISTENER_ORCL12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

dg4msql =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1531))
    (CONNECT_DATA =
      (SID = dg4msql)
    )
    (HS = OK)
  )
[oracle@localhost admin]$

Step 5 initdg4msql.ora file configuration

[oracle@localhost admin]$ pwd
/u01/app/oracle/product/12.2/gw_mssql/dg4msql/admin
[oracle@localhost admin]$ ls -ltr
total 28
-rw-rw-r--. 1 oracle oinstall   746 Jun  8  2007 dg4msql_tx.sql
-rw-rw-r--. 1 oracle oinstall 11120 Dec 17  2013 dg4msql_cvw.sql
-rw-rw-r--. 1 oracle oinstall   244 Sep  8 14:18 tnsnames.ora.sample
-rw-rw-r--. 1 oracle oinstall   409 Sep  8 14:18 listener.ora.sample
-rw-rw-r--. 1 oracle oinstall   362 Sep  8 18:29 initdg4msql.ora
[oracle@localhost admin]$ pwd
/u01/app/oracle/product/12.2/gw_mssql/dg4msql/admin
[oracle@localhost admin]$ cat initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=192.168.54.1:1433//TestDB
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

[oracle@localhost admin]$


[oracle@localhost admin]$ env | grep ORA
ORACLE_UNQNAME=orcl12c
ORACLE_SID=orcl12c
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2/db_1
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 8 15:03:10 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_id

CON_ID
------------------------------
1
SQL> create database link GW_LINK connect to "SA" identified by "xxxxxxxx" using 'dg4msql';

Database link created.

Testing the connection

[oracle@localhost oracle]$ . orane
bash: orane: No such file or directory
[oracle@localhost oracle]$ . oraenv
ORACLE_SID = [orcl12c] ?
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost oracle]$
[oracle@localhost oracle]$
[oracle@localhost oracle]$
[oracle@localhost oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 8 18:31:15 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from "inventory"@GW_LINK;

	id name 						quantity
---------- -------------------------------------------------- ----------
	 1 banana						     150
	 2 orange						     154

SQL> select * from "inventory"@GW_LINK;

	id name 						quantity
---------- -------------------------------------------------- ----------
	 1 banana						     150
	 2 orange						     154

SQL>

Troubleshooting

Error message

SQL> select * from "inventory"@GW_LINK
  2  ;
select * from "inventory"@GW_LINK
                          *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from GW_LINK

SQL> select * from dual@GW_LINK;
select * from dual@GW_LINK
                   *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from GW_LINK

You May Also Enjoy

Publish Custom Metrics using OCI CLI

4 minute read

I have covered how to use Java SDK to publish custom database metrics to OCI Monitoring service, folks who followed my blog How to Publish Custom Metrics to ...