Shulou(Shulou.com)05/31 Report--

What this article shares with you is the solution to the ORA-12516 fault. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Just went to work in the morning, my colleague told me that the database could not be connected and prompted a "ORA-12516" error. I tried to connect to the database remotely through PL/SQL Developer, and sure enough, I got the wrong "ORA-12516: TNS: the listener could not find an available handle to match the protocol stack"; then I logged on to the server through the remote desktop and tried to log in to the database with a sys user and reported the same error. Strange, I was fine when I got off work yesterday.

I checked on the Internet, this error is generally due to the insufficient number of current sessions in the database, there are two related parameters: processes and sessions. I want to check these two parameters in the database, but sys users can't log in, so I'm in a hurry. Later, at the suggestion of a friend, the following steps were taken to solve the problem smoothly.

a. Close listener and disable new connections

b. Kill some or all of the processes of local=no (depending on the importance of the business), kill a few, and ensure that sys users can log in.

c. Log in to see which business has gone wrong and kill the user process that has gone wrong.

d. Check the database

e. Start listener

Introduce my operating environment:

Operating system: Windows Server 2008 R2

Database: Oracle 10g

First, turn off the listener through lsnrctl stop and disable new connections to ensure that the second step can be performed successfully

Second, close two applications that connect to the database, and then try to log in to the database with a sys user, and log in successfully

Third, check the initialization parameter values of processes and sessions, which are 150,170 respectively, both of which are default values

Click (here) to collapse or open

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, look at all the current session information through select sid,serial#,program,terminal from vessionsession.From the results at that time, you can see that there are more than a hundred records, which have exceeded the session limit of the database; moreover, except for a dozen sessions of Oracle itself, the other 100 sessions are all in the same terminal. As a result, the point of failure is found (this device is a terminal that was just installed last night).

Fifth, close the application on the faulty device and use select sid,serial#,program,terminal from session again; look at all the current session information, and the query results show that there are only more than 20 sessions left. Considering that Oracle itself has more than a dozen sessions and several applications started at the same time, it should be normal.

Sixth, start listener and try to connect to the database through other clients. Everything is fine and the problem is solved.

Next, I want to see what caused this failure. Go on.

Seventh, check the alarm log and see a large number of Process M000 died alarms in the log

Click (here) to collapse or open

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 corresponding time trace file, see the "ORA-00020: maximum number of processes 150 exceeded Died during process startup with error 20 (seq=5413)" statement, the original is the number of connections exceeded the threshold, the database can no longer establish new connections, so the error.

Click (here) to collapse or open

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:3339M/8181M, Ph+PgF:10815M/16361M

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:3347M/8181M, Ph+PgF:10813M/16361M

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:3350M/8181M, Ph+PgF:10812M/16361M

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:3857M/8181M, Ph+PgF:11421M/16361M

Instance name: hoegh

Redo thread mounted by this instance: 1

Oracle process number: 0

Windows thread id: 8032, image: ORACLE.EXE

As for why the new equipment will produce a large number of connections, it is not clear yet. It is suspected that it has something to do with the operating system. The operating system installed on this device is the windows xp embeded cut version system, and it is said that the installation of the system is not very smooth. Start the application on the faulty device, monitor real-time session information through select sid,serial#,program,terminal from sessions, and the number of sessions increases until the touch threshold, the database error, and the problem reappear successfully

We found another device with the same configuration and the same operating system for testing, and this problem did not occur. In the end, the equipment can only be reinstalled.

Here is a summary of the solutions to ORA-12516 errors:

First, it is generally caused by the dissatisfaction of the current number of sessions in the database. You can increase the size of processes and sessions parameters according to business needs. The relationship between the two is: sessions= (1.1*processes+5)

Second, if there is a malicious connection similar to the above case, you can follow the above steps to find the problem session and directly kill the relevant process.

These are the solutions to ORA-12516 failures, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.