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.

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.

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.

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.

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.

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.

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.

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 . . .

Advertisement