SAP on Oracle: Questions and Answers on Oracle Net

              Oracle Net Services (Oracle 9), Oracle Net (Oracle 8), and SQL*Net V2 (Oracle 7) are used for communication between an Oracle client (for example, an R/3 instance) and an Oracle server (that is, the Oracle database). In the case of R/3, they are used to establish a connection between R/3 work processes and the database, and to exchange data between R/3 and Oracle. The connection is operated using predefined protocols such as TCP/IP.

              When a client wants to connect to the Oracle server, this connection is made on the basis of the configuration file tnsnames.ora located on the client. This file is used to establish a connection to the Oracle listener on the server. The listener.ora file (on the Oracle server) is used to configure the listener. The listener accepts incoming inquiries and establishes a connection between the client process (for example, the R/3 work process) and an Oracle shadow process.

              The sqlnet.ora configuration file (and also protocol.ora up to Oracle 8i) exists on both the client and server, and it is used to configure and activate certain functions in the Oracle Net area.

              Once the connection has been established, the client and server communicate directly with each other, which means that the listener is not required for connections that already exist. Since R/3 work processes usually only log on to the database once (and then remain logged on), you can restart the listener without any adverse effects while the R/3 system is running (for example, you can restart the listener to activate configuration changes). However, if you want to establish a new connection in precisely this period of time (for example, from sapdba or the BR tools, or if rdisp/wp_auto_restart is set), an error will occur when establishing the connection (ORA-12203, for example).

              The following files are significant for the Oracle Net configuration:

tnsnames.ora: This file is required on the client, so that you can establish a connection to the listener on the server.

listener.ora: This is the listener configuration file, which is required to correctly assign requests received by the server to Oracle shadow processes.

sqlnet.ora: This file defines the technical details for the Oracle Net setup (name resolution type, domain name, and so on).

protocol.ora (Oracle <= 8.1): This file contains protocol-dependent definitions; as of Oracle 9, protocol.ora is no longer supported. Instead, the definitions are made in sqlnet.ora.

              The configuration files are searched for in the following sequence:

3. a) The path that is specified in the environment variable TNS_ADMIN (R/3 <= 3.1I: TNS_ADMIN = /usr/sap/trans; R/3 >= 4.0B: TNS_ADMIN is no longer set; as of Oracle 10g: TNS_ADMIN on application servers on the TNS files path)
4. b) The directory /etc and in individual cases /var/opt/oracle (UNIX only)
5. c) The directory $ORACLE_HOME/network/admin

6. In the R/3 environment, which protocols are the basis for Oracle Net?

              In the R/3 environment, two protocols are used as the basis for Oracle Net communication:

TCP/IP: Connection of remote and local clients to the Oracle server using the network interface.

IPC: Alternative connection of local clients using shared memory (up to Oracle 8.0 or if tnsnames.ora has a specific configuration).

              Just like IPC, the Bequeath protocol (BEQ) is a type of shared memory connection. However, unlike IPC, it does not run on the listener. This protocol is only used by Oracle tools such as svrmgrl or sqlplus. It is not used by SAP tools.

              Example:

1)  <sid>.WORLD=

2) (DESCRIPTION =

3)      (SDU = 32767)

4)      (ADDRESS_LIST =

5)        (ADDRESS = (PROTOCOL = IPC) (KEY = <sid>))

6)        (ADDRESS = (PROTOCOL = TCP) (HOST = <host>)

7)                   (PORT = <port>))

8)      )

9)      (CONNECT_DATA =

10)       (SID = <sid>)

11)     )

