SQLNet Basics

1
SQLNet Basics
Tim Gorman Principal Evergreen Database
Technologies, Inc. http//www.EvDBT.com
2
Agenda

• How SQLNet works
• Configuring SQLNet
• Debugging SQLNet
• Trouble-shooting common error messages and their
  remedies
• Note
• Although Oracle renamed SQLNet to Net8, the
  basic functionality covered in this presentation
  has not changed, so both products will be
  referred to as SQLNet throughout...

3
How SQLNet works

• Its mostly a code layer on the client-side and
  the server-side of an Oracle connection
• all tools and programs include the client-side
  code layer of SQLNet
• SQLPlus, PROC programs, Developer, Designer,
  Discoverer, Express using the Oracle Call
  Interface (OCI) API directly
• other connectivity middleware (i.e. ODBC) include
  the SQLNet client-side code layer as well, thus
  incorporating OCI indirectly
• the server processes (a.k.a. shadow processes)
  include the server-side code layer of SQLNet
• Also, there are independent server-side
  processes, for establishing connections and
  (optionally) name resolution

4
How SQLNet works establishing connections

• standard network listener model is used
• similar to the network listener mechanism
  employed by ftp, telnet, nfs, rlogin and other
  standard services
• listener can establish connections for
• local-clients (on the same machine as the server)
• pipe mechanism bypasses SQLNet listener
  altogether
• for PROTOCOLIPC, listener monitors UNIX-domain
  sockets on UNIX, mailboxes on VMS, to establish a
  bequeathed connection via pipes
• remote-clients (across a LAN/WAN)
• PROTOCOLTCP IPX NMP SNA DEC
• for TCP, listener monitors a network port or
  socket (i.e. 1521)

5
Establishing connections

• SQLNet listener can be (should be!) registered
  as a known service on the network (i.e. UNIX
  /etc/services file)
• SQLNet listener process listens for connection
  requests
• client processes send a connection request to the
  known service
• the known service validates the received
  connection request (i.e. correct ORACLE_SID
  specified, etc)
• spawns server process (i.e. Oracle shadow
  process)
• re-directs the client process to attach to the
  server process
• disengages to listen for more connection requests
• REPEAT

6
Establishing connections (contd)
Client Process
1
1521
SQLNet Listener
Oracle instance
7
Establishing connections (contd)
Client Process
1
1521
SQLNet Listener
Oracle server
2
Oracle instance
8
Establishing connections (contd)
Client Process
1
3
1521
9900
SQLNet Listener
Oracle server
2
Oracle instance
9
Establishing connections (contd)
Client Process
1
3
1521
9900
4
SQLNet Listener
Oracle server
2
Oracle instance
10
Establishing connections (contd)
Client Process
1
5
3
1521
9900
4
SQLNet Listener
Oracle server
2
Oracle instance
11
Establishing connections (contd)
Client Process
6
1
5
3
1521
9900
4
SQLNet Listener
Oracle server
2
Oracle instance
12
Establishing connections (contd)
Client Process
1521
9900
SQLNet Listener
Oracle server
Oracle instance
13
How SQLNet works names resolution

• What is names resolution?
• A generic networking issue
• SQLNet V1 didnt include names resolution
  address components were specified at run-time
  (i.e. Thostnameoracle-sid)
• Still true with JDBC thin driver
• introduced with SQLNet v2, continued into Net8
  (SQLNet v3)
• symbolic name (i.e. PROD.world) resolves to
  multiple address components
• SQLNet offers five methods for names resolution
• file-based (i.e. TNSNAMES.ORA) repository
• centralized service (i.e. Oracle Names) as
  repository
• Directory naming (i.e. LDAP)
• Host naming
• External repository (i.e. NIS, Novell NDS,
  CDE/CDS)

14
File-based names resolution

• Decision between file-based or service-based
  names resolution decided with SQLNET.NAMES_DIRECTO
  RY parameter in SQLNET.ORA file on the
  client-side
• parameter setting is a list of directory services
• TNSNAMES, ONAMES, HOSTNAME
• if not set at all, defaults to HOSTNAME then
  TNSNAMES
• if no SQLNET.ORA file exists, defaults to
  HOSTNAME then TNSNAMES also
• TNSNAMES.ORA file is a repository on the
  client-side
• Win95/98/NT/2000 use regedit to find NET20 or
  NET80 entry in Registry
• default is drive\ORAxxx\NETWORK\ADMIN
• Unix look for TNS_ADMIN variable
• Default is ORACLE_HOME/network/admin
• Some platforms default to /etc also

15
Service-based names resolution

• SQLNET.ORA file must exist on client-side
• parameter SQLNET.NAMES_DIRECTORY must contain the
  value ONAMES
• another parameter value identifies where the name
  service is located
• Oracle Names is a repository on the server-side
• anywhere on the network, another network listener
  process, called an Oracle Names Server, is
  available to respond to resolution requests from
  client processes
• this should be administered globally, not for
  each database or application
• administration role closer to network admin, not
  DBA

16
Configuring SQLNet

• simple client-side configuration
• UNIX directory ORACLE_HOME/network/admin
• Windows directory Drive\ORAxxx\NETWORK\ADMIN
• file SQLNET.ORA (existance is optional)
• if it exists, then it could contain configuration
  parameters for
• name resolution method (TNSNAMES or ONAMES)
• SQLNet client-side tracing
• other more advanced features
• file TNSNAMES.ORA
• contains definitions of SQLNet connect strings

