Okay, you've installed the
Oracle Server onto your Unix box and created your database. How do you
connect? Unlike other aspects of database management, such as creating
rollback segments or resizing a table, both of which fall directly under
the duties of a DBA, setting up the interface between Oracle client applications
with the Oracle server is task that often requires the services of the
DBA, network administrator and the system administrator. As a result of
this, setting up the Oracle client/server interface is an often overlooked
aspect of database management.
IPC database connections:
Before we get into SQL*NET,
let's take a look at local database connections, also known as Inter Process
Communication or IPC. When an Oracle database starts, the background
processes grab a contiguous section of system memory to initialize the
System Global Area, or SGA. The SGA is the primary work area of an Oracle
database. Tasks such as rows getting modified, package bodies getting compiled,
and transactions getting rolled back are all performed within the SGA.
If your client application
is on the same machine as the database server, and if you have access to
the underlying operating system on which your database is running on, then
you can directly attach to the SGA. This type of connection is known as
a LOCAL CONNECTION. Under this connection, the client and server processes
communicate via shared memory.
In order to perform
a local connection to Oracle, you first must have access to the underlying
operating running the database server, i.e. you must have a operating system
login to the database server. In addition, several environment settings
must also be properly configured.
The environment variable $ORACLE_HOME
must point to the directory containing all Oracle software. Be careful
to set $ORACLE_HOME to the proper directory level. The various Oracle
sub directories: bin, rdbms, lib, orainst, network, etc should all appear
immediately under $ORACLE_HOME.
$ORACLE_SID
must also be set to point to the proper database. The SID is a unique identifier
of up to six characters that is assigned to each database during its creation.
During a local connection, the client will use this identifier to determine
the location of the SGA..
$LD_LIBRARY_PATH must
also point to $ORACLE_HOME/lib, as this directory contains all Oracle
libraries and the $PATH environment variable should include $ORACLE_HOME/bin,
as this directory contains all Oracle program executables.
These environment variables
can be set manually on the command line, or in the user's login script.
Under sh, ksh or bash:
$
ORACLE_HOME=/u01/oracle/product/7.3.3.5 ; export ORACLE_HOME
$ ORACLE_SID=SEED
; export ORACLE_SID
$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
; export LD_LIBRARY_PATH
$ PATH=$PATH:$ORACLE_HOME/bin
; export PATH
Oracle provides a script
called oraenv (or coraenv for csh or tcsh users) which is typically
installed in /usr/lbin or in /usr/local/bin. When this script is
executed, it will prompt the user for what SID he/she wants to point to,
and then set all environment variables accordingly.
$
. oraenv
ORACLE_SID = []
SEED
$ echo $ORACLE_SID
SEED
$ echo $ORACLE_HOME
/u01/oracle/product/7.3.3.5
At this point, you are set
to perform a local connection to the database. Simply start the desired
Oracle client application, for example SQL*PLUS, and login to the database.
For example, to locally
connect as the user SYSTEM :
$
sqlplus
SQL*Plus: Release
3.3.3.0.0 - Production on Wed May 13 12:31:24 1998
Copyright (c) Oracle
Corporation 1979, 1996. All rights reserved.
Enter user-name:
system
Enter password:
*********
Connected to:
Oracle7 Server
Release 7.3.3.5.0 - Production Release
With the distributed,
replication and Spatial Data options
PL/SQL Release
2.3.3.5.0 - Production
SQL>
Connecting to
remote databases:
If the Oracle client application
and database are not on the same machine, or if the system administrator
does not allow a user O/S access to the database server, then a local connect
will not be possible. Under such circumstances, the user must perform a
remote connection into the database. A remote connection allows the Oracle
client application to reside on a separate node and bypasses any connection
with the underlying operating system.
Remote connections require
the Oracle networking protocol, SQL*NET, to be present and properly functioning
on both the client and the server. SQL*NET is a session level protocol
under the OSI network model, and thus depends on lower-level network and
transport levels to be working properly. SQL*NET is used on a number of
Oracle applications including Developer 2000, ODBC, and Oracle Reports
While SQL*NET works with
most major network protocols, DEC-NET, IPX and TCP/IP, it is most widely
used and most easily configured under the latter protocol. The current
version of SQL*NET is 2.3.x for Oracle 7 and Net8 for Oracle 8.
To install this product during
an Oracle install, select the "ORACLE TCPIP Adapter" or SQL*NET Client
for TCPIP under the "Software Asset Manager". SQL*NET must be installed
on both client & server ends in order for it to function properly.
Setting up the
SQL*NET Server
Once SQL*NET is installed
on the database server, the next step in establishing a remote connection
is to start one or more SQL*NET Listener daemons on the database server.
As the name suggests, this purpose of this daemon(s) is to listen on the
network for incoming database requests. Once a request is found, it will
create a connection between the remote client application and a particular
database. Although one listener can create connections for all databases
on a node, the DBA is free to create multiple listeners that attach for
specific databases.
These daemons typically
runs standalone and not as part of the internet daemon, inetd. The configuration
file for these listeners is called listener.ora and is located under $ORACLE_HOME/network/admin,
or in the directory contained in the environment variable $TNS_ADMIN
(on NT systems this is the TNS_ADMIN String located under the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
registry key) , if it is defined.
Listener.ora is an ASCII
file that defines what port each daemon is to run from, what protocol to
use, and what databases to create connections for. While Oracle recommends
that you use the Oracle Network Manager to generate the file, you can edit
it directly with any text editor. Using TCP/IP as the underlying transport,
the basic syntax of the file is as follows:
# comments are preceded by
a hash mark
<LISTENER_NAME #1>
=
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST=<IP
ADDRESS OF DATABASE SERVER>
(PORT=<PORT
## LISTENER #1 SHOULD BE LISTENING ON>)
)
)
STARTUP_WAIT_TIME_<LISTENER_NAME>=<wait
time>
CONNECT_TIMEOUT_<LISTENER_NAME>=<timeout
interval>
SID_LIST_<LISTENER
NAME>
(SID_LIST =
(SID_DESC =
(SID_NAME=<SID
of database #1>)
(ORACLE_HOME=<$ORACLE_HOME
of the above database)
)
(SID_DESC =
(SID_NAME=<SID
of database #2>)
(ORACLE_HOME=<$ORACLE_HOME
of the above database)
)
(SID_DESC =
(SID_NAME=<SID
of database #n>)
(ORACLE_HOME=<$ORACLE_HOME
of the above database)
)
)
A sample listener.ora file
is show below. This file defines a TCP listener named MYLISTENER that runs
on port 1526. The hostname of the database server is my.test.edu. MYLISTENER
attaches to the databases SEED, TEST and PROD all of which run from the
directory /u01/oracle/product/7.3.3.5.
MYLISTENER
=
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST=my.test.edu)
(PORT=1526)
)
)
STARTUP_WAIT_TIME_MYLISTENER=0
CONNECT_TIMEOUT_MYLISTENER=60
SID_LIST_MYLISTENER
(SID_LIST =
(SID_DESC =
(SID_NAME=SEED)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
(SID_DESC =
(SID_NAME=TEST)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
(SID_DESC =
(SID_NAME=PROD)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
)
The executable that controls
the listener is called lsnrctl. When you invoke this application you should
see something similar to the following:
LSNRCTL for Unix: Version
2.3.3.0.0 - Production on 18-MAY-98 23:51:31
Copyright (c) Oracle
Corporation 1994. All rights reserved.
Welcome to LSNRCTL, type
"help" for information.
LSNRCTL>
To start a particular listener,
type:
LSNRCTL
> start <LISTENER_NAME>
For example, to start MYLISTENER:
LSNRCTL>
start MYLISTENER
Starting
/u01/oracle/product/7.3.3.5/bin/tnslsnr: please wait...
TNSLSNR for Unix:
Version 2.3.3.0.0 - Production
System parameter
file is /u01/oracle/product/7.3.3.5/network/admin/listener.ora
Log messages written
to /u01/oracle/product/7.3.3.5/network/log/mylistener.log
Listening on:
(ADDRESS=(PROTOCOL=tcp)(DEV=10)(HOST=my.test.edu)(PORT=1526))
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=my.test.edu)(PORT=1526))
STATUS of the
LISTENER
------------------------
Alias
mylistener
Version
TNSLSNR for Unix: Version 2.3.3.0.
0 - Production
Start Date
19-MAY-98 21:33:31
Uptime
0 days 0 hr. 0 min. 0 sec
Trace Level
off
Security
OFF
SNMP
ON
Listener Parameter
File /u01/oracle/product/7.3.3.5/network/admin/listener.ora
Listener Log File
/u01/oracle/product/7.3.3.5/network/log/mylistener.log
Services Summary...
SEED has 1 service
handler(s)
TEST has 1 service
handler(s)
PROD has 1 service
handler(s)
The command completed
successfully
LSNRCTL>
The program lsnrctl
calls another executable, tnslsnr, which actually starts the daemon.
At this point, the databases SEED, TEST and PROD are ready to accept remote
connections from the network.
To stop a listener, invoke
lsnrctl and enter the following:
LSNRCTL>
stop <LISTENER_NAME>
In the case of MYLISTENER:
LSNRCTL>
stop MYLISTENER
The command completed
successfully
LSNRCTL> exit
$
Typically, the oracle software
owner starts/stop the listener, although anyone in the SYSDBA or SYSOPER
groups can do so.
The above syntax defines
a single listener that attaches to the SEED, TEST and PROD databases. Under
this setup, users can remotely log into any of the three databases attached
to the daemon while MYLISTENER is running. If you need to restrict users
from remotely logging into specific databases while the databases are open,
then you may want to attach these databases to separate listeners. Databases
remain remotely inaccessible as long as the listeners that attach to it
are not running.
You can define as many listeners
as you want, each with its own set of attached databases, provided that
each listener runs from a separate TCP port. The listener.ora file shown
below defines three listeners named FIRSTLISTENER, SECONDLISTENER and THIRDLISTENER
that run from ports 1526, 1527 and 1528, respectively and attach to the
databases SEED, TEST and PROD.
FIRSTLISTENER =
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST=my.test.edu)
(PORT=1526)
)
)
STARTUP_WAIT_TIME_FIRSTLISTENER=0
CONNECT_TIMEOUT_FIRSTLISTENER=60
SID_LIST_FIRSTLISTENER
(SID_LIST =
(SID_DESC =
(SID_NAME=SEED)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
)
SECONDLISTENER =
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST=my.test.edu)
(PORT=1527)
)
)
STARTUP_WAIT_TIME_SECONDLISTENER=0
CONNECT_TIMEOUT_SECONDLISTENER=60
SID_LIST_SECONDLISTENER
(SID_LIST =
(SID_DESC =
(SID_NAME=TEST)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
)
THIRDLISTENER =
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST=my.test.edu)
(PORT=1528)
)
)
STARTUP_WAIT_TIME_THIRDLISTENER=0
CONNECT_TIMEOUT_THIRDLISTENER=60
SID_LIST_THIRDLISTENER
(SID_LIST =
(SID_DESC =
(SID_NAME=PROD)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
)
If you have multiple
listeners, each must be started individually under lsnrctl. This
allows the DBA greater control over database access. To prevent users to
remotely logging to the TEST, for example, the DBA can shut down SECONDLISTENER.
However, keep in mind that
more listeners take up more system resources.
SQL*NET Client
configuration
In addtion to setting
up SQL*NET on the database server, SQL*NET must also be properly configured
on each Oracle client.
Several ASCII configuration
files exist for setting up SQL*NET on the Oracle client. These files include
sqlnet.ora,
names.ora, topology.ora and tnsnames.ora. Each file defines a different
component of SQL*NET clients. However, it is the tnsnames.ora file
that is critical for remote database connections. As with listener.ora,
Oracle recommends that you generate these files with Network Manager but
you can edit them directly.
On Unix clients, tnsnames.ora
is also located under the $ORACLE_HOME/network/admin directory or
in the directory contained in $TNS_ADMIN if it is defined.
On PCs running Windows 95
or NT, ORACLE_HOME is defined by a registry key \\HKEY\LOCAL_MACHINE\SOFTWARE\ORACLE.
This key contains a STRING value ORACLE_HOME which defines the Oracle software
directory. PC clients will also look for tnsnames.ora under ORACLE_HOME\network\admin
or in the directory contained by the string TNS_ADMIN if it is defined.
The tnsames.ora file consists
of a series of database connect/host string definitions. Each string defines
a specific database which the client can connect to. The basic syntax of
these strings is shown below:
# comments are preceeded
by hash marks
<string name>
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS =
(PROTOCOL = TCP)
(Host = <hostname of database server>)
(Port = <port number that listener is running on>)
)
)
(CONNECT_DATA
= (SID = <Oracle SID of database>)
)
)
Each host string entry defines
the hostname of the database server, port that the listener is running
on, and the Oracle SID of the desired database. While you are free to choose
any name you want as the host string, each value in the string MUST match
those defined in the corresponding Oracle listener. For example, using
the latter listener.ora file as the listener definition, the corresponding
tnsnames.ora should look like the following:
|
Tnsnames.ora
|
Listener.ora
|
SEED_DATABASE
(DESCRIPTION =
(ADDRESS_LIST
=
(ADDRESS =
(PROTOCOL = TCP)
(Host = my.test.edu)
(Port = 1526)
)
)
(CONNECT_DATA
= (SID = SEED)
)
) |
LISTENER =
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST=my.test.edu)
(PORT=1526)
)
)
STARTUP_WAIT_TIME_FIRSTLISTENER=0
CONNECT_TIMEOUT_FIRSTLISTENER=60
SID_LIST_FIRSTLISTENER
(SID_LIST =
(SID_DESC =
(SID_NAME=SEED)
(ORACLE_HOME=/u01/oracle/product/7.3.3.5)
)
) |
Note that in each connection
string definition, the host, port and SID match those of the corresponding
Oracle listener. When editing tnsnames.ora, be sure to keep track of parenthesis
on this file as a single missplaced one can cause the connect to fail.
Once this file is properly
setup, you are ready to connect to a remote database. Oracle clients require
three parameters for remote connects, these values are the username/password
for the Oracle logon, and a connect string that defines the database.
From the command line,
setting up a remote connection is very similar to that of a local connection.
You would start the desired Oracle client as before, but during the database
logon you would specify a username followed by an "@<connect string>"
during the username prompt. For example, to remotely connect as SYSTEM
into the SEED database using SQL*PLUS:
$ sqlplus
SQL*Plus: Release
3.3.3.0.0 - Production on Wed May 13 12:31:24 1998
Copyright (c) Oracle
Corporation 1979, 1996. All rights reserved.
Enter user-name:
system@SEED_DATABASE
Enter password:
*********
Connected to:
Oracle7 Server
Release 7.3.3.5.0 - Production Release
With the distributed,
replication and Spatial Data options
PL/SQL Release
2.3.3.5.0 - Production
SQL>
On GUI-based Oracle clients,
these values are similarly required in the initial logon. You would simply
type the tnsnames string in the database connect string field.
Note that both GUI and command-line
remote connections function in the same manner. The Oracle client first
takes the host string you specify in the login and attempts to find a matching
string in tnsnames.ora. If it finds a match, it will attempt to find a
listener running at the specified host and port. If an Oracle listener
is found, the client application will then attempt to locate a database
with the SID as specified in the host string. If the client finds this
database, it will then take the username and password and attempt to login.
Checking on
the status of connections into the database
Each connection into
the database takes place using an Oracle server process. These processes
can be either dedicated server processes or shared server processes depending
on how the DBA has configured the database. The server process attaching
your client application will show up in the system process tables and will
describe whether the connection is local or remote.
$
ps -ef | grep SEED
oracle 27293
1 0.0 May 11 ??
2:22.20 ora_dbwr_SEED
oracle 28298
1 0.0 May 11 ??
0:22.48 ora_lgwr_SEED
oracle 28513
1 0.0 May 11 ??
0:21.72 ora_pmon_SEED
oracle 28600
1 0.0 May 11 ??
5:33.84 ora_smon_SEED
oracle 28790
1 0.0 May 11 ??
0:29.82 ora_reco_SEED
oracle 28868
29408 0.0 May 11 ??
0:00.49 oracleSEED (DESCRIPTION=(LOCAL=YES))
oracle 29469
26033 0.0 May 11 ??
0:01.45 oracleSEED (DESCRIPTION=(LOCAL=YES))
oracle 31012
1 0.0 Apr 27 ??
0:00.49 oracleSEED (LOCAL=NO)
The above example shows all
processes associated with the SEED database. The first five processes are
the background processes associated with the Oracle instance. The next
two processes are Oracle server processes associated with local connections
to the database. The last process is an Oracle server process associated
with a SQL*NET connection.
Troubleshooting
SQL*NET
Setting up remote database
connections is a complicated process, that requires numerous components
to be properly functioning. Below are a list of common errors that can
arise along with their likely resolutions.
ORA-12154: TNS:could
not resolve service name
This error means that
the host string you entered was not found in tnsnames.ora. Make sure that
tnsnames.ora is in the proper directory. Also make sure that TNS_ADMIN
is either defined properly or not defined at all. Check that the host string
that you typed EXACTLY matches that specified in tnsnames.ora. Also if
the directory containing tnsnames.ora also contains a sqlnet.ora
file, rename sqlnet.ora to some other name. Sqlnet.ora is used with more
other networking options, but is not required for simple remote connections.
However, the presence of a sqlnet.ora file may affect the manner
that Oracle clients interprete connect strings.
ORA-12545: TNS:name lookup
failure
This error occurs when
the Oracle client cannot resolve the hostname specified in tnsnames.ora.
Make sure the client is able to resolve hostnames, if not, replace hostnames
with IP addresses.
ORA-12203: TNS:unable
to connect to destination
This error occurs
when the client is not able to establish a connection with the database
server. Check to make sure the underlying TCPIP transport is working properly,
and that you are able to ping the database server from the client.
This error also occurs
when the Oracle client could not find a listener running on the port specified
in tnsnames.ora. Make sure the appropriate listener has been started on
the database server. Also check the entry in tnsnames.ora to make sure
the port number specified is correct.
ORA-01034: ORACLE not
available
ORA-07429: smsgsg: shmget()
failed to get segment.
These errors mean that
the database you are connecting to is shut down. Remember that both the
listener and database need to be running in order for remote connections
to occur. Use Server Manager or Enterprise Manager to start the database.
ORA-12505: TNS:listener
could not resolve SID given in connect descriptor
This error occurs when
Oracle has found a listener running on the port as specified in tnsnames.ora,
but it did not find a database with the corresponding SID attached to the
listener. This error most often occurs when you are running multiple listeners.
Check that the port and SID in tnsnames.ora match that appropriate listener
as defined in listener.ora.
