Online Tutorials & Training Materials | STechies.com
Register Login

Oracle Net Interview Questions and Answers

|| || 0

Oracle Net Interview Questions and Answers
Stechies

Oracle Net FAQs

1. What is Oracle Net or (Net8/SQL*Net V2)?

Ans) Oracle Net Services (Oracle 9), Oracle Net (Oracle 8) or 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.

2. Can you give me an overview of the Oracle Net architecture?

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

3. Can I start the listener safely while the R/3 is running?

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

4. Which Oracle Net configuration files exist and what are their tasks?

Ans) 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 or lower): This file contains protocol-dependent definitions; as of Oracle 9, protocol.ora is no longer supported. Instead, the definitions are made in sqlnet.ora.

5. Where can I find the Oracle Net configuration files?

 Ans) The configuration files are found in the following sequence:

a) The path that is specified in the environment variable TNS_ADMIN (R/3 3.1I or lower: TNS_ADMIN = /usr/sap/trans; R/3 4.0B or higher: TNS_ADMIN is no longer set; as of Oracle 10g:  TNS_ADMIN on application servers on the TNS files path)

b) The directory /etc and in individual cases /var/opt/oracle (UNIX only)

c) The directory $ORACLE_HOME/network/admin

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

Ans) 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: Faster connection of local clients using shared memory (up to Oracle 8.0 or if tnsnames.ora has a specific configuration).

Note: For performance reasons, IPC should be used for R/3 instances located directly on the database server.

7. What is the bequeath protocol?

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

8. What do the contents of tnsnames.ora look like?

Ans) For example:

1)  < sid>.WORLD=
2)   (DESCRIPTION =
3)   SDU = 32768)
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 packets that were sent at session level. The maximum value is 32768. 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 for 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.

9. What do the contents of listener.ora look like?

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

For 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 Note 131561, to prevent the listener queue from overflowing.

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

For example:

1)  SID_LIST_LISTENER =
2)    (SID_LIST =
3)      (SID_DESC =
4)        (SDU = 32768)
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 for the target database.

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

1 c) Parameter definitions

For example:

STARTUP_WAIT_TIME_LISTENER = 0
INBOUND_CONNECT_TIMEOUT_LISTENER = 120
TRACE_LEVEL_LISTENER = OFF

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 after how many seconds a connection should be closed again if you cannot perform a complete logon. This parameter may be required as of Oracle 10g so that you can avoid problems such as those from Note 1055400.

CONNECT_TIMEOUT_LISTENER defines the number of seconds that the listener should wait for an affirmative confirmation from the database before it establishes a connection.

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

10. What do the contents of sqlnet.ora look like?

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

For 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, which was taken from protocol.ora with earlier releases (see below).

SQLNET. INBOUND_CONNECT_TIMEOUT specifies after how many seconds a connection should be closed again if you cannot perform a complete logon. This parameter may be required as of Oracle 10g so that you can avoid problems such as those in Note 1055400.

11. What do the contents of protocol.ora look like (as of 9i: sqlnet.ora)?

Ans) For 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 or higher on the server and on the client.

12. How can I generate these files?

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

13. Do I require a dedicated listener for each database instance?

Ans) 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 Note 153835.

14. What are the connection types created by certain connect strings?

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

    General: connect <user>/<password>@<target> For 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 or lower 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:

    General: connect <user>/<password> For example:  connect sapr3/sap       

15. How can I check whether Oracle Net is working correctly?

Ans) 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").

16. What are the trace options for Oracle Net?

Ans) 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
  • Parameter: TRACE_LEVEL_CLIENT = <level>

Values:

<level> =  0 | OFF      (deactivated)
<level> =  4 | USER     (standard trace)
<level> = 10 | ADMIN    (extended trace)
<level> = 16 | SUPPORT  (most precise trace)

Trace file: cli_<pid>.trc or cli.trc in the current directory

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

trcasst <client_trace> > <output_file>

1 c) Server trace:

  • Parameter file: sqlnet.ora
  • Parameter: TRACE_LEVEL_SERVER = <level>

Values:

<level> =  0 | OFF      (deactivated)
<level> =  4 | USER     (standard trace)
<level> = 10 | ADMIN    (extended trace)
<level> = 16 | SUPPORT  (most precise trace)

Trace file: $ORACLE_HOME/network/trace/svr_<pid>.trc

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

trcasst <server_trace> > <output_file>

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.

17. How can I check which protocol is actually used for a connection?

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

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

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

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

18. Why do several entries appear in the listener.log file when I start a work process?

 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 in succession to establish up to three connections to Oracle. 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.

19. Why does the listener report services with the status "UNKNOWN" as of Oracle 9?

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

If you use the current standard method, which involves the SID_NAME definition in listener.ora, and not automatic instance registration (see Note 563574), this is not an error, but a normal response. Consequently, there is no handling requirement.

20. What does automatic instance registration mean?

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

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

21. Which Oracle Net mechanisms can also be used in the RAC environment?

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

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

22. What must I take into account with regard to network performance between SAP and Oracle?

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

  1. 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.
  2. If you are using Oracle 9i or lower, 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.
  3. 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 32768 (see the sample files above). However, note that a higher SDU value may result in an overhead when lower volumes of data are transferred.
  4. 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.
  5. 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).
  6. 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".

23. Why does the system also use ports that are not defined in tnsnames.ora and listener.ora?

Ans) The Oracle Net configuration files contain only the port used to reach the listener. It does not make any sense to run all database accesses through the listener and the associated port, since this could result in bottlenecks. For this reason, 1:1 communication is set up between the Oracle shadow process and the client process. Here, each client process uses an individual port to communicate with the associated Oracle shadow process. The port number is chosen as required, but there is no overlap with the services that are defined under /etc/services.

24. Can I explicitly set TNS_ADMIN even though it differs from the SAP standard?

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

25. Which important notes provide information on Oracle Net problems in the R/3 environment?

Ans) The following notes describe errors that frequently occur in relation to Oracle Net:

  • Doc 131561: Work process sometimes terminate during database connect
  • Doc 445038: Problems when accessing Oracle Net configuration files
  • Doc 437362: Composite SAP Note: ORA-12500
  • Doc 441518: Composite SAP Note: ORA-12560
  • Doc 443867: Composite SAP Note: ORA-12154
  • Doc 445029: Composite SAP Note: ORA-12203
  • Doc 505630: Composite SAP Note: ORA-12547
  • Doc 513524: Composite SAP Note: ORA-12540
  • Doc 563574: Composite SAP Note: ORA-12514
  • Doc 576919: Composite SAP Note: ORA-12505
  • Doc 609332: Composite SAP Note: ORA-12541
  • Doc 614036: Composite SAP Note: ORA-12638
  • Doc 632483: Composite SAP Note: ORA-06401
  • Doc 654717: Composite SAP Note: ORA-12538
  • Doc 722966: Composite SAP Note: ORA-12546
  • Doc 723641: Composite SAP Note: ORA-12545

26. Where can I find additional information about Oracle Net?

Ans) Comprehensive information about Oracle Net is contained in the following two documents in the Oracle 9i online documentation:

  • Net Services Administrator's Guide
  • Net Services Reference Guide


Related Articles