umzzil nng

Posted on May 28 • Originally published at oraerror.com

Oracle ORA-00020 오류 원인과 해결 방법 완벽 가이드

#oracle #database #dba #sql

ORA-00020 maximum number of processes exceeded 는?

ORA-00020 에러는 Oracle 데이터베이스 인스턴스에 연결 가능한 최대 프로세스 수를 초과했을 때 발생하는 에러입니다. Oracle은 초기화 파라미터인 PROCESSES로 동시에 접속할 수 있는 OS 프로세스(백그라운드 프로세스 + 서버 프로세스 포함)의 최대값을 제한하며, 이 한도를 넘는 순간 신규 연결 요청은 모두 이 에러와 함께 거부됩니다. 특히 트래픽이 급증하는 업무 피크 타임이나 커넥션 풀 설정이 잘못된 경우, 또는 좀비(zombie) 세션이 누적될 때 자주 목격되는 에러입니다.

주요 발생 원인

1. PROCESSES 파라미터 값이 실제 부하에 비해 너무 낮게 설정된 경우

Oracle 설치 시 기본값이나 과거 트래픽 기준으로 PROCESSES 값을 설정한 후 애플리케이션 사용자나 서버 수가 증가했을 때 파라미터를 조정하지 않으면 금방 한계에 도달합니다. 특히 PROCESSES 값은 백그라운드 프로세스(PMON, SMON, DBWR 등)도 포함하기 때문에 실제 유저 세션 수보다 여유 있게 설정해야 합니다.

2. 애플리케이션의 커넥션 누수(Connection Leak) 또는 좀비 세션 누적

애플리케이션 코드에서 DB 커넥션을 사용 후 명시적으로 반환하지 않거나, WAS(웹 애플리케이션 서버)의 커넥션 풀이 비정상 종료된 세션을 정리하지 못하는 경우 세션이 계속 누적됩니다. 이런 좀비 세션들은 V$SESSION에서는 INACTIVE 상태로 보이지만 실제 OS 레벨 프로세스를 점유하고 있어 결국 PROCESSES 한도를 소진시킵니다.

3. 배치 작업 또는 외부 연동 프로그램의 폭발적인 동시 접속

야간 배치 작업이 여러 스레드로 동시에 DB에 연결하거나, 외부 연동 시스템이 짧은 시간에 대량의 커넥션을 열 때 순간적으로 PROCESSES 한도를 초과할 수 있습니다. 커넥션 풀을 사용하지 않고 매번 새 커넥션을 생성하는 구조의 레거시 시스템에서 특히 자주 발생하며, 이런 패턴은 운영 환경에서 사전에 반드시 검토해야 합니다.

해결 방법

① 즉각적인 현황 파악 — 현재 세션 및 프로세스 수 확인

에러 발생 직후 가장 먼저 현재 상태를 파악해야 합니다.

-- 현재 PROCESSES 파라미터 값과 실제 사용 중인 프로세스 수 비교
SELECT 
    p.value                          AS processes_limit,
    COUNT(s.sid)                     AS current_sessions,
    p.value - COUNT(s.sid)           AS remaining_capacity
FROM v$parameter p, v$session s
WHERE p.name = 'processes'
GROUP BY p.value;

-- 상태별 세션 현황 (ACTIVE / INACTIVE 구분)
SELECT 
    status,
    COUNT(*) AS session_count
FROM v$session
GROUP BY status
ORDER BY session_count DESC;

-- 사용자별 세션 점유 현황 (상위 10명)
SELECT 
    username,
    COUNT(*) AS session_count,
    SUM(CASE WHEN status = 'ACTIVE'   THEN 1 ELSE 0 END) AS active_cnt,
    SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive_cnt
FROM v$session
WHERE username IS NOT NULL
GROUP BY username
ORDER BY session_count DESC
FETCH FIRST 10 ROWS ONLY;

② 좀비 세션 즉시 정리

오래된 INACTIVE 세션을 강제 종료하여 빠르게 여유 공간을 확보합니다.

-- 30분 이상 아무 작업도 하지 않은 INACTIVE 세션 목록 조회
SELECT 
    sid,
    serial#,
    username,
    status,
    machine,
    program,
    ROUND((SYSDATE - last_call_et / 86400) * 1440) AS inactive_min,
    last_call_et
FROM v$session
WHERE status   = 'INACTIVE'
  AND username IS NOT NULL
  AND last_call_et > 1800   -- 1800초(30분) 이상 비활성
ORDER BY last_call_et DESC;