17
Configuring SQLNet (contd)

• simple listener (server-side) configuration
• UNIX directory TNS_ADMIN or ORACLE_HOME/network
  /admin or, for some platforms, hard-coded
  directories like /etc (HP-UX) or /var/opt/oracle
  (Solaris), etc
• Windows directory Drive\ORAxxx\NETWORK\ADMIN
• file SQLNET.ORA (existance is optional) contains
• server-side tracing
• Dead Connection Detector (DCD)
• file LISTENER.ORA (existance is mandatory)
  contains
• list of protocols and ports on which to listen
• list of Oracle database instances to establish
  connections for
• (optional) log file and tracing configuration
  information

18
Sample SQLNET.ORA file
SQLNET.EXPIRE_TIME 10 SQLNET.NAMES_DIRECTORY(TN
SNAMES,ONAMES) TRACE_LEVEL_CLIENT
OFF TRACE_DIRECTORY_CLIENT/tmp TRACE_FILE_CLIENT
cli.trc TRACE_UNIQUE_CLIENTtrue TRACE_LEVEL_SERVE
R OFF TRACE_DIRECTORY_SERVER/tmp TRACE_FILE_SER
VERsvr.trc

• SQLNET.EXPIRE_TIME sets dead connection detection
• SQLNet trace levels OFF, USER, ADMIN, or DEV
• TRACE_UNIQUE_CLIENT TRUE will create a unique
  filename for each client process
• can exist on either the client or the server
• TRACE_xxx_CLIENT is used by client-side processes
  only
• TRACE_xxx_SERVER is used by the Oracle server
  processes only

19
Sample TNSNAMES.ORA file
PROD01 (DESCRIPTION (ADDRESS_LIST
(ADDRESS (PROTOCOL TCP)
(HOST dbserver.us.mycorp.com) (PORT
1521) ) ) (CONNECT_DATA (SID
PROD) ) )

• Example shows TCP/IP parms change with other
  protocols
• HOST can be either hostname or IP address
• PORT is a TCP/IP socket number
• SID is the ORACLE_SID of the instance on the
  machine
• For TCP/IP, this is the minimum information to
  resolve to a specific database on a specific
  machine

20
Sample LISTENER.ORA file
LISTENER (ADDRESS_LIST (ADDRESS
(PROTOCOLIPC) (KEY PROD) )
(ADDRESS (PROTOCOL TCP)
(HOST dbserver.us.mycorp.com) (PORT
1521) ) ) SID_LIST_LISTENER (SID_LIST
(SID_DESC (SID_NAME PROD)
(ORACLE_HOME /opt/dev1/oracle/products/7.3) )
) STARTUP_WAIT_TIME_LISTENER
0 CONNECT_TIMEOUT_LISTENER 10 TRACE_LEVEL_LISTEN
ER OFF TRACE_DIRECTORY_LISTENER
/tmp TRACE_FILE_LISTENER listener.trc
21
LISTENER.ORA parameters

• ADDRESS_LIST contains list of listening points
• for local connections, PROTOCOLIPC
• for network connections, PROTOCOLTCP, etc
• listener name (default LISTENER) is used to
  link to other parameters
• SID_LIST_listener-name
• database instances served by listener-name
• TRACE_xxx_listener-name
• SQLNet tracing parameters for the listener
  process
• STARTUP_WAIT_TIME_listener-name
• CONNECT_TIME_listener-name
• others...

22
How to debug SQLNet

• SQLNet tracing initiated from three separate
  points
• client-side code-layer TRACE_xxx_CLIENT
• server-side code-layer TRACE_xxx_SERVER
• listener process TRACE_xxx_LISTENER
• where xxx is either LEVEL, DIRECTORY, or FILE
• LEVEL can have values of OFF, USER, ADMIN, or DEV
• where lsnr-name is the name of the listener
  (default LISTENER)
• SQLNet listener logging
• LOG_DIRECTORY_lsnr-name for the directory
  location of the log file belonging to the SQLNet
  listener named lsnr-name (default name
  LISTENER)
• LOG_FILE_lsnr-name for the name of the log file

23
Trouble-shooting

• Verify network connectivity
• ping IP-Address
• Verify network names resolution
• ping symbolic-hostname
• Verify SQLNet connectivity and names resolution
• tnsping connect-string
• Verify Oracle database instance availability
• SQLPlus
• Verify ODBC connectivity
• ODBC Test utility

24
How to debug SQLNet (contd)
Client Process
CLI.TRC
SQLNET.ORA
1521
9900
SQLNet Listener process
Oracle server process
SVR.TRC
SQLNET.ORA
LISTENER.TRC
LISTENER.ORA
25
Common Error Messages and their remedies

• TNS-12154 TNScould not resolve service name
• the SQLNet connect string you specified is not
  in TNSNAMES.ORA
• TNS-12545 "TNSname lookup failure"
• HOST specified not a valid network hostname
  either typo or a network DNS failure (try using
  only IP address instead)
• TNS-12203 "TNSunable to connect to destination"
• either the specified HOST machine is down or
  the SQLNet listener at the specified HOST and
  PORT is not running
• ORA-03113 "end-of-file on communication channel"
• either the specified HOST, the Oracle database
  instance, or your particular Oracle server
  process terminated abnormally after connection
  established

26
Questions?

• QA?