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

[oracle@db ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:16:06 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (150) exceeded
Method 1: Change the processes parameter
To solve the ORA-00020 error, you can increase the parameter value of processes, but you need to start the database normally and log in successfully before you can modify it.
1. Firstly, log in the database successfully by adding the parameter “-prelim”.

1
2
3
4
5		 [oracle@db ~]$ sqlplus -prelim/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:22:07 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

At this point, the database can be closed and opened normally, following the following command to solve the problem.

1
2
3
4
5
6
7		 SQL> shutdown immediate;
SQL> startup;
SQL> show parameter processes;
SQL> alter system set processes=1000 scope=spfile;
SQL> startup force;
SQL> show parameter processes;
SQL> exit;

2.Start-up example

1
2
3
4
5
6
7
8
9		 SQL> startup
ORACLE instance started.
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0SQL> exit
Disconnected from ORACLE

It means that it is already open.
3. Modify the parameters

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42		 [oracle@db ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:22:35 2020

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     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
processor_group_name                 string
SQL> select status from v$instance;

STATUS
————
STARTED

SQL> alter system set processes=1000 scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

4. Restart the instance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29		 [oracle@db ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 15:24:59 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6313463808 bytes
Fixed Size                  2264976 bytes
Variable Size            1476395120 bytes
Database Buffers         4815060992 bytes
Redo Buffers               19742720 bytes
Database mounted.
Database opened.
SQL> show parameter processes;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
aq_tm_processes                      integer     1
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     1000

.
Method 2: Kill all oracle processes

1
2		 $ ps -ef |grep $ORACLE_SID|grep -v grep|awk ‘{print $2}’ | xargs kill -9
$ ipcs -m | grep oracle | awk ‘{print $2}’ | xargs ipcrm shm

Read More: