백업&복구 14 - Supplemental logging / log miner - log 분석해서 장애 시간 알아내기

※ Supplemental logging
 - disable : redo log에 변경된 칼럼 정보만 기록
 - enable : 하나의 칼럼이 변경되더라도 전체 row의 정보를 모두 redo log에 저장
 
ORACLE 9i R2 버전부터 supplemental logging 기능의 기본값 : disable
이유 : 사용하게 되면 redo log의 양이 커지기 때문에 성능저하 우려
       → 실제 크게 차이 안난다 = 사용하는게 유리함
주의 : 활성화 시키고 난 후부터 생성된 redo lgo 만 분석이 되고 이 기능을 화성화 시키기 이전에 생성된 redo log는 분석 제한있음.
 
Ex) log miner이용 예
백업데이터파일에 최종적으로 3번까지 데이터 저장
복구할때 4,5,6 부터 복구한다.
만약 archive log 4,5번이 없는 상황이라면?
3번까지밖에 복구가 안된다.
6번이후 자료는 다 있지만, 4,5번이 없어서 복구할 수 없다.
로그마이너 이용해서 6번의 데이터를 다 읽어와 수동으로 입력할 수 밖에 없다.
(Supplemental logging 을 enable로 해두면 자세한 정보를 얻을 수 있다.)
 


----------- supplemental logging 기능 -----------------
 
1. supplemental logging 기능 Enable 시키기
 
① 조회

SQL> select supplemental_log_data_min from v$database;

 
SUPPLEMENTAL_LOG
----------------
NO
 
② 활성화(Enable) 시키기

SQL> alter database add supplemental log data;

 
Database altered.
 
SQL> select supplemental_log_data_min from v$database;
 
SUPPLEMENTAL_LOG
----------------
YES
 
※ 비활성화(Disable) 시키기

SQL> alter database drop supplemental log data;

 
※ 특정 테이블만 적용 시키기

SQL> alter table scott.emp ad supplemental log data (all) columns;

 
 



----------- log miner 활용하기 -----------------
 
2. 딕셔너리 생성
 : 일반파일(Flat file)에 저장하는 방법 - 가장많이 사용함
 
① 파라미터파일에 딕셔너리 파일 위치 지정

$ vi /home/oracle/product/10g/dbs/inittestdb.ora
utl_file_dir=/home/oracle/logminer  → 해당 디렉토리 수동으로 생성 해놓기

 
② DB 시작
③ DB OPEN 후 딕셔너리 생성

SQL> exec dbms_logmnr_d.build('dict.dat','/home/oracle/logminer');

 
 
3. 분석할 log file 등록 및 삭제 & 조회
SQL> @log
 
① 첫파일 등록

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo02.log',1);

 
② 파일 추가

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo01.log',3);
SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo03.log',3);

 
③ 파일 삭제

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo03.log',2);

※ 옵션설명
1: 신규등록, 2:파일삭제, 3:추가등록

 
④ 현재 log miner에 등록된 log file 내역 조회
SQL> col db_name for a15
SQL> col filename for a50

SQL> select db_name, filename from v$logmnr_logs;

 
 
4. log file 분석
※ 분석 범위 설정 옵션
startscn (기본값 : 0)
endscn (기본값 : 0)
starttime (기본값 : 01-JAN-1988)
endtime (기본값 : 01-JAN-2988)
dictfilename (기본값 : null)
Options (기본값 : 0)
 
등록되어 있는 모든 log 분석

SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/home/oracle/logminer/dict.dat');

 
startscn 과 endscn 을 사용해서 분석

SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/home/oracle/logminer/dict.dat', startscn=>884300, endscn=>889000);

 
▶ 이렇게 분석하면 분석된 내용은 v$logmnr_contents에 기록됨
 
 
5. v$logmnr_contents(분석된 자료 기록되어있는 곳) 조회
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';
 
 

SQL> select timestamp, username, operation, sql_redo
  2  from v$logmnr_contents
  3  where seg_name='TEST1';

 
 
 

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-실습1 시작
drop table시 해당정보 찾아내는 법
 
1. 현재 상태 확인
SQL> @log
SQL> select supplemental_log_data_min from v$database;
 
2. 새로운 테이블 생성 후 데이터 입력 하고 삭제
SQL> create table scott.test1 (no number);
SQL> insert into scott.test1 values (1);
SQL> commit;
SQL> drop table scott.test1 purge;
 
SQL> @log
커런트 log group 확인
 
3. 딕셔너리 생성
SQL> show parameter utl
 
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict.dat', dictionary_location=>'/home/oracle/logminer');
 
redo log file 을 확인
SQL> col filename for a50
SQL> col db_name for a10
SQL> select db_name, filename from v$logmnr_logs;
 
로그 추가
SQL> exec dbms_logmnr.add_logfile ('/home/oracle/oradata/testdb/redo01_a.log',1);
 
로그 분석
SQL> exec dbms_logmnr.start_logmnr (dictfilename => '/home/oracle/logminer/dict.dat', options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.committed_data_only);
 
v$logmnr_contents 내용확인
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';
 
SQL> select timestamp, username, operation, sql_redo
  2  from v$logmnr_contents
  3  where seg_name='TEST1';
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-실습1 끝
 


 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-실습2 시작
update table 정보 확인하기
 
1. 현재 상태 조회
SQL> @log
SQL> select supplemental_log_data_min from v$database;
 
2. 신규 테이블 생성 후 데이터 입력하고 update
SQL> create table scott.test2 (no number, name varchar2(10));
SQL> insert into scott.test2 values (1,'AAA');
SQL> insert into scott.test2 values (2,'BBB');
SQL> insert into scott.test2 values (3,'CCC');
SQL> commit;
 
3. 딕셔너리 생성
SQL> show parameter utl
파라미터에 경로 지정 되어있는가 확인
 
SQL> exec dbms_logmnr_d.build(dictionary_filename => 'dict2.dat', dictionary_logcation => '/home/oracle/logminer');
 
 
4. redo log file 등록
@log
커런트 확인
 
로그 추가
SQL> exec dbms_logmnr.add_logfile ('/home/oracle/oradata/testdb/redo01.log',1);
 
로그 분석
SQL> exec dbms_logmnr.start_logmnr (dictfilename => '/home/oracle/logminer/dict2.dat', options => dbms_logmnr.ddl_dict_trackng + dbms_logmnr.committed_data_only);
 
v$logmnr_contents 내용확인
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';
 
SQL> select timestamp, username, operation, sql_redo
  2  from v$logmnr_contents
  3  where seg_name='TEST2';

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-실습2 끝