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

ORA-12153
2026년 09월 07일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-12153 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-12153 TNS: not connected 는?

ORA-12153 에러는 Oracle Net Services(TNS)를 통해 데이터베이스에 연결을 시도했으나, 실제로 연결이 수립되지 않은 상태에서 데이터베이스 작업을 실행하려 할 때 발생하는 에러입니다. 쉽게 말해, 클라이언트와 Oracle 서버 간의 네트워크 세션이 끊어진 상태이거나 아예 연결이 이루어지지 않은 채 SQL 또는 PL/SQL 명령을 수행하려는 상황에서 나타납니다. 이 에러는 주로 애플리케이션 서버, JDBC/ODBC 드라이버, SQL*Plus, Toad 등 다양한 클라이언트 환경에서 발생하며, 네트워크 불안정, 세션 타임아웃, 잘못된 TNS 설정 등 다양한 원인에 의해 유발됩니다.

주요 발생 원인

1. 네트워크 연결 단절 또는 세션 타임아웃

가장 흔한 원인은 클라이언트와 Oracle 서버 사이의 네트워크 연결이 비정상적으로 종료된 경우입니다. 방화벽(Firewall)이 일정 시간 동안 유휴 상태(Idle)인 TCP 세션을 강제로 끊거나, 네트워크 장비의 세션 타임아웃 설정으로 인해 기존에 맺어진 DB 연결이 클라이언트 모르게 종료되는 경우가 많습니다. 특히 커넥션 풀(Connection Pool)을 사용하는 WAS(Web Application Server) 환경에서는 풀에 보관 중인 유효하지 않은 커넥션을 재사용하려 할 때 이 에러가 빈번하게 발생합니다.

2. 잘못된 TNS 설정 또는 리스너(Listener) 문제

tnsnames.ora 파일에 HOST, PORT, SERVICE_NAME 등이 잘못 설정되어 있으면 초기 연결 자체가 실패하거나 연결이 불안정해질 수 있습니다. Oracle 리스너가 중지되어 있거나 응답하지 않는 경우, 클라이언트가 연결 요청을 보내도 정상적인 응답을 받지 못해 ORA-12153이 발생합니다. 또한 sqlnet.ora의 SQLNET.EXPIRE_TIME 파라미터가 올바르게 설정되지 않은 경우에도 세션 유지에 문제가 생길 수 있습니다.

3. Oracle 서버 측 리소스 부족 또는 프로세스 한계 초과

Oracle 인스턴스의 PROCESSES 또는 SESSIONS 파라미터 한계에 도달하면 새로운 연결 요청이 거부되고, 기존 세션도 불안정해질 수 있습니다. 서버 측 메모리 부족이나 OS 레벨의 파일 디스크립터(File Descriptor) 한계 초과도 TNS 연결 실패를 유발합니다. DBA는 이러한 상황을 사전에 모니터링하여 임계치 도달 전에 조치를 취해야 합니다.

해결 방법

원인 1: 네트워크 연결 단절 / 세션 타임아웃 해결

현재 유효하지 않은 세션 확인 및 정리:

-- 현재 데이터베이스 세션 상태 확인
SELECT sid,
       serial#,
       username,
       status,
       machine,
       program,
       last_call_et AS idle_seconds
FROM   v$session
WHERE  type = 'USER'
ORDER  BY idle_seconds DESC;

