I am a tinkerer…I can take things apart and put them back together…an inherited trait from my dad (works well with my VW / Isetta hobby)…I’ve always been curious how things work.

Connecting to Oracle isn’t magic…there is a file on every Oracle client that contains the database connection information, sometimes called the host string, and technically called the TNS Names Entry…   Transparent Network Substrate. This is Oracle’s term for how Oracle client machines connects to Oracle databases using TCP/IP.

The server has a ‘listener’ process. This process is typically started when the database is started. On the server, you can easily see what ports the listener is listening to traffic on and the status of the listener. LSNRCTL <start> <stop> <status> is used to start/stop or get the status of the listening process.

The listener gets its configuration details from the listener.ora file. Contents of this file look like this:

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC=
     (GLOBAL_DBNAME=ora11r2)
     (ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_3)
     (SID_NAME=ora11r2)
   )
)

LOGGING_LISTENER = OFF

LISTENER =
   (DESCRIPTION_LIST =
   (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DanXP)(PORT = 1521)))
   ) 

ADR_BASE_LISTENER = C:\Oracle\product\11.2.0

This file is from my main PC (host name = DanXP) running an Oracle database with a System Identifier (SID) of ora11r2. This name is given when the database is installed. Notice that the listener is also listening on port 1521.

TOAD allows you to edit these files from the connection page.

On the client, there is a TNSNAMES.ora file. This file gives a simple name to the information needed to connect to the database. The information includes: host computer name/address, port the listener is listening on, and the name of the database to connect to.

DANXP =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ora11r2)
   )
)

BRIANXP =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = BrianXP)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = oraxp10g)
   )
)

I show two entries here. The DANXP says that the HOST is the local machine (I usually give a host name here though), to check port 1521 for a listener process, and to connect to the ora11r2 database on that computer. This connect string works well for Oracle11.

If Oracle12 differs from either of these two…I’ll let you know!

I show another entry for BRIANXP…I name my computers and databases after friends… This is for an Oracle10 instance. Notice I name the computer name in the HOST parameter and that the SID is listed instead of the SERVICE_NAME. Not much of a difference but this combination works for these databases.

I have to share with you that usually my biggest issue with a new database is connecting to it for the first time! Once I find the combination that the host computer/oracle likes…it rarely acts up.

The important directory for these networking files is the <ORACLE_HOME>\network\admin. Oracle processes will look for these files in this location unless given full path (the listener can be given a file name and full path location) and the TNSNames.ora file can be referenced with the TNS_ADMIN variable specified in your .profile (Unix/Linux) or in a windows environment variable.

Summary:

You may find yourself needing to set this up for your own test environment. These are the SQL*Net tips I have learned over the years and I too have struggled a bit with connecting to new Oracle databases.

See you next week.

Dan

Dan Hotka
Oracle ACE Director
Author/CEO/Instructor