ORA-00020: maximum number of processes
This error occurs when your total numbers of sessions connecting to oracle database has exceeded the max limit set in parameter file. Simplest way to overcome this error is to reset the max sessions value to a greater value than existing.Here is how to do it
oracle@LINUX201:[~] $ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:20:26 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (500) exceeded
oracle@LINUX201:[~] $ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:23:42 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (500) exceeded
Enter user-name:
Disconnected from ORACLE
I wasn't able get into the oracle database to kill some database session. So I tried to kill few sessions on OS to make my way into DB.
oracle@LINUX201:[~] $ ps -ef|grep oracle
.
.
.
.
.
.
.
oracle 64373 1 0 Jun08 ? 00:00:03 oracleQPDEV (LOCAL=NO)
oracle 64540 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle 64554 1 0 Jun08 ? 00:00:01 oracleQPDEV (LOCAL=NO)
oracle 64633 1 0 Jun08 ? 00:00:03 oracleQPDEV (LOCAL=NO)
oracle 64637 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
.
.
.
oracle 65186 1 0 Jun08 ? 00:00:04 oracleQPDEV (LOCAL=NO)
oracle 65190 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle 65192 1 0 Jun08 ? 00:00:01 oracleQPDEV (LOCAL=NO)
oracle 65202 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle 65206 1 0 Jun08 ? 00:00:02 oracleQPDEV (LOCAL=NO)
root 65407 65381 0 May16 pts/2 00:00:00 sudo -u oracle -i
oracle 65408 65407 0 May16 pts/2 00:00:00 -bash
oracle 65458 65408 0 May16 pts/2 00:00:00 sqlplus
oracle 65459 65458 0 May16 ? 00:00:00 oracleQPDEV (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 65518 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle 65520 1 0 Jun08 ? 00:00:02 oracleQPDEV (LOCAL=NO)
oracle 65534 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle@LINUX201:[~] $ kill -9 64785
oracle@LINUX201:[~] $ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:26:25 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (500) exceeded
Enter user-name: ^C
Killing few processes on Linux :
oracle@LINUX201:[~] $ kill -9 65192 65085 64785 64777 64655 64653 64637
oracle@LINUX201:[~] $ ps -ef|grep 65192 65085 64785 64777 64655 64653 64637
.
.
.
.
oracle 50258 1 0 Jun07 ? 00:00:04 oracleQPDEV (LOCAL=NO)
oracle 50264 1 0 Jun07 ? 00:00:03 oracleQPDEV (LOCAL=NO)
oracle 50268 1 0 Jun07 ? 00:00:02 oracleQPDEV (LOCAL=NO)
.
.
.
oracle 64554 1 0 Jun08 ? 00:00:01 oracleQPDEV (LOCAL=NO)
oracle 64633 1 0 Jun08 ? 00:00:03 oracleQPDEV (LOCAL=NO)
oracle 65186 1 0 Jun08 ? 00:00:04 oracleQPDEV (LOCAL=NO)
oracle 65190 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle 65202 1 0 Jun08 ? 00:00:00 oracleQPDEV (LOCAL=NO)
oracle 65206 1 0 Jun08 ? 00:00:02 oracleQPDEV (LOCAL=NO)
.
.
.
oracle@LINUX201:[~] $ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:30:07 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 500
processor_group_name string
Now reset the max processes to a greater value:
SQL> alter system set processes=1200 scope=spfile;
System altered.
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 500
processor_group_name string
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
QPDEV READ WRITE
This will need a restart to take affect
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1275071888 bytes
Database Buffers 1912602624 bytes
Redo Buffers 16904192 bytes
Database mounted.
Database opened.
SQL> show parameter process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1200
processor_group_name string
SQL>
No comments:
Post a Comment