【RAC】AIX6.1下Oracle 10g RAC(10.2.0.1)下ORA-12518的解决步骤

最新推荐文章于 2024-11-07 15:10:30 发布

congbeili7411

最新推荐文章于 2024-11-07 15:10:30 发布

阅读量117

点赞数

文章标签：数据库网络

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 Oracle10g RAC(10.2.0.1)安装在 AIX 6.1上

环境：AIX 6.1 ，数据库版本： Oracle 10g for AIX 5L 64bit

1，用户所有客户端连接数据库时都报ORA-12518: TNS: 监听程序无法分发客户机提示；

p550a:/home/oracle$oerr ora 12518

12518, 00000, "TNS:listener could not hand off client connection"

// *Cause: The process of handing off a client connection to another process

// failed.

// *Action: Turn on listener tracing and re-execute the operation. Verify

// that the listener and database instance are properly configured for

// direct handoff. If problem persists, call Oracle Support.

// *Comment: The problem can be worked around by configuring dispatcher(s)

// to specifically handle the desired presentation(s), and connecting

// directly to the dispatcher, bypassing the listener.

The reason ORA-12518 is being throw may be because of DEDICATED connections because Oracle 10g has a value of PROCESSES is defaulted at 150 which can be lower than necessary in a production system. Also, in pre-9i MTS, ORA-12518 may be thrown in SHARED SERVER because the dispatcher may have reached the maximum connection value, so it denies all other.

There are two solutions for ORA-12518 depending on which symptom you may be experiencing.
a.For the DEDICATED occurrence of ORA-12518, you would need to try increasing the PROCESSES parameter so that it can handle the needed number of processes. You can ensure that you have the needed value by monitoring the listener log for ORA-12518. Also, note that because the PROCESSES parameter is static, the database will need to be bounced.
b.If you are experiencing ORA-12518 because of a shared server issue, you first would need to use the command below to shutdown the dispatcher:
SQL> alter system shutdown immediate 'D001';
Then, add on new dispatchers:
SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';

通过以上资料分析得出可能出现问题的原因有两种：一是processes值设的过小，二是需要增加参数DISPATCHERS。

2，远程登录,查看环境

telnet 10.10.10.200

a，查看服务器端监听器运行状态

lsnrctl status正常

发现监听地址为10.10.10.200和10.10.10.201

b，在客户端查看tnsnames.ora文件内容：显示主机地址为10.10.10.101和10.10.10.201;

把10.10.10.101改为10.10.10.200，可以正常连接数据库；

在调换10.10.10.101和10.10.10.201的次序，仍然报ORA-12518: TNS: 监听程序无法分发客户机提示

查看listener.ora文件

c, 查看processes参数

SQL> show parameter processes

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

aq_tm_processes integer 0

db_writer_processes integer 1

gcs_server_processes integer 2

job_queue_processes integer 10

log_archive_max_processes integer 2

processes integer 1000

c，查看当前会话数、进程数

SQL> select count(*) from v$session;

SQL> select count(*) from v$process;

发现用户会话数远未超过1000

3,经如上分析，导致ORA-12518: TNS: 监听程序无法分发客户机的错误原因应该是

在另一节点上的问题

telnet 10.10.10.100

a，查看服务器端监听器运行状态

lsnrctl status 正常

发现监听地址为10.10.10.100和10.10.10.101

查看listener.ora文件

b，查看processes参数

SQL> show parameter processes

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

aq_tm_processes integer 0

db_writer_processes integer 1

gcs_server_processes integer 2

job_queue_processes integer 10

log_archive_max_processes integer 2

processes integer 1000

c，查看当前会话数、进程数

SQL> select count(*) from v$session;

SQL> select count(*) from v$process;

发现用户会话数远未超过1000

d，查看alert log,看最经的错误消息

Errors in file …….trc:
……

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

……

查看…….trc文件

……

Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
*** ……
Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

……

网上查看有人遇到相同问题，metalink 查询. Doc ID: Note:560309.1

附录: Doc ID: Note:560309.1
Applies to:
OracleServer- Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.

Symptoms
The database can not start up due to the following errors:

Errors in file …….trc:
……

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

……

Cause
This issue is mainly caused by lack of memory / swap. Checking the memory configuration on the server, we have found the following:

Total Physical Memory 38912 MB
Swap: Max Size 17664 MiB
So, RAM is 38 GB, SWAP space is only 17 GB

Solution
-We should increase the server swap space (paging space) . The general rule of thumb is that swap space should be:
RAM                            SWAP
1GB to 2GB                1.5 times RAM
> 2GB and <= 8GB       1 times RAM
> 8GB                         .75 times RAM

So in our case, the recommended swap space is @28 GB .

We can also try to increase physical memory, if possible.

We should also check the ulimits for Oracle user.:
memory - unlimited
data    - unlimited
cpu       - unlimited
stack    - at least 32768
nofile    - OS dependent

We should also check memory parameters in the pfile/spfile that add more load to the memory consumption on the server.  In our issue, we found these settings which added more pressure to the memory:
-lock_sga=true
-large db_keep_cache_size=14000m.