Handle irregular Oracle listening exceptions

The operating system is SunOS5.10 database version: OracleRAC11.2.0.3.0. This host has two databases, and one node of RAC is located on it. There is another database.

The operating system is SunOS 5.10 database version: Oracle RAC 11.2.0.3.0. This host has two databases, one of which is the node 1 of RAC, and another database.

Environment:
Operating System: SunOS 5.10
Database Version: Oracle RAC 11.2.0.3.0
There are two databases on the host, with node 1 of a RAC located on the host, and another database. The reason why the database is used for listening is that the GRID listening is not used, this is to avoid affecting the use of another database when the CRS is stopped.

Fault symptom:
Database listening is occasionally abnormal. from applying the tnsping database, it takes a long time or even cannot be connected.

Troubleshooting process:

The tnsping from the application host is as follows:

Racdb1_scenemon $ tnsping racdb_new

TNS Ping Utility for Solaris: Version 11.2.0.3.0-Production on-2014 15:46:23

Copyright (c) 1997,201 1, Oracle. All rights reserved.

Used parameter files:

Alias resolved using TNSNAMES Adapter
Try to connect (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 11.111.11.1) (PORT = 1521) (ADDRESS = (PROTOCOL = TCP) (HOST = 11.111.11.2) (PORT = 1521) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5 ))))

OK (60000 ms)

Dynamic and Static monitoring features of Oracle listeners

Non-default listeners and port configurations for placing an instance in the Oracle 11g RAC Environment

Configure and manage Oracle listener logs

Oracle error-ORA-12514: TNS: No listener

ORA-12514 listening error solved

Six connection problems and solutions for Oracle listeners

Oracle LISTENER does not listen to Oracle instances. Problem solved

View the listening status on the database server:

$ Lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.3.0-Production on 19-FEB-2014 15:45:58

Copyright (c) 1991,201 1, Oracle. All rights reserved.

Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))

TNS-12535: TNS: operation timed out
TNS-12560: TNS: protocol adapter error
TNS-00505: Operation timed out.

Kill the listener process and restart the listener:

$ Ps-ef | grep tns
Oracle 18504 18468 0 15:45:15 pts/10 grep tns
Oracle 24037 1 0 Nov 28? 608/oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER-inherit
Oracle 9488 1 3 Jan 24? 4447: 11/oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER-inherit
$
$ Kill-9 9488
$ Lsnrctl start

LSNRCTL for Solaris: Version 11.2.0.3.0-Production on 19-FEB-2014 15:48:22

Copyright (c) 1991,201 1, Oracle. All rights reserved.

Starting/oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 11.2.0.3.0-Production
Log messages written to/oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log. xml
Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = racdb) (PORT = 1521 )))

Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0-Production
Start Date 19-FEB-2014 15:48:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File/oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = racdb) (PORT = 1521 )))
The listener supports no services
The command completed successfully

Check listener. log as follows:

17-FEB-2014 16:02:26 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5 )) (CID = (PROGRAM = sqlplus @ racdb1) (HOST = racdb1) (USER = tmn) * (ADDRESS = (PROTOCOL = tcp) (HOST = 11.111.11.89) (PORT = 41603) * establish * racdb * 0
17-FEB-2014 16:02:26 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5 )) (CID = (PROGRAM = sqlplus @ racdb1) (HOST = racdb1) (USER = tmn) * (ADDRESS = (PROTOCOL = tcp) (HOST = 11.111.11.89) (PORT = 41659) * establish * racdb * 12518
TNS-12518: TNS: listener cocould not hand off client connection
TNS-12560: TNS: protocol adapter error
TNS-00530: Protocol adapter error
Solaris Error: 24: Too enabled open files <
17-FEB-2014 16:02:26 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5 )) (CID = (PROGRAM = sqlplus @ racdb1) (HOST = racdb1) (USER = tmn) * (ADDRESS = (PROTOCOL = tcp) (HOST = 11.111.11.89) (PORT = 41652) * establish * racdb * 0
17-FEB-2014 16:02:26 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5 )) (CID = (PROGRAM = sqlplus @ racdb1) (HOST = racdb1) (USER = tmn) * (ADDRESS = (PROTOCOL = tcp) (HOST = 11.111.11.89) (PORT = 41669) * establish * racdb * 0
17-FEB-2014 16:02:26 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5 )) (CID = (PROGRAM = sqlplus @ racdb1) (HOST = racdb1) (USER = tmn) * (ADDRESS = (PROTOCOL = tcp) (HOST = 11.111.11.89) (PORT = 41682) * establish * racdb * 12518
TNS-12518: TNS: listener cocould not hand off client connection
TNS-12560: TNS: protocol adapter error
TNS-00530: Protocol adapter error
Solaris Error: 24: Too enabled open files <

Too program open files means that Maximum Number Of Open Files Per Process has reached the upper limit. Therefore, the reason why listener hang lives is that the limit setting is too small. For more information, see the hardware restrictions as follows: