listener production problem – troubleshooting guide for the new oracle dba





you are a new oracle dba and you want to fix some production problem with your listener. Below article is a collection of the various production issues and their solutions concerning the listener. This is from my personal notes of the articles i liked and found useful from google.

In case  the listener.log has grown to huge size on unix or solaris ..do this thing

You can also clear a log with the unix command

> listener.log

This will "truncate" the log, and will not crash the listener process. The ">" is part of the command–not the prompt! I do this nightly after taking a backup of the log. Then I also purge old backups at the same time.

Find out status of a listener.

a)get the listener name from the listener.ora

b)type lsnctrl

c)at the command prompt of the listener type the sttaus command with the name of the listener.

LSNRCTL> status LISTENER_g3u0426

No need for listener.ora to have service names list

Sometimes the database init.ora can have two parameters local_listener and compatible.

The local_listener parameter in the init.ora specifies the listener name. This listener name entry will be present in the tnsnames.ora file identifying the database sid. When the database is up the pmon will automatically register the database with the listener that is running.

So don’t be afraid when you take the status of the listener using lsnrctl status command  and you see message that no services found. Instead use the above method to find the status of a listener by specifying the listener name.

I  get the ORA-12541 error message when I try to connect through Forms6i and Reports6i Builder. I’ve started the listener also. I have no problems accessing the database through SQL*Plus. I’ve checked the listener.ora and tnsnames.ora files and everything seems okay.

We Said:

From the docs:

ORA-12541: TNS no listener

Cause: Listener for the source repository has not been started.

Action: Start the Listener on the machine where the source repository resides.

Basically the Forms server could not find the listener. First check the tnsnames.ora file and ensure that it points to the correct server and port. If the Forms server is on another machine, test the TNS resolve with tnsping from the command prompt. Finally, check the listener.

# lsnrctl status

This should show the status of the listener and the services it is listening for. Remember that when you run SQL*Plus on the database server, it does not need the listener to connect unless you specify the database tnsnames.ora entry.

You can use the lsnrctl status command to verify that the listener is down, and lsnrctl start to start your listener. If you are on Windows, also verify that the listener service is running.  Here are more notes on lsnrctl:

·         Oracle listener lsnrctl command

·         Oracle listener lsnrctl command

·         Oracle listener security tips

·         Blocking Oracle access by IP Addresses

 

TNS-03505: Failed to resolve name

TNS-03505: Failed to resolve name

The service name you are trying to tnsping was not found.

First of all you’ll have to determine what technologies will be used to lookup the requested service name.

In order to do this, you will have to find the sqlnet.ora file used by the Oracle client. This file is typically in your %oracle_home%\network\admin (or net80\admin for Oracle 8 client)directory.

It might be that you are using Oracle Instant Client, or that the location is changed. This can be done by setting the TNS_ADMIN variable, either via a batch script, stored in the registry or as an environment variable.
See what the value of TNS_ADMIN is and go to that directory in order to find sqlnet.ora

If you found the sqlnet.ora file, open it and search for following line:
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, ONAMES, HOSTNAME)

Default installations will use a TNSNAMES.ora file to resolve database/service names.
See if the tnsnames.ora file exists in your directory.

If it doesn’t exist and NAMES.DIRECTORY_PATH only contains TNSNAMES (and optionally HOSTNAME), there will be no name resolving.

You will need to create a tnsnames.ora file which will contain all databases you want to connect to.
A typical entry looks like this:

ORA102.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = databasename)
    )
  )

You will have to replace hostname and databasename and possible the portnumber to reflect your Oracle databases.

If you think both sqlnet.ora and tnsnames.ora are correct, you might be using multiple Oracle Homes.
Typically this is the case if you install Oracle Developer suite after you install a database on your pc.

With Oracle Locator Express you can easily switch between multiple Oracle Homes.

If you are using other resolving technologies such as Oracle names or Oracle Internet Directory, you can add the service to either ONAMES or OID.

