Log miner
Log miner
-> redo log file 의 내용을 sql 문장으로 변환해 사용자가 sql 문장으로 조회해서 원하는 내용을 확인하는 것
log miner 조회 순서
-> 1. log miner 전용 Dictionary 생성
2. log miner 로 분석할 로그 보냄 (redo log buffer, archive log) = 등록
3. log miner 에서 분석
4. sql 로 분석 결과 조회
supplemental logging 기능
-> 데이터에 DML이 발생할 경우 redo log 에 추가적인 내용을 기록 ( 기본값 보다 자세한 내용 기록 )
- 최소 기록 (disable) : 기본 모드
- 자세히 기록 (enable)
supplemental logging 활성화 여부 조회
SYS> select supplemental_log_data_min from v$database ;
SUPPLEMENTAL_LOG
-----------------
NO <- 기본값
활성화 시키기
SYS> alter database add supplemental log data ;
SYS> select supplemental_log_data_min from v$database ;
SUPPLEMENTAL_LOG
-----------------
YES
비활성화 시키기
SYS> alter database drop supplemental log data ;
SYS> select supplemental_log_data_min from v$database ;
SUPPLEMENTAL_LOG
-----------------
NO
특정 테이블만 supplemental log 적용
SYS> alter table scott.emp add supplemental log data (all) columns ;
-------------------------------------------------------------------------------------------------------------------------------
Log Miner 실습하기 1. - drop table 정보 찾기
-------------------------------------------------------------------------------------------------------------------------------
step 1. 현재 상태 확인
SYS> @log
GROUP# MEMBER MB seq STATUS arc
------ --------------------------------------------------------
1 /data/temp3/redo01.log 5 1 CURRENT NO
2 /data/temp3/redo02.log 5 0 UNUSED YES
3 /data/temp3/redo03.log 5 0 UNUSED YES
SYS> select supplemental_log_data_min from v$database ;
SUPPLEMENTAL_LOG
-----------------
YES <-- 활성화 시키고 실습 시작
step 2. 새로운 테이블 생성 후 데이터 입력하고 삭제
SQL> create table scott.test1 (no number);
Table created.
SQL> insert into scott.test1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table scott.test1 purge;
Table dropped.
SQL> @log
6 ;
GROUP# MEMBER MB seq STATUS arc
------ --------------------------------------------------------
1 /data/temp3/redo01.log 5 1 CURRENT NO <-- 1번 그룹에서 진행 중
2 /data/temp3/redo02.log 5 0 UNUSED YES
3 /data/temp3/redo03.log 5 0 UNUSED YES
step 3. Log Miner 딕셔너리를 생성
-> log miner 가 redo log file 을 분석해 그 안에 있는 테이블 이름이나 컬럼 이름들이 있는 부분을 딕셔너리를 확인해서 사용자가 볼 수 있도록 변환해줌
( 여기서 딕셔너리는 SQL Parse 단계에서 사용하는 딕셔너리가 아니라 Log Miner 가 사용하는 별도의 딕셔너리를 말함 )
딕셔너리 생성방법 3가지
1. Online Catalog 사용
2. Redo Log File 자체에 저장하는 방법
3. Flat file 에 저장하는 방법
※ 1,2 의 방법은 잘 사용 안함
1) DB 전체를 종료한 후 Parameter file 에 딕셔너리 파일의 위치를 아래와 같이 지정
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@localhost ~]$ vi /app/oracle/product/11g/dbs/inittestdb.ora
utl_file_dir=/app/oracle/logminer
2) DB 시작
[oracle@localhost ~]$ exit
exit
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 293604248 bytes
Database Buffers 121634816 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
3) 아래와 같이 딕셔너리 생성
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict.dat',-
> dictionary_location=>'/app/oracle/logminer');
PL/SQL procedure successfully completed.
4) Log Miner 에 분석할 로그 추가
SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo01.log',1);
-- 위 명령어 마지막 숫자의 의미는 1= 신규등록 2= 파일삭제 3= 추가등록
PL/SQL procedure successfully completed.
2,3 번 그룹을 추가 등록
SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo02.log',3);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo03.log',3);
BEGIN dbms_logmnr.add_logfile('/data/temp3/redo03.log',3); END;
*
ERROR at line 1:
ORA-01289: cannot add duplicate logfile /data/temp3/redo03.log
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
3번 그룹을 추가할 때 위와 같은 에러가 나면 로그파일이 UNUSED 상태라서 그럼
alter system switch logfile 과 alter system checkpoint 를 실행시켜 Inactive 와 current 상태로 만들어주자
SQL> alter system switch logfile ;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL> @log
6 ;
GROUP# MEMBER MB seq STATUS arc
------ --------------------------------------------- ---- -----
1 /data/temp3/redo01.log 5 4 CURRENT NO
2 /data/temp3/redo02.log 5 2 INACTIVE YES
3 /data/temp3/redo03.log 5 3 INACTIVE YES
위의 에러를 해결하고 redo log file 을 다시 등록해주고 log miner 에 등록된 log file을 확인
SQL> select db_name,filename from v$logmnr_logs;
DB_NAME FILENAME
--------------- --------------------
TESTDB /data/temp3/redo01.log
TESTDB /data/temp3/redo02.log
TESTDB /data/temp3/redo03.log
5) Log Miner 를 시작해서 log 를 분석한 후 결과를 조회
SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/app/oracle/logminer/dict.dat',-
> options=>dbms_logmnr.ddl_dict_tracking+dbms_logmnr.committed_data_only);
PL/SQL procedure successfully completed.
SQL> col username for a10
SQL> col operation for a10
SQL> col sql_redo for a50
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;
Session altered.
-------------------------------------------------------------------------------------------------------------------------------
Log Miner 실습하기 2 - Update table 시간 확인하기
-----------------------------------------------------------------------------------------------------------------------------
step1. 현재 상태 조회하기
SQL> @log
6 ;
GROUP# MEMBER MB seq STATUS arc
------ --------------------------------------------- -----
1 /data/temp3/redo01.log 5 19 INACTIVE YES
2 /data/temp3/redo02.log 5 20 INACTIVE YES
3 /data/temp3/redo03.log 5 21 CURRENT NO <-- redo03 에서 작업
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
step2. 신규 테이블 생성 후 데이터 입력하고 update
SQL> create table scott.test2(no number,name varchar2(10));
Table created.
SQL> insert into scott.test2 values(1,'aaa');
1 row created.
SQL> insert into scott.test2 values(2,'bbb');
1 row created.
SQL> insert into scott.test2 values(3,'ccc');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.test2;
NO NAME
---------- ----------
1 aaa
2 bbb
3 ccc
SQL> update scott.test2 set name='ddd';
3 rows updated.
SQL> select * from scott.test2;
NO NAME
---------- ----------
1 ddd
2 ddd
3 ddd
SQL> commit;
Commit complete.
step3. Log Miner 딕셔너리를 생성
SQL> show parameter utl
NAME TYPE VALUE
---------------------------------------------
create_stored_outlines string
utl_file_dir string /app/oracle/logminer
SQL> exec dbms_logmnr_d.build(dictionary_filename =>'dict2.dat',-
> dictionary_location=>'/app/oracle/logminer');
PL/SQL procedure successfully completed.
step4. 분석을 해야 할 Redo Log File 을 등록한 후 분석
SQL> @log
6 ;
GROUP# MEMBER MB seq STATUS arc
------ --------------------------------------------- ----
1 /data/temp3/redo01.log 5 19 INACTIVE YES
2 /data/temp3/redo02.log 5 20 INACTIVE YES
3 /data/temp3/redo03.log 5 21 CURRENT NO
SQL> select db_name,filename from v$logmnr_logs; <--3번이 current 이기에 3번 등록
DB_NAME FILENAME
--------------- --------------------------------------------------
TESTDB /data/temp3/redo03.log
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/app/oracle/logminer/dict2.dat')
PL/SQL procedure successfully completed.
step5. 분석결과 조회
SQL> alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
Session altered.
SQL> select timestamp,username,operation,sql_redo
2 from v$logmnr_contents
3 where seg_name='TEST2'
4 ;
출처: https://goalker.tistory.com/106?category=568848 [오라클 스터디]