SQL*Net FAQ

Oracle SQL*Net (Net8) FAQ:

What is SQL*Net/ Net8?

Net8 (called SQL*Net prior to Oracle8) is Oracle's client/server middleware product that offers transparent connection from client tools to the database, or from one database to another.

Net8 enables services and their applications to reside on different computers and communicate as peer applications. The main function of Net8 is to establish network sessions and transfer data between a client machine and a server or between two servers. Net8 is located on each machine in the network. Once a network session is established, Net8 acts as a data courier for the client and the server.

Essentially, SQL*Net provides the software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to another. SQL*Net/ Net8 works across multiple network protocols and operating systems.

TNS or Transparent Network Substrate is Oracle's networking architecture. TNS provides a uniform application interface to enable network applications to access the underlying network protocols transparently.

The TNS architecture consists of three software components:

• TNS-based applications (like SQL*Plus or TOAD)
• Oracle Protocol Adapters (OPA)
• Networking software like TCP/IP

What is the difference between SQL*Net V1, V2 and NET8?

	SQL*Net V1	SQL*Net V2	Net8 and above
Default port	1525/tcp	1521/tcp	1521/tcp
Start command	tcpctl start	lsnrctl start	lsnrctl start
Stop command	tcpctl stop	lsnrctl stop	lsnrctl stop
Connect string	protocol:host:sid eg. T:SRV1:DB1	Specified in TNSNAMES.ORA	Specified in TNSNAMES.ORA
Config files	/etc/oratab	tnsnames.ora, sqlnet.ora & listener.ora	tnsnames.ora, sqlnet.ora & listener.ora
Env variable	LOCAL=	TWO_TASK=	TWO_TASK=

Where are the SQL*Net configuration files located?

The SQL*Net configuration files (like SQLNET.ORA and TNSNAMES.ORA) can be found in one of the following locations (SQL*Net searches for it in this order):

• Directory pointed to by the TNS_ADMIN parameter ($TNS_ADMIN on Unix)
• /etc (Unix only)
• /var/opt/oracle (Unix only)
• $ORACLE_HOME/network/admin (or sometimes net8/admin directory)

How does one configure SQL*Net?

Most people (myself included) prefer to edit the SQL*Net configuration files by hand. The only "officially supported" configuration method, however, is via the Oracle Net8 Assistant or Oracle Net8 Easy Config utility (previously called Oracle Network Manager).

This configuration utility is PC based. You need to generate the necessary files on your PC and FTP or copy them to the relevant operating systems you use Oracle on.

Look at the following sample configuration files:

$ORACLE_HOME/network/admin/sqlnet.ora

automatic_ipc = ON               # Set to OFF for PC's
trace_level_client = OFF         # Set to 16 if tracing is required
sqlnet.expire_time = 0           # Idle time in minutes
sqlnet.authentication_services = (ALL)
names.directory_lookup = (TNSNAMES,ONAMES)
names.default_domain = world
name.default_zone = world

$ORACLE_HOME/network/admin/tnsnames.ora

dbname1, aliasname1, aliasname2 =
  (description =
       (address_list =
         (address =
           (protocol = tcp)
           (host = yourHost.domain)
           (port = 1521)
         )
      )
      (connect_data =
        (sid = yourSID)
      )
  )

$ORACLE_HOME/network/admin/listener.ora

LISTENER =           # Listener name is LISTENER
 (address_list =
  (address=
   (protocol=ipc)
   (key=yourSID)
  )
  (address=
    (protocol = tcp)
    (host = yourHost.domain)
    (port = 1521)
  )
 )

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = ON
TRACE_FILE_LISTENER = $ORACLE_HOME/network/trace/listener.trc

SID_LIST_LISTENER =
 (SID_LIST=
   (SID_DESC=
     (SID_NAME=yourSID)
     (ORACLE_HOME=YOUR_ORACLE_HOME)
   )
 )

NOTE: A wrong TNSNAMES.ORA entry on a line will block all valid entries below. Copy names to the top until you find the incorrect entry.

I have some trouble with SQL*Net. How does one produce a trace file?

SQL*Net client and server tracing can be enabled by setting parameters in the SQLNET.ORA file:

Client Side Tracing:

Your SQLNET.ORA file should contain the following lines to produce a client side trace file:

trace_level_client = 10
trace_unique_client = on
trace_file_client = sqlnet.trc
trace_directory_client = <path_to_trace_dir>

Server Side Tracing:

To enable server side tracing, use the following parameters:

trace_level_server = 10
trace_file_server = server.trc
trace_directory_server = <path_to_trace_dir>

Tracing TNSPING packets:

Sometimes it is useful to only trace TNSPING packets. Add the following parameters to your SQLNET.ORA file:

TNSPING.TRACE_LEVEL = 4
TNSPING.TRACE_DIRECTORY = <path_to_trace_dir>

Tracing Levels:

The following values can be used for TRACE_LEVEL* parameters:

• 16 or SUPPORT — WorldWide Customer Support trace information
• 10 or ADMIN — Administration trace information
• 4 or USER — User trace information
• 0 or OFF — no tracing, the default

How does one set up a dedicated server connection?

When you configure your database to use Shared Server (previously called Multi-threaded server or MTS), all client requests are handed off to one of the shared server processes by the listener, via a dispatcher. If you want certain clients to use a dedicated server process, you need to set the dedicated server option in your database connect string: ie.

SQLPLUS SCOTT/TIGER@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(NODE=yourServerName)))(CONNECT_DATA=(SID=yourSid)(SERVER=DEDICATED)))

You can also edit your TNSNAMES.ORA file and add the (SERVER=DEDICATED) part in the CONNECT_DATA list or simply set USE_DEDICATED_SERVER=ON in your SQLNET.ORA file.

How does one enable dead connection detection?

Dead database connections can be detected and killed by SQL*Net if you specify the SQLNET.EXPIRE_TIME=n parameter in your SQLNET.ORA file (usually in $ORACLE_HOME/network/admin). This parameter will instruct SQL*Net to send a probe through the network to the client every n minutes, if the client doesn't respond, it will be killed.

NOTE: This parameter is only useful on the database server side, specifying it on a client workstation will have no effect.

What are inband and out of band breaks?

Data exceptions like Control-C can be transmitted as part of the regular data stream (inband) or as a separate asynchronous message (outband). Obviously outband breaks are much faster as they can interrupt the flow of data.

Out Of Bound Breaks (OOB) are enabled by default. One can disable OOB from the SQLNET.ORA file:

DISABLE_OOB=on

What is a bequeath session?

A bequeath session is a session for which the listener spawned a Dedicated Server Process and then passed (bequeaths) the connection to that server process.

What can be done to increase SQL*Net performance?

Reduce polling:

While a SQL statement is running SQL*Net polls the client continuously to catch CONTROL-C situations. This results into a lot of poll and fstat system calls.

The following SQLNET.ORA parameter can be specified to reduce polling overhead on your system:

# Number of packets to skip between checking for breaks (default=4)
BREAK_POLL_SKIP=10000

Prespawn servers:

Prespawned server sessions. You can tell the listener to start up a pool of idle server processes. When a connection request is made, it doesn't have to start a server process; it just hands one of the idle processes to the client (and then starts a new connection in its own time). This is configured in LISTENER.ORA, in the SID_LIST_LISTENER section, as follows:

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = yourSID)
       (PRESPAWN_MAX = 50)
       (PRESPAWN_LIST =
         (PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 5)
         (TIMEOUT = 2))))
)

PRESPAWN_MAX: if there are over 50 sessions connected to the database, the listener won't prespawn any more.

POOL_SIZE: the listener will maintain an idle pool of 5 server processes.

TIMEOUT: after a client disconnects, the listener will keep the freed-up server process around for two minutes, waiting for a new connection request, before killing that process.

Configure multiple listeners:

Multiple listeners with load balancing. You can start multiplelisteners on a server, and reference all of the listeners in the TNSNAMES.ORA file. When a client makes a connection request, the SQL*Net client will randomly pick one of the listeners to contact.

In LISTENER.ORA, specify multiple listeners as in:

# Define listener A...
STARTUP_WAIT_TIME_LISTENER_A = 0
CONNECT_TIMEOUT_LISTENER_A = 10
LISTENER_A=
  (ADDRESS_LIST =
     (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = yourHost.domain)
        (PORT = 1521)))
SID_LIST_LISTENER_A =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = yourSID)
         (PRESPAWN_MAX = 50)))

# Define the second listener...
STARTUP_WAIT_TIME_LISTENER_B = 0
CONNECT_TIMEOUT_LISTENER_B = 10
LISTENER_B=
  (ADDRESS_LIST =
     (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = yourHost.domain)
        (PORT = 1522)))
SID_LIST_LISTENER_B =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = yourSID)
         (PRESPAWN_MAX = 50)))

