ORA-12518: TNS: listener cocould not hand off client connection, ora-12518tns

ORA-12518: TNS: listener cocould not hand off client connection, ora-12518tns

1. ORA-12518: TNS: listener cocould not hand off client connection

When the number of team members increases, problems such as "unable to distribute client connections" often occur. After searching online, the problem is finally solved. The solution is summarized as follows for reference and future backup.

Change the process in the SPFILEORCL. ORA File

<span style="font-size: 14px; ">F:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA</span>

Cause:As the number of team members increases and the original database settings are insufficient, an exception "unable to distribute client connections" is often thrown when plsql is connected and tomcat is started.

Solution:

Step 1: Adjust the process and session values

1. Check whether the process and session are sufficient.

A) Use plsql to connect to oracle and check the number of process processes:

<Span style = "font-family: Comic Sans MS; font-size: 14px;"> select count (*) from v $ process; -- gets the current number of processes in the database. Select value from v $ parameter where name = 'processs'; -- obtain the maximum number of processes. </Span>

B) view the number of session sessions:

<Span style = "font-family: Comic Sans MS; font-size: 14px;"> select count (*) from v $ session; -- gets the current number of sessions in the database. Select value from v $ parameter where name = 'session'; -- obtain the maximum number of sessions. </Span>

Check whether the current process and sessions are close to the upper limit. If it is close, you can increase it.

2. Adjust the values of these two parameters.

There is a relationship between the number of system processes and the number of system sessions:

Number of processes = number of sessions X 1.1 + 5

We can refer to this rule for configuration.

A) modify the process value.

Alter system set processes = 1000 scope = spfile; -- change the process value to 1000

B) modify the session Value

Alter system set sessions = 1105 scope = spfile; -- change the sessions value to 1105

3. Back up pfile and restart oracle

A) Back up pfile after modification.

Create pfile from spfile; -- create pfile (system configuration) from spfile (runtime configuration)

C) Restart oracle

There are many restart Methods: restart the oracle service or restart the database. In Windows, you can directly restart the service.

Step 2: modify the number of dispatchers

If the first step cannot solve the problem. You can perform the second step.

1. view the number of dispatchers and dispatchers usage.

Select name, busy, status, accept, idle from v $ dispatcher; -- view the current number of dispatchers and some information. Generally, only one library is installed by default.

Select name, (busy/(busy + idle) * 100 "busy rate %" from v $ dispatcher; -- View dispatchers usage

If the usage rate is greater than 50%, increase the number of dispatchers.

2. Adjust the number of dispatchers

Alter system set dispatchers = '(protocol = tcp) (dispatchers = 3) (service = youroracleservicenameXDB )';

-- Change the number of dispatchers to 3.

3. Restart oracle.

When the service is restarted, there is no listener error.

2. ORA-12541: TNS: No listener

1: F: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ listener. ora Add the following code:

 (SID_DESC =      (SID_NAME = PLSExtProc)      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)      (PROGRAM = extproc)    )    

# listener.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =  (SID_LIST = (SID_DESC =    (GLOBAL_DBNAME = orcl)    (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)    (SID_NAME = orcl)    )     (SID_DESC =      (SID_NAME = PLSExtProc)      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)      (PROGRAM = extproc)    )      )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))      (ADDRESS = (PROTOCOL = TCP)(HOST = wl-fe65b9e18e24)(PORT = 1521))    )  )

Note: restart listener and service.

Since spfileorcl. ora is changed above, this file may be lost, leading to oracle unavailability.

Iii. unavailability of oralce

Create the SPFILEORCL. ORA file. Note: SPFILEORCL is the Instance name.

<span style="font-size:14px;">F:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA</span>

Run in cmd:

<span style="font-size:14px;">sqlplus /nologconn / as sysdba;create spfile='F:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA' from pfile='F:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.7292014151052'; </span>

If this error occurs, create the SPFILEORCL. ORA file, change the listener error, and restart the listener and service.

Oracle connection problems, you can help solve

Check the content of tnsnames. ora in the network/admin directory of the Oracle main directory.
Conn = DriverManager. getConnection ("jdbc: oracle: thin: @ localhost: 1522: oracle1", "scott", "tiger, check whether it is on this port.

Tnsname can be customized =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = Database Service ip address) (PORT = PORT number ))
)
(CONNECT_DATA =
(SERVICE_NAME = Instance name)
)
)

When ORACLE11g is installed in windows 2003 32-bit operating system, cannot it be connected when the number of connections reaches 120?