In the sqlnet.ora file locatated in the $ORACLE_HOME/network/admin directory, there are two parameters that can be set:
1. NAMES.DIRECTORY_PATH
2. NAMES.DEFAULT_DOMAIN

If you are running the db instance on your local machine, then you can remove the NAMES.DEFAULT_DOMAIN setting and set the
NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME).

If you are at a client, as I am, then you will need to set the NAMES.DEFAULT_DOMAIN to the company domain, e.g. ‘mycompany.com’.

The service name is resolved by appending the instance name (from tnsnames.ora) to the default domain (from sqlnet.ora), e.g. orcl.mycompany.com.

So if you connect at work using username/password@database it appends the domain: username/password@database.mycompany.com.

If you attempt to connect locally, then you will receive the error as you are no longer on the work domain.

Error:  TNS 1101

Text:   Could not find service name %s

——————————————————————————-

Cause:  The service name could not be resolved by name-lookup.

Action: Verify that the listener name or service name specified to

        LSNRCTL has the correct name and address defined in LISTENER.ORA or in

        TNSNAMES.ORA.

Multiple Listeners on Unix
————————–
Due to reasons, such as upgrades or migrations, or even just the desire to have
multiple Oracle database versions on one UNIX machine, there is a possible need
to have multiple Oracle TNS Listeners on the same machine.
These listeners can be of the same version or different versions, and this note
will cover both cases.

It used to be the case, that when there was more than one version of the
database on a single server, only one listener would be run, and this would be
the most current version. However, in theory, the version of SQL*Net/Net8 that
is packaged with the database should be used because it is supported as a
bundle. Development does not test current releases of SQL*Net/Net8 with prior
releases of the RDBMS.

Now that the 8i database registration is different to the older method, there
must be caution when running these listeners for older versions, or older
listeners for the newer 8i databases.

B> Some common questions:
========================

1. How many listener.ora files do I need?
This can be done with one listener, and the TNS_ADMIN environment variable set
to the one common directory (as would be the case with multiple listeners of
the same version), or it can be done with separate listener.ora files – one for
each version.

Please use discretion and local setup when choosing which method to use.
Factors which might influence this decision would be how safe the one file
would be, or how complex multiple ones are to manage.

2. Should I manually configure the listener.ora file or use Netasst?
You may use the Net8 Assistant to edit the listener.ora files. However, this
note will only cover the manual editting of them.

C> How to do it:
===============

Here are the steps to manually setup two (or more) Listeners on the same UNIX
server:

1) Change the LISTENER to another name (for example LSNR) in every
occurrence in the version specific LISTENER.ORA.

Example "vi" command:
%s/LISTENER/LSNR/g

2) Use a different PORT number.
In SQL*Net V2, you can use any PORT number (Oracle recommends 1521),
as the entry in the /etc/services file is for the system
administrator’s reference only (that is, SQL*Net does not look for a
/etc/services entry like it did in V1). Select an additional PORT number
to use for the second listener, such as 1522, and check /etc/services
to be sure it is available.

Example (to check for availability):
% grep 1522 /etc/services

3) Append the new name (for example LSNR) to "lsnrctl" commands
Example: % lsnrctl start LSNR
Example: % lsnrctl status LSNR

4) Use different KEY values for any IPC protocol addresses you may have.

When starting listeners for different versions of SQL*Net be sure to have the
$ORACLE_HOME and PATH environment variables set so that you will start the
appropriate version.

The versions and SID’s in the examples below are as follows:

1) V7.3.4 RDBMS SID=V734 and SID=ORCL
$ORACLE_HOME /u01/oracle/7.3.4

2) V8.1.6 RDBMS SID=V816
$ORACLE_HOME /u01/oracle/8.1.6

D> Two Cases (examples):
=======================
Please note that the following are examples ONLY and do not simply assume
that the instances should all be there, should be the same names or that these
are the ONLY instances available.
Please follow normal listener configurations for exact case scenarios.

