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

#oracle #database #dba #sql

ORA-00028 your session has been killed 는?

ORA-00028 에러는 현재 사용 중인 Oracle 세션이 DBA 또는 권한 있는 사용자에 의해 강제로 종료되었을 때 발생하는 에러입니다. 사용자가 다음 SQL 문을 실행하려는 순간, Oracle은 해당 세션이 이미 kill 처리되었음을 감지하고 이 에러를 반환합니다. 주로 장시간 실행되는 쿼리, 락(Lock) 점유, 과도한 리소스 사용 등의 이유로 DBA가 ALTER SYSTEM KILL SESSION 명령을 수행했을 때 영향받는 세션 측에서 경험하게 됩니다.

주요 발생 원인

1. DBA에 의한 명시적 세션 강제 종료

가장 흔한 원인으로, DBA가 ALTER SYSTEM KILL SESSION 또는 ALTER SYSTEM DISCONNECT SESSION 명령을 사용하여 특정 세션을 강제로 종료한 경우입니다. 장시간 실행 중인 배치 쿼리, 불필요한 락 보유 세션, 또는 시스템 성능에 악영향을 미치는 세션을 제거하기 위해 이 조치가 취해지는 것이 일반적입니다. 종료 명령이 내려진 이후에도 세션이 즉시 정리되지 않고 KILLED 상태로 잠시 남아 있다가, 해당 세션에서 다음 요청이 들어오는 시점에 ORA-00028이 반환됩니다.

2. 락(Lock) 경합 및 데드락(Deadlock)으로 인한 세션 종료

여러 세션이 동일한 리소스를 동시에 점유하려 할 때 발생하는 락 경합이나 데드락 상황에서 Oracle 또는 DBA가 해당 세션 중 하나를 강제 종료하는 경우입니다. 특히 애플리케이션 레벨에서 트랜잭션을 적절히 관리하지 않고 커밋이나 롤백 없이 장시간 대기 상태에 놓인 세션은 DBA의 주요 종료 대상이 됩니다. 이 경우 종료된 세션의 사용자는 다음 데이터베이스 호출 시점에 ORA-00028 에러를 마주하게 됩니다.

3. 유휴 세션 타임아웃 또는 리소스 프로파일 제한

Oracle의 PROFILE 설정에서 IDLE_TIME, CONNECT_TIME 등의 파라미터가 설정되어 있을 경우, 해당 시간을 초과한 세션은 자동으로 kill 처리될 수 있습니다. 또한 Oracle Resource Manager를 통해 특정 소비자 그룹에 CPU 또는 I/O 제한이 설정된 경우, 제한을 초과한 세션이 강제로 종료될 수도 있습니다. 이 원인은 개발자 입장에서 예측하기 어려운 경우가 많아 정확한 원인 파악을 위해 DBA와 협력이 필요합니다.

해결 방법

원인 1: DBA에 의한 명시적 세션 강제 종료

먼저 현재 KILLED 상태로 남아 있는 세션을 확인합니다.

-- KILLED 상태의 세션 확인
SELECT sid,
       serial#,
       username,
       status,
       osuser,
       machine,
       program,
       sql_id,
       last_call_et
FROM   v$session
WHERE  status = 'KILLED'
ORDER  BY last_call_et DESC;

KILLED 상태의 세션이 오랫동안 정리되지 않는다면 OS 레벨의 프로세스를 확인하고 정리해야 합니다.

-- 세션과 연결된 OS 프로세스 정보 조회
SELECT s.sid,
       s.serial#,
       s.status,
       s.username,
       p.spid AS os_pid
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.status = 'KILLED';

위 쿼리로 os_pid를 확인한 후, Linux/Unix 환경에서는 다음과 같이 프로세스를 종료합니다.

-- (참고용 Shell 명령 - SQL이 아닌 OS 명령어)
-- kill -9 [os_pid]

-- Oracle 레벨에서 즉시 연결 해제 방법 (DBA 권한 필요)
ALTER SYSTEM DISCONNECT SESSION '&sid,&serial#' IMMEDIATE;

클라이언트 애플리케이션에서는 세션이 kill되면 반드시 커넥션을 재생성해야 합니다. 커넥션 풀 환경에서는 해당 커넥션을 풀에서 제거하고 새로운 커넥션으로 교체하는 로직이 필요합니다.

원인 2: 락 경합 및 데드락으로 인한 세션 종료

현재 락을 보유하고 있는 세션과 대기 중인 세션을 파악합니다.

-- 락 경합 현황 조회
SELECT l.sid,
       s.serial#,
       s.username,
       s.status,
       l.type,
       l.lmode,
       l.request,
       o.object_name,
       o.object_type
FROM   v$lock l
JOIN   v$session s ON l.sid = s.sid
LEFT   JOIN dba_objects o ON l.id1 = o.object_id
WHERE  l.request > 0
   OR  (l.lmode > 0 AND l.type IN ('TM', 'TX'))
ORDER  BY l.sid;

블로킹 세션과 대기 세션을 한눈에 파악하려면 아래 쿼리를 활용합니다.

