umzzil nng

Posted on May 28 • Originally published at oraerror.com

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

#oracle #database #dba #sql

ORA-00021 session attached to some other process 는?

ORA-00021 에러는 Oracle 데이터베이스에서 특정 세션이 이미 다른 프로세스에 연결(attach)되어 있을 때 발생하는 에러입니다. 주로 MTS(Multi-Threaded Server, 현재는 Shared Server라고도 불림) 환경이나 Oracle Connection Manager, 또는 특수한 세션 전환 작업 중에 하나의 세션을 두 개의 프로세스가 동시에 사용하려 할 때 나타납니다. 실무에서는 잘못된 세션 관리, 비정상적인 프로세스 종료 후 세션이 정리되지 않은 상황, 또는 Oracle 내부적인 세션-프로세스 매핑 불일치가 원인이 되는 경우가 많습니다.

주요 발생 원인

1. Shared Server(MTS) 환경에서의 세션-프로세스 충돌

Oracle Shared Server 구성에서는 여러 클라이언트 세션이 소수의 서버 프로세스를 공유합니다. 디스패처(Dispatcher)가 세션을 특정 공유 서버 프로세스에 할당하는 과정에서 이전 작업이 완전히 종료되지 않은 채 다른 프로세스가 동일 세션에 접근을 시도하면 ORA-00021이 발생합니다. 특히 네트워크 불안정이나 클라이언트 비정상 종료 이후 세션 상태가 애매하게 남아 있을 때 이 문제가 빈번하게 나타납니다.

2. 비정상적인 프로세스 종료 이후 세션 잔존

OS 레벨에서 서버 프로세스(예: oracle 프로세스)가 강제로 kill되거나 비정상 종료된 경우, 해당 프로세스에 연결되어 있던 세션 정보가 Oracle SGA(System Global Area) 내에 여전히 "attached" 상태로 남아 있을 수 있습니다. 이 상태에서 새로운 프로세스가 동일 세션 슬롯을 재사용하려 하거나, DBA가 해당 세션을 정리하려 할 때 ORA-00021이 발생합니다. V$SESSION과 V$PROCESS의 매핑이 불일치하는 상황이 대표적인 징후입니다.

3. 잘못된 DBMS_SESSION 또는 세션 전환 로직 사용

DBMS_SESSION.ATTACH 또는 Oracle Advanced Queuing, XA 트랜잭션 등에서 세션을 명시적으로 detach/attach 하는 로직에 오류가 있을 경우 발생합니다. 예를 들어, XA 트랜잭션 처리 중 글로벌 트랜잭션 브랜치가 이미 다른 프로세스에서 처리 중인데 추가 요청이 들어오면 세션 충돌이 생깁니다. 애플리케이션 코드에서 커넥션 풀 관리 로직이 잘못 구현되어 있을 때도 동일한 문제가 발생할 수 있습니다.

해결 방법

원인 1: Shared Server 환경에서의 충돌 해결

먼저 현재 세션과 프로세스 상태를 조회하여 문제가 되는 세션을 식별합니다.

-- 세션과 프로세스 매핑 현황 조회
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.server,
       s.program,
       s.machine,
       p.spid        AS os_pid,
       s.logon_time,
       s.last_call_et AS idle_seconds
FROM   v$session s
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.type = 'USER'
ORDER BY s.last_call_et DESC;

-- Shared Server 환경에서 디스패처 상태 확인
SELECT name,
       status,
       messages,
       bytes,
       breaks,
       accept
FROM   v$dispatcher;

-- 문제가 되는 세션 강제 종료 (DBA 권한 필요)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 예시: SID=125, SERIAL#=3467인 세션 종료
ALTER SYSTEM KILL SESSION '125,3467' IMMEDIATE;

세션 강제 종료 후에도 STATUS가 'KILLED'로 남아있다면 OS 레벨에서 추가 조치가 필요합니다.

원인 2: 비정상 종료된 프로세스 이후 잔존 세션 정리

-- STATUS가 KILLED이거나 오랫동안 INACTIVE 상태인 세션 조회
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.osuser,
       s.machine,
       p.spid AS os_process_id,
       s.last_call_et AS idle_time_sec
FROM   v$session s
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.status IN ('KILLED', 'INACTIVE')
  AND  s.username IS NOT NULL
  AND  s.last_call_et > 3600  -- 1시간 이상 유휴 상태
ORDER BY s.last_call_et DESC;

-- PADDR이 있지만 실제 OS 프로세스가 없는 고아 세션 식별
-- (이 쿼리는 paddr이 v$process에 없는 세션을 찾음)
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.paddr
FROM   v$session s
WHERE  s.paddr NOT IN (SELECT addr FROM v$process)
  AND  s.username IS NOT NULL;

-- OS 레벨에서 프로세스 확인 후 정리 (Linux/Unix 기준)
-- SQL*Plus에서 spid를 확인 후 OS에서 직접 kill
-- 1단계: Oracle에서 세션 종료 시도
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

-- 2단계: 위 명령이 효과 없으면 IMMEDIATE 옵션 사용
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

OS 레벨에서 직접 프로세스를 확인하고 종료할 때는 아래 절차를 따릅니다.

