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

ORA-00051 timeout occurred while waiting for a resource 는?

ORA-00051은 Oracle 데이터베이스에서 특정 리소스를 기다리는 동안 설정된 타임아웃 시간이 초과되었을 때 발생하는 에러입니다. 주로 분산 트랜잭션(Distributed Transaction) 환경에서 원격 데이터베이스와의 통신 중 락(Lock) 또는 리소스를 획득하지 못한 채 대기 시간이 넘어갈 때 발생합니다. 단일 인스턴스 환경보다는 DB Link를 통한 원격 쿼리나 RAC(Real Application Clusters) 환경에서 더 빈번하게 나타나며, 트랜잭션 롤백이나 세션 종료로 이어질 수 있어 운영 중 즉각적인 조치가 필요합니다.

주요 발생 원인

1. 분산 트랜잭션에서의 락 대기 타임아웃 (DISTRIBUTED_LOCK_TIMEOUT)

Oracle의 DISTRIBUTED_LOCK_TIMEOUT 파라미터는 분산 트랜잭션 환경에서 락을 기다리는 최대 시간을 초(seconds) 단위로 정의합니다. 기본값은 60초이며, DB Link를 통해 원격 테이블에 DML(INSERT, UPDATE, DELETE)을 수행할 때 원격 세션이 락을 보유한 채 응답하지 않으면 이 파라미터의 값을 초과하여 ORA-00051이 발생합니다. 특히 배치 작업이나 대용량 트랜잭션이 원격 DB에서 오래 실행 중일 때 자주 목격됩니다.

2. 네트워크 불안정 또는 원격 DB 응답 지연

DB Link로 연결된 원격 Oracle 인스턴스가 네트워크 단절, 과부하, 또는 장애 상태일 경우 로컬 세션은 리소스 응답을 기다리다 타임아웃됩니다. TCP 수준에서 패킷 손실이 발생하거나 방화벽 세션 만료(Firewall Session Timeout)로 인해 연결이 끊겼음에도 Oracle이 이를 인지하지 못하고 대기하는 경우에도 본 에러가 트리거됩니다. 이런 상황은 야간 배치 윈도우나 인프라 유지보수 시간대에 특히 많이 발생합니다.

3. RAC 환경에서의 글로벌 리소스 경합 (GCS/GES 지연)

Oracle RAC 환경에서는 GCS(Global Cache Service)와 GES(Global Enqueue Service)를 통해 인스턴스 간 리소스를 공유합니다. 특정 인스턴스가 블록 전송이나 인큐(Enqueue) 획득에 과도한 시간이 소요될 경우 내부 타임아웃이 발생하고 ORA-00051로 이어질 수 있습니다. 인터커넥트 네트워크의 레이턴시가 높거나 특정 노드에 과부하가 집중될 때 이 문제가 심화됩니다.

해결 방법

원인 1: DISTRIBUTED_LOCK_TIMEOUT 값 조정

현재 설정값을 확인하고, 업무 요건에 맞게 값을 조정합니다. 단, 값을 무작정 늘리면 락 경합이 길어질 수 있으므로 근본 원인 파악이 우선입니다.

-- 현재 DISTRIBUTED_LOCK_TIMEOUT 파라미터 확인
SELECT name, value, description
FROM v$parameter
WHERE name = 'distributed_lock_timeout';

-- 동적으로 변경 (ALTER SYSTEM)
-- 기본값 60초 → 필요에 따라 조정 (예: 120초)
ALTER SYSTEM SET distributed_lock_timeout = 120 SCOPE = BOTH;

-- 변경 후 확인
SHOW PARAMETER distributed_lock_timeout;

현재 분산 트랜잭션 대기 세션을 파악하여 원인 세션을 식별합니다.

-- 분산 트랜잭션 대기 세션 확인
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.wait_class,
       s.event,
       s.seconds_in_wait,
       s.sql_id,
       s.program,
       s.machine
FROM   v$session s
WHERE  s.wait_class != 'Idle'
  AND  s.event LIKE '%distributed%'
ORDER BY s.seconds_in_wait DESC;

-- 현재 분산 트랜잭션 목록 확인
SELECT local_tran_id,
       global_tran_id,
       state,
       mixed,
       advice,
       tran_comment,
       fail_time,
       force_time,
       retry_time,
       os_user,
       os_oid,
       db_user,
       interface
FROM   dba_2pc_pending;

In-doubt 상태로 남겨진 분산 트랜잭션을 강제로 처리합니다.

-- In-doubt 트랜잭션 강제 커밋 (원격 DB와 협의 후 수행)
COMMIT FORCE 'local_tran_id_값';

-- 또는 강제 롤백
ROLLBACK FORCE 'local_tran_id_값';

-- 예시
COMMIT FORCE '1.23.456';
ROLLBACK FORCE '1.23.456';

원인 2: 네트워크 관련 락 대기 세션 강제 종료

응답하지 않는 세션을 식별하고 종료합니다.

-- 장시간 대기 중인 세션 조회 (300초 이상)
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.event,
       s.seconds_in_wait,
       s.blocking_session,
       s.sql_id,
       q.sql_text
FROM   v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE  s.seconds_in_wait > 300
  AND  s.status = 'ACTIVE'
ORDER BY s.seconds_in_wait DESC;

-- 블로킹 세션 체인 파악
SELECT blocker.sid          AS blocker_sid,
       blocker.serial#      AS blocker_serial,
       blocker.username     AS blocker_user,
       blocker.program      AS blocker_program,
       waiter.sid           AS waiter_sid,
       waiter.serial#       AS waiter_serial,
       waiter.username      AS waiter_user,
       waiter.event         AS wait_event,
       waiter.seconds_in_wait
