umzzil nng

Posted on May 27 • Originally published at oraerror.com

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

#oracle #database #dba #sql

ORA-00017 완벽 가이드: 세션 트레이스 이벤트 설정 요청 처리

ORA-00017란?

ORA-00017은 "session requested to set trace event" 라는 메시지와 함께 발생하는 에러로, 특정 세션이 트레이스 이벤트를 설정하도록 요청받았을 때 Oracle 내부적으로 발생하는 신호(signal) 성격의 에러입니다. 일반적인 애플리케이션 에러와는 다소 다른 성격을 가지며, 주로 DBA가 ALTER SYSTEM 또는 ORADEBUG 명령어를 통해 특정 세션에 트레이스를 걸거나 이벤트를 설정할 때 해당 세션이 이를 인지하고 처리하는 과정에서 나타납니다. 실무에서는 성능 진단, SQL 트레이스 활성화, 또는 내부 디버깅 과정에서 의도적으로 또는 비의도적으로 마주칠 수 있는 에러이며, 대부분의 경우 Oracle 엔진이 내부적으로 처리하므로 실제 업무에 치명적인 영향을 주지는 않지만, 트레이스 파일이 과도하게 생성되거나 세션 성능에 영향을 미치는 경우가 있어 정확한 이해가 필요합니다.

주요 발생 원인

1. DBA의 명시적 트레이스 이벤트 설정 (ORADEBUG / ALTER SESSION)

가장 빈번한 원인으로, DBA가 특정 세션의 성능 문제를 진단하기 위해 ORADEBUG 명령이나 ALTER SYSTEM SET EVENTS 구문을 사용할 때 발생합니다. 이때 대상 세션은 Oracle 내부 메커니즘을 통해 트레이스 요청을 수신하게 되며, 해당 세션의 다음 Oracle call 시점에 ORA-00017이 내부적으로 발생하고 트레이스가 활성화됩니다. 이 과정은 정상적인 동작이나, 애플리케이션이 예외 처리를 잘못 구현한 경우 해당 에러가 애플리케이션 레벨까지 노출될 수 있습니다.

2. SQL*Net 또는 애플리케이션 연결 과정의 비정상 이벤트 처리

Oracle Net(SQL*Net)을 통한 원격 접속 과정에서 트레이스 이벤트 신호가 제대로 처리되지 않을 경우 해당 에러가 클라이언트 애플리케이션에 노출되기도 합니다. 특히 Connection Pooling 환경이나 미들웨어(WAS)를 통한 연결에서 Oracle 내부 신호가 풀(Pool)에 의해 잘못 해석되거나 재전송되는 경우, 애플리케이션 로그에 ORA-00017이 기록되는 사례가 실무에서 종종 확인됩니다. 이 경우 Oracle 자체의 문제보다는 연결 관리 로직의 예외 처리 미흡이 원인인 경우가 많습니다.

3. 자동화된 모니터링 툴 또는 스크립트의 과도한 트레이스 활성화

엔터프라이즈 환경에서 사용하는 APM(Application Performance Monitoring) 툴이나 DBA가 작성한 자동화 스크립트가 주기적으로 세션 트레이스를 ON/OFF 하는 과정에서 ORA-00017이 반복 발생할 수 있습니다. 특히 10046 이벤트(SQL Trace)나 10053 이벤트(CBO 트레이스)를 자동으로 걸고 해제하는 스크립트가 다수의 세션에 동시에 적용될 때, 시스템 부하가 증가하고 udump 또는 diag 경로에 트레이스 파일이 폭증하는 현상이 발생합니다. 이는 디스크 공간 부족으로 이어질 수 있어 모니터링이 필요합니다.

해결 방법

원인 1 해결: 명시적 트레이스 이벤트 해제

DBA가 의도적으로 설정한 트레이스를 해제하려면 아래와 같이 진행합니다.

현재 활성화된 트레이스 세션 확인:

