Oracle - 세션 수집 프로시저 제작

DBA 업무를 수행하다 보니...

DB 폐기를 수행하기 전에 유입되는 세션이 있는지 확인요청하는 건이 생겼던 적이 있다.

DB접근제어도 없고, 그렇다고 audit을 걸기에는 디스크 공간이 넉넉치 않은 이런 악조건에서 세션 유입이 되는지 확인하려면 어떻게 해야 하는걸까?

 

바로, v$session에 있는 내용을 주기적으로 수집하면 간단하게 해결된다.

 

 

전체적인 프로세스는 다음과 같다.

 

1. 수집 테이블 생성

2. 세션 수집 프로시저 생성

3. 세션 수집 프로시저 수행 쉘 생성

4. crontab 등록

 

 

1. 수집 테이블 생성

CREATE TABLE "SYSTEM"."SESSION_STAT"      ( "SD" DATE DEFAULT sysdate,   "SADDR" VARCHAR2(4000),   "SID" VARCHAR2(4000),   "SERIAL#" VARCHAR2(4000),   "AUDSID" VARCHAR2(4000),   "PADDR" VARCHAR2(4000),   "USER#" VARCHAR2(4000),   "USERNAME" VARCHAR2(4000),   "COMMAND" VARCHAR2(4000),   "OWNERID" VARCHAR2(4000),   "TADDR" VARCHAR2(4000),   "LOCKWAIT" VARCHAR2(4000),   "STATUS" VARCHAR2(4000),   "SERVER" VARCHAR2(4000),   "SCHEMA#" VARCHAR2(4000),   "SCHEMANAME" VARCHAR2(4000),   "OSUSER" VARCHAR2(4000),   "PROCESS" VARCHAR2(4000),   "MACHINE" VARCHAR2(4000),   "TERMINAL" VARCHAR2(4000),   "PROGRAM" VARCHAR2(4000),   "TYPE" VARCHAR2(4000),   "SQL_ADDRESS" VARCHAR2(4000),   "SQL_HASH_VALUE" VARCHAR2(4000),   "SQL_ID" VARCHAR2(4000),   "SQL_CHILD_NUMBER" VARCHAR2(4000),   "PREV_SQL_ADDR" VARCHAR2(4000),   "PREV_HASH_VALUE" VARCHAR2(4000),   "PREV_SQL_ID" VARCHAR2(4000),   "PREV_CHILD_NUMBER" VARCHAR2(4000),   "PLSQL_ENTRY_OBJECT_ID" VARCHAR2(4000),   "PLSQL_ENTRY_SUBPROGRAM_ID" VARCHAR2(4000),   "PLSQL_OBJECT_ID" VARCHAR2(4000),   "PLSQL_SUBPROGRAM_ID" VARCHAR2(4000),   "MODULE" VARCHAR2(4000),   "MODULE_HASH" VARCHAR2(4000),   "ACTION" VARCHAR2(4000),   "ACTION_HASH" VARCHAR2(4000),   "CLIENT_INFO" VARCHAR2(4000),   "FIXED_TABLE_SEQUENCE" VARCHAR2(4000),   "ROW_WAIT_OBJ#" VARCHAR2(4000),   "ROW_WAIT_FILE#" VARCHAR2(4000),   "ROW_WAIT_BLOCK#" VARCHAR2(4000),   "ROW_WAIT_ROW#" VARCHAR2(4000),   "LOGON_TIME" VARCHAR2(4000),   "LAST_CALL_ET" VARCHAR2(4000),   "PDML_ENABLED" VARCHAR2(4000),   "FAILOVER_TYPE" VARCHAR2(4000),   "FAILOVER_METHOD" VARCHAR2(4000),   "FAILED_OVER" VARCHAR2(4000),   "RESOURCE_CONSUMER_GROUP" VARCHAR2(4000),   "PDML_STATUS" VARCHAR2(4000),   "PDDL_STATUS" VARCHAR2(4000),   "PQ_STATUS" VARCHAR2(4000),   "CURRENT_QUEUE_DURATION" VARCHAR2(4000),   "CLIENT_IDENTIFIER" VARCHAR2(4000),   "BLOCKING_SESSION_STATUS" VARCHAR2(4000),   "BLOCKING_INSTANCE" VARCHAR2(4000),   "BLOCKING_SESSION" VARCHAR2(4000),   "SEQ#" VARCHAR2(4000),   "EVENT#" VARCHAR2(4000),   "EVENT" VARCHAR2(4000),   "P1TEXT" VARCHAR2(4000),   "P1" VARCHAR2(4000),   "P1RAW" VARCHAR2(4000),   "P2TEXT" VARCHAR2(4000),   "P2" VARCHAR2(4000),   "P2RAW" VARCHAR2(4000),   "P3TEXT" VARCHAR2(4000),   "P3" VARCHAR2(4000),   "P3RAW" VARCHAR2(4000),   "WAIT_CLASS_ID" VARCHAR2(4000),   "WAIT_CLASS#" VARCHAR2(4000),   "WAIT_CLASS" VARCHAR2(4000),   "WAIT_TIME" VARCHAR2(4000),   "SECONDS_IN_WAIT" VARCHAR2(4000),   "STATE" VARCHAR2(4000),   "SERVICE_NAME" VARCHAR2(4000),   "SQL_TRACE" VARCHAR2(4000),   "SQL_TRACE_WAITS" VARCHAR2(4000),   "SQL_TRACE_BINDS" VARCHAR2(4000)     )   TABLESPACE AAA;

 

2. 세션 수집 프로시저 생성

v$session에 있는 내용을 session_stat이라는 테이블에 데이터 수집을 하되, sysdate를 함께 기록하게 하였다. 

그래야 해당세션이 언제쯤 유입되었는지 대략적으로 확인이 될테니 말이다.

create or replace PROCEDURE        SESSION_MON_PROC  IS  BEGIN  insert into system.session_stat  select sysdate,s.*  from v$session s  ;    COMMIT;    --      DBMS_OUTPUT.PUT_LINE (V_STR);  EXCEPTION  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');  WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE('OTHER ERROR : STOP ===> '||SQLERRM);  END;

 

 

3. 세션 수집 프로시저 수행 쉘 생성

해당 프로시저를 수행하는 sqlplus 수행 쉘을 생성하였다.

sqlplus system/***** << TOY exec SYSTEM.SESSION_MON_PROC  exit;  TOY

 

4. crontab 등록

사용량이 없는 DB이므로 매시에 한번씩 수집하게 crontab으로 스케줄링한다.

oracle scheduler job이나 job에 등록하여 수행할 수 있으나 crontab으로 등록하는 것이 아주 간단하므로 crontab으로 등록하였다.

# session monitor 00 * * * * /bin/su - oracle -c /home/oracle/dba/session_mon.sh   > /dev/null 2>&1

 

아주 간단하지 않는가? 사용량이 없는 DB라면 물려 있는 세션도 적기 때문에 데이터가 많이 쌓일 걱정을 할 필요 없다.

 

결국, 해당 데이터를 토대로 상대 팀 업무리더를 설득하였고, 해당DB를 중지 및 폐기처리를 할 수 있었다.

 

이렇게 간단한 배치 job을 작성할 수 있다면 업무에 많은 도움이 된다.

운영DBA일지라도 이런 PL/SQL을 알아두면 개발자와 소통하는데 도움이 되는 것을 알 수 있다.

 

좀 오래된 작업방법이지만 간만에 생각나서 기록해 보았다.