Oracle SQL*Net/ Net8 FAQ

$Date: 03-Jun-2000 $
$Revision: 2.00 $
$Author: Frank Naudé $

Topics

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, Oracle Protocol Adapters (OPA), and networking software like TCP/IP.

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)
          )
         )

• 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 net8/admin directory

        trace_level_client=16
        trace_unique_client=yes

        TNSPING.TRACE_LEVEL = 16
        TNSPING.TRACE_DIRECTORY = /tmp/tnsping/

        trace_file_client = cli.trc
        trace_directory_client = <path_to_trace_dir>
        log_file_client = sqlnet.log
        log_directory_client = <path_to_log_dir>

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

You can also list V1 connect strings in your TNSNAMES.ORA file. Eg:

ORA1_NET1 = T:machine_name/port:database_name

Note that SQL*Net V1 is not available from version 7.3 of the database.

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

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

DISABLE_OOB=on

1. 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:

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

2. 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.

3. Multiple listeners with load balancing. You can start multiple listeners 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))))

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

   tcp.validnode_checking = yes
   tcp.invited_nodes = (www.orafaq.org,139.185.5.111)
   tcp.excluded_nodes = (133.17.15.21)

• SQL*NET 1-2-3

• Download SQL*Net V2 Light for DOS

• Create a domain database: Run script $ORACLE_HOME/network/admin/namesins.sql
• From a Windows9x/ NT workstation, start the Oracle Net8 Assistant and configure
• For server start the names server: namesctl start

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= wblp-nt-011b_orcl.companyX.com)
           )
           (ADDRESS=
             (PROTOCOL=IPC)
             (KEY= orcl)
           )
           (ADDRESS =
             (COMMUNITY = TCPIP.companyX.com)
             (PROTOCOL = TCP)
             (Host = wblp-nt-011b.companyX.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 = wblp-nt-011b_orcl.companyX.com)
         (SID_NAME = orcl)
         (ORACLE_HOME = /)
         (PRESPAWN_MAX = 10)
       )
     )

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)))

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

NAMESCTL> query mydb.world *

• Connenction 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.

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)
)

ED23_cman =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST=141.145.83.4)(PORT=1610))
      (ADDRESS=(PROTOCOL=TCP)(HOST=zaedu2.za.oracle.com)(PORT=1923))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = ed23)
    )
    (SOURCE_ROUTE = YES)
  )