ORACLE LogMiner

Oracle LogMiner는
Redo Log를 사람이 읽을 수 있는 SQL 형태로 해석해주는 도구.

 

DB에서 무슨 데이터가, 언제, 어떻게 바뀌었는지를 테이블 데이터 기준으로 추적할 때 쓰는 정식 Oracle 기능이다.

LogMiner

Oracle DB는 모든 변경사항을 Redo Log에 기록함.

Redo Log는 로그 스위치가 일어나면 Archive Log로 저장함(아카이빙 모드시).

로그마이너로 추적 범위로 판단하여 필요한 Redo Log / Archived Log 를 통해 변경내용을 SQL 형태로 제공한다.

 

추출 SQL

@ [YYYYMMDD HH24:MI 형식의 추적시작 일시] : 추적을 시작할 시간 예 : 20260211 10:00 

@ [YYYYMMDD HH24:MI 형식의 추적종료 일시] : 추적을 종료할 시간 예 : 20260211 15:00 

@ #thread = ??  :  RAC DB 인 경우 REDO 로그와 ARCHIVE 로그는 노드별 따로 관리하기에

     현재 접속 인스턴스에 맞게 조회해줘야한다. 예 1노드일경우 #thread=1

select 'alter session set db_file_multiblock_read_count=256; ' RUN_SQL from dual union all
select 'alter session set sort_area_size=200000000;' RUN_SQL from dual union all
select 'alter session set sort_area_retained_size=200000000;' RUN_SQL from dual union all
select 'alter session set workarea_size_policy=MANUAL;' RUN_SQL from dual union all
select 'alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss'';' RUN_SQL
from dual
UNION ALL
select 'EXEC sys.dbms_logmnr.add_logfile(logfilename=>''' || name || decode(ROWNUM , 1, ''',options=>sys.dbms_logmnr.NEW);',''',options=>sys.dbms_logmnr.ADDFILE);') RUN_SQL
from
(
select * from
(
  select * from 
  (
    select thread#,b.first_time,nvl(b.next_time,TO_DATE('40000101','YYYYMMDD')) next_time,b.archived,b.status,a.member name
    from   v$logfile a, v$log b
    where  a.group# = b.group#
    and    b.status <> 'UNUSED'
    and    (a.status is null or a.status = 'STALE')
    and    a.type = 'ONLINE'
    and    b.archived <> 'YES'
    union all
    select c.thread#,c.first_time,nvl(c.next_time,TO_DATE('40000101','YYYYMMDD')) next_time,c.archived,c.status,c.name  
    from v$archived_log c where standby_dest = 'NO' 
  ) V1
  where first_time <= TO_DATE('[YYYYMMDD HH24:MI 형식의 추적종료 일시]','YYYYMMDD HH24:MI') and thread# =1  -- RAC인 경우엔 1노드 2노드 ... 각각 뽑아야한다
)
where next_time > TO_DATE('[YYYYMMDD HH24:MI 형식의 추적시작 일시]','YYYYMMDD HH24:MI') 
order by 2 desc,3,1
)
UNION ALL
select 'EXEC sys.dbms_logmnr.start_logmnr(starttime=>to_date(''[YYYYMMDD HH24:MI 형식의 추적시작 일시]'',''YYYYMMDD HH24:MI''), endtime => to_date(''[YYYYMMDD HH24:MI 형식의 추적종료 일시]'',''YYYYMMDD HH24:MI''),options=>sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);' RUN_SQL
from dual
union all
select 'create table [스키마].[데이터를 저장할 임시테이블] nologging parallel 8 as select SESSION#, SERIAL#, XIDUSN, XIDSLT, XIDSQN, XID, scn, timestamp, sql_redo, sql_undo, username,os_username,machine_name from v$logmnr_contents where SEG_OWNER = ''[작업 대상 스키마]'' and TABLE_NAME = ''[작업 대상 테이블]'' order by scn;' RUN_SQL
from dual
union all
select 'EXEC sys.dbms_logmnr.end_logmnr;'
from dual;