logminor 로그마이너

logminor 로그마이너

-- 로그 마이너 설정 확인

SQL> show parameter utl_file_dir

NAME     TYPE VALUE

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

utl_file_dir                        string          /DB/ORACLE/ims

-- 로그 마이너 대상 확인

-rw-r-----  1 oracle dba 3097775616 Sep  4 15:06 MEDDB_1_24672_844241927.arc

-rw-r-----  1 oracle dba 3097775616 Sep  4 15:21 MEDDB_1_24673_844241927.arc

-rw-r-----  1 oracle dba 3097775616 Sep  4 15:39 MEDDB_1_24674_844241927.arc

-rw-r-----  1 oracle dba 3097775616 Sep  4 15:56 MEDDB_1_24675_844241927.arc

-rw-r-----  1 oracle dba 3097775616 Sep  4 16:15 MEDDB_1_24676_844241927.arc 

-- 로그 마이너 작업 디렉토리 생성

SQL> exec dbms_logmnr_d.build('logminrfile.ora','/DB/ORACLE/imsi');

PL/SQL procedure successfully completed.

-- 로그 파일 등록

SQL> exec dbms_logmnr.add_logfile ('/DB/ORACLE/imsi/MEDDB_1_24672_844241927.arc',dbms_logmnr.new);  -- 처음만 new

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile ('/DB/ORACLE/imsi/MEDDB_1_24673_844241927.arc',dbms_logmnr.addfile);  -- 두번째 부터는 add

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile ('/DB/ORACLE/imsi/MEDDB_1_24674_844241927.arc',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile ('/DB/ORACLE/imsi/MEDDB_1_24675_844241927.arc',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile ('/DB/ORACLE/imsi/MEDDB_1_24676_844241927.arc',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

-- 로그 마이너 시작

SQL> exec dbms_logmnr.start_logmnr(dictFileName => '/DB/ORACLE/imsi/logminrfile.ora');

PL/SQL procedure successfully completed.

-- 로그 내용 확인

select to_char(timestamp,'YYYY-MM-DD HH24:MI:SS'), username, session_info, sql_redo

from v$logmnr_contents

where seg_owner='MEDUSER' and seg_name in ('MED_DNA_RULE_CONDITION','MED_DNA_RULE_CONDITION_DATA');

### 관련 view ###

v$logmnr_contents : 현재 분석되고 있는 redo log file의 내용

v$logmnr_dictionary : 사용중인 dictionary file

v$logmnr_log : 분석되고 있는 redo log file

v$logmnr_parameters : logminer에 setting된 현재 parameter

ORA-01295 에러 발생시

SQL> exec dbms_logmnr.start_logmnr(dictFileName => '/home/oracle/imsi/logminrfile.ora'); BEGIN dbms_logmnr.start_logmnr(dictFileName => '/home/oracle/imsi/logminrfile.ora'); END; * ERROR at line 1: ORA-01295: DB_ID mismatch between dictionary /home/oracle/imsi/logminrfile.ora and logfiles ORA-06512: at "SYS.DBMS_LOGMNR", line 58 ORA-06512: at line 1

-- product DB 에서 실행

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

PL/SQL procedure successfully completed.

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';

NAME

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

/DB/ORACLE/ARCHIVE/MEDDB_1_27326_844241927.arc

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

NAME

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

/DB/ORACLE/ARCHIVE/MEDDB_1_27326_844241927.arc

-- 조회하여 나온 파일을 minor DB에 추가 

SQL> exec dbms_logmnr.add_logfile ('/home/oracle/imsi/MEDDB_1_27326_844241927.arc',dbms_logmnr.addfile);  -- 두번째 부터는 add

PL/SQL procedure successfully completed.

-- dictFileName 옵션을 제거하고, 하기 명령 실행

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

PL/SQL procedure successfully completed.