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

環境：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

……

e,

網上檢視有人遇到相同問題，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.

f,檢查伺服器10.10.10.100上的實體記憶體

Total Physical Memory 8GB
Swap: 8GB
應該正常

g,檢查交換空間

lsps –a

size 8GB  used% 69%

h，使用smitty chps命令新增4GB交換空間，至此，客戶端可以正常連線資料庫

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-731281/，如需轉載，請註明出處，否則將追究法律責任。