-- 현재 트레이스가 활성화된 세션 조회
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_trace,
       s.program,
       s.machine
FROM   v$session s
WHERE  s.sql_trace = 'ENABLED'
   OR  s.status    = 'ACTIVE';

특정 세션의 트레이스 비활성화 (SID, SERIAL# 기반):

-- 특정 세션의 SQL Trace 비활성화
-- SID=142, SERIAL#=4321 인 세션 대상
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid => 142, serial# => 4321, sql_trace => FALSE);

ORADEBUG를 이용한 트레이스 이벤트 해제:

-- ORADEBUG를 이용하여 특정 OS 프로세스의 이벤트 해제
-- SQL*Plus에서 SYSDBA 권한으로 실행
ORADEBUG SETORAPID 12345;           -- Oracle PID 설정
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;  -- 10046 이벤트 해제
ORADEBUG EVENT 10053 TRACE NAME CONTEXT OFF;  -- 10053 이벤트 해제

ALTER SYSTEM을 이용한 세션 이벤트 설정 및 해제:

-- 전체 시스템에 대한 10046 레벨 12 트레이스 활성화 (진단 목적)
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- 진단 완료 후 반드시 해제
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';

-- 특정 세션에만 SQL Trace 활성화 (부하 최소화)
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

-- 해당 세션에서 트레이스 해제
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

원인 2 해결: 애플리케이션 예외 처리 보완

ORA-00017이 애플리케이션까지 전파되지 않도록 예외 처리를 보완해야 합니다. PL/SQL 내에서 이 에러를 명시적으로 처리하는 방법은 다음과 같습니다.

-- PL/SQL 내 ORA-00017 예외 처리 예제
DECLARE
   v_result NUMBER;
   e_trace_event EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_trace_event, -17);  -- ORA-00017
BEGIN
   -- 비즈니스 로직 수행
   SELECT COUNT(*) INTO v_result FROM emp;
   DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

EXCEPTION
   WHEN e_trace_event THEN
      -- ORA-00017은 내부 신호이므로 로깅 후 정상 처리
      DBMS_OUTPUT.PUT_LINE('INFO: Trace event signal received. Continuing normally.');
      -- 필요시 재시도 로직 또는 무시 처리
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
      RAISE;
END;
/

-- 세션 단위 트레이스 상태 확인 쿼리
SELECT sid,
       serial#,
       username,
       sql_trace,
       sql_trace_waits,
       sql_trace_binds
FROM   v$session
WHERE  audsid = USERENV('SESSIONID');

원인 3 해결: 트레이스 파일 관리 및 모니터링 스크립트 정비

트레이스 파일이 폭증하는 경우, 파일 크기 제한 및 자동 정리 설정이 필요합니다.

-- 트레이스 파일 크기 제한 설정 (단위: OS block 수)
-- init.ora 또는 spfile에서 설정
ALTER SYSTEM SET MAX_DUMP_FILE_SIZE = '500M' SCOPE=BOTH;

-- 현재 트레이스 파일 경로 확인
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';
SELECT value FROM v$parameter WHERE name = 'tracefile_identifier';

-- ADR(Automatic Diagnostic Repository) 정책 확인
-- adrci 툴에서 실행
-- SHOW CONTROL
-- SET CONTROL (SHORTP_POLICY = 720)   -- 단기 보관: 720시간(30일)
-- SET CONTROL (LONGP_POLICY = 8760)   -- 장기 보관: 8760시간(1년)

-- 현재 열린 트레이스 파일 수 모니터링
SELECT count(*) AS trace_file_count
FROM   v$diag_trace_file
WHERE  adr_home LIKE '%rdbms%';

-- 과도한 트레이스를 유발하는 세션 식별
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       s.machine,
       s.sql_trace,
       se.value AS physical_reads