-- 블로킹 세션 분석 쿼리
SELECT  blocker.sid          AS blocking_sid,
        blocker.serial#      AS blocking_serial,
        blocker.username     AS blocking_user,
        blocker.status       AS blocking_status,
        waiter.sid           AS waiting_sid,
        waiter.serial#       AS waiting_serial,
        waiter.username      AS waiting_user,
        waiter.sql_id        AS waiting_sql_id,
        waiter.wait_class    AS wait_class,
        waiter.seconds_in_wait
FROM    v$session blocker
JOIN    v$session waiter ON blocker.sid = waiter.blocking_session
WHERE   waiter.blocking_session IS NOT NULL
ORDER   BY waiter.seconds_in_wait DESC;

락을 보유한 채 장시간 대기 중인 세션을 종료합니다.

-- 문제 세션 종료 (DBA 권한 필요)
ALTER SYSTEM KILL SESSION '&blocking_sid,&blocking_serial' IMMEDIATE;

-- 특정 세션에서 실행 중인 SQL 확인 후 종료 결정
SELECT sql_fulltext
FROM   v$sql
WHERE  sql_id = '&sql_id';

원인 3: 유휴 세션 타임아웃 또는 리소스 프로파일 제한

현재 적용된 프로파일 설정을 확인합니다.

-- 사용자별 적용 프로파일 확인
SELECT username,
       profile
FROM   dba_users
WHERE  account_status = 'OPEN'
ORDER  BY username;

-- 프로파일 상세 리소스 제한 확인
SELECT profile,
       resource_name,
       resource_type,
       limit
FROM   dba_profiles
WHERE  profile IN (
           SELECT profile FROM dba_users WHERE username = '&target_user'
       )
ORDER  BY resource_type,
          resource_name;

필요 시 프로파일의 타임아웃 값을 조정합니다.

-- 특정 프로파일의 IDLE_TIME 제한 완화 (분 단위)
ALTER PROFILE app_user_profile LIMIT
    IDLE_TIME    30
    CONNECT_TIME UNLIMITED;

-- DEFAULT 프로파일 확인 및 수정 (주의: 모든 사용자에게 영향)
ALTER PROFILE DEFAULT LIMIT
    IDLE_TIME UNLIMITED;

-- 변경 사항 즉시 반영 확인
SELECT profile,
       resource_name,
       limit
FROM   dba_profiles
WHERE  profile = 'APP_USER_PROFILE'
  AND  resource_name IN ('IDLE_TIME', 'CONNECT_TIME');

예방 방법

1. 커넥션 풀 및 애플리케이션 레벨의 예외 처리 강화

애플리케이션에서 ORA-00028 에러를 명시적으로 캐치하고, 해당 커넥션을 즉시 폐기한 뒤 새로운 커넥션을 획득하도록 재시도 로직을 구현해야 합니다. 커넥션 풀(HikariCP, DBCP 등)을 사용하는 경우 testOnBorrow, validationQuery 옵션을 설정하여 KILLED된 커넥션이 애플리케이션에 반환되지 않도록 설정하고, 세션이 kill되더라도 사용자 경험에 영향이 최소화되도록 graceful한 에러 처리 흐름을 설계해야 합니다.

-- 커넥션 유효성 검증용 쿼리 (validationQuery로 활용)
SELECT 1 FROM dual;

-- 세션 상태 모니터링 자동화를 위한 뷰 생성 예시
CREATE OR REPLACE VIEW v_long_running_sessions AS
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et       AS elapsed_seconds,
       s.sql_id,
       q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q ON s.sql_id = q.sql_id
WHERE  s.status = 'ACTIVE'
  AND  s.last_call_et > 300   -- 5분 이상 실행 중인 세션
  AND  s.username IS NOT NULL
ORDER  BY s.last_call_et DESC;

2. 정기적인 세션 및 락 모니터링 체계 구축

장기 실행 쿼리나 락 경합이 발생하기 전에 사전에 감지하고 조치를 취할 수 있는 모니터링 체계를 구축해야 합니다. Oracle Enterprise Manager(OEM) 또는 커스텀 모니터링 스크립트를 통해 임계값(예: 실행 시간 10분 초과, 락 대기 5분 초과)을 설정하고 알람을 발송하면, 세션이 강제 kill되는 상황을 예방하거나 빠르게 원인을 파악할 수 있습니다. 또한 개발팀에 SQL 튜닝 가이드라인을 공유하고 코드 리뷰 시 장기 실행 쿼리나 명시적 락 사용에 대한 검토를 의무화하는 것이 장기적으로 효과적입니다.

-- 정기 모니터링 스크립트 예시: 10분 이상 실행 중인 세션 감지
SELECT s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.machine,
       s.program,
       ROUND(s.last_call_et / 60, 1) AS running_minutes,
       s.sql_id,
       SUBSTR(q.sql_text, 1, 100)    AS sql_preview
FROM   v$session s
LEFT   JOIN v$sql q ON s.sql_id = q.sql_id
WHERE  s.type = 'USER'
  AND  s.status = 'ACTIVE'
  AND  s.last_call_et > 600
ORDER  BY s.last_call_et DESC;