-- spid 확인
SELECT p.spid, s.sid, s.serial#, s.username
FROM   v$process p
JOIN   v$session s ON p.addr = s.paddr
WHERE  s.sid = &target_sid;

위 쿼리로 얻은 spid 값을 OS에서 kill -9 <spid> 명령으로 제거한 후, SMON 백그라운드 프로세스가 자동으로 잔여 세션을 정리할 때까지 대기합니다.

원인 3: XA 트랜잭션 또는 세션 전환 로직 문제

-- 현재 진행 중인 XA/분산 트랜잭션 확인
SELECT local_tran_id,
       global_tran_id,
       state,
       mixed,
       advice,
       tran_comment
FROM   dba_2pc_pending;

-- 오래된 인-더브트(in-doubt) 트랜잭션 강제 커밋 또는 롤백
-- 주의: 반드시 애플리케이션 팀과 협의 후 실행할 것

-- 강제 커밋
COMMIT FORCE 'local_tran_id';

-- 강제 롤백
ROLLBACK FORCE 'local_tran_id';

-- 예시
ROLLBACK FORCE '1.23.456';

-- 세션 전환 관련 DBMS_SESSION 사용 예시 (올바른 패턴)
-- Attach 전 반드시 현재 세션 상태 확인
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_count
  FROM   v$session
  WHERE  audsid = SYS_CONTEXT('USERENV','SESSIONID')
    AND  status  = 'ACTIVE';

  IF v_count > 0 THEN
    -- 안전하게 세션 작업 수행
    DBMS_OUTPUT.PUT_LINE('세션 정상 상태 확인됨');
  ELSE
    DBMS_OUTPUT.PUT_LINE('세션 상태 이상 - 관리자 확인 필요');
  END IF;
END;
/

예방 방법

1. 세션 타임아웃 및 유휴 세션 자동 정리 정책 수립

Oracle Profile을 활용하여 유휴 세션이 일정 시간 이상 지속될 경우 자동으로 종료되도록 설정합니다. 이를 통해 비정상 종료된 클라이언트로 인한 좀비 세션이 시스템에 누적되는 것을 방지할 수 있습니다.

-- 유휴 세션 자동 종료 Profile 생성
CREATE PROFILE limited_session_profile LIMIT
  IDLE_TIME          30    -- 30분 유휴 시 세션 종료
  CONNECT_TIME       480   -- 최대 8시간 연결 유지
  SESSIONS_PER_USER  10    -- 사용자당 최대 10개 세션
  FAILED_LOGIN_ATTEMPTS 5;

-- 해당 Profile을 사용자에게 적용
ALTER USER app_user PROFILE limited_session_profile;

-- 현재 Profile 설정 확인
SELECT username, profile
FROM   dba_users
WHERE  username = 'APP_USER';

-- Profile 상세 설정 확인
SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'LIMITED_SESSION_PROFILE'
  AND  resource_type = 'KERNEL';

2. 정기적인 세션 및 프로세스 모니터링 자동화

Shared Server 환경이나 커넥션 풀을 사용하는 환경에서는 주기적으로 세션과 프로세스 매핑 상태를 점검하는 모니터링 스크립트를 스케줄링하여 실행합니다. 이상 징후를 조기에 발견하면 ORA-00021과 같은 에러가 서비스 장애로 이어지기 전에 선제적으로 대응할 수 있습니다.

-- 모니터링용 뷰 생성 (DBA가 주기적으로 조회)
CREATE OR REPLACE VIEW v_session_health AS
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.server,
       s.machine,
       s.program,
       p.spid              AS os_pid,
       s.last_call_et      AS idle_sec,
       s.logon_time,
       CASE
         WHEN p.spid IS NULL AND s.paddr != '00' THEN 'ORPHAN_SESSION'
         WHEN s.status = 'KILLED'               THEN 'KILLED_SESSION'
         WHEN s.last_call_et > 7200             THEN 'LONG_IDLE'
         ELSE 'NORMAL'
       END AS health_status
FROM   v$session s
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.type = 'USER';

-- 비정상 세션만 필터링하여 조회
SELECT * FROM v_session_health
WHERE  health_status != 'NORMAL'
ORDER BY idle_sec DESC;

에러 코드	설명
ORA-00020	`maximum number of processes exceeded` - 최대 프로세스 수 초과. ORA-00021과 마찬가지로 프로세스/세션 관리 문제에서 함께 발생하는 경우가 많음
ORA-00031	`session marked for kill` - 세션이 종료 대상으로 표시되었으나 아직 완전히 종료되지 않은 상태. ORA-00021 해결 시도 중 나타날 수 있음
ORA-03113	`end-of-file on communication channel` - 서버 프로세스가 예기치 않게 종료될 때 발생. 비정상 프로세스 종료로 인한 ORA-00021의 선행 에러가 되기도 함
ORA-03114	`not connected to ORACLE` - 연결이 끊어진 상태에서 작업 시도 시 발생. 세션-프로세스 연결 이상과 관련됨
ORA-12537	`TNS: connection closed` - 네트워크 레벨에서 연결이 닫힌 경우로, Shared Server 환경에서 ORA-00021과 연계되어 나타날 수 있음

DEV Community