FROM   v$session s
JOIN   v$sesstat se ON s.sid = se.sid
JOIN   v$statname sn ON se.statistic# = sn.statistic#
WHERE  sn.name     = 'physical reads'
  AND  s.sql_trace = 'ENABLED'
ORDER BY se.value DESC;

예방 방법

1. 트레이스 이벤트 설정 표준 프로세스 수립 및 반드시 해제 확인

실무에서 가장 흔한 실수는 진단을 위해 트레이스를 활성화한 후 해제를 잊어버리는 것입니다. 이를 방지하기 위해 트레이스 활성화 시 반드시 해제 시간을 스케줄에 등록하고, 아래와 같이 DBMS_SCHEDULER를 활용한 자동 해제 잡(Job)을 함께 등록하는 습관을 들여야 합니다.

-- 트레이스 자동 해제 스케줄러 잡 등록 예시
-- 트레이스 활성화 후 30분 뒤 자동 해제
BEGIN
   DBMS_SCHEDULER.CREATE_JOB(
      job_name        => 'AUTO_DISABLE_TRACE_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => q'[
         BEGIN
            -- 특정 세션 트레이스 해제
            DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(
               sid      => 142,
               serial#  => 4321,
               sql_trace => FALSE
            );
            DBMS_OUTPUT.PUT_LINE('Trace disabled at: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
         END;
      ]',
      start_date      => SYSTIMESTAMP + INTERVAL '30' MINUTE,
      enabled         => TRUE,
      auto_drop        => TRUE,
      comments        => 'Auto disable trace event after 30 minutes'
   );
   DBMS_OUTPUT.PUT_LINE('Auto-disable job registered successfully.');
END;
/

또한, 정기적으로 트레이스가 활성화된 세션을 점검하는 일일 점검 스크립트를 crontab 또는 DBMS_SCHEDULER에 등록하여 운영하는 것을 권장합니다.

-- 일일 트레이스 활성 세션 점검 스크립트
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS check_time,
       s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.machine,
       s.program,
       s.sql_trace,
       s.logon_time
FROM   v$session s
WHERE  s.sql_trace = 'ENABLED'
  AND  s.type      = 'USER';

2. ADR 및 트레이스 파일 용량 임계치 모니터링 자동화

트레이스 파일이 누적되어 디스크 풀(Full)이 발생하는 것은 데이터베이스 운영에 치명적인 영향을 미칩니다. 따라서 diagnostic_dest 경로의 디스크 사용량을 주기적으로 모니터링하고, 임계치(예: 80%) 초과 시 알람이 발생하도록 설정해야 합니다. 아울러 Oracle 12c 이상에서는 MAX_DUMP_FILE_SIZE 파라미터와 ADR의 SHORTP_POLICY/LONGP_POLICY를 적절히 설정하여 오래된 트레이스 파일이 자동으로 정리되도록 구성하고, 정기적으로 adrci 명령을 통해 수동 정리를 병행하는 것이 Best Practice입니다.

-- 트레이스 관련 주요 파라미터 현황 일괄 확인
SELECT name, value, description
FROM   v$parameter
WHERE  name IN (
   'diagnostic_dest',
   'max_dump_file_size',
   'tracefile_identifier',
   'sql_trace',
   'timed_statistics',
   'timed_os_statistics'
)
ORDER BY name;

에러 코드	설명
ORA-00018	maximum number of sessions exceeded - 세션 수 초과로 트레이스 세션 추가 불가 시 연관 발생
ORA-00031	session marked for kill - 트레이스 설정 중 세션 종료 요청 시 연관
ORA-01031	insufficient privileges - ORADEBUG 또는 DBMS_SYSTEM 실행 권한 부족 시 발생
ORA-04031	unable to allocate shared memory - 트레이스 이벤트로 인한 메모리 할당 실패 시 연관
ORA-00600	internal error code - 트레이스 이벤트 설정 중 Oracle 내부 버그 발생 시 동반

DEV Community