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

bb652826616

于 2021-06-22 19:46:51 发布

阅读量2.1k

点赞数 1

本文链接：https://blog.youkuaiyun.com/bb652826616/article/details/118114646

版权

[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

方法1：更改processes参数

解决 ORA-00020 错误，加大processes的参数值即可，但是需要正常启动数据库并成功登陆后才能修改。
1.首先通过加参数 “-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.

此时就可以正常关闭和开启数据库，按照如下命令操作解决问题：

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.启动实例

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: 0

SQL> exit
Disconnected from ORACLE

说明已经是开的。
3.修改参数

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.重启实例

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

方法2：杀死所有oracle进程

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