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

ORA-65023 active transaction exists in pluggable database 는?

ORA-65023 에러는 Oracle Multitenant 환경에서 특정 Pluggable Database(PDB)에 아직 커밋되지 않은 활성 트랜잭션이 존재하는 상태에서, PDB를 닫거나(CLOSE) 언플러그(UNPLUG)하려고 할 때 발생하는 에러입니다. Oracle 12c 이후 멀티테넌트 아키텍처가 도입되면서 함께 등장한 에러로, CDB(Container Database) 관리자가 PDB의 라이프사이클을 제어할 때 자주 마주치게 됩니다. 진행 중인 트랜잭션이 완전히 종료되거나 롤백되지 않으면, Oracle은 데이터 일관성 보호를 위해 해당 작업을 차단하고 이 에러를 반환합니다.

---

주요 발생 원인

1. PDB CLOSE 또는 UNPLUG 시 미완료 트랜잭션 존재

가장 흔한 원인으로, DBA가 ALTER PLUGGABLE DATABASE ... CLOSE 명령을 실행할 때 해당 PDB 내에 아직 COMMIT 또는 ROLLBACK이 수행되지 않은 세션이 존재하는 경우 발생합니다. 배치 작업, 장시간 실행 중인 DML, 또는 애플리케이션이 연결을 열어두고 트랜잭션을 종료하지 않은 경우가 대표적입니다. 특히 야간 배치 작업 도중 PDB 유지보수를 시도할 때 매우 빈번하게 발생하므로, 작업 전 반드시 활성 트랜잭션 여부를 사전 점검해야 합니다.

2. PDB 언플러그 또는 데이터베이스 마이그레이션 작업 중 트랜잭션 잔존

PDB를 다른 CDB로 이동하거나 XML 파일로 언플러그할 때, 해당 PDB에 연결된 세션이 트랜잭션을 보유하고 있으면 ORA-65023이 발생합니다. 마이그레이션이나 클라우드 이관 작업처럼 계획된 다운타임 중에도 애플리케이션 서버가 연결 풀을 유지하고 있어 예상치 못한 트랜잭션이 남아있는 경우가 많습니다. 이런 경우는 네트워크 단절이나 애플리케이션 행(hang)과 함께 발생하는 경우가 많아 상황을 더욱 복잡하게 만들 수 있습니다.

3. RAC 환경에서 특정 인스턴스의 PDB 제어 시 분산 트랜잭션 문제

Oracle RAC(Real Application Clusters) 환경에서는 여러 인스턴스가 동일한 PDB에 접근할 수 있으므로, 한 인스턴스에서 PDB를 닫으려 할 때 다른 인스턴스에서 열려 있는 트랜잭션이 존재하면 ORA-65023이 발생할 수 있습니다. 분산 트랜잭션(Distributed Transaction)이나 In-Doubt Transaction이 남아 있는 경우에도 이 에러가 발생하며, 이때는 일반 세션 종료 방법만으로는 해결이 어렵습니다. 모든 RAC 노드에서 트랜잭션 상태를 점검해야 하므로 진단과 해결이 더 까다롭습니다.

---

해결 방법

해결책 1: 활성 트랜잭션 및 세션 확인 후 종료

먼저 해당 PDB에서 활성 트랜잭션과 세션을 조회합니다.

-- CDB$ROOT에서 실행: 특정 PDB의 활성 트랜잭션 확인
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id,
       t.used_ublk, t.used_urec, s.con_id
FROM   v$session s, v$transaction t
WHERE  s.taddr = t.addr
AND    s.con_id = (SELECT con_id FROM v$pdbs WHERE name = 'PDB1');

-- 활성 세션 목록 확인 (PDB 이름으로 필터링)
SELECT sid, serial#, username, status, machine, program, con_id
FROM   v$session
WHERE  con_id = (SELECT con_id FROM v$pdbs WHERE name = 'PDB1')
AND    status = 'ACTIVE';

확인 후 해당 세션을 종료합니다.