CASE A:
——
One Listener.ora file is used for both 734 and 816 listeners (this example
assumes that the the environment variable TNS_ADMIN for EACH $ORACLE_HOME is
set).
These two listeners can reside in the same listener.ora file.
In fact, you can run MULTIPLE listeners in the same file.

One common LISTENER.ORA file:
—————————-
#########
# Entry for 734 listener
#########
LSNR734 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V734))
(ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1521))
)
STARTUP_WAIT_TIME_LSNR734 = 0
CONNECT_TIMEOUT_LSNR734 = 10
SID_LIST_LSNR734=
(SID_LIST =
(SID_DESC =
(SID_NAME = V734)
(ORACLE_HOME=/u01/oracle/7.3.4)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME=/u01/oracle/7.3.4)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME=/u01/oracle/7.3.4)
(PROGRAM = extproc)
)
)

#########
# Entry for 816 listener
#########
LSNR816 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V816))
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1522))
)
SID_LIST_LSNR816 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME=/u01/oracle/8.1.6)
(PROGRAM = extproc)
)
)

CASE B:
——
Two Listener.ora files are being used for each listener version (one in each
respective $ORACLE_HOME and the TNS_ADMIN set to them).
These can be expanded to the number of homes existing if required.

Two listener.ora files:
———————–
###################
# Listener.ora file
# /u01/oracle/7.3.4
###################
LSNR734 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V734))
(ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1521))
)
STARTUP_WAIT_TIME_LSNR734 = 0
CONNECT_TIMEOUT_LSNR734 = 10
SID_LIST_LSNR734=
(SID_LIST =
(SID_DESC =
(SID_NAME = V734)
(ORACLE_HOME=/u01/oracle/7.3.4)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME=/u01/oracle/7.3.4)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME=/u01/oracle/7.3.4)
(PROGRAM = extproc)
)
)

LISTENER.ORA for V8.1.6
———————–
###################
# Listener.ora file
# /u01/oracle/8.1.6
###################
LSNR816 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V816))
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1522))
)

SID_LIST_LSNR816 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME=/u01/oracle/8.1.6)
(PROGRAM = extproc)
)
)

E> Additional Notes:
===================

Please note the following as well:

1. The SID_LIST for the 8.1.6 instance does NOT need to be placed in
the listener.ora entry (actually recommended), as it can clash
with the automatic service process.
Automatic Service Regsitration will take place if the "Compatible"
and "Local_listener" parameters are set in the init.ora file for V816.
This will be true for both tnsnames.ora file entries using
SERVICE_NAME or SID (ie: Net8i Client or 8.0.x and older).

2. Make sure that the init.ora file for 8i has the following parameter
local_listener="(address=(protocol=tcp)(host=<hostname1>)(port=<port#>))"
The <hostname1> must be the hostname or IP address for the database server.
The <port#> should be the same number as that specified in the 8i
listener.ora.

Installation Note:
—————–
Regardless of which listener you run with a database, it is critical that the
protocol adapters are linked in with the version of SQL*Net that is packaged
with the database. Remember, the installation of SQL*Net consists of 2
portions, the listener executables and the protocol adapters. If you need to go
back to the Oracle installer and add the protocol adapters, be sure to relink
all executables via PRODUCT MAINTENANCE.

19th October 2007

there are multiple listeners for different oracle versions on cent server

cent*cistest-/var/opt/oracle :->pg  oratab_listener

#

#

# entries

# column 1: listener name

# column 2: ORACLE_HOME

# column 3: start automatically y/n

# column 4: unix owner (ora10, ora92_32, ora817, etc)

# column 5: any database owned by the corresponding oracle_home

#  (not needed for lsnrctl, as long a .oraspecial sets env properly)

listener10:/opt/oracle/product/10.2.0:Y:ora10

listener81:/opt/oracle/product/8.1.7:Y:ora81

