SQL*NET 1-2-3
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.