-- 특정 세션 종료 (SID와 SERIAL# 사용)
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

-- 여러 세션을 한 번에 종료하는 스크립트
BEGIN
  FOR r IN (
    SELECT sid, serial#
    FROM   v$session
    WHERE  con_id = (SELECT con_id FROM v$pdbs WHERE name = 'PDB1')
    AND    status != 'INACTIVE'
    AND    username IS NOT NULL
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' 
                        || r.sid || ',' || r.serial# 
                        || ''' IMMEDIATE';
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('세션 종료 실패: ' || r.sid || ',' || r.serial#);
    END;
  END LOOP;
END;
/

해결책 2: PDB CLOSE 시 IMMEDIATE 옵션 사용

일반적인 CLOSE 명령 대신 IMMEDIATE 옵션을 사용하면 Oracle이 내부적으로 세션 종료를 처리합니다.

-- PDB를 즉시 닫기 (활성 트랜잭션 강제 롤백 포함)
ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;

-- 모든 인스턴스에서 닫기 (RAC 환경)
ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE INSTANCES=ALL;

-- 닫은 후 상태 확인
SELECT name, open_mode, restricted, con_id
FROM   v$pdbs
WHERE  name = 'PDB1';

해결책 3: In-Doubt 트랜잭션 처리

분산 트랜잭션이나 In-Doubt 트랜잭션이 원인인 경우 아래와 같이 처리합니다.

-- In-Doubt 트랜잭션 확인 (해당 PDB에 접속 후 실행)
ALTER SESSION SET CONTAINER = PDB1;

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_값';

-- 처리 후 정리
DELETE FROM dba_2pc_pending WHERE local_tran_id = 'local_tran_id_값';
COMMIT;

해결책 4: PDB 언플러그 전 체크 스크립트

-- PDB 언플러그 전 사전 점검 스크립트
DECLARE
  v_pdb_name VARCHAR2(30) := 'PDB1';
  v_con_id   NUMBER;
  v_count    NUMBER;
BEGIN
  SELECT con_id INTO v_con_id FROM v$pdbs WHERE name = v_pdb_name;

  SELECT COUNT(*) INTO v_count
  FROM   v$session s, v$transaction t
  WHERE  s.taddr = t.addr
  AND    s.con_id = v_con_id;

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('경고: ' || v_count || '개의 활성 트랜잭션이 존재합니다.');
    DBMS_OUTPUT.PUT_LINE('PDB 닫기 전에 트랜잭션을 종료하십시오.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('활성 트랜잭션 없음. PDB 닫기 안전.');
  END IF;
END;
/

---

예방 방법

1. PDB 유지보수 전 자동화된 사전 점검 스크립트 운영

PDB를 닫거나 언플러그하기 전에, 자동화된 사전 점검 스크립트를 Shell 또는 PL/SQL로 구성하여 활성 트랜잭션이 있으면 알림을 보내거나 작업을 자동으로 중단하도록 구성하는 것이 중요합니다. 아울러 Oracle Enterprise Manager(OEM) 또는 커스텀 모니터링 툴을 활용하여 PDB별 활성 세션과 트랜잭션을 실시간으로 모니터링하는 대시보드를 구축하면 운영 안정성을 크게 높일 수 있습니다.

-- 정기적으로 실행할 모니터링 쿼리 (예: 크론잡 또는 DBMS_SCHEDULER 활용)
SELECT p.name AS pdb_name, COUNT(*) AS active_txn_count
FROM   v$pdbs p, v$session s, v$transaction t
WHERE  p.con_id = s.con_id
AND    s.taddr  = t.addr
GROUP BY p.name
ORDER BY active_txn_count DESC;

2. 애플리케이션 레벨에서 트랜잭션 타임아웃 및 Connection Pool 관리 강화

애플리케이션 서버의 Connection Pool에서 유휴 커넥션에 대한 트랜잭션 타임아웃을 설정하고, 장시간 미완료 트랜잭션을 자동으로 롤백하는 정책을 적용해야 합니다. Oracle Database 레벨에서는 IDLE_TIME Profile 파라미터나 SQLNET.EXPIRE_TIME을 설정하여 유휴 세션을 자동으로 정리하고, PDB 유지보수 일정과 애플리케이션 배포 일정을 사전에 조율하여 트랜잭션 충돌을 예방하는 것이 Best Practice입니다.

-- 유휴 세션 자동 종료를 위한 Profile 설정
CREATE PROFILE pdb_app_profile LIMIT
  IDLE_TIME        30   -- 30분 유휴 시 자동 종료
  SESSIONS_PER_USER 100
  CONNECT_TIME      480; -- 8시간 최대 연결 시간

-- 사용자에게 Profile 적용
ALTER USER app_user PROFILE pdb_app_profile;

---

관련 에러

• ORA-65020: pluggable database string already closed — PDB가 이미 닫힌 상태에서 CLOSE 명령을 재실행할 때 발생합니다.
• ORA-65011: Pluggable database string does not exist — 존재하지 않는 PDB 이름을 지정할 때 발생합니다.
• ORA-01940: cannot drop a user that is currently connected — PDB 내에서 연결된 사용자를 삭제하려 할 때 발생하며, ORA-65023과 함께 나타나는 경우가 있습니다.
• ORA-02049: timeout: distributed transaction waiting for lock — 분산 트랜잭션 환경에서 잠금 대기 타임아웃 시 발생하며, In-Doubt 트랜잭션과 함께 ORA-65023을 유발할 수 있습니다.
• ORA-00603: ORACLE server session terminated by fatal error — 강제 세션 종료 과정에서 드물게 동반 발생할 수 있는 에러입니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.