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

-- 현재 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 트랜잭션 강제 커밋 (원격 DB와 협의 후 수행)
COMMIT FORCE 'local_tran_id_값';

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

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

-- 장시간 대기 중인 세션 조회 (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 목록 조회
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
    );

-- 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;

-- 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;

-- 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;
/