The Oracle Transparent Gateway

In this article I will trace the chain of events in establishing a link from an Oracle database to a non-Oracle database, using the Oracle Transparent Gateway. We will see how each configuration point in the chain points to the next, establishing all the links in the chain for an Oracle database to act as a client to a non-Oracle database. I will also show a high-level comparison of how the same functionality is implemented in the reverse situation, where SQL Server is connecting to Oracle.

I first developed this presentation as a response to a statement made by a colleague, regarding the supposed complexity of Oracle. He insisted that Oracle “has too many parts” whereas SQL Server “just automatically connects”. I had to show that regardless of database product, heterogeneous connectivity comes down to the same fundamentals. It is just that various RDBMS products package those functions differently. Before getting into the specifics, let’s take a look at those fundamentals.

Fundamental Number 1: Every database product has its own unique call interface.

This is much deeper than just implementation of the SQL language. It involves the program calls at the binary executable level. Any connection between any two disparate database products must pass through a process to make the necessary translation, to meet the specifications of the receiving binary API. This is the reason ODBC was developed in the first place. If database “A” attempts to connect to database “B”, database “A” is no different than any other client program attempting to connect to database “B”. As far as “B” is concerned, “A” is “just another client” and must be able to communicate with “B”s API.

  • Oracle provides the Transparent Gateway to allow its databases to connect to non-Oracle databases.
  • Microsoft provides OLE DB Providers to allow its databases to connect to non-MSSQL databases.

Fundamental Number 2: All communication relies on established network protocols

All network communication conforms to the Open Systems Interconnection (OSI) model. In the OSI model, communication between separate computers occurs in a stack-like fashion with information passing from one node to the other through several layers of code. These layers are as follows:

  1. Physical layer
  2. Data link layer
  3. Network layer
  4. Transport layer
  5. Session layer
  6. Presentation layer
  7. Application layer

On the client side the communication request enters the OSI stack at the application layer and leaves the client  computer at the physical layer. The request arrives at the target (server) computer at the physical layer and works its way back up the stack to the application layer. Further discussion of the relationship between Oracle’s networking components and their place in the OSI model can be found in the Oracle Database Net Services Administrator’s Guide.

Oracle / MSSQL Database Communication Overview

  • Oracle provides translation to MSSQL via the Oracle Transparent Gateway.
  • MSSQL provides translation to Oracle via OLE DB providers.
  • Oracle Gateway and OLE DB Provider serve the exact same purpose. Both come with either a direct host-to-target version, or a generic version that uses ODBC as an intermediary.
Oracle-MSSQL Communication Overview
Overview of Heterogeneous Database Connections

For the remainder of this article I will show how each link of the configuration points to the next.  While I will be demonstrating with an Oracle database connecting to a Microsoft SQL Server database (MSSQL), you can just as easily substitute any other non-Oracle database for the MSSQL references.

SQL statement references a database link

In Oracle’s implementation of the SQL language, the “@” symbol following a table name reference indicates that the table is in another database. The string following the “@” symbol is the name of a database link (db_link).  That link provides the first step from the current Oracle database to the MSSQL database.

Oracle Transparent Gateway - sql to dblink
SQL Query to Database Link

Database link to tnsnames.ora

When the SQL engine locates the referenced db_link, the value of the ‘hosts’ column is used as the SQL*Net connection string.  This can be either a fully formed network address description or a SQL*Net Net Service Name.  If the latter (recommended) it will be resolved just like any other client connection request – usually by looking it up in the tnsnames.ora file.  It is important to note that the Oracle database itself is acting as a client, so it will be the tnsnames.ora located on the Oracle host server.

dblink to tnsnames
Database Link to Names Resolution

Network Transmission

When sqlnet receives the request from the client, it resolves the actual addressing information from tnsnames.ora, creates an IP packet, and hands it off to the protocol layer of the OSI stack.

prep_for_transmission
Oracle TNS Network Transmission

Listener Receives IP Transmission

When the listener receives the packet, it sees that the specified SID matches an entry in its SID_LIST configuration – the SID_LIST section of listener.ora.  And in that SID_DESC specification, there is a specified program to be launched – dg4odbc.  Instead of attempting to spawn a server process to connect the client to a database, the listener will launch the executable db4odbc and pass the name of the requested SID.

