[轉載]ORA-12518 TNS:監聽程式無法分發客戶機連接配接 解決辦法

ORA-12518: TNS:listener could not hand off client connection

2008-06-23 08:57

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.

先試試 connect / as sysdba,看行不行，如果行的話，

再試試connect / @ as sysdba看看監聽配置是否有問題。

如果這一步有問題，說明監聽這塊有問題。

1.cmd

2.sqlpls /nolog

3.connect sys/test as sysdba

沒有問題

1.cmd

2.sqlplus /nolog

3.connect sys/test@test as sysdba

ORA-12518: TNS:listener could not hand off client connection

說明監聽有問題

查到了相關資料如下：

http://www.dba-oracle.com/sf_ora_12518_tns_listener_could_not_hand_off_client_connection.htm

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。

考慮到之前經常遇到ora-00020超出最大程序數的問題在安裝資料庫的時候已經把processes設到800了，為保險起見再show parameter processes

SQL> show parameter process;

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     0

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

job_queue_processes                  integer     10

log_archive_max_processes            integer     2

processes                            integer     1000

再查程序數

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

  COUNT(*)

----------

       224

是以最後得出結論應該不是processes的問題，應該用b方法來解決

1.connect sys/test as sysdba

2.show parameters dispatchers;

NAME                   TYPE             VALUE

dispatchers         string           (protocol=tcp)(service=oracle10xdb)

max_dispatchers      integer  

NAME                                 TYPE        VALUE

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

dispatchers                          string      (PROTOCOL=TCP) (SERVICE=smsvrXDB)

max_dispatchers                   integer   

5.SQL>alter system set dispatchers = '(protocol=tcp)(dispatchers=3)(service=oracle10xdb)';

system altered

問題解決。

當然根據實際情況你也可以用a方法解決，