12)   )

              Explanation:

              Line 1) shows the TNS alias (usually the SID for the R/3 system) for which the following section is valid. In the R/3 environment, this alias is retrieved from the environment variable dbs_ora_tnsname or from the R/3 profile parameter dbs/ora/tnsname. If you are using secondary databases, the “Conn.Info” entry in the DBCON table is relevant. The “.WORLD” after <sid> is the domain. This domain is defined using the entry NAMES.DEFAULT_DOMAIN in sqlnet.ora. In the R/3 environment, WORLD is used as the default domain name.

              Line 3) contains the SDU (session data unit) that defines the size of the data packages that were sent at session level. The maximum value is 32767 (Oracle >= 11.2.0.2: 65535). The larger the value selected, the fewer the number of packages that have to be sent when larger volumes of data are exchanged.

              Line 5) defines an IPC connection to the database with the SID <sid>.

              Lines 6) and 7) define a TCP/IP connection. This is only used if the IPC connection does not work (for example, because the target database does not run on the same host). Details of the host name of the target database and the port that the listener is listening to are vital for the TCP/IP connection. These two pieces of information must correspond to the relevant entries in listener.ora on the target database.

              Line 10) contains the SID of the database to which you want to establish a connection. As of Oracle 8.1, you can also define a SERVICE_NAME that does not depend on the SID. For more information, see Note 563574.

              In some sections of the file, the listener.ora contains the name of the listener used in parameter names or definitions. The following example refers to the default name “LISTENER”. Depending on the configuration, you can use any other names (even though each occurrence of “LISTENER” in listener.ora must be replaced with the actual listener name).

              The listener.ora comprises three parts:

1. a) Definition of the possible connections

                       Example:

1)  LISTENER =

2)    (ADDRESS_LIST =

3)      (ADDRESS = (PROTOCOL = IPC) (KEY = <sid>))

4)      (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)

5)                 (PORT = <port>)) 6)    )

                       Lines 3) and 4) define the protocols and connections covered by the listener. In general, these entries should correspond to the relevant entries in tnsnames.ora.

                       If sporadic terminations occur when connections are simultaneously established for numerous work processes, you can add “(QUEUESIZE = <queue length>)” to the TCP-ADDRESS section in accordance with SAP Note 131561 in order to prevent the listener queue from overflowing.

1. b) Definition of the SIDs and home directories of the target databases

                       Example:

1) SID_LIST_LISTENER =

2)    (SID_LIST =

3)      (SID_DESC =

4)        (SDU = 32767)

5)        (SID_NAME = <sid>)

6)        (ORACLE_HOME = /oracle/<sid>/817_64)

7)      )

8)    )

                       Line 4) contains the SDU that is also contained in tnsnames.ora.

                       Line 5) contains the SID of the target database.

                       Line 6) contains the home directory of the target database.

1. c) Parameter definitions

                       Example:

STARTUP_WAIT_TIME_LISTENER = 0

INBOUND_CONNECT_TIMEOUT_LISTENER = 120

TRACE_LEVEL_LISTENER = OFF

ADR_BASE_LISTENER = <sapdata_home>/saptrace (Oracle >= 11g)

                       STARTUP_WAIT_TIME_LISTENER specifies the number of seconds that the listener should wait after it starts before it answers the first “lsnrctl status” request.

                       INBOUND_CONNECT_TIMEOUT_LISTNER specifies the number of seconds after which a connection should be closed again if a complete logon cannot be performed. This parameter might be required as of Oracle 10g so that you can avoid problems such as those described in SAP Note 1055400.

                       CONNECT_TIMEOUT_LISTENER defines the maximum number of seconds that the listener should wait for an affirmative confirmation from the database when establishing a connection.

                       TRACE_LEVEL_LISTENER enables a listener trace with different levels (see details below).

                       ADR_BASE_LISTENER (Oracle >= 11g) defines the ADR location for the listener (SAP Note 1431751).

              In sqlnet.ora, you can define numerous parameters that are relevant for Oracle Net.

              Example:

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

SQLNET.EXPIRE_TIME = 5

NAMES.DEFAULT_DOMAIN = WORLD

NAMES.DIRECTORY_PATH = (TNSNAMES)

