Handle irregular Oracle listening exceptions

Handle irregular Oracle listening exceptions

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:

$ Ulimit-Ha
Time (seconds) unlimited
File (blocks) unlimited
Data (kbytes) unlimited
Stack (kbytes) unlimited
Coredump (blocks) unlimited
Nofiles (descriptors) 65536
Memory (kbytes) unlimited

View the software restrictions as follows:

$ Ulimit-Sa
Time (seconds) unlimited
File (blocks) unlimited
Data (kbytes) unlimited
Stack (kbytes) 8192
Coredump (blocks) unlimited
Nofiles (descriptors) 256
Memory (kbytes) unlimited

View/etc/system as follows:

* Ident "@ (#) system 1.18 97/06/27 SMI"/* SVR4 1.5 */

*

* SYSTEM SPECIFICATION FILE

*

 

* Moddir:

*

* Set the search path for modules. This has a format similar to

* Csh path variable. If the module isn't found in the first directory

* It tries the second and so on. The default is/kernel/usr/kernel

*

* Example:

* Moddir:/kernel/usr/kernel/other/modules

 

 

 

 

* Root device and root filesystem configuration:

*

* The following may be used to override the defaults provided

* The boot program:

*

* Rootfs: Set the filesystem type of the root.

*

* Rootdev: Set the root device. This shoshould be a fully

* Expanded physical pathname. The default is

* Physical pathname of the device where the boot

* Program resides. The physical pathname is

* Highly platform and configuration dependent.

*

* Example:

* Rootfs: ufs

* Rootdev:/sbus @ 1, f8000000/esp @ 0, 800000/sd @ 3, 0:

*

* (Swap device configuration shocould be specified in/etc/vfstab .)

 

 

 

 

* Exclude:

*

* Modules appearing in the moddir path which are NOT to be loaded,

* Even if referenced. Note that 'clude' accepts either a module name,

* Or a filename which provided des the directory.

*

* Examples:

* Exclude: win

* Exclude: sys/shmsys

 

 

 

 

* Forceload:

*

* Cause these modules to be loaded at boot time, (just before mounting

* The root filesystem) rather than at first reference. Note that

* Forceload expects a filename which provided des the directory. Also

* Note that loading a module does not necessarily imply that it will

* Be installed.

*

* Example:

* Forceload: drv/foo

 

 

 

 

* Set:

*

* Set an integer variable in the kernel or a module to a new value.

* This facility shocould be used with caution. See system (4 ).

*

* Examples:

*

* To set variables in 'unix ':

*

* Set nautopush = 32

* Set maxusers = 40

*

* To set a variable named 'debug' in the module named 'test _ module'

*

* Set test_module: debug = 0x13

 

* Begin FJSVssf (do not edit)

Set ftrace_atboot = 1

Set kmem_flags = 0x100

Set kmem_lite_maxalign = 8192

* End FJSVssf (do not edit)

Forceload: drv/fjpfca

* Begin MDD root info (do not edit)

Rootdev:/pseudo/md @ 0: 0, 0, blk

* End MDD root info (do not edit)

* ** Begin EMCpower added lines ** do not edit below this line ***

Forceload: drv/emcpsf

Forceload: drv/sd

Forceload: drv/ssd

Forceload: drv/emcp

Forceload: misc/emcpgpx

Forceload: misc/emcpmpx

Forceload: misc/emcpvlumd

Forceload: misc/emcpxcrypt

Forceload: misc/emcpdm

Forceload: misc/emcpioc

Set emcp: bPxEnableInit = 1

* ** End EMCpower added lines ** do not edit abve this line ***

 

 

 

Exec_user_stack = 1

Set noexec_user_stack_log = 1

View/etc/project as follows:

System: 0 ::::

User. root: 1 ::::

Noproject: 2 ::::

Default: 3 ::::

Group. staff: 10 ::::

User. oracle: 100: oracle ::

To sum up, from the output of listener. log, the soft limit of nofiles is Too small, only 256, which causes Solaris Error: 24: Too open files Error. From/etc/system and/etc/project, we do not see soft limit settings.

 

Troubleshooting summary and follow-up suggestions

Upgrade the oracle user's soft limit to at least 1024, and then log on to the oracle user again. After the ulimit is verified, restart the database and listen.

The specific solution is as follows:

1. Add the following lines in/etc/system:

Set rlim_fd_max = 65536

Set rlim_fd_cur = 4096

2. log on to ORACLE again and check oracle user restrictions

Su-oracle

Ulimit-Ha

Ulimit-Sa

3. Restart the database and listen