1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. Chap.06 Log Miner 활용과 Redo log 장애 NEW

Chap.06 Log Miner 활용과 Redo log 장애 NEW (by ljh1148) [2015.11.13]

06 Log Miner 활용과 Redo log 장애

6.1 Oracle log Miner - 8i

  • Redo log file : 바이너리 파일

제약 사항

  • 1. Log Miner는 8i 이후 버전에서만 작동을 합니다.
  • 2. 분석 대상 인스턴스와 같은 플래폼에서 동작하고 같은 케릭터셋을 사용하는
    db에서 생성된 Redo Log File만 분석할 수 있습니다.
  • 3. 분석 대상 인스턴스와 Redo log가 생성된 인스턴스는 DB_BLOCK_SIZE가 동일해야
    합니다. 만약 다를 경우 Corruption이 발생했다는 내용이 기록됩니다.


-- 디폴트 : NO
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
1행에 오류:
ORA-28365: 전자 지갑이 열려 있지 않습니다.


SQL> alter system set encryption key identified by foobar;

시스템이 변경되었습니다.

SQL> -- DATABASE LEVAL
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

데이타베이스가 변경되었습니다.


SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

SQL>  ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

데이타베이스가 변경되었습니다.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL>  -- TABLE LEVEL
SQL>  ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

테이블이 변경되었습니다.

SQL>

6.2 Oracle Log Miner - 9i New Feature

  • 1) DDL 문장을 지원합니다. v$logmnr_contents의 operation 칼럼에서 조회가 가능합니다.
    단 이 기능은 9i 이후에 만들어진 Redo Log File만 지원합니다.
  • 2) Dictionary file을 기존처럼 flat file에 생성할 수 있고 redo log 파일에도 저장 가능합니다.
    단 이렇게 딕셔너리를 Redo Log File에 저장하는 기능을 사용하면 반드시 archive log mode여야만 합니다.
  • 3) 분석하려는 Redo Log File 에 block corruption이 발생할 경우 해당 Block을 건너뛰고
    분석을 완료 합니다. 기존 8i에서는 분석 중 block corruption 이 발생할 경우
    일단 종료되고 다시 시작해서 분석을 계속하였던 것이 skip 기능으로 변경되것입니다.
    • options => dbms_logmnr.skip_corruption
  • 4) Committed_data_only 옵션이 추가 되어서 commit된 데이터만 조회가 가능합니다.
  • 5) Index clustered와 관련된 DML도 분석됩니다. ( 8i X )

9i 에서 지원 되지 않는 기능

  • Long / LOB data type
  • Object Types
  • Nested tables
  • Object refs
  • IOT

6.3 Oracle Log Miner - 10g New Feature

  • 1) Shared Server 지원 - 기존에 9i까지는 Dedicated Server mode만 지원했지만
    10g 버전부터 Shared server mode도 지원하게 되었습니다.
  • 2) Index Organized Table( IOT ) 지원
  • 3) Long / LOB / NCLOB 지원
  • 4) NO_ROWID_IN_STMT 지원
    • sql_redo와 sql_undo 칼럼을 조회할 때 rowid를 나타내지 않도록 하여
      다른 환경에서도 쉽게 사용할 수 있도록 지원합니다.
  • 5) Log Miner 세션에서 Redo Log File을 list에서 삭제할 수 있습니다.

6.4 Log Miner 실습하기 1

  • drop table 장애가 발생했을 경우 Log Miner을 활용하여 어떻게 Redo Log File에서
    해당 정보을 찾아내는지 실습해보겠습니다.

Step 1. 현재 상태 확인


SQL> @LOG

    GROUP# MEMBER                                           MB        SEQ ARC STATUS           FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
         1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG          51200        256 NO  INACTIVE               7814798
         2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG          51200        257 NO  INACTIVE               7848117
         3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG          51200        258 NO  CURRENT                7849319

SQL>
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

Step 2. 새로운 테이블 생성 후 데이터 입력하고 삭제


SQL>
SQL> CREATE TABLE SCOTT.TEST1 ( NO NUMBER );

테이블이 생성되었습니다.

SQL> INSERT INTO SCOTT.TEST1 VALUES( 1 );

1 개의 행이 만들어졌습니다.

SQL>
SQL> COMMIT;

커밋이 완료되었습니다.

SQL> DROP TABLE SCOTT.TEST1 PURGE;

테이블이 삭제되었습니다.

SQL> @LOG

    GROUP# MEMBER                                           MB        SEQ ARC STATUS           FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
         1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG          51200        256 NO  INACTIVE               7814798
         2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG          51200        257 NO  INACTIVE               7848117
         3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG          51200        258 NO  CURRENT                7849319 <--

SQL>

Step 3. Log Miner 딕셔너리를 생성합니다.

  • 반드시 분석 하고자 하는 DB 에서 생성


  -- dictionary file ( 존재시 )
  INSERT INTO EMP ( NAME, SALARY ) VALUES( 'John Doe', 50000 );
  
  -- dictionary file ( 미 존재시 )
  INSERT INTO Object#2581 ( col#1, col#2 ) VALUES( hextoraw( '4a6f686e20446f65'), hextoraw('c306' ) );

1. Online Catalog 사용

  • Redo Log File이 생성된 Source Database에 접근이 가능한 경우
  • 관련 table들에 칼럼 정의가 변경이 없을 경우
  • exec dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog );

2. Redo Log File 자체에 저장하는 방법

  • Redo Log File이 생성된 Source Database에 접근이 불가능할 경우
  • 9i 이상에서 지원됩니다.
  • DB는 Archive mode여야 합니다.
  • 딕셔너리를 추출하는 동안 어떤한 DDL도 허용되지 않습니다.
  • exec dbms_logmnr_d.build( options => dbms_logmnr_d.store_in_redo_logs );

3. Flat file에 저장하는 방법

  • 이전 버전과 호환성을 보장해서 작업 시에 맣이 사용하는 방법입니다.
  • exec dbms_logmnr_d.build( 'dict.dat','/home/oracle/logmnr/', dbms_logmnr_d.store_in_flat_file );
1) DB 전체를 종료한 후 parameter file 에 딕셔너리 파일의 위치를 아래와 같이 지정합니다.
{code:sql}
SQL> show parameter pfile;

NAME TYPE VALUE

---
--
spfile string D:\APP\LG\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SPFILEJHLEE68.ORA
SQL> alter system set utl_file_dir="D:\APP\LG\ORADATA\JHLEE68" scope=spfile;

시스템이 변경되었습니다.

SQL>
SQL> show parameter utl_file_dir;

NAME TYPE VALUE

---
--
utl_file_dir string

SQL>

{code}2) DB를 시작합니다.
{code:sql}
SQL> startup force;
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 2522038272 bytes
Fixed Size 2283864 bytes
Variable Size 620758696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 19947520 bytes
데이터베이스가 마운트되었습니다.
ORA-28365: 전자 지갑이 열려 있지 않습니다.

SQL> show parameter utl

NAME TYPE VALUE

---
--
create_stored_outlines string
utl_file_dir string D:\APP\LG\ORADATA\JHLEE68

SQL>
SQL> alter system set encryption wallet open identified by foobar;

시스템이 변경되었습니다.

SQL> alter database open;

데이타베이스가 변경되었습니다.

{code}3) 아래와 같이 딕셔너리를 생성합니다.
{code:sql}

CREATE TABLE SCOTT.TEST1 ( NO NUMBER );

INSERT INTO SCOTT.TEST1 VALUES( 1 );

COMMIT;

SQL> -- 딕셔너리 생성
SQL> exec dbms_logmnr_d.build( dictionary_filename => 'dict.dat', dictionary_location => 'D:\APP\LG\ORADATA\JHLEE68');

PL/SQL 처리가 정상적으로 완료되었습니다.

{code}4) Log Miner에 분석할 로그를 추가합니다.
{code:sql}

SQL> -- 1 : 신규등록, 2 : 삭제, 3 : 추가등록
SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG', 1 );

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG', 3 );

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG', 3 );

PL/SQL 처리가 정상적으로 완료되었습니다.

{code}5) Log Miner를 시작해서 log를 분석한후 결과를 조회합니다.
{code:sql}

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, SEG_OWNER --, SQL_REDO
2 FROM V$LOGMNR_CONTENTS
3 WHERE 1 =1
4 AND SEG_NAME='TEST1'
5 --AND SEG_OWNER='SCOTT';

선택된 레코드가 없습니다.

SQL>


|| 6) 데이타가 없어서 전체 재실행||
|{code:sql}

20:03:46 SQL> @log

    GROUP# MEMBER                                           MB        SEQ ARC STATUS           FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
         1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG          51200        271 NO  CURRENT                8035468
         2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG          51200        269 NO  INACTIVE               8010248
         3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG          51200        270 NO  INACTIVE               8012422

20:03:48 SQL>
20:03:49 SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

20:03:52 SQL> CREATE TABLE SCOTT.TEST1 ( NO NUMBER );

테이블이 생성되었습니다.

20:04:06 SQL>
20:04:06 SQL> INSERT INTO SCOTT.TEST1 VALUES( 1 );

1 개의 행이 만들어졌습니다.

20:04:07 SQL>
20:04:07 SQL> COMMIT;

커밋이 완료되었습니다.

20:04:08 SQL> DROP TABLE SCOTT.TEST1 PURGE;

테이블이 삭제되었습니다.

20:04:15 SQL>
20:04:31 SQL> exec dbms_logmnr_d.build( dictionary_filename => 'dict.dat', dictionary_location => 'D:\APP\LG\ORADATA\JHLEE68');

PL/SQL 처리가 정상적으로 완료되었습니다.

20:05:09 SQL>
20:05:09 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG', 1 );

PL/SQL 처리가 정상적으로 완료되었습니다.

20:05:09 SQL>
20:05:09 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG', 3 );

PL/SQL 처리가 정상적으로 완료되었습니다.

20:05:09 SQL>
20:05:09 SQL>
20:05:09 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG', 3 );

PL/SQL 처리가 정상적으로 완료되었습니다.

20:05:09 SQL> begin
20:05:14   2  dbms_logmnr.start_logmnr( dictfilename => 'D:\APP\LG\ORADATA\JHLEE68\dict.dat'
20:05:14   3                          , options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.committed_data_only
20:05:14   4                          );
20:05:14   5  end;
20:05:15   6  /

PL/SQL 처리가 정상적으로 완료되었습니다.

20:06:32 SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
20:06:56   2    FROM V$LOGMNR_CONTENTS
20:06:56   3   WHERE 1 =1
20:06:56   4    AND  SEG_NAME='TEST1';

선택된 레코드가 없습니다.

20:06:59 SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
20:07:28   2    FROM V$LOGMNR_CONTENTS
20:07:28   3   WHERE 1 =1
20:07:28   4    --AND  SEG_NAME='TEST1'
20:07:28   5    AND  SEG_OWNER='SCOTT'
20:07:28   6    ;

선택된 레코드가 없습니다.

20:07:30 SQL> @LOG

    GROUP# MEMBER                                           MB        SEQ ARC STATUS           FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
         1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG          51200        274 NO  CURRENT                8061772
         2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG          51200        272 NO  ACTIVE                 8058290
         3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG          51200        273 NO  ACTIVE                 8060270

20:07:38 SQL>

6.5 Log Miner 실습하기 2 - update table 시간 확인하기

1. 현재 상태 조회하기
{code:sql}

20:12:01 SQL> @log

GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#

--
--
-- ---
-
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 274 NO CURRENT 8061772
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 272 NO INACTIVE 8058290
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 273 NO INACTIVE 8060270

20:12:08 SQL>
20:12:18 SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME


YES

20:12:19 SQL>

{code}2. 신규 테이블 생성 후 데이터 입력하고 update 합니다.
{code:sql}

20:18:55 SQL> CREATE TABLE SCOTT.TEST2 ( NO NUMBER, NAME VARCHAR2(10) );

테이블이 생성되었습니다.

20:18:56 SQL>
20:18:56 SQL> INSERT INTO SCOTT.TEST2 VALUES( 1, 'AAA' );

1 개의 행이 만들어졌습니다.

20:18:56 SQL>
20:18:56 SQL> INSERT INTO SCOTT.TEST2 VALUES( 2, 'BBB' );

1 개의 행이 만들어졌습니다.

20:18:56 SQL>
20:18:56 SQL> INSERT INTO SCOTT.TEST2 VALUES( 3, 'CCC' );

1 개의 행이 만들어졌습니다.

20:18:57 SQL>
20:20:00 SQL> SELECT * FROM SCOTT.TEST2;

NO NAME

--
--
1 AAA
2 BBB
3 CCC

20:20:01 SQL> UPDATE SCOTT.TEST2 SET NAME ='DDD';

3 행이 갱신되었습니다.

20:20:34 SQL>
20:20:39 SQL> SELECT * FROM SCOTT.TEST2;

NO NAME

--
--
1 DDD
2 DDD
3 DDD

20:20:39 SQL> COMMIT;

커밋이 완료되었습니다.

20:20:42 SQL>
20:20:51 SQL> SHOW PARAMETER UTL

NAME TYPE VALUE

---
--
create_stored_outlines string
utl_file_dir string D:\APP\LG\ORADATA\JHLEE68
20:20:57 SQL>
20:22:43 SQL>
20:23:00 SQL>
20:23:00 SQL> exec dbms_logmnr_d.build( dictionary_filename => 'dict2.dat', dictionary_location => 'D:\APP\LG\ORADATA\JHLEE68');

PL/SQL 처리가 정상적으로 완료되었습니다.

20:23:37 SQL> @LOG

GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#

--
--
-- ---
-
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 274 NO CURRENT 8061772
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 272 NO INACTIVE 8058290
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 273 NO INACTIVE 8060270

20:24:23 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG', 1 );

PL/SQL 처리가 정상적으로 완료되었습니다.

20:24:34 SQL>
20:24:34 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG', 3 );

PL/SQL 처리가 정상적으로 완료되었습니다.

20:24:34 SQL>
20:24:34 SQL>
20:24:34 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG', 3 );

PL/SQL 처리가 정상적으로 완료되었습니다.

20:24:36 SQL> begin
20:25:23 2 dbms_logmnr.start_logmnr( dictfilename => 'D:\APP\LG\ORADATA\JHLEE68\dict2.dat'
20:25:23 3 );
20:25:23 4 end;
20:25:23 5 /

PL/SQL 처리가 정상적으로 완료되었습니다.

20:25:32 SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
20:26:36 2 FROM V$LOGMNR_CONTENTS
20:26:36 3 WHERE 1 =1
20:26:36 4 AND SEG_NAME='TEST2'
20:26:36 5 -- AND SEG_OWNER='SCOTT'
20:26:36 6 ;

TIMESTAM USERNAME OPERATI SEG_OWN SQL_REDO

--
---
---
--
15/11/05 SYS DDL SCOTT CREATE TABLE SCOTT.TEST2 ( NO NUMBER, NAME VARCHAR2(10) );
15/11/05 SYS INSERT SCOTT insert into "SCOTT"."TEST2"("NO","NAME") values ('1','AAA');
15/11/05 SYS INSERT SCOTT insert into "SCOTT"."TEST2"("NO","NAME") values ('2','BBB');
15/11/05 SYS INSERT SCOTT insert into "SCOTT"."TEST2"("NO","NAME") values ('3','CCC');
15/11/05 SYS UPDATE SCOTT update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'AAA' and ROW
ID = 'AAAVfkAAEAAAAKkAAA';

15/11/05 SYS UPDATE SCOTT update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'BBB' and ROW
ID = 'AAAVfkAAEAAAAKkAAB';

15/11/05 SYS UPDATE SCOTT update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'CCC' and ROW
ID = 'AAAVfkAAEAAAAKkAAC';

{code}

6.6 Supplemental logging

1) 개요

  • 미설정 : 변경 컬럼의 ROWID와 변견 컬럼만 기록
  • 실 정 : 변경 컬럼의 모든 레코드가 기록
    • 장점
      • 정합성 검증
      • 정장하고 싶은 칼럼을 수동으로 지정할 수 있다( Supplemental log group )

2) Supplemental logging의 종류

  • table level : 특정 테이블만 이 기능을 활성화
    • 장점 : 부하 적고, 성능도 괜찮다
    • 단점 : 다소 불편 ( 건건이 )
  • database level
    • 단점 : Redo log의 발생량이 아주 많아진다는 것입니다.
      • minmal logging ( 권장 )
        • Log Miner가 sql을 재생성하는데 필요한 최소한의 정보만을 기록
      • identification key logging
        • 모든 내역을 기록으로 redo log 발생량이 많이짐

3) Supplemental logging group

  • supplemental logging을 할 때 변경이 일어나는 칼럼 외에 추가로 저장할 다른 컬럼들을 지정하는 기능
    • conditional supplemental log groups ( 조건적 )
      • supplemental log group에 설정된 컬럼이 변경될 때만 before image가 기록되는 것을 말함
    • unconditional supplemental log groups ( 비조건적 ) == always log group
      • 어떤 컬럼이 변경되는 상관없이 기록되는 것을 의미합니다.
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. Chap.06 Log Miner 활용과 Redo log 장애 NEW