ORACLE 11g features a detailed analysis of the lsnrctl hang-to-death fog caused by a new feature

1, problem description

Colleague said stuck, connect Oracle database is very slow, it takes a long time, even to make a simple query is very slow, it feels like Hang master general.

2, AnalysisOracleServer Load

Start logging in, view the Oracle server, the load is low, the server is no pressure, it is not a server card problem:

[Email protected] data]# W 19:59:47 up 122 days, 4:32, 4 users, Load average:0.65, 0.71, 0.59 USER TTY from [email protected] IDLE jcpu PCPU Root tty1:0 17jun16 122days 5:00pm 5:00/usr/bin/xorg:0-nr-verbose-audit 4-auth/var/run/gdm/auth-for-gdm-yorxlj/database-noli Sten TCP VT1 Root pts/0 192.168.120.154 12:49 4:42m 0.04s 0.04s-bash Root pts/1 192.168.120.154 12:49 0.00s 0.13s 0.05s W Root PTS/2 192.168.120.154 13:41 6:09m 0.06s 0.02s-bash [Email protected] data]#

3, AnalysisOracleServerLsnrctlMonitor

Since it is not an Oracle server load problem, then look at the Lsnrctl listening status, slow, and see the last exception information:

[Email protected] admin]$ lsnrctl status Lsnrctl for linux:version 11.2.0.1.0-production on 17-oct-2016 13:27:51 Copyright (c) 1991, Oracle. All rights reserved. Connecting to (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) # and got stuck, stuck, Hang The Lord, without any move, takes a long, long time to release. # look again . tnsping under the service, also relatively slow, found that there are error problems appear, as follows [Email protected] admin]$ tnsping PD236 TNS Ping Utility for linux:version 11.2.0.1.0-production on 17-oct-2016 13:21:24 Copyright (c) 1997, Oracle. All rights reserved. Used parameter files: Used TNSNames Adapter to resolve the alias Attempting to contact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.10.104) (PORT = 1521)) (CONN Ect_data = (service_name = powerdes))) Tns-12547:tns:lost Contact [Email protected] admin]$

4, analysis monitoringLsnrctlof theTraceTrace File

Go inside the Lsnrctl management interface to see the trace file directory:

( 1 ) go in Lsnrctl managing command Lines [Email protected] oradata]$ Lsnrctl Lsnrctl for linux:version 11.2.0.1.0-production on 17-oct-2016 20:36:34 Copyright (c) 1991, Oracle. All rights reserved. Welcome to Lsnrctl, type ' help ' for information. Lsnrctl> ( 2 ) View Commands Lsnrctl> Show The following operations is available after show An asterisk (*) denotes a modifier or extended command: Rawmode DisplayMode Rules Trc_file Trc_directory Trc_level Log_file Log_directory Log_status Current_listener Inbound_connect_timeout Startup_waittime Snmp_visible Save_config_on_stop Dynamic_registration Enable_global_dynamic_endpoint Oracle_home PID ( 3 ) To view the trace file already in the file directory Lsnrctl> Show Trc_file Connecting to (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) LISTENER parameter "Trc_file" set to ORA_2994_139957275965184.TRC The command completed successfully Lsnrctl> Lsnrctl> Show Trc_directory Connecting to (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) LISTENER parameter "trc_directory" set To/oracle/app/oracle/diag/tnslsnr/pldb236/listener/trace The command completed successfully Lsnrctl>

Then go to the background of the log, see the display is normal, did not find a path error, such as problems:

[[email protected] ~]# tail-f/oracle/app/oracle/diag/tnslsnr/pldb236/ LISTENER/TRACE/ORA_2994_139957275965184.TRC 2016-10-17 13:42:38.443831:nsbrfr:nsbfs at 0X25D94C0, the data at 0x25aa940. 2016-10-17 13:42:38.443843:nsbrfr:nsbfs at 0x256f3b0, data at 0x2576610. 2016-10-17 13:42:38.443855:nsbrfr:nsbfs at 0X25A4AC0, and data at 0x25af850. 2016-10-17 13:42:38.443867:nsbrfr:nsbfs at 0x25a4b70, and data at 0x25b52c0. 2016-10-17 13:42:38.443880:nsbrfr:nsbfs at 0x25b1fb0, and data at 0x25b8a10. 2016-10-17 13:42:38.443891:nsbrfr:nsbfs at 0X2568F10, and data at 0x2566670. 2016-10-17 13:42:38.443903:nsbrfr:nsbfs at 0X256AFE0, and data at 0x256b090. 2016-10-17 13:42:38.443916:nsbrfr:nsbfs at 0x250fd00, and data at 0x2568fc0. 2016-10-17 13:42:38.443937:nlse_term_audit:entry 2016-10-17 13:42:38.443951:nlse_term_audit:exit

 5, out of the trickLsnrctl Restart