listener:/opt/oracle/product/9.2.0_32bit:Y:ora92_32

(EOF):

Subject: TroubleShooting Guide For ORA – 12502 TNS:listener received no CONNECT_DATA from client
  Doc ID: Note:453505.1 Type: TROUBLESHOOTING
  Last Revision Date: 18-NOV-2007 Status: PUBLISHED

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details


Applies to:

Oracle Net Services – Version: 9.2 to 10.2
Information in this document applies to any platform.

Purpose

This is a step-by-step troubleshooting guide for error:
ERROR:
ORA – 12502 TNS:listener received no CONNECT_DATA from client

Last Review Date

August 7, 2007

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

The error indicates that the Listener did not recieve the complete information of the connect string, it lacks the CONNECT_DATA section.

 Start point to diagonise this issue would be the Listener log.

Open the Listener log and check for ORA – 12502 error .Look out how frequent this error has been logged. 

Frequent Occurrence ? 
If its is frequent and consistently happening with specific time interval,

07-AUG-2007 09:00:25 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
07-AUG-2007 09:10:25 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
07-AUG-2007 09:20:25 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client

This indicates there is port scanning utility / some application which is trying to check the availability of the port.

Is it one time Occurrence  ? or intermittent Occurrence  ?

For rare occurrences, its suggested to enable the sqlnet tracing to capture the event that is causing the error.You should enable the listener tracing to capture the client information

nttcnr: waiting to accept a connection.
nttcnr: getting sockname
snlinGetNameInfo: entry
snlinGetNameInfo: Using numeric form of host's address 152.69.209.10
snlinGetNameInfo: exit
nttcnr: connected on ipaddr 152.69.209.10
nttvlser: entry
snlinGetNameInfo: entry
snlinGetNameInfo: Using numeric form of host's address 152.69.209.20
snlinGetNameInfo: exit
nttvlser: valid node check on incoming node 152.69.209.20
nttvlser: Accepted Entry: 152.69.209.20
nttcnr: exit

The client IP here would be 152.69.209.20.
Once the client IP is found, check whether the same client IP logged for all the intermittent cases.
If so, then its something on that specific machine causing the issue and should be traced by OS tools.
If its not happening on specific machine, but from various machines, then its your application which is not providing the entire connect string information to the listener in the right manner.

If you recieve TNS -12502 on a RAC setup, then follow Note 453293.1 to check your remote and local listener setup.

Logging Service Request (SR)  With Oracle Support

The files and information that would be helpful for Oracle Support to resolve this issue while logging SR,ensure to take these information when you face the problem:

(a) Listener tracing at support level taken at the time of the issue

(b) Listener log for the same period

(c) Listener services output
$ lsnrctl services <listener_name>

(d) Settings of Local & Remote Listeners (if any)
$ sql> show parameter listener

Subject: Listener.log contains many TNS-12502: TNS:listener received no CONNECT_DATA from client (TNS-12502)
  Doc ID: Note:422931.1 Type: PROBLEM
  Last Revision Date: 10-APR-2007 Status: MODERATED

In this Document
  Symptoms
  Cause
  Solution


This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Net Services – Version: 9.0.1.4 to 10.2.0.3
This problem can occur on any platform.

Symptoms

Entries like the following appear regularly in the listener log:

TNS-12502: TNS:listener received no CONNECT_DATA from client

 

Cause

(Possibly malicious) client which was using a monitoring tool that was probing TCP port 1521 (among others).

Probing the TCP port means that the client is closing the connection as soon as it was opened (he just wants to see whether the port is open for access). This breaks the SQL*Net protocol which is waiting for the client request data upon the connection is opened.

Other causes may lie with dropped connections due to lack of resources or defective client applications which are failing to correctly connect with the listener, per the SQL*Net protocol.

Solution

Interrupt monitoring tool activity or restrict access from that client.

Be aware that TCP probes may also indicate hostile activity with regards to network security — please review this matter with your system/network administrator.

Author: admin