Troubleshooting ORA-12516

Troubleshooting ORA-12516

At work in the morning, my colleague told me that the database can not connect, the prompt "ORA-12516" error, I tried to connect to the database remotely through PL/SQL Developer, and indeed, the error "ORA-12516: TNS: the listener cannot find the available handle that matches the protocol stack. "Then I log on to the server through the Remote Desktop and try to log on to the database using the sys user. The same error is reported. Strange: It was good yesterday when I got off work.

I checked it online. This error is generally caused by insufficient number of current sessions in the database. There are two related parameters: processes and sessions. I want to check the two parameters of the database, but the sys user cannot log on, so I am in a hurry. Later, at the suggestion of a friend, I took the following steps to solve the problem smoothly.

A. Disable listener and disable new connections;
B. Kill part or all of the processes with local = no (based on the importance of the business), and kill several processes to ensure that sys users can log on to the system;
C. log in to see which business has a problem and kill the problematic user process;
D. Check the database;
E. Start listener;

Introduce my operating environment:
Operating System: Windows Server 2008 R2
Database: Oracle 10g

First, use lsnrctl stop to close the listener and disable new connections to ensure that the second step is successful;
Second, two applications connected to the database are closed, and then the System user is used to log on to the database;
3. Check the values of the initialization parameters processes and sessions, which are 150 and 170 respectively;

SQL>

SQL> show parameter processes

NAME TYPE VALUE

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

Aq_tm_processes integer 0

Db_writer_processes integer 3

Gcs_server_processes integer 0

Job_queue_processes integer 10

Log_archive_max_processes integer 2

Processes integer 150

SQL> show parameter sessions

NAME TYPE VALUE

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

Java_max_sessionspace_size integer 0

Java_soft_sessionspace_limit integer 0

License_max_sessions integer 0

License_sessions_warning integer 0

Logmnr_max_persistent_sessions integer 1

Sessions integer 170

Shared_server_sessions integer

SQL>
Fourth, you can use select sid, serial #, program, terminal from v $ session; To view all current sessions. from the current results, you can see that there are more than one hundred records, the maximum number of sessions in the database has been exceeded. In addition to more than a dozen sessions in Oracle, more than one hundred sessions are the same terminal. Thus, the fault point is found (this device is a terminal just installed last night ).
Fifth, shut down the application on the faulty device, and view all current session information through select sid, serial #, program, terminal from v $ session again, the query results show that there are only more than 20 sessions left. It should be normal considering the dozens of external sessions of Oracle and several applications started at the same time;
Sixth, start listener and try to connect to the database through other clients. Everything is normal and the fault is resolved here;
Next, I want to see what causes the fault and continue;
7. view the alarm log and view a large number of Process m000 died alarms in the log;

Wed Apr 29 21:27:31 2015

Ksvcreate: Process (m000) creation failed

Wed Apr 29 21:28:32 2015

Process m000 died, see its trace file

Wed Apr 29 21:28:32 2015

Ksvcreate: Process (m000) creation failed

Wed Apr 29 21:29:33 2015

Process m000 died, see its trace file
Eighth, find the trace file corresponding to the time and see the "ORA-00020: maximum number of processes 150 exceeded Died during process startup with error 20 (seq = 5413)" statement, it turns out that the number of connections exceeds the threshold and the database cannot create new connections. Therefore, an error is returned.

Dump file c: \ oracle \ product \ 10.2.0 \ admin \ hoegh \ bdump \ hoegh_ora_8032.trc

Wed Apr 29 21:28:31 2015

ORACLE V10.2.0.4.0-64bit Production vsnsta = 0

Vsnsql = 14 vsnxtr = 3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1 Service Pack 1

CPU: 24-type 8664, 12 Physical Cores

Process Affinity: 0x0000000000000000

Memory (Avail/Total): Ph: 3339 M/8181 M, Ph + PgF: 10815 M/16361 M

Instance name: hoegh

Redo thread mounted by this instance: 1

Oracle process number: 0

Windows thread id: 8032, image: ORACLE. EXE

ORA-00020: maximum number of processes 150 exceeded

Died during process startup with error 20 (seq = 5413)

OPIRIP: Uncaught error 20. Error stack:

ORA-00020: maximum number of processes (150) exceeded

Dump file c: \ oracle \ product \ 10.2.0 \ admin \ hoegh \ bdump \ hoegh_ora_8032.trc

Thu Apr 30 00:19:05 2015

ORACLE V10.2.0.4.0-64bit Production vsnsta = 0

Vsnsql = 14 vsnxtr = 3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1 Service Pack 1

CPU: 24-type 8664, 12 Physical Cores

Process Affinity: 0x0000000000000000

Memory (Avail/Total): Ph: 3347 M/8181 M, Ph + PgF: 10813 M/16361 M

Instance name: hoegh

Redo thread mounted by this instance: 1

Oracle process number: 0

Windows thread id: 8032, image: ORACLE. EXE

ORA-00020: maximum number of processes 150 exceeded

Died during process startup with error 20 (seq = 5582)

OPIRIP: Uncaught error 20. Error stack:

ORA-00020: maximum number of processes (150) exceeded

Dump file c: \ oracle \ product \ 10.2.0 \ admin \ hoegh \ bdump \ hoegh_ora_8032.trc

Thu Apr 30 01:27:31 2015

ORACLE V10.2.0.4.0-64bit Production vsnsta = 0

Vsnsql = 14 vsnxtr = 3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1 Service Pack 1

CPU: 24-type 8664, 12 Physical Cores

Process Affinity: 0x0000000000000000

Memory (Avail/Total): Ph: 3350 M/8181 M, Ph + PgF: 10812 M/16361 M

Instance name: hoegh

Redo thread mounted by this instance: 1

Oracle process number: 0

Windows thread id: 8032, image: ORACLE. EXE

ORA-00020: maximum number of processes 150 exceeded

Died during process startup with error 20 (seq = 5650)

OPIRIP: Uncaught error 20. Error stack:

ORA-00020: maximum number of processes (150) exceeded

Dump file c: \ oracle \ product \ 10.2.0 \ admin \ hoegh \ bdump \ hoegh_ora_8032.trc

Thu Apr 30 09:54:12 2015

ORACLE V10.2.0.4.0-64bit Production vsnsta = 0

Vsnsql = 14 vsnxtr = 3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1 Service Pack 1

CPU: 24-type 8664, 12 Physical Cores

Process Affinity: 0x0000000000000000

Memory (Avail/Total): Ph: 3857 M/8181 M, Ph + PgF: 11421 M/16361 M

Instance name: hoegh

Redo thread mounted by this instance: 1

Oracle process number: 0

Windows thread id: 8032, image: ORACLE. EXE
As to why a large number of connections are generated when a new device is added, it is still unclear that it is related to the operating system. The operating system installed on this device is the windows xp embeded cropping system, it is said that the system is not properly installed. Start the application on the faulty device and monitor the real-time session information through select sid, serial #, program, terminal from v $ session, the number of sessions continues to increase until the threshold value is reached and the database reports an error. The problem is repeated successfully;
We found another device with the same configuration and operating system for testing. This problem did not occur. Finally, you can only reinstall the device.

The following summarizes the solution to ORA-12516 errors:
1. Generally, the current number of sessions in the database is not sufficient. You can increase the processes and sessions parameters as needed. The relationship between these two parameters is as follows: sessions = (1.1 * processes + 5 );
2. If there is a malicious connection similar to the above case, you can find the problematic session and kill the relevant process directly according to the above steps.