-- 특정 세션 강제 종료 (SID, SERIAL# 확인 후 실행)
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

-- 여러 좀비 세션을 한 번에 종료하는 스크립트 생성
SELECT 
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
    AS kill_script
FROM v$session
WHERE status    = 'INACTIVE'
  AND username  IS NOT NULL
  AND last_call_et > 3600;  -- 1시간 이상 비활성 세션

⚠️ 주의: KILL SESSION 실행 전 반드시 해당 세션이 업무상 필요한 세션인지 확인하세요.

③ PROCESSES 파라미터 값 상향 조정

근본적인 해결을 위해 파라미터 값을 늘립니다. PROCESSES는 동적 변경이 불가하므로 SPFILE 수정 후 재기동이 필요합니다.

-- 현재 PROCESSES 관련 파라미터 전체 확인
SELECT name, value, description
FROM v$parameter
WHERE name IN ('processes', 'sessions', 'transactions')
ORDER BY name;

-- SPFILE을 이용한 PROCESSES 파라미터 변경
-- (SESSIONS = PROCESSES * 1.1 + 5, TRANSACTIONS = SESSIONS * 1.1 공식 참고)
ALTER SYSTEM SET PROCESSES = 500 SCOPE = SPFILE;

-- SESSIONS와 TRANSACTIONS는 PROCESSES 변경 시 함께 조정 권장
ALTER SYSTEM SET SESSIONS     = 555  SCOPE = SPFILE;
ALTER SYSTEM SET TRANSACTIONS = 611  SCOPE = SPFILE;

-- 변경 후 재기동 필요 (DBA 확인 후 수행)
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- 재기동 후 적용 확인
SELECT name, value
FROM v$parameter
WHERE name IN ('processes', 'sessions', 'transactions');

💡 권장 공식:

SESSIONS = CEIL(PROCESSES * 1.1) + 5

TRANSACTIONS = CEIL(SESSIONS * 1.1)

④ IDLE_TIME 프로파일로 비활성 세션 자동 정리

-- 사용자 프로파일에 IDLE_TIME 설정 (단위: 분)
-- 기존 DEFAULT 프로파일 수정
ALTER PROFILE default LIMIT IDLE_TIME 30;

-- 특정 애플리케이션 계정 전용 프로파일 생성
CREATE PROFILE app_user_profile LIMIT
    IDLE_TIME         30      -- 30분 이상 비활성 시 자동 종료
    SESSIONS_PER_USER 50      -- 동일 유저 최대 세션 수 제한
    CONNECT_TIME      480;    -- 최대 연결 유지 시간 (분)

-- 프로파일을 특정 계정에 적용
ALTER USER app_user PROFILE app_user_profile;

-- 적용 확인
SELECT username, profile
FROM dba_users
WHERE username = 'APP_USER';

예방 방법

1. 모니터링 알림 체계 구축 — PROCESSES 사용률 임계치 경보

PROCESSES 파라미터 대비 실제 사용률이 80%를 넘으면 사전에 알람을 받을 수 있도록 모니터링 스크립트나 OEM(Oracle Enterprise Manager) 임계치를 설정해야 합니다. 아래 쿼리를 크론잡(cron) 또는 모니터링 도구에 등록해 주기적으로 체크하면 장애 전에 선제적으로 대응할 수 있습니다.

-- PROCESSES 사용률 체크 쿼리 (80% 초과 시 경고 필요)
SELECT 
    p.value                                              AS max_processes,
    COUNT(s.sid)                                         AS used_processes,
    ROUND(COUNT(s.sid) / p.value * 100, 2)               AS usage_pct,
    CASE 
        WHEN ROUND(COUNT(s.sid) / p.value * 100, 2) >= 90 
            THEN '🚨 CRITICAL'
        WHEN ROUND(COUNT(s.sid) / p.value * 100, 2) >= 80 
            THEN '⚠️  WARNING'
        ELSE '✅ NORMAL'
    END                                                  AS status
FROM v$parameter p, v$session s
WHERE p.name = 'processes'
GROUP BY p.value;

2. 애플리케이션 레벨 커넥션 풀 및 Connection Validation 설정 강화

WAS(Tomcat, WebLogic, JBoss 등)의 커넥션 풀에서 testOnBorrow, validationQuery, removeAbandonedTimeout 등의 옵션을 반드시 활성화해야 합니다. 이를 통해 풀에서 커넥션을 꺼낼 때마다 유효성을 검증하고, 일정 시간 이상 사용되지 않는 커넥션은 자동으로 회수하여 좀비 세션이 DB 측에 누적되는 것을 원천 차단할 수 있습니다. 또한 커넥션 풀의 maxTotal(최대 커넥션 수)을 DB의 PROCESSES 한도를 고려하여 서버 대수에 맞게 적절히 분배해야 합니다.

에러 코드	설명
ORA-00018	`maximum number of sessions exceeded` — SESSIONS 파라미터 초과. ORA-00020과 함께 발생하는 경우가 많음
ORA-00019	`maximum number of session licenses exceeded` — 라이선스 기반 세션 제한 초과
ORA-12519	`TNS: no appropriate service handler found` — 리스너가 DB에 연결을 전달하지 못할 때 발생하며, ORA-00020 상황에서 클라이언트가 받는 에러
ORA-12516	`TNS: listener could not find available handler with matching protocol stack` — 가용 서버 프로세스 부족 시 발생
ORA-04031	`unable to allocate shared memory` — PROCESSES 증가 시 SGA 크기도 함께 검토해야 하는 연관 에러

DEV Community