问题描述
一台数据库在跑应用的测试过程中挂掉了,因为默认的会话数是150,很显然,是会话数不够的原因。
用sqlplus工具也无法登陆
[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
Enter user-name: exit
Enter password:
ERROR:
ORA-01005: null password given; logon denied
Enter user-name: ^C
问题解决
方法1:更改processes参数
解决 ORA-00020 错误,加大processes的参数值即可,但是需要正常启动数据库并成功登陆后才能修改。
1.首先通过加参数 “-prelim” 成功登陆数据库
[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.
此时就可以正常关闭和开启数据库,按照如下命令操作解决问题:
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.开库
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.修改参数
[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.重启实例
[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进程
$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
$ ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm
未用过这种方法,参考:https://blog.csdn.net/liyaohui_szz/article/details/81077800