Network Management Administration in UNIX for Oracle Oracle UNIX/Linux Tips by Burleson Consulting

Network Management in UNIX for Oracle

With UNIX being the dominant platform for Oracle databases, se must spend some time discussing how distributed Oracle databases communicate.

This chapter will cover the following topics in UNIX network management.

* Network Traffic in a UNIX environment

* Oracle networking and UNIX

* TNS and UNIX

* Optimizing Net8 configuration

* Monitoring Network performance with STATSPACK

As we noted in Chapter 1, the performance of any Oracle UNIX server can be impacted by externals issues with disk, CPU, RAM and the network. Let?s begin with an overview of network issues with UNIX.

Network tuning in a UNIX environment

Tuning a network is a very long painstaking process of gathering statistics and analyzing them. Unfortunately, there are no quick or simple answers that will solve all network performance issues. Basically, you will have to generate a sniffer trace and check for utilization statistics, retransmissions, and delta times.

Note that while it is easy to extend STATSPACK to monitor disk I/O information, it is extremely difficult to extend STATSPACK to capture network traffic information. Network information varies widely between systems, and it is almost impossible to capture meaningful disk I/O information into STATSPACK extension  tables.

The most basic tool used by network administrators is the UNIX netstat utility. Unfortunately, netstat is implemented differently by all of the UNIX vendors, and the output from netstat looks very different depending on the operating system that you are using. Let?s take a brief tour of netstat and see how it can be used by the Oracle DBA to monitor network activity.

Using the UNIX netstat Utility

Netstat is a generic UNIX utility that displays the contents of various network-related structures in various formats. These formats are determined by the options passed to the netstat command.

Netstat is very good at telling the DBA what is happening on the network at the current time, but netstat does not give a good trending capability or periodic snapshot functionality. Most network administrators purchase a specialized third-party tool for long-term network monitoring. Let?s look at some of the differences in netstat and see some of the network information that netstat provides about the current state of the network.

Netstat on Solaris

On a Sun Solaris server, the netstat utility provides information about all network traffic touching the server:

root> netstat

TCP: IPv4
   Local Address   Remote Address    Swind Send-Q Rwind Recv-Q  State
--------------- -------------------- ----- ------ ----- ------ -------
sting.32773     ting.1521           32768      0 32768      0 ESTABLISHED
sting.1521      ting.32773          32768      0 32768      0 ESTABLISHED
sting.32774     ting.1521           32768      0 32768      0 ESTABLISHED
sting.1521      ting.32774          32768      0 32768      0 ESTABLISHED
sting.32775     ting.1521           32768      0 32768      0 ESTABLISHED
sting.1521      ting.32775          32768      0 32768      0 ESTABLISHED
sting.1521      az.janet.com.32777  24820      0 24820      0 ESTABLISHED
sting.1521      rumpy.jan.com.34601 24820      0 24820      0 ESTABLISHED
sting.22        onsrv1.jan.com.1120 31856      0 24616      0 ESTABLISHED
sting.1521      rumpy.jan.com.35460 24820      0 24820      0 ESTABLISHED

Active UNIX domain sockets

Address  Type          Vnode     Conn  Local Addr      Remote Addr
300021bda88 stream-ord 30002225e70 00000000 /var/tmp/.oracle/s#255.1
300021bdc30 stream-ord 300021f02c0 00000000 /var/tmp/.oracle/sextproc_key
300021bddd8 stream-ord 300021f0848 00000000 /var/tmp/.oracle/s#252.1

Netstat for Linux

In Linux, we see that the output from netstat is quite different from Solaris:

Proto Recv-Q Send-Q Local Address     Foreign Address         State     
tcp     0   0 donsrv1.rov:netbios-ssn intranet.janet.com:1351 ESTABLISHED
tcp     0   0 donsrv1.janet.com:1120  sting.janet.com:ssh     TIME_WAIT  
tcp     0  40 donsrv1.janet.com:ssh   hpop3-146.gloryroa:1096 ESTABLISHED
tcp     0   0 donsrv1.rov:netbios-ssn 192.168.1.105:1025      ESTABLISHED
tcp     0   0 donsrv1.janet.com:6010  donsrv1.janet.com:1104  CLOSE_WAIT 
tcp     0   0 donsrv1.janet.com:6010  donsrv1.janet.com:1103  CLOSE_WAIT 
tcp     0   0 donsrv1.janet.com:1023  grumpy.janet.com:ssh    ESTABLISHED
tcp     0   0 donsrv1.janet.com:ssh   exodus-rtr-2.arsdi:2195 ESTABLISHED
tcp     0   0 donsrv1.rov:netbios-ssn 192.168.1.107:1025      ESTABLISHED
tcp     0   0 donsrv1.rov:netbios-ssn 192.168.1.126:1030      ESTABLISHED

Active UNIX domain sockets (w/o servers)

Proto RefCnt Flags       Type       State         I-Node Path
unix  1      [ ]         STREAM     CONNECTED     741    @0000002a
unix  1      [ ]         STREAM     CONNECTED     745    @0000002b
unix  0      [ ]         STREAM     CONNECTED     182    @0000001a
unix  1      [ ]         STREAM     CONNECTED     763    @00000030
unix  8      [ ]         DGRAM                    397    /dev/log
unix  0      [ ]         DGRAM                    234471
unix  0      [ ]         DGRAM                    234252
unix  0      [ ]         DGRAM                    843   
unix  1      [ ]         STREAM     CONNECTED     764    /tmp/.X11-unix/X0
unix  1      [ ]         STREAM     CONNECTED     746    /tmp/.font-unix/fs-1
unix  1      [ ]         STREAM     CONNECTED     748    /tmp/.X11-unix/X0
unix  0      [ ]         DGRAM                    654   
unix  0      [ ]         DGRAM                    589   
unix  0      [ ]         DGRAM                    560   
unix  0      [ ]         DGRAM                    419   
[oracle@donsrv1 oracle]$ netstat -sp tcp
Ip:
    15753092 total packets received
    1 with invalid headers
    0 forwarded
    0 incoming packets discarded
    99397 incoming packets delivered
    20325485 requests sent out
