Connecting Oracle 11g to RDB database with Oracle Heterogenous Services

One of my clients has been running an Oracle RDB database on VMS for decades.

This RDB database stores mission critical data and interfaces with COBOL applications and Oracle 8i databases.
(If you wonder what RDB is; it’s a RDBMS created by DEC in 1984 and bought by Oracle in 1994)

While modernizing the IT landscape, the client expressed the desire to reduce complexity and migrate the mission critical data in RDB to an Oracle 11g Standard Edition One database, yet keep the legacy Oracle 8i and COBOL applications up and running until they are ready to be replaced much later. While exploring the possibilities to create interfaces between 11g and the COBOL applications and 8i databases (and starting to feel more like historians then DBA’s) we soon realized the end result would probably make the IT landscape much more complex. Then our colleagues from Project Management had a brilliant idea; why not create a new Oracle 11g database to store the mission critical data, and then have that data replicated in the RDB database so it can still feed the legacy systems. With that setup we can continue using the existing interfaces to feed the legacy systems. Once all legacy systems have been replaced with new versions (which get connected to the 11g DB), we will be able to just switch off the RDB and 8i databases and COBOL applications. Sounds good in theory doesn’t it?

So then our main goal was just to create an interface between Oracle 11g and RDB. For the network communication layer on the RDB/VMS side we use Oracle SQL/Services configured with an OCI interface so we can make SQL*Net connections. This interface presents itself as / acts like an Oracle 7 database. Unfortunately there is too much version difference between Oracle 7 and Oracle 11 to support a direct DB link between them. We briefly experimented with adding an Oracle 9i in between to solve this although we weren’t very happy about that idea. Then our technical project lead had a brilliant idea; why not use Oracle Heterogeneous Services?

This is how we did it:

1) Create and configure a SQL/Service on the RDB VMS server
Although we could have just used one of our existing services, we defined a new one so we would be able to switch it on or off for just this part of the infrastructure.
(Note; we granted the use of this service to an identifier which was a specific requirement by the client. In other environments you might want to grant it directly to a specific user for simplicity’s sake)

SQLSRV> CREATE SERVICE 
AUTOSTART ON
REUSE_SCOPE_IS SESSION
SQL_VERSION 7.0
PROCESS_INITIALIZATION ''
ATTACH 
OWNER ''
DATABASE_AUTHORIZATION CONNECT_USERNAME
APPLICATION_TRANSACTION_USAGE SERIAL
IDLE_USER_TIMEOUT 300
IDLE_EXECUTOR_TIMEOUT 1800
MIN_EXECUTORS 0
MAX_EXECUTORS 10
CLIENTS_PER_EXECUTOR;

SQLSRV> GRANT USE ON SERVICE  TO IDENTIFIER ;

2) Install and configure an Oracle ODBC driver on the Oracle 11 database server

Next, after installing the Oracle ODBC driver (its a very basic install, just execute the setup and click next) we configure a system datasource using the service-name and userid from the SQL Service on VMS created in the previous step. Give the datasource a short but descriptive name, you’ll need it in the next step.

3) Configure an Oracle Heterogeneous Service

This is where the real magic starts to happen.

First we make a init.ora file with the initialization parameters for the Heterogeneous Service in the \hs\admin directory of the 11g DB server, where is the name of the datasource configured in step 2.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=C:\Windows\system32\SQRDB64.DLL

(obviously the last parameter is dependent on your OS.)

Next, add the datasource to the listener.ora file, using the datasource name as SID_NAME, and the RESTART the listener for the changes to take effect:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = )
      (ORACLE_HOME = )
      (PROGRAM = dg4odbc)
    )
  )

Finally, add an alias to the TNSNAMES.ORA file on the 11g database server

ALIAS =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA= (SID=))
    (HS=OK)
  )

That did the trick! We are now able to make a DBlink in the 11g databases directly to the RDB database on VMS. Many thanks to Oracle for making such a powerful feature freely available in Standard Edition One, and to Toon Koppelaars for solving this puzzle so elegantly.

Some final notes; be aware that the DBlink using this heterogeneous service doesn’t support 2 phase commit, so you might want to build some data integrity checks yourself. Also, different character-sets might cause some headaches during the configuration. We were lucky enough our RDB database is as ASCII as it gets. Otherwise the HS configuration-file in step 1 might need some extra parameters specifying the character-sets used.