LOGMINER를 이용한 분석 예제 - DELETE된 DATA 복구방법
====================================================


PURPOSE
-------
LOGMINER를 이용하여 DML 에 의해 실행된 문장을 확인하고 다시
복구하는 방법을 통하여 LOGMINER의 활용방법을 높여보자.


Explanation
-----------
logminer의 사용방법을 위해서는 먼저 "Bulletin#12033 Logminer의
기능과 사용방법" 을 확인해야 한다.

Redo log file에 쌓인 사용자의 작업에 대한 정보를 확인하는 것은
여러가지 방법으로 유용하다.
예를 들어, 어떤 작업이 돌아가고 있는 지를 확인한다거나,
특정한 시간에 다량의 archiving file이 쌓였을 때 어느 세션에서
어떤 작업을 실행하였는 지 등도 확인할 수 있다.
그 중에서도 data가 잘못 delete등이 된 경우 이를 되돌리기 위하여도
유용하게 이용할 수 있다.

Oracle8i logminer에서는 DDL 문장에 대한 정보는 지원되지 않는 데,
그것은 ddl 문장이 dictionary table에 대한 dml 형식으로 나타나기
때문이다. 이 DDL은 Oracle9i logminer부터 지원된다.

예제를 통해 logminer가 분석한 정보를 이용하는 방법을 확인해 보자.


Example
-------

Data를 delete한 후에 logminer를 이용하여 확인하고 삭제된
record를 복구하는 간단한 예제를 살펴보자.

1. scott user에서 dept table 전체와 emp table의 한 row를 삭제한다.

SQL> connect scott/tiger
SQL> delete from dept;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from emp where deptno = 10 and empno= 7934;
1 row deleted.
SQL> commit;
Commit complete.


2. 분석할 log file을 확인한다.

SQL> connect system/manager
SQL> select a.group#, a.sequence#, b.member
2 from v$log a, v$logfile b
3 where a.status = 'CURRENT' and a.group# = b.group#;

GROUP# SEQUENCE# MEMBER
---------- ---------- -------------------------------------------------------
1 801 /app/ora817/oradata/ORA817/oradata/ORA817/redo01.log


3. logminer를 사용하기 위해 dictionary file을 생성하고 delete되었을 때의
redo log file 또는 archiving file을 등록한다.

(1) dictionary file을 생성한다.

SQL> call dbms_logmnr_d.build('dir_file','/app/ora817/utl_dir');
Call completed.

(2) 분석할 file을 등록한다.

SQL> call dbms_logmnr.add_logfile('/app/ora817/admin/ORA817/arch/arch_1_801.arc',1);

또는 다음과 같이 archiving file을 등록한다. 보통의 경우 online redo log file은
금방 overwrite될 수 있기 때문에 archiving file을 이용한다.

SQL> call dbms_logmnr.add_logfile('/app/ora817/oradata/ORA817/oradata/ORA817/redo01.log',1);


4. logminer를 실행한다. 다음과 같이 archiving file 전체의 정보를 뽑아내거나
특정한 scn 번호 또는 시간의 지정할 수도 있다.

SQL> execute dbms_logmnr.start_logmnr(DictFileName=>'/app/ora817/utl_dir/dir_file');

또는

SQL> execute dbms_logmnr.start_logmnr(DictFileName=>'/app/ora817/utl_dir/dir_file',
startTime=>to_date('2002/04/20 13:00:00','yyyy/mm/dd hh24:mi:ss'),
endTime=>to_date('2002/04/30 18:00:00','yyyy/mm/dd hh24:mi:ss'));


5. v$logmnr_contents view를 통한 log file의 정보를 확인

(1) DEPT table에서 삭제한 data 확인하기.

SQL> select seg_owner, seg_name, operation, sql_redo, sql_undo
2 from v$logmnr_contents
3 where seg_owenr = 'SCOTT' and seg_name = 'DEPT' ;

SEG_OWNER SEG_NAME
-------------------------------- --------------------------------
OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
SCOTT DEPT
DELETE
delete from "SCOTT"."DEPT" where "DEPTNO" = 10 and "DNAME" = 'ACCOUNTING' and "L
OC" = 'NEW YORK' and ROWID = 'AAAAxCAAFAAAAAzAAA';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values (10,'ACCOUNTING','NEW
YORK');

SCOTT DEPT
DELETE
delete from "SCOTT"."DEPT" where "DEPTNO" = 20 and "DNAME" = 'RESEARCH' and "LOC
" = 'DALLAS' and ROWID = 'AAAAxCAAFAAAAAzAAB';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values (20,'RESEARCH','DALLAS
');

SCOTT DEPT
DELETE
delete from "SCOTT"."DEPT" where "DEPTNO" = 30 and "DNAME" = 'SALES' and "LOC" =
'CHICAGO' and ROWID = 'AAAAxCAAFAAAAAzAAC';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values (30,'SALES','CHICAGO')
;

SCOTT DEPT
DELETE
delete from "SCOTT"."DEPT" where "DEPTNO" = 40 and "DNAME" = 'OPERATIONS' and "L
OC" = 'BOSTON' and ROWID = 'AAAAxCAAFAAAAAzAAD';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values (40,'OPERATIONS','BOST
ON');

=> column SQL_REDO 는 실행된 DELETE문장이며, SQL_UNDO는 이 작업을 다시 되돌리기
위한 문장이다. 실제로 user가 실행한 문장은 'delete from dept;' 이지만,
redo log file에는 이 작업을 되돌릴 수 있는 각각의 문장으로 저장된다. 그래서
user가 실행한 command보다 많은 row가 나온다.

위의 경우는 SQL_UNDO column에 나오는 insert 문장을 실행하면 delete된 정보를 다시
되돌릴 수가 있다.


(2) EMP table에서 삭제된 data 확인하기.

SQL> select seg_owner, seg_name, operation, sql_redo, sql_undo
2 from v$logmnr_contents
3 where seg_owenr = 'SCOTT' and seg_name = 'EMP' ;

SEG_OWNER SEG_NAME
-------------------------------- --------------------------------
OPERATION
--------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
SCOTT EMP
DELETE
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
EPTNO") values (7934,'MILLER','CLERK',7782,TO_DATE('23-JAN-1982 00:00:00', 'DD-M
ON-YYYY HH24:MI:SS'),1300,NULL,10);


=> 위의 경우도 insert문장을 실행하여 delete된 data를 복구할 수 있다.


(3) 실행한 session의 정보 확인하기.

delete 문장을 실행한 시간과 session정보를 확인할 수 있다.

SQL> select to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') "Time", session_info
2 from v$logmnr_contents where seg_name = 'EMP';

Time
-------------------
SESSION_INFO
--------------------------------------------------------------------------------
2002/04/25 21:06:24
LoginUserName = SCOTT, ClientInfo = , OsUserName = ora817, MachineName = rcsunin
tel, OsTerminal = pts/9, OsProcessId = 5327, OsProgramName = sqlplus@rcsunintel
(TNS V1-V3)


6. 작업이 끝나면 다음과 같이 logminer를 종료한다.

SQL> call dbms_logmnr.end_logmnr();

이 문장을 실행하면 v$logmnr_contents view나 등록한 log file의 정보등이 모두
자동으로 없어지므로 그 전에 분석한 내용을 모두 확인하거나 v$logmnr_contents
view의 내용을 다른 table로 backup받아 두어야 한다.

출처 : otn