Icmp:
    1041 ICMP messages received
    37 input ICMP message failed.
    ICMP input histogram:
        destination unreachable: 972
        timeout in transit: 31
        echo requests: 27
        echo replies: 11
    490 ICMP messages sent
    0 ICMP messages failed
    ICMP output histogram:
        destination unreachable: 463
        echo replies: 27
Tcp:
    131 active connections openings
    0 passive connection openings
    14 failed connection attempts
    0 connection resets received
    6 connections established
    15652680 segments received
    20276668 segments send out
    6933 segments retransmited
    2 bad segments received.
    25 resets sent
Udp:
    97289 packets received
    11 packets to unknown port received.
    3 packet receive errors
    48279 packets sent
TcpExt:
    9 packets pruned from receive queue because of socket buffer overrun
         unix  0      [ ]         DGRAM                    407

Hopefully, this brief description of the netstat utility will give you an appreciation for the scope and complexity of network tuning.

In practice, an experienced UNIX network administrator will have specialized utilities such as UNIX sniffers, that will monitor and tune network traffic.

Next, let?s have a brief overview of UNIX for Oracle distributed connections.

Oracle networking and UNIX

While the Oracle Transparent Network Substrate (TNS) keeps the underlying UNIX layer hidden from Oracle it is still quite important to fully understand the interaction between Oracle networking and UNIX..

When a client process communicate with a UNIX Oracle server, Oracle goes through several layers of abstraction to establish the connection (Figure 5-1).

Figure 1.1:  Net Interface Layers

          ---------------------------------------------
          | Client Code             |  "Client"
          | Net8 general code                  |  Process
          |     Net8 Protocol Adapter           |
          |    OS Interface for given Protocol  |
          ---------------------------------------------
          |     OS Device driver/s        |  Unix Kernel
          ---------------------------------------------
          |      Hardware interface to network  |  Hardware
          ---------------------------------------------
                        ^   |
                        |   |
                        |   v
          ---------------------------------------------
          |      Hardware interface to network  |  Hardware
          ---------------------------------------------
          |     OS Device driver/s        |  Unix Kernel
          ---------------------------------------------
          |    OS Interface for given Protocol  |
          |     Net8 Protocol Adapter           |  "Server"
          | Net8 general code                  |  Process
          | Server Code             | 
          ---------------------------------------------

Figure 1: Oracle networking and UNIX

Here we see that the client code calls Net8, and Net8 calls the Net8 protocol adapter.  The protocol adapter, in turn passes the information to the UNIX interface, which creates the connection.

To understand this abstraction, let?s take a simple example.  Let?s assume that we issue the following distributed request to a remote Oracle UNIX database.

select
   count(*)
from
   emp@new_york;

Let?s follow each step of the process:

Preparing to connect to a remote server

The first step in the process is for Oracle to go to the database link.  In the case of this query, we are looking at the new_york database link.  A review of this link from the DBA_DB_LINKS view show use the information contained in the link.

SQL> select * from dba_db_links where db_link = ?NEW_YORK?;
OWNER
------------------------------
DB_LINK
---------------------------------------------------------------------
USERNAME
------------------------------
HOST
---------------------------------------------------------------------
CREATED
---------
READMAN
NEW_YORK
MASTER
(DESCRIPTION =
         (ADDRESS =
          (COMMUNITY = TCP)
          (PROTOCOL = TCP)
          (HOST = nyserv1)
          (PORT = 1521)
         )
        (CONNECT_DATA = (SID = prodcust)
        (SERVER = DEDICATED))
       )
01-AUG-01

Here we see that the database link contains almost all of the information that we need to connect to the remote server.  We have:

      1 ? The remote server host name (nyserv1)

      2 ? The network protocol (TCP/IP)

      3 ? The listening port on the remote server (1521)

      4 ? The name of the remote Oracle database (prodcust)

      5 ? The remote Oracle user ID (master)

      6 ? The remote user ID password (hidden from display)

The only this remaining is the translation of the UNIX hostname into an IP address.  In UNIX, a file called /etc/hosts is used to look-up the IP address using the hostname.  Here is an example of a /etc/hosts file.

root> cat /etc/hosts
127.0.0.1    localhost
192.144.12.205  marvin  marvin.ibm.com loghost pr4oddb-01
192.121.13.206  nyserv1  nyserv1.ibm.com loghost pr4oddb-01
192.144.1.200  blake  blake.ibm.com loghost pr4oddb-01

Here we see that UNIX will lookup the IP address in /etc/hosts and resolve the host name to 192.121.13.206.

Once Oracle has built the connect string for the remote server, the information is passed to UNIX, which establishes the connection to the remote server.  The Oracle listener process is attached to port 1521 on the remote server, and this listener process receives the connection request and spawns a UNIX [process ID (PID) on behalf on the incoming connection.  After the process is created, the listener attaches the request to Oracle.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.