SQLNET.INBOUND_CONNECT_TIMEOUT = 120

              If AUTOMATIC_IPC is activated, the listener automatically converts a TCP/IP request into an IPC connection if it is a local connection and the IPC connection is defined in listener.ora. However, this parameter is no longer supported as of Oracle 8.1. Instead, you must enter the IPC connection directly in tnsnames.ora, if you want to use it.

              TRACE_LEVEL_CLIENT enables the Oracle Net client trace with different levels (see below for details).

                            SQLNET.EXPIRE_TIME specifies the number of minutes after which a connection is terminated if there are connection problems (for example, scheduling on the client). If the value is 0, this “Dead Connection Detection” is deactivated. To avoid problems such as those described in Note 20071, we recommend that you specify a positive value (such as 5 or 10).

                            NAMES.DEFAULT_DOMAIN specifies a default domain name that is automatically attached to unqualified connection requests. In standard SAP systems, the default domain WORLD is used. The default domain must correspond to the <sid>.<domain> entries in tnsnames.ora.

                            NAMES.DIRECTORY_PATH specifies the sequence of methods for the name resolution of client requests. In the SAP environment, TNSNAME is used for name resolution by default, that is, using the configuration file tnsnames.ora.

              With Oracle 9i, TCP.NODELAY must also be entered in sqlnet.ora. This was taken from protocol.ora with earlier releases (see below).

              SQLNET. INBOUND_CONNECT_TIMEOUT_LISTNER specifies the number of seconds after which a connection should be closed again if a logon cannot be performed in full. This parameter might be required as of Oracle 10g so that you can avoid problems such as those described in SAP Note 1055400.

              Example:

TCP.NODELAY = YES

                            TCP.NODELAY should always be included in protocol.ora, to avoid performance problems when a TCP connection is used. If the parameter is not set, Nagle’s TCP algorithm is executed and, as a result, small data packets in conjunction with the outstanding acknowledgments of previous packages can only be sent with a delay. For more information, see Note 198752. Upper case, lower case, blank spaces and YES, TRUE or 1 as the parameter value are of no importance. All of the following entries are therefore valid:

tcp.nodelay = 1

TCP.nodelay=TRUe

TCP.NODELAY =yes

              As of Oracle 9i, TCP.NODELAY is taken from sqlnet.ora. Since a lot of SAP kernels are still linked with Oracle 8 (see Note 521230), the entry remains relevant in protocol.ora, and also with Oracle 9i.

                            With some Oracle releases, TCP.NODELAY was mistakenly taken from .protocol.ora instead of protocol.ora. Therefore, a .protocol.ora file should be created with the TCP.NODELAY entry as a precaution.

              In addition, you can use protocol.ora to control which hosts can or cannot access the database. This task is performed with the parameters tcp.validnode_checking, tcp.invited_nodes and tcp.excluded_nodes. See Note 186119 for further information.

                            As of Oracle 10g, TCP.NODELAY is set to YES by default. Therefore, you are no longer required to explicitly set this parameter if you use Oracle >= 10g on the server and on the client.

              The Oracle Net Configuration Assistant is available on NT and Windows 2000. You can use this assistant to create the Oracle Net configuration files in accordance with SAP Note 445769. Note 555881 describes what you can do if the assistant does not start correctly.

              On UNIX, you can use “netca” to start the Oracle Net Configuration Assistant.

              If you operate several database instances on a single node, only one listener is required even if the individual instances have different Oracle releases. You must simply make sure that you use the listener for the highest Oracle version that was installed (downward compatibility). You can then enter all existing instances in the relevant listener.ora. See the example for listener.ora in Note 98252.

              If you nevertheless decide to use several listeners in specific cases, refer to the procedure described in SAP Note 153835.

              The connection type (TCP, IPC, BEQ) is determined in the R/3 environment as follows:

TCP/IP and IPC are used when a logon is performed with the following:

Generic: connect <user>/<password>@<target>

Example: connect sapr3/sap@C11

            IPC is used under the following conditions if

           the target database is installed on the local host:

