WARNING: inboundconnection timed out (ORA-3136)
ORA-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程
在家接到同事电话，说数据库连不上，，连接报如下错误：
ORA-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程
这种错误一般是连接满了，但是被他们重启以后连接又报改错误，感觉增大连接用处不大。登录到后台alert_jscn.log日志，看到如下错误：
Wed Apr 4 15:04:25 2012
WARNING: inboundconnection timed out (ORA-3136)
Wed Apr 4 15:04:25 2012
WARNING: inboundconnection timed out (ORA-3136)
Wed Apr 4 15:04:25 2012
WARNING: inboundconnection timed out (ORA-3136)
Wed Apr 4 15:04:25 2012
WARNING: inboundconnection timed out (ORA-3136)
下面内容参考网络
参考metalink上的345197.1文章，内容如下：
Symptoms
The Oracle Net 10g parameters SQLNET.INBOUND_CONNECT_TIMEOUT andINBOUND_CONNECT_TIMEOUT_listenername default to 0 (indefinite) in 10.1. To address Denial of Service (DOS)issues, the parameters were set to havea default of 60 (seconds) in Oracle 10.2.
If applications are longer than 60 secs to authenticate with theOracle database, the errors occur.The following may be seen in the alert log:WARNING: inbound connection timed out (ORA-3136)
SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds anddetermines how long a client has to provide the necessary authenticationinformation to a database.
INBOUND_CONNECT_TIMEOUT_listenernameis set to a value in seconds and determines how long a client has to completeits connect request to the listener after the network connection has beenestablished.
To protect both the listener and the database server, OracleCorporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername incombination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.
Cause
Whenever default timeouts are assigned to a parameter, there may becases where this default does not work well with a particular application.However, some type of timeout on the connection establishment is necessary tocombat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUTand INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60seconds in Oracle 10.2. It is thesetimeout values that can cause the errors described in this note.
Also note thatit is possilbe the reason the database is slow to authenticate, may be due toan overloaded Oracle database or node.
Solution
Set theparameters SQLNET.INBOUND_CONNECT_TIMEOUT andINBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an appropratevalue for the application yet still combat DOS attacks (120 for example).
Theseparameters are set on the SERVER side:
listener.ora:INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT
Further tuning of these parameters may beneeded is the problem persists
Metalink上给出的解决方案如下:
1. set INBOUND_CONNECT_TIMEOUT_=0 inlistener.ora
2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0in sqlnet.ora of server.
3. stop and start both listener anddatabase.
4. Now try to connect to DB and observe thebehaviour
以下是Oracle官方文档的一段：
SQLNET.INBOUND_CONNECT_TIMEOUT
Use theSQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for aclient to connect with the database server and provide the necessaryauthentication information.
If the clientfails to establish a connection and complete authentication in the timespecified, then the database server terminates the connection. In addition, thedatabase server logs the IP address of the client and an ORA-12170: TNS:Connecttimeout occurred error message to the sqlnet.log file. The client receiveseither an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failederror message.
Without thisparameter, a client connection to the database server can stay openindefinitely without authentication. Connections without authentication canintroduce possible denial-of-service attacks, whereby malicious clients attemptto flood database servers with connect requests that consume resources.
To protect boththe database server and the listener, Oracle Corporation recommends settingthis parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_nameparameter in the listener.ora file. When specifying values for theseparameters, consider the following recommendations:
Set both parameters to an initial lowvalue.
Set the value of theINBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than theSQLNET.INBOUND_CONNECT_TIMEOUT parameter.
For example, you can setINBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUTparameter to 3 seconds. If clients are unable to complete connections withinthe specified time due to system or network delays that are normal for theparticular environment, then increment the time as needed.
修改listener的inbound_connect_timeout参数的方法
方法一:
$ lsnrctl
LSNRCTL for IBM/AIX RISC System/6000:Version 10.2.0.3.0 - Production on 29-OCT-2007 10:00:57
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help"for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier orextended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL> show
The following operations are availableafter show
An asterisk (*) denotes a modifier orextended 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
LSNRCTL> showinbound_connect_timeout
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SHOW-100-12)(PORT=1521)))
LISTENER parameter"inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> set inbound_connect_timeout0
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SHOW-100-12)(PORT=1521)))
LISTENER parameter"inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> showinbound_connect_timeout
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SHOW-100-12)(PORT=1521)))
LISTENER parameter"inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> set save_config_on_stop on #表示修改参数永久生效，否则只是临时生效，下次重启监听又还原为原来的值了
LSNRCTL> exit
方法二：
修改listener.ora文件，加入: INBOUND_CONNECT_TIMEOUT_LISTENER_NAME=0
更多Oracle相关信息见Oracle 专题页面 ?tid=12
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1657
14
1415
52
1309
25
1257
29
1231
24
When might a full table scan be faster than using an index in MySQL?
Apr 09, 2025 am 12:05 AM
Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.
Can I install mysql on Windows 7
Apr 08, 2025 pm 03:21 PM
Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
Can mysql and mariadb coexist
Apr 08, 2025 pm 02:27 PM
MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.
RDS MySQL integration with Redshift zero ETL
Apr 08, 2025 pm 07:06 PM
Data Integration Simplification: AmazonRDSMySQL and Redshift's zero ETL integration Efficient data integration is at the heart of a data-driven organization. Traditional ETL (extract, convert, load) processes are complex and time-consuming, especially when integrating databases (such as AmazonRDSMySQL) with data warehouses (such as Redshift). However, AWS provides zero ETL integration solutions that have completely changed this situation, providing a simplified, near-real-time solution for data migration from RDSMySQL to Redshift. This article will dive into RDSMySQL zero ETL integration with Redshift, explaining how it works and the advantages it brings to data engineers and developers.
The relationship between mysql user and database
Apr 08, 2025 pm 07:15 PM
In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.
Laravel Eloquent ORM in Bangla partial model search)
Apr 08, 2025 pm 02:06 PM
LaravelEloquent Model Retrieval: Easily obtaining database data EloquentORM provides a concise and easy-to-understand way to operate the database. This article will introduce various Eloquent model search techniques in detail to help you obtain data from the database efficiently. 1. Get all records. Use the all() method to get all records in the database table: useApp\Models\Post;$posts=Post::all(); This will return a collection. You can access data using foreach loop or other collection methods: foreach($postsas$post){echo$post->
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