The TNSNAMES.ORA service for this database would be something like:

oradb1.world =
  (description_list=
     (description=
        (address_list=
           (address=
              (protocol=tcp)
              (host=yourHost.domain)
              (port=1521)))
           (connect_data =
              (sid = yourSID)))
     (description =
        (address_list =
           (address=
                (protocol=tcp)
                (host=yourHost.domain)
                (port=1522)))
           (connect_data =
              (sid = yourSID))))

Can one get connected to a system regardless of machine failure?

You can place multiple address entries for a single connection alias in the TNSNAMES.ORA file. This means that you can connect to a database, even if some kind of physical failover occurred. Look at the following example:

oradb1 = (DESCRIPTION =
            (ADDRESS_LIST =
               (ADDRESS =
                  (COMMUNITY = TCP_COMM)
                  (PROTOCOL = TCP)
                  (HOST = Machine01))
                (ADDRESS =
                  (COMMUNITY = TCP_COMM)
                  (PROTOCOL = TCP)
                  (HOST = Machine02)))
            (CONNECT_DATA=(
                   (SID=oradb1))))

Suppose Machine01 is down, then every new SQL*NET connection using service oradb1 will automatically login to Machine02. However, there is one restriction, the SID must be the same on both machines. This feature can provide guaranteed login for application servers and for the Oracle Parallel Server.

Can one grant or restrict access to a system via SQL*Net?

Yes, add the following parameters to your sqlnet.ora file:

tcp.validnode_checking = yes
tcp.invited_nodes = (139.185.5.111)
tcp.excluded_nodes = (133.17.15.21)

The first line enables this features. The invited nodes is a list of the hosts, either DNS name or IP address, that are allowed to connect. The excluded nodes is a list of hosts that are not allowed to connect to the database.

Note: The tcp.invited_nodes parameter takes precedence over the tcp.excluded_nodes parameter if both lists are present.

Note 2: For Oracle 8i and below these parameters needs to be set in a protocol.ora file on your server (located in $ORACLE_HOME/network/admin or $TNS_ADMIN).

Why do I get a 12154 error?

This error message occurs when a user attempts to logon to an Oracle database. The tnsnames.ora file was not found or has a syntax error. The Oracle communications software (SQL*Net) did not recognize the database connect descriptor as being valid. For GUI utilities, this is the name entered in the third field of the ORACLE logon box.

You can find more information about this error in the Oracle Network Products Messages Manual:

ORA-12154 TNS:Could not resolve service name
Cause:
  The service name specified is not defined in the TNSNAMES.ORA file.
Action:
  Make the following checks and correct the error:
  - Verify that a TNSNAMES.ORA file exists and is in the proper
    place and accessible. See the operating system specific manual
    for details on the required name and location.
  - Check to see that the service name exists in one of the
    TNSNAMES.ORA files and add it if necessary.
  - Make sure there are no syntax errors anywhere in the file.
    Particularly look for unmatched parentheses or stray characters.
    Any error in a TNSNAMES.ORA file makes it unusable. See
    Chapter 4 in the SQL*Net Administrator's Guide. If possible
    regenerate the configuration files using the Oracle Network
    Manager.

Listener FAQ's

How does one start and stop the Oracle Listener?

The Oracle SQL*Net listener can be started and stopped with the following commands:

lsnrctl start LISTENER
lsnrctl stop  LISTENER

NOTE: 'LISTENER' is actually the name of the listener in the above example. It may be different on your system. Edit your listener.ora file to get the names of the defined listeners.

What happens to connected users when the listener goes down?

Connected users will still be able to work. Users trying to establish new connections to the database (after stopping the listener) will not be able to connect until the listener is restarted.

How does one start a listener trace?

Use:

lsnrctl trace 16  (to turn the trace on) 
lsnrctl trace off (to turn the trace off)

Or set the following listener.ora parameters and reload:

TRACE_LEVEL_LISTENER=16
TRACE_FILE_LISTENER=listener.trc (listener.trc is the default)
TRACE_DIRECTORY_LISTENER=$ORACLE_HOME/network/trace

NOTE: 'LISTENER' is actually the name of the listener in the above example. It may be different on your system. View/ edit your listener.ora file to get the names of the defined listeners.

What does "UNKNOWN" status mean in lsnrctl status output?

An 'UNKNOWN' status means that the instance is registered statically (with a SID_LIST) in the listener.ora file, rather than dynamically with service registration.

