ORACLE INTERNALS

For the second time in less than a year, I’ve seen an OTN forum posting regarding how/where to acquire Oracle’s Wire-Level Network Protocol documentation. As such, I’ll address a couple things here in my blog that I’ve posted on various locations on the Internet.

Where can I get Oracle’s Wire-Level Protocol Specification?
Oracle. Outside of Oracle, only a few wire-level driver vendors have it. Likewise, unlike the driver vendors, Oracle is the only one with permission to distribute it. And, before you ask, I’ve never seen the specification myself. All of my protocol knowledge is based on years of research and significant trial-and-error.

How much does access to it cost?
While I’ve never requested access to it, several years ago I was told by a somewhat reliable source that, in addition to being an Oracle Partner, you must license it under a fairly strict technology use contract at a minimum of $100,000 USD (depending on use). Though, as I said, this was several years ago and just something I’d heard; YMMV.

Where else can I find information (on the cheap)?
If you don’t mind a bit of research… the basics can be found in the Oracle® Database Net Services Administrator’s Guide (especially the older versions).

The Transparent Network Substrate (TNS) protocol is extremely simple and has been documented several places. But, the best location for information I would suggest, is the Wireshark TNS dissector source code:

• http://anonsvn.wireshark.org/wireshark/trunk/epan/dissectors/packet-tns.c
• http://anonsvn.wireshark.org/wireshark/trunk/epan/dissectors/packet-tns.h

Just for the fun of it, my own TNS packet type definitions (along with their naming conventions), are as follows:

/* ---------------------------------------------------------------------- */

/* ------------------ Network Substrate Packet Types -------------------- */

/*
 * The following constants are defined for each network substrate packet
 * type.
 */
#define NSPTCN          1                       /* NS Packet Type CoNnect */
#define NSPTAC          2                        /* NS Packet Type ACcept */
#define NSPTAK          3                   /* NS Packet Type AcKnowledge */
#define NSPTRF          4                        /* NS Packet Type ReFuse */
#define NSPTRD          5                      /* NS Packet Type ReDirect */
#define NSPTDA          6                          /* NS Packet Type DAta */
#define NSPTNL          7                          /* NS Packet Type NuLl */
#define NSPTAB          9                         /* NS Packet Type ABort */
#define NSPTRS          11                       /* NS Packet Type ReSend */
#define NSPTMK          12                       /* NS Packet Type MarKer */
#define NSPTAT          13                    /* NS Packet Type ATtention */
#define NSPTCNL         14                      /* NS Packet Type CoNtroL */
#define NSPTHI          19        /* NS Packet Type HIghest (upper-bound) */

If you’re looking to write a wire-level driver or packet dissector, you need to go quite a bit deeper than TNS. For that, you can get a good starting point at:

• http://anonsvn.wireshark.org/wireshark/trunk/epan/dissectors/packet-sqloracle.c
• http://anonsvn.wireshark.org/wireshark/trunk/epan/dissectors/packet-sqloracle.h

Can I just ask you question X?
Sure. It never hurts to ask. However, given the amount of time and effort I’ve put into researching the protocol, and the security factors involved, I may not answer. It just depends on who you are, what you’re asking, what you intend to use the information for, and whether I’ve had enough coffee :)

Not to pull a shameless self-plug… but if you’re going to IOUG, you can visit my session to learn more about the protocol:

381: Listening In: Passive Capture and Analysis of Oracle Network Traffic
http://iougew.prod.web.sba.com/displaymod/detailevent.cfm?conference_id=52&event_id=1666

In this presentation we will discuss and demonstrate the methods for passively capturing, analyzing, and reporting the details of Oracle network traffic in real-time for use in end-to-end Oracle tuning and troubleshooting scenarios.

In cases where very short response time requirements must be met, or where sporadic spikes in response time occur, the most reliable way to tune and troubleshoot them is by capturing Oracle’s Ethernet traffic, analyzing it, and reporting on various aspects of it. Throughout this session we will demonstrate the passive capture of SQL statements, their frequency, time spent in execution, number of roundtrips, and all relevant response times.

Using the data from these reports can not only assist DBAs in diagnosing network-related issues and in tuning Oracle’s network settings, but also ensure that application developers are writing performant, network-friendly database access code.

Trackback URI | Comments RSS