The trick is invalid, still very slow, even lsnrctl stop and lsnrctl start are very slow.

# Stop very slow, stuck, at least needed - seconds to complete Lsnrctl> stop Connecting to (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) ... Card and card ... to my grandmother's house . The command completed successfully lsnrctl> start # start will be faster, almost ten seconds to a second to complete. Starting/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr:please wait ... Tnslsnr for Linux:version 11.2.0.1.0-production System parameter File Is/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages Written To/oracle/app/oracle/diag/tnslsnr/pldb236/listener/alert/log.xml Listening on: (Description= (Address= (protocol=tcp) (host=10.10.10.104) (port=1521))) Listening on: (Description= (Address= (PROTOCOL=IPC) (KEY=EXTPROC0))) Connecting to (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version Tnslsnr for Linux:version 11.2.0.1.0-production Start Date 17-oct-2016 20:46:58 Uptime 0 days 0 hr. 0 min. 0 sec Trace level off Security on:local OS Authentication SNMP OFF Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File/oracle/app/oracle/diag/tnslsnr/pldb236/listener/alert/log.xml Listening Endpoints Summary ... (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) (Description= (address= (PROTOCOL=IPC) (KEY=EXTPROC0))) Services Summary ... Service "Powerdes" has 1 instance (s). Instance "Powerdes", status UNKNOWN, have 1 handler (s) for the This service ... The command completed successfully Lsnrctl> PS : After reboot, remote connection Oracle The instance is still very slow, so restarting doesn't have much effect.

6, openLsnrctlof theTraceAnalysis

Try to open a higher level of TRACE16, see, this time stop a lot faster, but the remote connection is still very slow, the opening process is as follows:

Lsnrctl> Trace 16 Connecting to (Description= (address= (protocol=tcp) (host=10.10.10.104) (port=1521))) Opened trace file:/oracle/app/oracle/diag/tnslsnr/pldb236/listener/trace/ora_14662_140432957417216.trc The command completed successfully Lsnrctl> PS : then went to check the log, and did not find the exception information, it seems that the path is invalid.

7, a flash of light in the mist

All kinds of places are considered to check, the problem is not resolved, carefully think, this situation is to do the Rman after the recovery of things, in the absence of Rman recovery before it is normal;

Rman Recovery, Rman Recovery, Rman Recovery, Rman Recovery ...

By the same, Rman recovery is the 2015 data, then the database user name password is 2015 years, 2016 database user password modified, remembered that Rman recovery is restored all things including environment variables including system parameters and so on. Then it means that the application connection to the database using the user name password is not connected, the connection will be error ah, but the alert log backstage did not see, that would be what problem, suddenly the light flashes in the brain,oracle11g new features password authentication delay Ah, will be stuck in the database, the user is not connected up.

Go to block the password delay verification:

sql> ALTER SYSTEM SET EVENT = ' 28401 TRACE NAME ' CONTEXT FOREVER, Level 1 ' SCOPE = spfile;   System altered.   Sql> create Pfile from SPFile;   File created.   Sql> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Sql> startup ORACLE instance started.   Total System Global area 6680915968 bytes Fixed size              2213936 bytes Variable size              4898949072 bytes Database buffers    1744830464 bytes Redo buffers                 34922496 bytes Database mounted. Database opened. Sql>

Then happy, connect the database also not card, go up query data also not card, everything is very fast. So the analysis is the new feature of oracle11g. Password validation delay Lsnrctl Hang, previously also encountered a similar due to the oracle11g new features caused by the problem, it took a long time to analyze, to verify, and details see I used to organize blog Records, Inside for oracle11g characteristics have a more in-depth discussion, blog connection address: http://blog.csdn.net/mchdba/article/details/51794443

In addition the trace reference article address: http://blog.itpub.net/17203031/viewspace-713587/

ORACLE 11g features a detailed analysis of the lsnrctl hang-to-death fog caused by a new feature