You are using Oracle <= 8.0 and AUTOMATIC_IPC is set to ON in sqlnet.ora.

In tnsnames.ora, a corresponding IPC section is defined before the TCP section.

           Otherwise, TCP is used to access the data. R/3 only uses logons with a specified <target>.

R/3 tools do not use BEQ. It is only used by Oracle tools such as sqlplus. In this case, the logon must use:

Generic: connect <user>/<password>

Example: connect sapr3/sap

              The following basic checks can help you to find possible errors in the Oracle Net environment:

“tnsping <target>” checks if a listener is running for <target> (<target> is usually the SID).

“lsnrctl status” on the database server displays the current listener status.

Logons in sqlplus with “<user>/<password>” and “<user>/<password>@<target>” verify that the BEQ and TCP or IPC connections are set up correctly.

“R3trans -x” checks the database connect and generates the trans.log log file in the current directory. This can be checked for Oracle Net-specific error codes (which usually begin with “12”).

1. a) Listener trace:

                       Parameter file: listener.ora

                       Parameter: TRACE_LEVEL_LISTENER = <level>

                       Values:

                        <level> = 0 | OFF (deactivated)

                        <level> = 4 | USER (standard)

                        <level> = 10 | ADMIN (extended)

                        <level> = 16 | SUPPORT (comprehensive)

                       Trace file: $ORACLE_HOME/network/trace/listener.trc

1. b) Client trace:

                       Parameter file: sqlnet.ora

                       Parameters: DIAG_ADR_ENABLED = OFF

                                         TRACE_LEVEL_CLIENT = <level>

                                         TRACE_DIRECTORY_CLIENT = <path for directory>

                                         TRACE_FILE_CLIENT = <file name>

                                         TRACE_FILELEN_CLIENT = <size in kB, greater than 10240 >

                       Values for TRACE_LEVEL_CLIENT:

                        <level> = 0 | OFF (deactivated)

                        <level> =  4 | USER     (standard trace)

                        <level> = 10 | ADMIN    (extended trace)

                        <level> = 16 | SUPPORT  (most precise trace)

                                              Trace file: If TRACE_DIRECTORY_CLIENT is set, this directory is used. If it is missing, the directory defined in “user_dump_dest” is used.

                       You can use the tool TRCASST to convert the output to a more readable format:

                                  trcasst <client_trace> > <output_file>

1. c) Server trace:

                       Parameter file: sqlnet.ora

                       Parameters: DIAG_ADR_ENABLED = OFF

                                         TRACE_LEVEL_SERVER = <level>

                                         TRACE_DIRECTORY_SERVER = <path for directory>

                                         TRACE_FILE_SERVER = <file name>

                                         TRACE_FILELEN_SERVER = <size in kB, greater than 10240>

                       Values for TRACE_LEVEL_SERVER:

                        <level> = 0 | OFF (deactivated)

                        <level> =  4 | USER     (standard trace)

                        <level> = 10 | ADMIN    (extended trace)

                        <level> = 16 | SUPPORT  (most precise trace)

                       Trace file: If TRACE_DIRECTORY_CLIENT is set, this directory is used. If it is missing, the directory defined in “user_dump_dest” is used.

                       You can use the tool TRCASST to convert the output to a more readable format:

                                  trcasst <server_trace> > <output_file>

1. d) Listener log:

                       Log file: $ORACLE_HOME/network/log/listener.log

                       The listener log, which is written automatically, logs the incoming connection requests. You can use this log, for example, to check whether the connection is a TCP or IPC connection. The log also records errors that may have occurred when establishing the connection.

CAUTION! If DIAG_ADR_ENABLED is not set in sqlnet.ora, the system considers only the following parameters:

                            ADR_BASE

                            TRACE_LEVEL_CLIENT

                            TRACE_LEVEL_SERVER

                            TRACE_TIMESTAMP_CLIENT

                            TRACE_TIMESTAMP_SERVER

              You can use $ORACLE_HOME/network/log/listener.log to determine which log is actually used when establishing a new connection (this is because an entry is written at the end of the file, using the listener, for each connection setup):