-- 특정 유휴 세션 강제 종료 (sid, serial# 값을 조회 결과로 대체)
ALTER SYSTEM KILL SESSION '145,2381' IMMEDIATE;

sqlnet.ora에 Dead Connection Detection(DCD) 설정 추가:

-- $ORACLE_HOME/network/admin/sqlnet.ora 파일에 아래 설정 추가
-- (SQL이 아닌 설정 파일이지만 참고용으로 제시)
-- SQLNET.EXPIRE_TIME = 10  (단위: 분, 10분마다 probe 패킷 전송)

커넥션 풀에서 유효하지 않은 커넥션 감지 쿼리:

-- 커넥션 풀 유효성 검증에 사용하는 표준 쿼리 (Validation Query)
SELECT 1 FROM dual;

-- 연결 상태 확인용 핑 쿼리
SELECT SYSDATE FROM dual;

원인 2: TNS 설정 및 리스너 문제 해결

리스너 상태 확인 (OS 명령어):

-- lsnrctl 명령으로 리스너 상태 점검
-- OS 커맨드이나 DBA가 반드시 숙지해야 할 점검 절차
-- $ lsnrctl status
-- $ lsnrctl start
-- $ lsnrctl reload

TNS 연결 테스트용 SQL*Plus 접속 테스트:

-- tnsnames.ora의 ORCL 항목으로 연결 테스트
-- $ sqlplus scott/tiger@ORCL

-- 연결 후 현재 연결된 인스턴스 정보 확인
SELECT instance_name,
       host_name,
       version,
       status
FROM   v$instance;

-- TNS 서비스 이름 확인
SELECT name,
       network_name
FROM   v$active_services
ORDER  BY name;

tnsnames.ora 정상 설정 예시:

-- $ORACLE_HOME/network/admin/tnsnames.ora 올바른 형식 예시
/*
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-server.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.example.com)
    )
  )
*/

-- 현재 DB의 서비스 이름 확인 (tnsnames.ora 설정값과 일치해야 함)
SELECT value
FROM   v$parameter
WHERE  name = 'service_names';

원인 3: 서버 리소스 부족 해결

현재 프로세스 및 세션 사용량 확인:

-- PROCESSES 파라미터 한계 대비 현재 사용량 확인
SELECT resource_name,
       current_utilization,
       max_utilization,
       limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions', 'enqueue_locks');

-- 현재 설정된 PROCESSES, SESSIONS 파라미터 값 확인
SELECT name,
       value,
       description
FROM   v$parameter
WHERE  name IN ('processes', 'sessions', 'transactions');

파라미터 조정 (SPFILE 사용 환경):

-- PROCESSES 파라미터 값 증가 (재시작 필요)
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;

-- SESSIONS는 PROCESSES 값에 의해 자동 계산되지만 명시적 설정도 가능
-- sessions = (1.1 * processes) + 5 공식 적용
ALTER SYSTEM SET sessions = 555 SCOPE = SPFILE;

-- 변경사항 적용을 위한 DB 재시작 필요
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- 재시작 후 파라미터 확인
SELECT name, value
FROM   v$parameter
WHERE  name IN ('processes', 'sessions');

장시간 유휴 세션 일괄 정리 스크립트:

-- 1시간 이상 유휴 상태인 USER 세션 Kill 스크립트 생성
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
       AS kill_command
FROM   v$session
WHERE  type       = 'USER'
AND    status     = 'INACTIVE'
AND    last_call_et > 3600  -- 3600초 = 1시간
AND    username IS NOT NULL;

예방 방법

1. Dead Connection Detection(DCD) 및 커넥션 풀 유효성 검증 설정

sqlnet.ora 파일에 SQLNET.EXPIRE_TIME 값을 10분 내외로 설정하여 Oracle 서버가 주기적으로 클라이언트 연결의 생존 여부를 확인하도록 합니다. WAS나 미들웨어 환경에서는 커넥션 풀의 testOnBorrow, validationQuery 옵션을 반드시 활성화하여, 풀에서 커넥션을 꺼낼 때마다 SELECT 1 FROM dual 같은 검증 쿼리로 유효한 연결인지 확인 후 사용하도록 구성합니다. 이 두 가지 설정만으로도 실제 운영 환경에서 발생하는 ORA-12153의 상당수를 사전에 차단할 수 있습니다.

2. 주기적인 세션 및 리소스 모니터링 자동화

아래와 같이 v$resource_limit과 v$session을 주기적으로 조회하는 모니터링 스크립트를 작성하고, DBMS_SCHEDULER 또는 외부 모니터링 도구(OEM, Zabbix, Grafana 등)와 연동하여 임계치 초과 시 자동 알림을 받도록 설정합니다.

-- DBMS_SCHEDULER를 활용한 주기적 리소스 모니터링 잡 생성 예시
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'JOB_MONITOR_RESOURCE_LIMIT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
      DECLARE
        v_current NUMBER;
        v_limit   NUMBER;
      BEGIN
        SELECT current_utilization, TO_NUMBER(limit_value)
        INTO   v_current, v_limit
        FROM   v$resource_limit
        WHERE  resource_name = ''processes'';

        -- 80% 이상 사용 시 DBA_ALERT 테이블에 기록 (커스텀 테이블)
        IF v_current > v_limit * 0.8 THEN
          INSERT INTO dba_custom_alert(alert_time, alert_msg)
          VALUES (SYSDATE, ''PROCESSES 사용량 80% 초과: '' || v_current || ''/'' || v_limit);
          COMMIT;
        END IF;
      END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
    enabled         => TRUE,
    comments        => 'Oracle 프로세스 리소스 사용량 모니터링'
  );
END;
/