본문 바로가기

카테고리 없음

ORACLE TRACE USER USING TRIGGER v1.0

ORACLE TRACE USER USING TRIGGER 

 

 

1.  Trace Trigger

A.   TRACE 방법은 AUTOTRACE SQL_TRACE, ORADEBUG, SET EVENTS 를 사용한

방법이 있다.

B.   TRIGGER PL SQL 블록의 실행시점을 암시적(자동)으로 정의할 수 있다.

C.   이번 글에서는 TRACE TRIGGER 를 같이 사용하여 DB에 접근한 세션에 대하여

자동으로 트레이스를 수행하고 이후 트레이스 정보와 대기이벤트 정보등을 조회하는 

방법에 대해 이야기 하겠다.

2.  생성구문

SQL> create or replace trigger logon_trace_on

  2  after logon on database

  3  begin

  4     execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

  5  end;

6  /

 

Trigger created.

 

SQL> create or replace trigger logon_trace_off

  2  before logoff on database

  3  begin

  4     execute immediate 'alter session set events ''10046 trace name context off''';

  5  end;

  6  /

 

Trigger created.

        

접근 세션에 대해 트레이스 하기 위해 사용한 실행 시점은 after logon on database before logoff on database 이다.전자의 경우는 커넥션을 맺고난 후 실행하란 의미이며 후자는 logoff 하기전에 실행하란 의미이다. PL/SQL 블록 부분엔 DDL 을 사용하기에 DYNAMIC SQL 을 사용하였다. trace 개시 후 off 하지 않는 경우 시스템에 심각한 부하를 초래할 수 있다.

 

3.  결과의 활용

A.   결과는 user_dump_dest <instance_name>_ora_<spid>.trc 로 생성된다. spid v$process spid 를 말한다.spid os process id 를 의미한다. 이를 사용하면 시스템 레벨에서 session kill 할 수도 있다. 현재 세션의 spid 를 조회하는 쿼리는 아래와 같다.

 

SQL> select spid from v$process

  2  where addr = (                 

  3  select paddr from v$session

  4  where sid in (select distinct sid from v$mystat));

 

SPID

------------

5722

 

v$process 에는 sid 정보가 없기에 inline view 를 사용해 조회하였다.

위 SPID를 근거로 user_dump_dest 에 생성된 파일을 tkprof 명령어를 사용해 정리해 보면 된다.

사용법은 tkprof 원본파일 새로운파일 로 입력하면된다. 

해당 프로세스를 os 레벨에서 kill 하려면 아래와 같은 과정을 밟으면 된다.

<<OS>>

RH3@PROD : /home/oracle>$ps -ef | grep 5722

oracle    5722  5721  0 19:17 ?        00:00:00 oraclePROD (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    5757  4392  0 19:22 pts/3    00:00:00 grep 5722

RH3@PROD : /home/oracle>$kill -9 5722

 

<<ORACLE>>

SQL> select spid from v$process

  2  where addr = (                 

  3  select paddr from v$session

  4  where sid in (select distinct sid from v$mystat));

 

SPID

------------

5722

 

SQL>

~~~ OS 단에서 KILLED ~~~

SQL> select * from tab;

select * from tab

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

 

 

SQL> desc tab

ERROR:

ORA-03114: not connected to ORACLE

 

 

SQL>