1. a) TCP/IP: Entry contains “PROTOCOL=tcp”, for example:

12-DEC-2002 10:38:47 * (CONNECT_DATA=(SID=HUY)(GLOBAL_NAME=HUY.WORLD)

(CID=(PROGRAM=)(HOST=saphost)(USER=huyadm))) * ADDRESS=(PROTOCOL=tcp) ((HOST=10.19.28.106)(PORT=50576)) * establish * HUY * 0

1. b) IPC: Entry contains “PROTOCOL=ipc”, for example:

14-DEC-2002 09:07:06 * (CONNECT_DATA=(SID=HUY)(GLOBAL_NAME=HUY.WORLD)

(CID=(PROGRAM=)(HOST=saphost)(USER=huyadm))) * (ADDRESS=(PROTOCOL=ipc)

(KEY=HUY.WORLD)) * establish * HUY * 0

1. c) BEQ: Since the listener is not used with the BEQ Connect, no entry is written to the listener.log either.

              When a connection is established from a work process (or R3TRANS, SAPLICENSE) to the database, the OPS$ mechanism and the two-step logon concept are used to establish up to three connections to Oracle in succession. Therefore, several entries also appear in the listener.log file. This behavior is normal and is not an error. For detailed information about the OPS$ mechanism, see Note 400241.

As of Oracle 9, “lsnrctl status” sometimes returns services with the status UNKNOWN, for example:

Service “C11” has 1 instance(s).

  Instance “C11”, status UNKNOWN, has 1 handler(s) for this service…

              This is normal behavior rather than an error if you use the previous standard method by means of the definition of SID_NAME in listerner.ora instead of the automatic instance registration process (cf. SAP Note 563574). Consequently, there is no handling requirement.

Previously, the target instances were announced to the listener using the SID_LIST_LISTENER section in listener.ora. Since Oracle 8.1, you can now also omit this section and instead activate an automatic registration of the target instances with the listener. In this case, the PMON process of an instance registers once per minute for all listeners defined by the Oracle parameters LOCAL_LISTENER or REMOTE_LISTENER. If the local listener is running on port 1521, PMON also finds it without LOCAL_LISTENER being specified explicitly.

Note 563574 contains a more detailed description of additional prerequisites for the successful use of automatic instance registration.

If you are using a Real Application Cluster (RAC), you can use the mechanisms described below for load distribution and high availability. Note that you can only use them in combination with automatic instance registration.

Client Load Balancing

On the Oracle client, an incoming connection is forwarded at random to one of the target addresses specified in tnsnames.ora.

You can activate it in tnsnames.ora using (LOAD_BALANCE=ON).

Example for tnsnames.ora:

…

(ADDRESS_LIST =

   (LOAD_BALANCE=ON)

   (ADDRESS = (PROTOCOL=TCP) (HOST=<racnode1>) (PORT=1521))

   (ADDRESS = (PROTOCOL=TCP) (HOST=<racnode2>) (PORT=1521))

)

…

Important note: You cannot use client load balancing in the SAP environment (see SAP Note 527843).

Connection Load Balancing

In the case of an incoming connection request, the listener checks the server to see which of its registered target instances has the lowest workload. If it is the local instance, a connection is opened directly. Otherwise, the request is forwarded to the listener belonging to the remote instance, so that it can establish a connection.

You do not have to activate it explicitly; it is always active if more than one instance has registered with a listener.

Transparent Application Failover

In many cases, it allows an Oracle reconnect after a connection terminates (for example, due to RAC instance crashes), without the client session terminating with an Oracle error.

It is activated by a FAILOVER_MODE section in tnsnames.ora.

Example section from tnsnames.ora:

…

(CONNECT_DATA =

    (SERVICE_NAME=<service>)

    (FAILOVER_MODE=

      (TYPE=SELECT)

      (METHOD=BASIC)

    )

  )

