Oracle SQL*Net/ Net8 FAQ

Topics

  • What is SQL*Net/ Net8?
  • What is the difference between Sql*Net V1, V2 and NET8?
  • How does one configure SQL*Net?
  • I have some trouble with SQL*Net. How can I produce a trace file?
  • How can I set up a dedicated server connection?
  • Can I upgrade to SQL*Net V2 if I still have V1 clients?
  • How can I enable dead connection detection?
  • What can be done to increase SQL*Net performance?
  • Can one get connected to a system regardless of machine failure?
  • Can one grant or restrict access to a system via SQL*Net?

    •  

     
     
     

    Oracle Names Topics:

  • Hoes does one setup an Oracle Names Server?
  • How to get your listener to register itself with the Names Server?
  • How does one register an Oracle Names Server Entry?
  • How can I check if a listener registered itself with the Names Server?
    •

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

  • Back to top of file 

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


      SQL*Net V1 SQL*Net V2 Net8
    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= 
  • Back to top of file 

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


$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)
              )
          )
  • Back to top of file 

    • I have some trouble with SQL*Net. How can I produce a trace file?

    Create/edit your SQLNET.ORA file. You will find this file in one of the following locations (SQL*Net V2 searches for it in this order): Your SQLNET.ORA file should contain the following lines to produce a trace file: Sometimes it is useful to only trace TNSPING packets. Add the following parameters to your SQLNET.ORA file: The following parameters are also worth setting:
  • Back to top of file 

    • How can I set up a dedicated server connection?

    When you configue your database to use MTS (Multi-threaded server), 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.
  • Back to top of file 

    • Can I upgrade to SQL*Net V2 if I still have V1 clients?

    SQL*Net V1 cannot talk with SQL*Net V2, and vice versa. The only way to overcome this problem is to run SQL*Net V1 and V2 simultaneously on the same database server. You can then install SQL*Net V2 on your clients as time permits. SQL*Net V1 and V2 can coexist on the same server, or on the same client.

    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.

  • Back to top of file 

    • How can I 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.

  • Back to top of file 

    • What can be done to increase SQL*Net performance?

    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.

      2.  

       
       
       

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

      4.  

       
       
       

      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))))
  • Back to top of file 

    • 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.
  • Back to top of file 

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

    Yes, create a protocal.ora file like this: 
       tcp.validnode_checking = yes
   tcp.invited_nodes = (www.orafaq.org,139.185.5.111)
   tcp.tcp.excluded_nodes = (133.17.15.21)
  • Back to top of file 

    • Hoes does one setup an Oracle Names Server

  • Back to top of file 

    • 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= 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)
       )
     )
  • Back to top of file 

    • 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 *
  • Back to top of file 

    • How can I 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:
  • Back to top of file