We may come across this kind of situation in production when there is no other session are allowed to create in the database.Because database already reached to its maximum limits of process thresholds value.
error:ORA-00020: maximum number of processes (1100) exceeded
ERROR at line 1:
ORA-01012: not logged on
There are two possible reason behind ora-00020 error.
1.If process limit is set to few value and total active connection reach the thresholds value than we received the same error .
2 .Zombie process , process those are created from a parent process and not releasing his resources after execution also. In linux there are no child process(PID) is created from parent process(PPID) and after executed those child not informed to his parent process about his task execution. So parent process is keep waiting for there acknowledged .Can we kill those zombie process by KILL -9 PID. The answer is no because process are already dead. You need to kill the parent process to kill all the zombie process.
Here is the example of zombie process , where 8559 is the parent process and waiting for acknowledgement from the other child process under the column PID.
PID PPID
oracle 6363 8559 0 12:19:20 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 12829 8559 0 12:36:09 ? 0:34 oracleislam02 (LOCAL=NO)
oracle 21644 8559 0 11:38:25 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 6308 8559 0 12:19:20 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 6295 8559 0 12:19:20 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 12764 8559 0 12:36:09 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 81 8559 0 10:38:22 ? 0:06 oracleislam02 (LOCAL=NO)
oracle 6341 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 1960 8559 0 07:55:51 ? 0:14 oracleislam02 (LOCAL=NO)
oracle 6347 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 6408 8559 0 12:19:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12810 8559 0 12:36:09 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 12333 8559 0 12:35:22 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 19031 8559 0 10:06:35 ? 0:09 oracleislam02 (LOCAL=NO)
oracle 6286 8559 0 12:19:19 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 8461 8559 0 08:14:38 ? 2:51 oracleislam02 (LOCAL=NO)
oracle 6389 8559 0 12:19:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 6423 8559 0 12:19:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 27610 8559 0 09:07:24 ? 4:21 oracleislam02 (LOCAL=NO)
oracle 12801 8559 0 12:36:09 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12772 8559 0 12:36:09 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12279 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 6379 8559 0 12:19:20 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 12311 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12823 8559 0 12:36:09 ? 0:15 oracleislam02 (LOCAL=NO)
oracle 21618 8559 0 10:14:42 ? 1:40 oracleislam02 (LOCAL=NO)
oracle 12302 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12285 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12806 8559 0 12:36:09 ? 0:04 oracleislam02 (LOCAL=NO)
oracle 6454 8559 0 12:19:21 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 6335 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12339 8559 0 12:35:22 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 6372 8559 0 12:19:20 ? 0:35 oracleislam02 (LOCAL=NO)
oracle 6315 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
But we are DBA and we are working in database . So when we executing a SQL script and after the execution got complete the session become inactive due to some reason . This Inactive session is a count of no session connecting to the database and there is a corresponding process is created in the OS level that is also keep waiting ....this cause the problem of no session exceeded in the database.
Solution: As we can't connect to the database because its already reached its maximum limits. So only way to connect the DB is connect without creating a session and kill all the inactive session .
sqlplus /nolog
@> set _prelim on
@> conn / as sysdba
Set _prelim on , will connect to the database without creating a session.
: Kill all the inactive session at a time
sql> select 'alter system kill session '''||sid||','||serial#||''' IMMEDIATE;' from v$session where sTatus= 'INACTIVE' and USERNAME NOT IN ('SYS','SYSTEM');
error:ORA-00020: maximum number of processes (1100) exceeded
ERROR at line 1:
ORA-01012: not logged on
There are two possible reason behind ora-00020 error.
1.If process limit is set to few value and total active connection reach the thresholds value than we received the same error .
2 .Zombie process , process those are created from a parent process and not releasing his resources after execution also. In linux there are no child process(PID) is created from parent process(PPID) and after executed those child not informed to his parent process about his task execution. So parent process is keep waiting for there acknowledged .Can we kill those zombie process by KILL -9 PID. The answer is no because process are already dead. You need to kill the parent process to kill all the zombie process.
Here is the example of zombie process , where 8559 is the parent process and waiting for acknowledgement from the other child process under the column PID.
PID PPID
oracle 6363 8559 0 12:19:20 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 12829 8559 0 12:36:09 ? 0:34 oracleislam02 (LOCAL=NO)
oracle 21644 8559 0 11:38:25 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 6308 8559 0 12:19:20 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 6295 8559 0 12:19:20 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 12764 8559 0 12:36:09 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 81 8559 0 10:38:22 ? 0:06 oracleislam02 (LOCAL=NO)
oracle 6341 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 1960 8559 0 07:55:51 ? 0:14 oracleislam02 (LOCAL=NO)
oracle 6347 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 6408 8559 0 12:19:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12810 8559 0 12:36:09 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 12333 8559 0 12:35:22 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 19031 8559 0 10:06:35 ? 0:09 oracleislam02 (LOCAL=NO)
oracle 6286 8559 0 12:19:19 ? 0:01 oracleislam02 (LOCAL=NO)
oracle 8461 8559 0 08:14:38 ? 2:51 oracleislam02 (LOCAL=NO)
oracle 6389 8559 0 12:19:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 6423 8559 0 12:19:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 27610 8559 0 09:07:24 ? 4:21 oracleislam02 (LOCAL=NO)
oracle 12801 8559 0 12:36:09 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12772 8559 0 12:36:09 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12279 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 6379 8559 0 12:19:20 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 12311 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12823 8559 0 12:36:09 ? 0:15 oracleislam02 (LOCAL=NO)
oracle 21618 8559 0 10:14:42 ? 1:40 oracleislam02 (LOCAL=NO)
oracle 12302 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12285 8559 0 12:35:21 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12806 8559 0 12:36:09 ? 0:04 oracleislam02 (LOCAL=NO)
oracle 6454 8559 0 12:19:21 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 6335 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
oracle 12339 8559 0 12:35:22 ? 0:02 oracleislam02 (LOCAL=NO)
oracle 6372 8559 0 12:19:20 ? 0:35 oracleislam02 (LOCAL=NO)
oracle 6315 8559 0 12:19:20 ? 0:00 oracleislam02 (LOCAL=NO)
But we are DBA and we are working in database . So when we executing a SQL script and after the execution got complete the session become inactive due to some reason . This Inactive session is a count of no session connecting to the database and there is a corresponding process is created in the OS level that is also keep waiting ....this cause the problem of no session exceeded in the database.
Solution: As we can't connect to the database because its already reached its maximum limits. So only way to connect the DB is connect without creating a session and kill all the inactive session .
sqlplus /nolog
@> set _prelim on
@> conn / as sysdba
Set _prelim on , will connect to the database without creating a session.
: Kill all the inactive session at a time
sql> select 'alter system kill session '''||sid||','||serial#||''' IMMEDIATE;' from v$session where sTatus= 'INACTIVE' and USERNAME NOT IN ('SYS','SYSTEM');
No comments:
Post a Comment