…

              To analyze and solve problems in the network area between SAP instances and the Oracle database, note the following points:

Make sure that the network has enough bandwidth. To do this, use the NIPING tool, for example (see Note 500235). It makes sense to use a network with a bandwidth of 1 GB (for example, Gigabit Ethernet) because smaller bandwidths may not be sufficient in certain cases.

If you are using Oracle <= 9i, make sure that TCP.NODELAY is set correctly as described above. To ensure this, create the entry “tcp.nodelay=true” in the three files protocol.ora, .protocol.ora and sqlnet.ora on the database server, and on all application servers.

The smaller the SDU set in listener.ora or tnsnames.ora, the less data can be transferred for each network communication. To avoid unnecessary network communications if there are large volumes of data, you can increase the SDU in listener.ora and tnsnames.ora to 32767 (Oracle 11.2.0.2 >=: 65535); see the sample files above. However, note that a higher SDU value may result in an overhead when lower volumes of data are transferred.

As of Oracle 9.2.0.4 or 10g, you can also adjust the default SDU size. To do this, you must set the DEFAULT_SDU_SIZE=<size_in_byte> parameter in sqlnet.ora. The maximum value is 32767 (Oracle >= 11.2.0.2: 65535).

To possibly ensure a faster connection for local communications, with IPC rather than TCP, you can add an IPC section to the ADDRESS_LIST section of tnsnames.ora before the TCP section (see the sample file above).

A large load on the network between the SAP instances and the Oracle database is always caused by SQL statements that return a large number of data records. If this occurs, refer to Note 766349 and check for problematic statements with a very high number of “rows processed”.

              The Oracle Net configuration files contain only the server port that can be used to reach the listener and which might also be used for further Oracle Net configuration. However, the client also uses an individual port for each client process and this port is chosen regardless of the listener port.

              For various reasons, for example, if you use a central Oracle Net configuration directory, it might make sense to explicitly set TNS_ADMIN. Even though this does not correspond to the SAP standard, it is nevertheless permitted.

              The following SAP Notes describe errors that frequently occur in relation to Oracle Net:

Note 131561: Work process sometimes terminate during database connect

Note 445038: Problems when accessing Oracle Net configuration files

Note 437362: Composite SAP Note: ORA-12500

Note 441518: Composite SAP Note: ORA-12560

Note 443867: Composite SAP Note: ORA-12154

Note 445029: Composite SAP Note: ORA-12203

Note 505630: Composite SAP Note: ORA-12547

Note 513524: Composite SAP Note: ORA-12540

Note 563574: Composite SAP Note: ORA-12514

Note 576919: Composite SAP Note: ORA-12505

Note 609332: Composite SAP Note: ORA-12541

Note 614036: Composite SAP Note: ORA-12638

Note 632483: Composite SAP Note: ORA-06401

Note 654717: Composite SAP Note: ORA-12538

Note 722966: Composite SAP Note: ORA-12546

Note 723641: Composite SAP Note: ORA-12545

              Comprehensive information about Oracle Net is contained in the following two documents in the Oracle 9i/10g online documentation:

Net Services Administrator’s Guide

Net Services Reference Guide

              In addition, the following Oracle Metalink documents describe the troubleshooting and analysis of Oracle Net:

Doc ID: 156485.1 – Examining Oracle Net, Net8, SQL*Net Trace Files

Doc ID: 779226.1 – Troubleshooting Oracle Net

Note 374116.1 – How to Match Oracle Net Client and Server Trace Files

Note 219968.1 – SQL*Net, Net8, Oracle Net Services – Tracing and

                Logging at at a Glance

Note 395525.1 – How to Enable Oracle SQLNet Client, Server, Listener,

                Kerberos and External Procedure Tracing from Net Manager

Note 793415.1 – How to Perform the Equivalent of Sqlnet Client Tracing with Oracle JDBC Thin Driver