I recently had a situation where access to the database was completely blocked because of the infamous error message
ORA-00020: maximum number of processes (%s) exceeded
Facts:
The database had processes set to 1000.
Simply by using
-bash-3.2$ sqlplus -prelim “/ as sysdba”
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 28 19:02:01 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> shu immediate;
ORA-01012: not logged on
SQL>
SQL>
SQL>
SQL> shu abort;
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SQL> exit
Disconnected from ORACLE
-bash-3.2$
-bash-3.2$ ps -ef | grep smon
oracle 6110 1 0 2019 ? 00:00:02 asm_smon_+ASM
oracle 8352 1 0 19:02 ? 00:00:00 ora_smon_ZPTSTNEW
oracle 9737 5651 0 19:02 pts/1 00:00:00 grep smon
-bash-3.2$
-bash-3.2$
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 28 19:02:47 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
After this point the database could once again be restarted:
SQL> shu immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 8551575552 bytes
Fixed Size 2270360 bytes
Variable Size 3372223336 bytes
Database Buffers 5167382528 bytes
Redo Buffers 9699328 bytes
Database mounted.
Database opened.
SQL>
1) Kiểm tra thông số hiện tại
SQL> show parameter sessions
SQL> show parameter processes
2) TINH CHỈNH
set số tiến trình
SQL> alter system set processes=<num_processes> scope=spfile
reset số tiến trình mặc định
SQL> alter system set sessions=<num_sessions>scope=spfile;
Lưu ý
sessions = processes * 1.5
3) sau đó restart database
SQL> shutdown immediate
SQL> startup
Quay lại bước 1 và kiểm tra