FROM   v$session blocker
JOIN   v$session waiter ON waiter.blocking_session = blocker.sid
WHERE  waiter.blocking_session IS NOT NULL;

-- 문제 세션 강제 종료
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 예시
ALTER SYSTEM KILL SESSION '145,2038' IMMEDIATE;

DB Link 연결 상태를 점검합니다.

-- 현재 DB Link 목록 조회
SELECT db_link, username, host, created
FROM   dba_db_links
ORDER BY db_link;

-- 특정 DB Link 연결 테스트 (간단한 원격 쿼리)
SELECT 1 FROM dual@your_db_link_name;

-- 활성화된 DB Link 세션 확인
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       s.status,
       s.event,
       s.seconds_in_wait
FROM   v$session s
WHERE  s.program LIKE '%dbnewid%'
    OR s.event   LIKE '%db link%'
    OR s.username IN (
        SELECT username FROM dba_db_links
    );

원인 3: RAC 환경 글로벌 리소스 경합 진단

-- RAC 인터커넥트 대기 이벤트 확인
SELECT inst_id,
       event,
       total_waits,
       total_timeouts,
       time_waited,
       average_wait,
       max_wait
FROM   gv$system_event
WHERE  event IN (
    'gc buffer busy acquire',
    'gc buffer busy release',
    'gc cr request',
    'gc current request',
    'global enqueue wait'
)
ORDER BY inst_id, time_waited DESC;

-- 인스턴스 간 블로킹 현황 조회
SELECT g1.inst_id       AS blocker_inst,
       g1.sid           AS blocker_sid,
       g2.inst_id       AS waiter_inst,
       g2.sid           AS waiter_sid,
       g2.event         AS wait_event,
       g2.seconds_in_wait
FROM   gv$session g1
JOIN   gv$session g2
  ON   g2.blocking_instance = g1.inst_id
 AND   g2.blocking_session  = g1.sid
WHERE  g2.blocking_session IS NOT NULL
ORDER BY g2.seconds_in_wait DESC;

-- GES(Global Enqueue Service) 락 현황
SELECT inst_id,
       type,
       id1,
       id2,
       lmode,
       request,
       block
FROM   gv$lock
WHERE  block = 1
ORDER BY inst_id;

예방 방법

1. SQLNET 및 DB Link Dead Connection 감지 설정 강화

네트워크 단절이 발생해도 Oracle이 이를 신속하게 감지하도록 sqlnet.ora에 Dead Connection Detection(DCD) 파라미터를 설정합니다. 이 설정은 방치된 분산 트랜잭션 세션을 자동으로 정리하여 타임아웃 에러 발생을 줄여줍니다.

-- sqlnet.ora 설정 예시 (DB 서버 측 $ORACLE_HOME/network/admin/sqlnet.ora)
-- SQLNET.EXPIRE_TIME = 10  (10분마다 연결 생존 여부 확인)

-- Oracle 파라미터 수준에서 분산 트랜잭션 자동 복구 설정
SELECT name, value
FROM   v$parameter
WHERE  name IN (
    'distributed_lock_timeout',
    'commit_point_strength',
    'open_links',
    'open_links_per_instance'
);

-- DB Link 최대 오픈 수 적정 수준 유지 (기본값 4, 최대 255)
ALTER SYSTEM SET open_links = 10 SCOPE = SPFILE;
ALTER SYSTEM SET open_links_per_instance = 10 SCOPE = SPFILE;

2. 정기적인 In-doubt 트랜잭션 모니터링 및 알림 체계 구축

DBA_2PC_PENDING 뷰를 주기적으로 조회하여 처리되지 못한 분산 트랜잭션을 빠르게 감지하고 조치합니다. 운영 환경에서는 아래 쿼리를 Cron이나 Oracle Scheduler Job으로 등록하여 In-doubt 트랜잭션 발생 시 담당자에게 알림이 가도록 자동화하는 것을 권장합니다.

-- In-doubt 트랜잭션 모니터링 쿼리 (Scheduler Job 또는 모니터링 툴에 등록)
SELECT COUNT(*) AS indoubt_count,
       MIN(fail_time) AS oldest_fail_time,
       MAX(fail_time) AS latest_fail_time
FROM   dba_2pc_pending
WHERE  state IN ('prepared', 'collecting', 'committed', 'rolled back');

-- 상세 내역 알림용 쿼리
SELECT local_tran_id,
       global_tran_id,
       state,
       fail_time,
       db_user,
       os_user,
       ROUND((SYSDATE - fail_time) * 24 * 60, 2) AS elapsed_minutes
FROM   dba_2pc_pending
WHERE  fail_time < SYSDATE - (1/24)  -- 1시간 이상 지난 미처리 트랜잭션
ORDER BY fail_time;

-- Oracle Scheduler를 이용한 자동 점검 Job 등록 예시
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'CHECK_INDOUBT_TRANS',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            DECLARE
                v_cnt NUMBER;
            BEGIN
                SELECT COUNT(*) INTO v_cnt FROM dba_2pc_pending;
                IF v_cnt > 0 THEN
                    -- 알림 로직 (DBMS_ALERT 또는 UTL_MAIL 활용)
                    DBMS_OUTPUT.PUT_LINE(''In-doubt transactions found: '' || v_cnt);
                END IF;
            END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
        enabled         => TRUE,
        comments        => 'Monitor in-doubt distributed transactions'
    );
END;
/

推荐订阅源

DEV Community