ORA-12518 / TNS-12518 Troubleshooting

最新推荐文章于 2021-04-14 15:28:10 发布

cuiyue1159

最新推荐文章于 2021-04-14 15:28:10 发布

阅读量215

点赞数

文章标签： php 操作系统数据库

最近系统很多次碰到 ORA-12518的问题，怀疑是连接太多了。但是查看session实际上不多。

多次重启系统后发想此问题还是与，

metalink 上的解决办法是：

ORA-12518 / TNS-12518 Troubleshooting [ID 556428.1]

1. These parameters are set on the SERVER side:

listener.ora: INBOUND_CONNECT_TIMEOUT_listenername

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT

设置为0

此方法行不通。

2. lack of available memory

这很显然不是

3 - Less of processes in the database,:

Increase processes parameter if using dedicated connexion (init.ora / spfile file)

In unix, adjust resource limits to increase the number of max processes allowed.

显然这个应该是其中一个原因

conn / as sysdba

>show parameters process

…

processes 200

>select count(*) from v$session;

198

> select count(*) from v$process;

199

这可能是问题的所在了。

然后

>alter system set processes=300 scope=both;

ORA-02095: specified initialization parameter cannot be modified

>alter system set processes=300 scope=spfile;

ORA-32001: write to SPFILE requested but no SPFILE specified at startup

发现EBS启动的时候不是用spfile的，那么直接去修改pfile: init$SID.ora 文件（/dbs）

D:\oracle\VIS\db\tech_st\11.1.0\dbs

processes = 300 # Max. no. of users x 2
sessions = 600 # 2 X processes

重启系统了。这是EBS系统，所以直接重启数据库是有问题的。

OK。

-Mountain 2010.12.04

http://www.itpub.net/viewthread.php?tid=521086

这个问题一直持续到现在，好像没有很明确的解决思路。

我最近也碰到这个问题了，解决思路也是看了pub里的兄弟的解决办法。
总结一下，我觉得目前这个问题还没有出现，大致可以说算是把这个问题解决了吧。

Env: windows 2003, 3G, Oracle EBS R12.1.2, DB 11.1.0.7

参考metalink：ID 556428.1
ORA-12518 / TNS-12518 Troubleshooting

Solution
1- Timer issues causing abnormal threads :
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application.
From Oracle base version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_ is 60 seconds. For previous releases it is zero by default.

a) Please add following parameter in listener.ora
INBOUND_CONNECT_TIMEOUT_ = 0

For example, if the listener name is listenerTest, then:
INBOUND_CONNECT_TIMEOUT_listenerTest = 0

AND

b) Add the following parameter to the sqlnet.ora file on the Database server
side:
SQLNET.INBOUND_CONNECT_TIMEOUT = 0

The listener and database will need to be restarted after these changes.

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT

Note: This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

Fore more info/setting on inbound connection time out, check the following note :
Note 465043.1 - Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out

2 - Lack of available memory :
Increase memory and swap in the system

3 - Less of processes in the database,:
Increase processes parameter if using dedicated connexion (init.ora / spfile file)
In unix, adjust resource limits to increase the number of max processes allowed.

4 - Set /3GB option :
Switch in the BOOT.INI file to enable the 4GB feature.
This allows a process to address 3GB and reserving 1GB for the kernel.

5- Tune SGA / shared_pool_size :
Reduce the SGA size to a reasonable figure which allows the user process to have enough memory to run.
Decrease the size of the database SGA by reducing the PGA_AGGREGATE_TARGET or SGA_MAX_SIZE or both.
The amount of memory for a session depends upon PGA_AGGREGATE_TARGET.
If PGA_AGGREGATE_TARGET is set to a very small value ,Oracle session may exceed this value.

6 - Set or Tune MTS :
Configure MTS or increase the number of DISPATCHERS (if Shared Server is being used).

7- If the listener crash with a core dump and the ora-12518 appears in the log, then you are probably hitting Bug 6139856 due to heavy load or out of memory.

Step 1, 按 solution 1 所说, Timer issue 不存在，改了，重启系统还是出现问题。
Step 2, 内存肯定不缺。
Step 3, 改了pfile中的processes 参数和 sessions参数，processes=300， sessions=600
重启系统还是能重现问题
Step 4，很显然，这个不是问题的限制原因。
Step 5, Set or Tune MTS，我尝试着去把server改成shared connection, 但是没有一个很好的建议方案去设置listener dispatcher相关的参数。我设置的是10个dispatcher, 然后每个50 连接。重启系统了，问题基本上不会重现了，但是能感觉到连接速度很慢，相关参数设置还是没有很好的方案。

Step 6, 参考 5- Tune SGA / shared_pool_size : 之前pga_aggregate_target 1G, sga_target 1G
然后把这两个都减小为850M，这样问题基本上能得到解决。现在看暂时没有碰到问题。

pub里好多兄弟都是改用linux 64, 问题基本上会解决，我们的另外一个环境是Redhat 5.2 32bit, Orace EBS 12.1.1, DB 11.1.0.7就没有发生过这个问题。
看来还是linux好啊。

--Mountian 2010/12/05