You can either ignore it, or remove the SID_LIST from your listener.ora so that your instance registers with default the listener running on port 1521, or specify a local listener in your pfile (or spfile) with the host, port, protocol information as follows:

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=1522))" SCOPE=SPFILE;

When you restart the instance, it should register with this listener automatically.

Oracle, in fact, doesn't recommend having SID_LIST in listener.ora (since 9i) because it seems to cause additional overhead on the listener process.

Connection Manager

What is the Connection Manager and what is it used for?

The Oracle Connection Manager (CMan) is a Net8 process that relays network traffic to a different address, and optionally changes its characteristics. The Connection manager is commonly used for the following:

• Connection Concentration
• Access Control
• Multiprotocol Support
• Provide Java applets to connect to a database that is not on the machine the Java applet was downloaded from.

How does one configure the Connection Manager?

The CMAN.ORA file specify Connection Manager configuration details. Look at this sample CMAN.ORA file:

CMAN = (ADDRESS=(PROTOCOL=tcp)(HOST=141.145.83.4)(PORT=1610))

CMAN_ADMIN = (ADDRESS=(PROTOCOL=tcp)(HOST=141.145.83.4)(PORT=1650))

CMAN_RULES = (RULE_LIST =
        (RULE = (SRC=141.145.*.*)  # Wildcard is "x"
                (DST=141.145.*.*)
                (SRV=ed23)
                (ACT=ACC)
        ))

CMAN_PROFILE = (PARAMETER_LIST=
        (MAXIMUM_RELAYS=8)         # Default is normally too small
        (LOG_LEVEL=1)
        (TRACING=NO)
        (SHOW_TNS_INFO=YES)
        (RELAY_STATISTICS=NO)
)

How does one route data through the Connection Manager?

Code a TNSNAMES.ORA entry with two addresses. The first address specifies the address CM is listening on (coded in CMAN.ORA). The second is the address the traffic must be routed to. You also need to specify SOURCE_ROUTE=YES.

ED23_cman =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST=141.145.83.4)(PORT=1610))
      (ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.com)(PORT=1923))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = orcl)
    )
    (SOURCE_ROUTE = YES)
  )

Oracle Names Server

How does one setup an Oracle Names Server?

Follow these steps to configure an Oracle Names Server:

• Create a domain database: Run script $ORACLE_HOME/network/admin/namesins.sql ($ORACLE_HOME/network/names/namesini.sql on WinNT)
• From a Windows9x/ NT workstation, start the Oracle Net8 Assistant and configure
• Copy the generated configuration files to your server
• Start Oracle Names on the server: namesctl start

How to get your listener to register itself with the Names Server?

Edit your LISTENER.ORA file and add a line USE_PLUGANDPLAY_listener_name=ON for each listener defined on your machine. Secondly, assign a GLOBAL_DBNAME parameter for each listener.

Sample LISTENER.ORA file:

USE_PLUG_AND_PLAY_LISTENER = ON
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL=IPC)
          (KEY= hostname_orcl.domain.com)
        )
        (ADDRESS=
          (PROTOCOL=IPC)
          (KEY= orcl)
        )
        (ADDRESS =
          (COMMUNITY = TCPIP.domain.com)
          (PROTOCOL = TCP)
          (Host = hostname.domain.com)
          (Port = 1526)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = hostname_orcl.domain.com)
      (SID_NAME = orcl)
      (ORACLE_HOME = /)
      (PRESPAWN_MAX = 10)
    )
  )

How does one register an Oracle Names Server Entry?

Oracle Names Server entries are normally entered from the Oracle Network Manager (V7) or the Oracle Net8 Assistant. Both these tools run from your desktop.

You can also manually register an entry on your server using the NAMESCTL command. Eg:

NAMESCTL> register mydb.world -t oracle_database -d (description=(address=(protocol=tcp)(host=123.45.67.8)(port=1526))(connect_data=(sid=MYDB)))

NOTE: the whole command must be entered on one line. Also, make sure you register this database with all Names Servers running in your domain.

To check if your entry is correctly recorded in the Names Server, issue this command:

NAMESCTL> query mydb.world *

How does one check if a listener registered itself with the Names Server?

Issue the LSNRCTL command and type either SERVICES or STATUS. If the listener successfully registered itself with the Oracle Names server you will notice the keyword "Registered" next to the service name. Example:

Services Summary...
  oraweb(Registered)            has 1 service handler(s)