Archive for the ‘Oracle Network’ Category

Common Oracle TNS Errors

In this post , I am trying to cover common TNS errors and possible corrective measures.

ORA-12154: TNS:could not resolve service name

SQL> connect SYSTEM/manager@DEV
ERROR:
ORA-12154: TNS:could not resolve service name

The most common TNS error mostly due to multiple ORACLE HOME, tnsnames.ora and sqlnet.ora conflicts.

  • Check that the instance or database name is being typed in correctly. Check ORACLE_SID,ORACLE_HOME
  • Make sure listener is up and running
  • lsnrctl > status           if down then

    lsnrctl > start

  • PING the server to see if network is up
  • ping 168.192.1.22

  • TNSPING the server and then try connecting
  • tnsping DEV

  • Check the tns names.ora file to validate that the instance name has been entered correctly. Make sure that all entries are properly terminated with parenthesis.
  • Validate the domain name entry in sqlnet.ora is not conflicting with the full database name resolution.
  • If nothings works, delete and recreate the entry to eliminate any character or control character issues.

For more details on ORA-12154 causes and actions

 

TNS-03505: Failed to resolve name

Message 3511 not found; product=network; facility=TNS
TNS-03505: Failed to resolve name

This is quite similar to the above error so you have to again check the environmental variables like ORACLE_HOME and PATH and make sure their values are set correctly.
The issue usually occurs when the tns entiries we provide has some unwanted characters or spaces etc.Please also check the tns entry(alias) is pointing to the correct database with sid hostname.

For more details on TNS-03505 reasons and remedy actions

ORA-12537: TNS: connection closed error

It happens if the connection has been closed most likely due to the following reasons:

  • Firewall has disconnected idle connections
  • Configuration problem in sqlnet.ora or listener.ora
  • Time out occurred on client connection
  • Path name that is too long for the Oracle TNS client on windows

Try the following to fix this issue:

  1. Install the exe in a shorter named directory.
  2. Modify the TNS Names entry so that it is shorter.
  3. Replace domain names with IP Addresses if that shortens and vice versa.
  4. Remove any parameters from the entry that are not needed.
  5. Patch the Database Listener to the most recent version

For more details on ORA-12537 reasons and remedy actions

 

Troubleshooting Tips from the Field for Oracle Net Services

Here are some tips you may find helpful when you are having difficulty diagnosing network problems from Oracle documentation:

  • Use the node or network address during configuration instead of the name of the server computer

    This eliminates any internal lookup problems and make the connection slightly faster.

  • If you are using TCP/IP addresses, use the IP address rather than host name

    For example, change the (HOST=server_name) line in the tnsnames.ora file with the internet address, for example (HOST=198.32.3.5).

  • Perform a loopback test

    Perform a loopback test on the server as described in “Testing Configuration on the Database Server”. If the test passes, ftp the tnsnames.ora and sqlnet.ora files to the client.

  • Check what is between you and the server

    If it is a wide area network (WAN), identify any intermediate systems that may not work correctly. If all computers are fine, the problem may be a timing issue.

  • Verify whether or not there is a timing issue

    Timing issues are associated with an ORA-12535 error in the client log files.

    To resolve this, try speeding up the connection by using exact addresses instead of names and increase the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. The default value for this parameter is 10 seconds.

  • Determine which Oracle applications are failing

    SQL*Plus may work, but CASE tools may not. If you determine the problem is a data volume issue, try to transfer a large (5 MB) file with the base connectivity.

 

Resolving most common Error messages for Oracle Net Services

 

Connections and Sessions

Establishing a Connection

Before users can submit SQL statements to an Oracle database, they must connect to an instance.

  • The user starts a tool such as SQL*Plus or runs an application developed using a tool such as Oracle Forms. This application or tool is executed as a user process.
  • In the most basic configuration, when a user logs on to the Oracle server, a process is created on the computer running the Oracle server. This process is called a server process. The server process communicates with the Oracle Instance on behalf of the user process that runs on the client. The server process executes SQL statements on behalf of the user.

Connection:

