[Oracle11g] ORA-00020 maximum number of processes (150) exceeded 오류해결 및 재기동

데이터베이스 접속 하려니까

listener refused the connection with the following error

: ora-12516, tns:listener could not find available handler with matching protocol stack

오류 발생

 

ORA-00020 maximum number of processes (150) exceeded

sys 계정으로 접속하니 오류발생

 

해결방법 : init.ora 세션갯수 수정 ( 150 -> 500)

 

 

init.ora 경로

/dbms/oracle11.2.0.1.0/app/ora11g/product/11.2.0/dbhome_1/dbs/init.ora

oracle 설치경로 밑에 dbs 경로에 init.ora 존재함

 

processes = 150 -> processes = 500

vi 편집기를 통해 수정 후 재기동!

 

 

linux oracle 재기동

# su - oracle11g          // oracle 계정으로 로그인!

$ lsnrctl stop            // oracle Listener 종료!
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-MAR-2023 15:37:27
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

$ sqlplus "/as sysdba"    // sqlplus로 오라클 접속!
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 13 15:37:39 2023
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> shutdown immediate   // oracle shutdown 
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup              // oracle start
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             331350888 bytes
Database Buffers          192937984 bytes
Redo Buffers                7958528 bytes
Database mounted.
Database opened.

SQL> exit                 // oracle exit

$ lsnrctl start           // oracle Listener 시작!
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-MAR-2023 15:38:08

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /dbms/oracle11.2.0.1.0/app/ora11g/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /dbms/oracle11.2.0.1.0/app/ora11g/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /dbms/oracle11.2.0.1.0/app/ora11g/diag/tnslsnr/호스트명/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=호스트명)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-MAR-2023 15:38:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File  (생략)
Listener Log File        (생략)
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=호스트명)(PORT=1521)))
The listener supports no services
The command completed successfully


$ tnsping localhost       // oracle Ping 확인!
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-MAR-2023 15:38:14

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/dbms/oracle11.2.0.1.0/app/ora11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)

 

정상 작동 확인

 

#processes, sessions LIMIT_VALUE 확인
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE 
FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('processes', 'sessions');

processes, sessions LIMIT_VALUE 확인

 

 

---

INACTIVE SESSION KILL 문

#전체 세션조회
 SELECT   '(ALL)' AS STATUS, '(ALL)' AS DBUSER, CURRENT_UTILIZATION AS ACCUMCOUNT,''||LIMIT_VALUE AS MAX_LIMIT
FROM V$RESOURCE_LIMIT 
WHERE RESOURCE_NAME = 'processes'
UNION
SELECT   STATUS, DBUSER, COUNT(DBUSER) AS ACCUMCOUNT, (select limit from dba_profiles where profile = 'DEFAULT' and RESOURCE_NAME = 'SESSIONS_PER_USER') AS MAX_LIMIT
FROM (
        SELECT S.STATUS AS STATUS, S.USERNAME AS DBUSER 
        FROM V$SESSION S, V$PROCESS P, SYS.V_$SESS_IO SI
        WHERE S.PADDR = P.ADDR(+) AND SI.SID(+) = S.SID AND (S.USERNAME IS NOT NULL) AND (NVL (S.OSUSER, 'x') <> 'SYSTEM') AND (S.TYPE <> 'BACKGROUND')
        ORDER BY 1
)
GROUP BY STATUS, DBUSER
ORDER BY ACCUMCOUNT DESC;

#전체 세션 상세 정보
select status1, type1, machine, module, dbuser, ClientUser, count(ClientUser) as accumCount
from (
        SELECT
                 s.status as status1, s.serial# "Serial#", s.TYPE as Type1,
                 s.username as DBUser, s.osuser as ClientUser, s.server "Server",
                 s.machine as Machine, s.module as Module, s.client_info "Client Info",
                 s.terminal "Terminal", s.program "Program", p.program "O.S. Program",
                 s.logon_time "Connect Time", lockwait "Lock Wait",
                 si.physical_reads "Physical Reads", si.block_gets "Block Gets",
                 si.consistent_gets "Consistent Gets",
                 si.block_changes "Block Changes",
                 si.consistent_changes "Consistent Changes", s.process "Process",
                 p.spid, p.pid, si.sid, s.audsid, s.sql_address "Address", s.sql_hash_value "Sql Hash", s.action
        FROM v$session s, v$process p, sys.v_$sess_io si
        WHERE s.paddr = p.addr(+)
              AND si.sid(+) = s.sid
              AND (s.username IS NOT NULL)
              AND (NVL (s.osuser, 'x') <> 'SYSTEM')
              AND (s.TYPE <> 'BACKGROUND')
        ORDER BY 1
)
group by status1, type1, machine, module, dbuser, ClientUser
order by accumCount DESC;


#세션조회
SELECT s.sid, serial#,  osuser,  program FROM v$session s;

#삭제스크립트
ALTER SYSTEM KILL SESSION '{SID},{SERIAL#}' IMMEDIATE ;

#일괄생성
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' FROM v$session where status = 'INACTIVE';