supplemental 기능의 활성화 여부 확인
SYS>select supplemental_log_data_min from v$database;
비활성화 상태
SUPPLEME
--------
NO
활성화상태로 변경
SYS>alter database add supplemental log data;
Database altered.
SYS>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
활성화->비활성화로 변경
SYS>alter database drop supplemental log data;
Database altered.
SYS>select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
이 기능은 DB 전체를 대상으로 적용할 수 있고,혹은 특정테이블만 적용가능
특정테이블만 적용하기
SYS>alter table scott.emp add supplemental log data (all) columns;
Table altered.
이 기능을 사용할 경우 리두로그 생성양이 많아지므로 아카이브 로그 모드를 사용할 때는
아카이브 용량을 충분하게 주고 사용해야 함
log miner 활용하기
- drop table 정보 찾기
SYS>@log
GROUP# MEMBER MB SEQ# STATUS ARC
------ --------------------------------------------- ---- ---- -------- -----
1 /data/temp7/redo01_a.log 5 3 CURRENT NO
1 /data/temp7/redo01_b.log 5 3 CURRENT NO
2 /data/temp7/redo02_a.log 5 2 INACTIVE YES
2 /data/temp7/redo02_b.log 5 2 INACTIVE YES
SYS>select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SYS>alter database add supplemental log data;
Database altered.
활성화 상태에서 실습
SYS>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
새로운 테이블 생성 후 데이터 입력, 삭제하기
SYS>create table scott.test1 (no number);
Table created.
SYS>insert into scott.test1 values(1);
1 row created.
SYS>commit;
Commit complete.
SYS>drop table scott.test1 purge;
Table dropped.
SYS>@log
GROUP# MEMBER MB SEQ# STATUS ARC
------ --------------------------------------------- ---- ---- -------- -----
1 /data/temp7/redo01_a.log 5 3 CURRENT NO
1 /data/temp7/redo01_b.log 5 3 CURRENT NO
2 /data/temp7/redo02_a.log 5 2 INACTIVE YES
2 /data/temp7/redo02_b.log 5 2 INACTIVE YES
테이블이 생성되고 데이터 입력, 삭제 된 내용이 1번 그룹에 들어가 있음을 current 로 확인할 수 있음
로그 딕셔너리 생성
방법
1 online
2
3
db 종료 후 파라미터 파일의 딕셔너리 파일 경로 지정
SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost ~]$ mkdir /data/logminer
파일에 아래 내용 추가
[oracle@localhost ~]$ vi $ORACLE_HOME/dbs/inittestdb.ora
utl_file_dir=/data/logminer
db시작
SYS>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.
딕셔너리 생성
SYS>exec dbms_logmnr_d.build(dictionary_filename=>'dict.dbt',-
> dictionary_location=>'/app/oracle/logminer');
log miner에 분석할 로그를 추가
딕셔너리 생성시 경로를 변경하고 싶으면 위와 같은 명령어를 그대로 치고,
경로만 바꾸어 주면 됨
SYS>exec dbms_logmnr_d.build(dictionary_filename=>'dict.dat', dictionary_location=>'/data/logminer');
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SYS>exec dbms_logmnr.add_logfile('/data/temp7/redo01_a.log',1);
PL/SQL procedure successfully completed.
분석시 add_logfilelsl('분석할 리두 경로',숫자옵션)
1 신규생성
2 파일삭제
3 추가등록
2번 그룹에 로그파일을 추가로 등록함
SYS>exec dbms_logmnr.add_logfile('/data/temp7/redo01_a.log',1);
PL/SQL procedure successfully completed.
SYS>exec dbms_logmnr.add_logfile('/data/temp7/redo02_a.log',3);
PL/SQL procedure successfully completed.
등록한 리두로그마이너 삭제할 때
SYS>exec dbms_logmnr.add_logfile('/data/temp7/redo02_b.log',2);
PL/SQL procedure successfully completed.
그룹이 있으면 그 그룹 중에 멤버 하나만 등록하면 리두마이너가 등록이 된다.
한 그룹에 어차피 여러 멤버가 있어서 등록하려고 하면 복사본이라고 에러뜸
그룹당 하나만 리두로그마이너 등록할 수 있음
ERROR at line 1:
ORA-01289: cannot add duplicate logfile /data/temp7/redo01_b.log
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
확인
SYS>set line 200
SYS>col db_name for a15
SYS>col filename for a50
SYS>select db_name, filename from v$logmnr_logs;
DB_NAME FILENAME
--------------- --------------------------------------------------
TESTDB /data/temp7/redo01_a.log
TESTDB /data/temp7/redo02_a.log
*대량의 아카이브 리두 로그파일을 분석해야 할 경우 일일이 수동으로 등록하기에 시간이 오래 걸리므로
아래와 같이 스크립트를 생생해서 등록 작업 설정을 쉽게 할 수 있음
[oracle@localhost ~]$ vi ar.sh
for
for i in $(ls /data/arc2);
for i in $(ls 아카이브가 있는 현재경로);
do echo "exec dbms_logmnr.add_logfile('아카이브 파일이 있는 현재경로/$i',3);" >> /home/oracle/arch.sql;
done;
wq!
[oracle@localhost ~]$ sh ar.sh
파일을 열어보면 명령어가 생성된 것을 확인 할 수 있음
[oracle@localhost ~]$ vi /home/oracle/arch.sql
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_10_b5ldx374_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_1_b5lgngbh_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_29_b5l414vm_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_2_b5mxx2on_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_3_b5n7wdy1_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_6_b5l00zkp_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_7_b5l0373g_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_8_b5l03lhl_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_8_b5lboj3h_.arc',3);
exec dbms_logmnr.add_logfile('/app/oracle/fast_recovery_area/TESTDB/archivelog/2014_11_05/o1_mf_1_9_b5lboo35_.arc',3);
~
~
~
"~/arch.sql" 10L, 1192C 1,1 All
주의!!! 테스트 환경을 만들때, 오라클 설치 파일경로인 /app/oracle 에 남은 용량이 많이 없어서 위와 같이
작업을 하게 되면 나중에 리두로그마이너가 많이 생겨서 용량이 부족하다는 에러가 생길수도 있음.
그래서 나는 아카이브 경로를 용량이 많은 다른 경로로 이동해 주겠다.
pfile을 사용하는 경우
[oracle@localhost ~]$ vi $ORACLE_HOME/dbs/inittestdb.ora
#아카이브 로그 파일 저장경로와 이름 지정
log_archive_dest_1='location=/data/arc1'
log_archive_dest_2='location=/data/arc2'
log_archive_format=%s_%t_%r.arc #아카이브 로그파일 이름지정#
:wq!
11g 환경에서 오라클은 기본으로 4G의 용량을 주고 아카이브 파일을 저장한다
하지만 내가 파일을 만들어서 주는 것은 내 마음대로 용량을 줄 수 있음
두 개의 파일은 다중화가 되서 아카이브 파일이 같이 들어감
확인방법
SYS>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.
SYS>alter system switch logfile;
System altered.
SYS>alter system switch logfile;
System altered.
[oracle@localhost arc1]$ ls
4_1_862809266.arc 5_1_862809266.arc
[oracle@localhost arc2]$ ls
4_1_862809266.arc 5_1_862809266.arc
위 파일의 명령을 실행시켜주면 됨
SYS>@/app/oracle/arch.sql
[oracle@localhost ~]$ vi ar.sh
for i in $(ls /data/arc1);
do echo "exec dbms_logmnr.add_logfile('/data/arc1/$i',3);" >> /home/oracle/arch.sql;
done;
wq!
[oracle@localhost ~]$ sh ar.sh
[oracle@localhost ~]$ vi /home/oracle/arch.sql
exec dbms_logmnr.add_logfile('/data/arc1/4_1_862809266.arc',3);
exec dbms_logmnr.add_logfile('/data/arc1/5_1_862809266.arc',3);
SYS>@/home/oracle/arch.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
로그마이너를 시작해서 log를 분석한 후 결과 조회
---------------------
157
SYS>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SYS>create table scott.test2(no number, name varchar2(10));
Table created.
SYS>insert into scott.test2 values(1,'AAA');
1 row created.
SYS>insert into scott.test2 values(2,'BBB');
1 row created.
SYS>insert into scott.test2 values(3,'CCC');
1 row created.
SYS>commit;
Commit complete.
SYS>select * from scott.test2;
NO NAME
---------- --------------------------------------------------
1 AAA
2 BBB
3 CCC
SYS>update scott.test2 set name='DDD';
3 rows updated.
SYS>select * from scott.test2;
NO NAME
---------- --------------------------------------------------
1 DDD
2 DDD
3 DDD
SYS>commit;
Commit complete.
SYS>show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /data/logminer
SYS>exec dbms_logmnr_d.build(dictionary_filename=>'/dict2.dat',dictionary_location=>'/data/logminer');
PL/SQL procedure successfully completed.
SYS>exec dbms_logmnr.start_logmnr(dictfilename=>'/data/logminer/dict2.dat');
PL/SQL procedure successfully completed.
SYS>col username for a10
SYS>col operation for a10
SYS>col sql_redo for a70
SYS>set pagesize 50
SYS>alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
Session altered.
SYS>select timestamp, username, operation, sql_redo
2 from v$logmnr_contents
3 where seg_name='TEST2';
TIMESTAMP USERNAME OPERATION SQL_REDO
------------------- ---------- ---------- ----------------------------------------------------------------------
2014-11-06:02:19:14 SYS DDL create table scott.test2(no number, name varchar2(10));
2014-11-06:02:19:47 SYS INSERT insert into "SCOTT"."TEST2"("NO","NAME") values ('1','AAA');
2014-11-06:02:20:08 SYS INSERT insert into "SCOTT"."TEST2"("NO","NAME") values ('2','BBB');
2014-11-06:02:21:23 SYS INSERT insert into "SCOTT"."TEST2"("NO","NAME") values ('3','CCC');
2014-11-06:02:21:44 SYS UPDATE update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'AAA' and ROW
ID = 'AAASm+AAEAAAAMnAAA';
2014-11-06:02:21:44 SYS UPDATE update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'BBB' and ROW
ID = 'AAASm+AAEAAAAMnAAB';
2014-11-06:02:21:44 SYS UPDATE update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'CCC' and ROW
ID = 'AAASm+AAEAAAAMnAAC';
7 rows selected.
리두로그 마이너는 세션별로 볼 수 있다.
세션창을 끄고 나오거나 셧다운 다른 창에서는 그 해당하는 리두로그 마이너의 결과를 볼 수 없다.
심지어... 창을 끄고 다시 접속하면 로그파일도 다 등록해줘야함..;;
