작성일: 2002-09-30
작성자: 강명규
OS: Linux 2.4.18
Oralce: Oracle EE 8.1.7
log miner는 8i부터 추가된 기능으로 오라클에서 제공되는
dbms_logmnr_d패키지와 dbms_logmnr패키지를 사용한다.
이것은 online redo log file, offline redo log file(archived log file)을 분석하여 여러가지 것들을 알려준다.
dbms_logmnr_d
log miner dictionary file을 생성하기 위한 프로시저를 제공한다.
몇개의 프로시저가 있으나 public인 것은 build뿐이므로 우리는 build프로시저만을 사용하면 되겠다.
이 프로시저는 현재 DB의 dictionary table에 질의하여 여기서 나온 정보를 가지고 텍스트기반의 파일을 생성한다.
이 외부(external) dictionary file은 차후 log miner가 log file을 분석하기 위한 용도로 사용된다.
이 프로시저는 내부적으로 UTL_FILE패키지를 사용하기 때문에 패러미터파일(init.ora)에 UTL_FILE_DIR패러미터를
설정해야 한다. set serveroutput on을 사용하면 build프로시저의 진행사항을 모니터링할 수 있다.
dbms_logmnr
이 패키지는 3개의 프로시저를 제공한다.
add_logfile(name varchar2, options number)
분석할 redo log의 추가/제거
start_logmnr(start_scn number, end_scn number,
start_time number, end_time number,
dictfilename varchar2, options number)
분석한 시간대나 SCN의 범위를 지정. 분석에 사용할 data dictionary extract를 지정.
end_logmnr()
마이닝 세션을 종료
즉, redo 스트림을 분석하고 dictionary extract를 읽기 위해 사용한 메모리를 해제한다.
v$logmnr_contents
사용자가 데이터베이스 변경사항에 대한 히스토리에 질의할 수 있도록 제공된다.
-- utl_file_dir를 패러미터파일에 설정하고 db를 재시작한다.
[oracle@ns 8.1.7]$ grep utl_file_dir $ORACLE_HOME/dbs/initdb.ora
utl_file_dir=/u01/app/oracle/oradata/db
[oracle@ns 8.1.7]$ svrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 13947040 bytes
Fixed Size 73888 bytes
Variable Size 5312512 bytes
Database Buffers 8388608 bytes
Redo Buffers 172032 bytes
Database mounted.
Database opened.
SVRMGR> quit
Server Manager complete.
[oracle@ns 8.1.7]$ sqlplus sys/xxxxxx
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 30 13:56:03 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
SQL> @?/rdbms/admin/dbmslm.sql -- dbms_logmnr 패키지 생성
Package created.
Grant succeeded.
SQL> @?/rdbms/admin/dbmslmd.sql -- dbms_logmnr_d 패키지 생성
Package created.
Package body created.
No errors.
Grant succeeded.
dbms_logmnr_d 패키지를 이용하여 dictionary 파일을 생성한다.
SQL> exec dbms_logmnr_d.build('logmnr_dic.ora', '/u01/app/oracle/oradata/db');
PL/SQL procedure successfully completed.
dbms_logmnr_d.build 실행시 에러처리
dbms_logmnr_d.build 실행시, 에러가 발생했다. 다음과 같이 조치하면 진행할 수 있을 것이다.
SQL> exec dbms_logmnr_d.build('logmnr_dic.ora', '/u01/app/oracle/oradata/db');
BEGIN dbms_logmnr_d.build('logmnr_dic.ora', '/u01/app/oracle/oradata/db'); END;
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 1
이런 망할... 왜 이러냐?
이 에러 아무리 해도 안잡혀서 소스를 수정할 수 밖에 없었다.
dbmslmd.sql 에서 dbms_logmnr_d 패키지를 생성하므로 이 파일을 수정해줬다.
145라인에 있는 다음의 내용을 수정한다.
TYPE col_desc_array IS VARRAY(513) OF col_description
=>
TYPE col_desc_array IS VARRAY(1000) OF col_description
수정후 dbms_logmnr_d 패키지 재생성
SQL> @?/rdbms/admin/dbmslmd.sql
Package created.
Package body created.
No errors.
Grant succeeded.
|
이제 분석할 로그파일을 로그파일목록(list of logfiles)에 등록하여 Log Miner세션에서 사용되도록 하자.
dbms_logmnr.add_logfile프로시저를 사용하여 로그파일목록에 등록 또는 제거할 수 있다.
주의할 점은 처음 등록시에는 dbms_logmnr.NEW 를 사용하여 등록하고
그 다음부터는 dbms_logmnr.ADDFILE으로 추가해야 한다는 점이다.
제거시에는 dbms_logmnr.REMOVEFILE을 사용한다.
SQL>
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
1 /u01/app/oracle/oradata/db/redo01.log
2 /u01/app/oracle/oradata/db/redo02.log
3 /u01/app/oracle/oradata/db/redo03.log
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
등록된 로그파일은 v$logmnr_logs뷰를 통해 알 수 있다.
SQL>
LOG_ID FILENAME
---------- -------------------------------------
35 /u01/app/oracle/oradata/db/redo02.log
36 /u01/app/oracle/oradata/db/redo03.log
37 /u01/app/oracle/oradata/db/redo01.log
검사할 로그파일을 등록했으므로, DBMS_LOGMNR.START_LOGMNR프로시저를 사용하여 log reader를 시작하도록 한다.
분석할 redo레코드의 필터링을 위해 시작/종료 SCN과 time패러미터를 지정할 수도 있다.
LogMiner세션에 지정된 패러미터는 V$LOGMNR_PARAMETERS뷰를 통해 알 수 있다.
DBMS_LOGMNR.START_LOGMNR(
startScn IN NUMBER default 0,
endScn IN NUMBER default 0,
startTime IN DATE default '01-jan-1988',
endTime IN DATE default '01-jan-2988',
DictFileName IN VARCHAR2 default '',
Options IN BINARY_INTEGER default 0 );
SQL>
PL/SQL procedure successfully completed.
분석내용을 알아보자.
v$logmnr_contents는 dbms_logmnr.start_logmnr가 시작된 상태여야 질의할 수 있다.
SQL>
SCN TIMESTAMP SQL_UNDO SQL_REDO
---------- --------- -------------------------------------------------- --------------------------------------------------
196566 30-SEP-02
196566 30-SEP-02 delete from "SYS"."SOURCE$" where "OBJ#" = 3210 an insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE"
d "LINE" = 569 and "SOURCE" = ' ELSE ) values (3210,569,' ELSE
' and ROWID = 'AAAABAAABAAAC0/AB5'; ');
196566 30-SEP-02
196566 30-SEP-02 delete from "SYS"."SOURCE$" where "OBJ#" = 3210 an insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE"
d "LINE" = 570 and "SOURCE" = ' UTL_FILE. ) values (3210,570,' UTL_FILE.PUT_LINE(di
PUT_LINE(dict_filehandle, 'to_date(''' || col_test ct_filehandle, 'to_date(''' || col_test_array(i).c
_array(i).col_value_vr2 || ''', ''MM/DD/YYYY HH24: ol_value_vr2 || ''', ''MM/DD/YYYY HH24:MI:SS''));'
MI:SS''));'); );
' and ROWID = 'AAAABAAABAAAC0/AB6'; ');
LOG MINER세션종료
SQL>
PL/SQL procedure successfully completed.
이번엔 archived log file로 해보겠다.
SQL>
PL/SQL procedure successfully completed.
SQL>
LOG_ID FILENAME
---------- --------------------------------------------------
1 /u01/app/oracle/admin/db/arch/arch_1_1.arc
로그마이너는 v$logmnr_contents 뷰를 통하여 redo stream에 relational 접근할 수 있도록 해준다.
아래에서는 이 뷰의 컬럼중 중요한 컬럼을 붉은 색으로 표시해 두었다.
SQL>
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMBER
TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
NUMBER
NUMBER
NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
VARCHAR2(32)
VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(19)
SESSION# NUMBER
SERIAL# NUMBER
VARCHAR2(32)
SESSION_INFO VARCHAR2(4000)
ROLLBACK NUMBER
VARCHAR2(32)
VARCHAR2(4000)
VARCHAR2(4000)
RS_ID VARCHAR2(32)
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
PH1_NAME VARCHAR2(32)
PH1_REDO VARCHAR2(4000)
PH1_UNDO VARCHAR2(4000)
PH2_NAME VARCHAR2(32)
PH2_REDO VARCHAR2(4000)
PH2_UNDO VARCHAR2(4000)
PH3_NAME VARCHAR2(32)
PH3_REDO VARCHAR2(4000)
PH3_UNDO VARCHAR2(4000)
PH4_NAME VARCHAR2(32)
PH4_REDO VARCHAR2(4000)
PH4_UNDO VARCHAR2(4000)
PH5_NAME VARCHAR2(32)
PH5_REDO VARCHAR2(4000)
PH5_UNDO VARCHAR2(4000)
SQL>
SCN TIMESTAMP SQL_UNDO SQL_REDO
---------- --------- -------------------------------------------------- --------------------------------------------------
154356 23-SEP-02
154357 23-SEP-02
154357 23-SEP-02 set transaction read write;
154357 23-SEP-02 update "SYS"."UNDO$" set "NAME" = 'RBS7', "USER#" update "SYS"."UNDO$" set "NAME" = 'RBS7', "USER#"
= 0, "FILE#" = 2, "BLOCK#" = 2178, "SCNBAS" = 1543 = 0, "FILE#" = 2, "BLOCK#" = 2178, "SCNBAS" = 1543
49, "SCNWRP" = 0, "XACTSQN" = 15, "UNDOSQN" = 5, " 49, "SCNWRP" = 0, "XACTSQN" = 15, "UNDOSQN" = 5, "
INST#" = 0, "STATUS$" = 2, "TS#" = 1 where ROWID = INST#" = 0, "STATUS$" = 2, "TS#" = 1 where ROWID =
'AAAAAPAABAAAABbAAI'; 'AAAAAPAABAAAABbAAI';
SQL>
START_DAT END_DATE START_SCN END_SCN I STATUS
--------- --------- ---------- ---------- - ----------
01-JAN-88 01-JAN-88 0 0 0
SQL>
PL/SQL procedure successfully completed.
APPENDIX
|
