오라클 ORA-00018: 최대 세션 수를 초과했습니다

최근 이직한 회사에서 서버 이전 진행 하면서

SCOUTER 라는 오픈소스 APM툴을 톰캣에 붙이고

alert를 커스텀하여 모니터링과 이메일로 에러등을 받는 것을 셋팅하였다 👩‍💻

ORA-00018: 최대 세션 수를 초과했습니다 이메일

그리고 위와 같은 에러메세지를 이메일로 받았다

근본적인 원인은 자체프레임워크 사용중인데 DBCP 관련해서 문제가 있는거 같았다 😵

TCP포트도 많이 물고 있다

(뜯어보는중이고, 사실 다른 문제들도 많아서 쉽지는 않겠지만 스프링으로 넘길 생각이다)

 

어쨋든 당장 주어진 상황에서 최적의 방법을 생각해야 좋은 개발자 아니겠습니까

에러부터 해결해봅시다 😀

 

 

0. 1번의 쿼리 조회시 ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

    이런 문구가 나온다면 권한이 없을 가능성이 90퍼센트입니다.

    sys계정으로 접속해서 테이블 뷰 권한을 열어줘야 합니다.

GRANT SELECT ON V_$SESSION TO GREENMRO;
-- GREENMRO는 사용자 계정입니다

v$session 테이블 SELECT 권한부여

 

1. 세션부터 확인해봅시다.

-- SESSION INACTIVE 쿼리
SELECT A.SID,
       A.SERIAL#,
       A.PROCESS,
       A.USERNAME,
       A.OSUSER,
       B.SQL_TEXT,
       C.PROGRAM,
       A.LOGON_TIME
  FROM v$session A
  	   LEFT OUTER JOIN v$sqlarea B
  	   		ON A.SQL_HASH_VALUE = B.HASH_VALUE
  	   	   AND A.SQL_ADDRESS = B.ADDRESS
	   LEFT OUTER JOIN v$process C
	   		ON A.PADDR = C.ADDR
 WHERE A.status='INACTIVE'
   AND A.username NOT IN ('SYS')
 ORDER BY A.LOGON_TIME ASC;

INACTIVE SESSION

문제 발생했을 당시가 9월인데, LOGON_TIME이 6월인 것도 있었다 😨

좀비세션들이 매우 많았다. 이런경우가 꽤 있었을텐데 매번 재기동을 했지 않을까 싶다.....

 

고민하다가 우선 KILL을 해주는 SP를 만들고 oracle JOB으로 일주일마다 돌리기로 했다

시작해보자 ✍

 

 

2. KILL을 해주는 쿼리부터 만들어보자

    SESSION KILL을 해줄려면 SID와 SERIAL#만 있으면 된다

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || '''' AS SESSION_KILL
  FROM V$SESSION
 WHERE status ='INACTIVE'
   AND username NOT IN ('SYS')
   AND LOGON_TIME < TRUNC(SYSDATE) - 7;

쿼리 실행

이런 결과가 나올꺼고 SP에서 쿼리 결과물들을 EXEC 시켜주면 된다

 

 

3. 프로시저 작성

create PROCEDURE SP_SESSION_KILL AS
BEGIN
    FOR CUR IN (
        SELECT
            'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''''
                AS SESSION_KILL
            FROM V$SESSION
           WHERE status ='INACTIVE'
             AND username NOT IN ('SYS')
             AND LOGON_TIME < TRUNC(SYSDATE) - 7
    )
    LOOP
        BEGIN
            EXECUTE IMMEDIATE CUR.SESSION_KILL;
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END LOOP;
END;

 

4. 오라클 스케줄러 잡 등록

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name => 'SP_SESSION_KILL_JOB',
        comments => '매주 12시 30분에 좀비세션을 죽이는 job',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN SP_SESSION_KILL; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'freq=weekly ; byhour=12;byminute=30;bysecond=0;',
        enabled => TRUE
    );
END;

 

5. 끝 -> 지속적인 모니터링