A connection is a communication pathway between a user process and an Oracle server. A database user can connect to an Oracle server in one of three ways:

  • The user logs on to the operating system running the Oracle Instance and starts an application or tool that accesses the database on that system. The communication pathway is established using the interprocess communication mechanisms available on the host operating system.
  • The user starts the application or tool on a local computer and connects over a network to the computer running the Oracle Instance. In this configuration, called client-server, network software is used to communicate between the user and the Oracle server.
  • In a three-tiered connection, the user’s computer communicates over the network to an application or a network server, which is connected through a network to the machine running the Oracle Instance. For example, the user runs a browser on a computer on a network to use an application residing on an NT server that retrieves data from an Oracle database running on a UNIX host.

Sessions

A session is a specific connection of a user to an Oracle server. The session starts when the user is validated by the Oracle server, and it ends when the user logs out or when there is an abnormal termination. For a given database user, many concurrent sessions are possible if the user logs on from many tools, applications, or terminals at the same time. Except for some specialized database administration tools, starting a database session requires that the Oracle server be available for use.

Note: The type of connection explained here, where there is a one-to-one correspondence between a user and server process, is called a Dedicated Server connection. When using a Shared Server configuration, it is possible for multiple user processes to share server processes.

 

User Process

A database user who needs to request information from the database must first make a connection with the Oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle server. Rather it generates calls through the user program interface (UPI), which creates a session and starts a server process.

Server Process

Once a user has established a connection, a server process is started to handle the user processes requests. A server process can be either a Dedicated Server process or a Shared Server process. In a Dedicated Server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated. In a Shared Server environment, the server process handles the request of several user processes. The server process communicates with the Oracle server using the Oracle Program Interface (OPI).

Oracle Listener and Service Name

Oracle Net Listener

The one operation unique to the Oracle database server side is the act of receiving the initial connection through an Oracle Net listener. The Oracle Net listener, commonly known as the listener, brokers a client request, handing off the request to the server. The listener is configured with a protocol address. Clients configured with the same protocol address can send connection requests to the listener. Once a connection is established, the client and Oracle database server communicate directly with one another.

Listener Control utility

The Listener Control utility enables you to administer listeners.You can use its
commands to perform basic management functions on one or more listeners.
Additionally, you can view and change parameter settings.

For a majority of commands, the Listener Control utility establishes an Oracle Net
connection with the listener that is used to transmit the command. To initiate an Oracle
Net connection to the listener, the Listener Control utility needs to obtain the protocol
addresses for the named listener or a listener named LISTENER. This is done by
resolving the listener name with one of the following mechanisms:
■ listener.ora file in the directory specified by the TNS_ADMIN environment
variable mostly in $ORACLE_HOME/network/admin directory on UNIX.
■ Naming method, for example, a tnsnames.ora file
If the listener name is LISTENER and it cannot be resolved, a protocol address of
TCP/IP, port 1521 is assumed.

The Listener Control utility supports several types of commands:

LSNRCTL> HELP
The following operations are available
An asterisk (*) denotes a modifier or extended command:
change_password
exit
quit
reload
services
set*
show*
spawn
start
status
stop
trace
version

LSNRCTL> SET
The following operations are available with set.
current_listener
displaymode
inbound_connect_timeout
log_file
log_directory
log_status
password
raw_mode
save_config_on_stop
startup_waittime
trc_file
trc_directory
trc_level

LSNRCTL> SET LOG_STATUS on

LSNRCTL> SET LOG_DIRECTORY /usr/oracle/admin

 

Net Service Name

A net service name is an alias that is used for a connect descriptor. To access a net service (such as a database) across the network, a net service name is required.

net service names are either found in the tnsnames.ora file

· Directory server

· Oracle Names Server

· External Naming services

Oracle Network Issues Link

Network DBA

This link contains common network issues for Oracle Connectivity along with brief description of network files.

NETWORK DBA 

1. LISTENER.ORA file

It is a server side file and contains information regarding network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the server.

Sample File

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
  )

 

If listener is modified then the listener should be restarted or reloaded to allow the new configuration to take place.  Basic commands of Listener control utility are as follows:

LSNRCTL>start   LSNRCTL>stop  LSNRCTL>reload LSNRCTL>status LSNRCTL>show

 

2.TNSNAMES.ORA file

It is a Client side file which is created on both sites SERVER/CLIENT. It contains client side network configuration parameters.It can be found in the ORACLE_HOME/network/admin. It can be found on the server if client style connections are used on the server side.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

3. SQLNET.ORA file

It is a Client side file which is created on both sites SERVER/CLIENT. It is use for user AUTHENTICATION purpose.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

Another good link for commonly known TNS errors from

Emre Baransel – Oracle Blog