Listener Receives Packet
Listener Receives Packet

Oracle Gateway Bridges to ODBC

When the program otg4odbc receives the request, it uses the specified SID as the basis for identifying the necessary configuration file.  In our example, the SID was specified as ‘northwind’, so the name of the configuration file is ‘initnorthwind.ora’.  This file, in turn, provides the ODBC Data Source Name (DSN) used to actually access the target database.

Oracle OTG to ODBC
OTG Links to specified ODBC driver.

At this point the final leg of the journey is made by the ODBC driver specified when defining the DSN ‘northwind’.  Configuration of the ODBC DSN is as appropriate for the target database.

Once these connections are made, the final communications path is as follows.  The originating database, through its db link, communicates with the Oracle Transparent Gateway for ODBC, via a sqlnet link.  The gateway communicates with the ODBC driver. And the ODBC drive communicates with the target database.

Final Oracle Transparent Gateway to MSSQL chain
Complete Communication Chain

Conclusion

If you have followed this carefully, you should have a much better understanding of how the pieces fit together.  There are a few points that bear highlighting:

  1. We used the database name ‘northwind’ throughout. While it is nice to have the same name at every point, you need to be aware of where the same name is required between two points and where one usage is just an alias for the next.  For instance, at the very beginning we had a database link named ‘northwind’ that referenced net service name (tnsnames.ora entry) named ‘northwind’.  Technically, we could have made the net service name something else. Oracle would not care.  It is just for our own sanity as humans that we keep the same name throughout.
  2. The listener is only involved in getting the server process (otg4odbc) started and informing the calling client what port to use for continued communications.  Once that is done, the listener is out of the picture.  The listener’s port (1521, by default) is only used to contact the listener. It is not used for continuing communication between the client database (Oracle) and the target database.
  3. The Oracle Transparent Gateway can reside on any server.  It is not required that it be on the same server as the originating Oracle database. Nor is it required that it be on the same server as the target non-Oracle database.  In my shop, we have multiple Oracle databases residing on multiple servers.  Each database is subject to having links to multiple MSSQL databases, also residing on any of several different servers.  To simplify administration, I selected one server to host the Oracle Transparent Gateway for all combinations. This could be one of the Oracle servers. Or it could be one of the MSSQL servers.  Or it could be another server that hosts no database at all.

Questions? Feel free to ask.  My next article will explain the same process in reverse – when an MSSQL database uses its linked server mechanism to act as a client to an Oracle database.

Film at eleven . . .

Please like us:

5 Comments


  1. // Reply

    Your HS=OK clause should be in the description no?


    1. // Reply

      Everything is in DESCRIPTION=(). Take a look at the parings of the parenthesis. Maybe this format and breakout will make it a bit more clear:

      NORTHWIND =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)
                     (HOST = oratghose)
                     (port = 1521)
          )
          (CONNECT_DATA =
            (SID = northwind)
          )
          (HS = OK)
        )
      

      For reference, look at the Oracle Database Net Services Reference.


  2. // Reply

    I need to connect to FoxPro .dbf tables. I set up the DSN as Free Table Directory…How do I setup the Database Link? Since I’m not really logging on to a database as scott/tiger just looking at files in the directory.


    1. // Reply

      I haven’t worked with FoxPro in over 20 years, and well before I had any dealings with Oracle. The idea of ‘just looking at files in the directory’ seems a bit odd for any database product, as the files have a binary structure that is known only to the database management system to which they belong. I just Googled “odbc connection to fox pro” and there are several hits that tell how to set up the ODBC data source for FoxPro.

      Your statement about ‘not logging on to a database’ is equally strange. If you are not logging on to an Oracle database (doesn’t matter if it’s scott or beetlebaily) then the whole idea of setting up a database link and connection through the Oracle Transparent Gateway is a moot point. I’d be happy to help with this but you need to explain a bit more.

      What I can say is this: If you start with getting the ODBC DSN for your FoxPro database set up and working, then everything else I wrote is just the same. Keep in mind that the “oracle stuff” is from the Oracle database, through the Oracle Gateway to the ODBC DSN. Once it gets to ODBC, it doesn’t really know or care what database product is on the other side of that.

Leave a Reply

Your email address will not be published. Required fields are marked *