SQL 실행 계획 확인 방법 (SQL Plus 의 Autotrace 기능)

 

SQL*PLUS Autotrace 기능 활성화

- PLUSTRACE 권한 생성

SQL>conn / as sysdba

SQL>@?/sqlplus/admin/plustrce.sql (오타아님)

 

- 해당 사용자에게 PLUSTRACE 권한 부여

SQL>grant PLUSTRACE to scott;

 

- 해당 사용자로 로그인 후 PLAN Table 생성

SQL>conn scott/tiger

SQL>@?/rdbms/admin/utlxplan.sql

 

Autotrace 모드 설정

 

SQL>set autotrace on

SQL>set autotrace off

SQL>set autotrace traceonly

SQL>set autotrace traceonly explatin

SQL>set autotrace traceonly statistics

 

SQL*plus Autotrace 실습

- 100000 개의 Record 가 들어있는 Table 에서 name 컬럼에 abc 가 들어있는 Row 를 질의하는 SQL 의 실행계획 만들기.

SQL> set autotrace on

SQL> select * from scott.tt920

2 where name LIKE '%abc%';

- 위의 실행 계획을 보면 Operation 컬럼에 Row 를 보면 위쪽과 아래쪽을 비교하여 오른쪽으로 밀린 것을 먼저 수행, 즉 id=1 부터 수행 후 id=0 을 수행한다는 뜻 입니다.

- index 가 있을경우 더 다양한 실행계획이 나올 수 있습니다.

 

 

 

 

 

 

BBED (Block Browser and Editor)

 

< BBED 를 이용한 Block Recovery >

- Block 을 탐색하고 수정하는 유틸리티

- 사용을 위해서는 Linking 작업이 추가로 필요

 

10g Version

[oracle@rac1 lib]$ pwd

/home/oracle/product/10g/db/rdbms/lib

[oracle@rac1 lib]$ make -f ins_rdbms.mk

/home/oracle/product/10g/db/rdbms/lib/bbed

[oracle@rac1 lib]$ pwd

/home/oracle/product/10g/db/rdbms/lib

[oracle@rac1 lib]$ ls -l bbed

-rwxr-xr-x 1 oracle dba 554330 10월 21 15:33 bbed

[oracle@rac1 lib]$ cp bbed $ORACLE_HOME/bin/

[oracle@rac1 lib]$

Next Step – 모든 Version 동일

[oracle@rac1 lib]$ bbed

Password: blockedit (기본 암호)

 

BBED: Release 2.0.0.0.0 - Limited Production on Fri Oct 21 15:39:55 2011

 

Copyright (c) 1982, 2007, Oracle. All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED>

 

BBED Option

 

옵션명

설명

BLOCKSIZE

편집할 데이터 파일의 블록 사이즈를 적어줍니다.

MODE 

BBED를 실행할 모드를 설정합니다.(browser or edit)

SILENT 

작업결과를 표준 출력으로 보여줄지를 제어(Y,N)

SPOOL

BBED 작업 log 를 bbed.log 파일에 저장할 것인지를 제어 (Y, N)

LISTFILE

작업할 파일의 목록을 적어줍니다.

CMDFILE 

실행할 파일들의 목록을 적어줍니다.

BIFILE

변경 전 파일의 이미지를 저장할 파일명을 적어줍니다.(기본값-Bifile.bbd)

LOGFILE

User log를 저장할 파일명을 지정합니다.(기본값- log.bbd)

PARFILE

BBED 를 실행할 때 사용할 파라미터 등을 적어주는 파일명을 지정합니다.

 

예제 (옵션사용)

 

SQL> select file#||' '||name||' '||bytes from v$datafile;

 

- 위 내용을 list file 을 만들어서 거기에 등록하고 listfile 옵션을 사용하여 bbed 를 실행

1 /dev/raw/raw6 628097024

2 /dev/raw/raw8 208666624

3 /dev/raw/raw7 313524224

4 /dev/raw/raw9 208666624

5 /dev/raw/raw10 9437184

6 /dev/raw/raw28 52428800

7 /dev/raw/raw30 20971520

8 /dev/raw/raw31 8388608

 

[oracle@rac1 lib]$ cd ~

[oracle@rac1 ~]$ vi filelist.log

[oracle@rac1 ~]$

 

[oracle@rac1 ~]$ vi bbed.par

blocksize=8192

listfile=/home/oracle/filelist.log

mode=edit

 

[oracle@rac1 ~]$ bbed parfile=bbed.par

 

BBED 명령어

 

1) help all

- BBED 관련 도움말을 보여 줍니다.

 

2) set dba

- 작업하고자 하는 데이터 파일과 블록을 지정.

- 여기에 지정될 데이터 파일은 listfile 에 등록되어 있어야 함

BBED> set dba 2,50

DBA 0x00800032 (8388658 2,50)

 

BBED>

- 위 명령은 2번 파일의 50번 블록에 작업을 하겠다고 설정하는 것입니다.

 

3) set filename

- 작업할 데이터 파일을 이름으로 지정합니다.

BBED> set filename '/dev/raw/raw30'

FILENAME /dev/raw/raw30

 

4) set file

- 작업할 데이터 파일을 번호로 지정합니다.

BBED> set file 8;

FILE# 8

 

5) set block

- 현재 설정되어 있는 파일에서 작업을 원하는 block 을 지정합니다.

BBED> set block 20

BLOCK# 20

 

6) set offset

- 현재 작업하기 원하는 offset 번호를 지정합니다.

 

7) set blocksize

- 현재 작업하는 파일의 blocksize 를 지정합니다.

BBED> set blocksize 8192

BLOCKSIZE 8192

 

8) set listfile

- 작업을 수행할 파일의 목록이 적혀있는 list file 을 지정합니다.

BBED> set listfile '/home/oracle/filelist.log'

LISTFILE /home/oracle/filelist.log

 

9) set width

- 현재 보이는 화면의 폭을 지정합니다.

BBED> set width 200

WIDTH 200

 

10) set count

- Dump 명령어 수행시 화면에 보여줄 data block 의 byte 수를 지정합니다.

BBED> set count 100

COUNT 100'

 

11) set ibase

- 내부적으로 사용되는 값들의 표현식을 지정합니다.

- 기본값은 10진수이며 16진수(Hexadecimal) 이나 8진수(Octal) 로 변경 가능합니다.

 

12) set obase

- 기능 모름

 

13) set mode

- BBED 수행 mode 를 설정 합니다.

 

14) set spool

- 기능 모름

 

15) show

- 현재 설정되어 있는 내용들을 보여줍니다.

BBED> show

FILE# 1

BLOCK# 1

OFFSET 0

DBA 0x00400001 (4194305 1,1)

FILENAME /dev/raw/raw6

BIFILE bifile.bbd

LISTFILE /home/oracle/filelist.log

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 200

COUNT 100

LOGFILE log.bbd

SPOOL No

 

16) info

- 현재 작업중인 파일 내용을 보여줍니다.

BBED> info

File# Name Size(blks)

----- ---- ----------

1 /dev/raw/raw6 76672

2 /dev/raw/raw8 25472

3 /dev/raw/raw7 38272

4 /dev/raw/raw9 25472

5 /dev/raw/raw10 1152

6 /dev/raw/raw28 6400

7 /dev/raw/raw30 2560

8 /dev/raw/raw31 1024

 

17) map

- 현재 작업중인 블록에 대한 자세한 정보를 보여줍니다.

BBED> map /v dba 1,20

File: /dev/raw/raw6 (1)

Block: 20 Dba:0x00400014

------------------------------------------------------------

Undo Data

 

struct kcbh, 20 bytes @0

ub1 type_kcbh @0

ub1 frmt_kcbh @1

ub1 spare1_kcbh @2

ub1 spare2_kcbh @3

ub4 rdba_kcbh @4

ub4 bas_kcbh @8

ub2 wrp_kcbh @12

ub1 seq_kcbh @14

ub1 flg_kcbh @15

ub2 chkval_kcbh @16

ub2 spare3_kcbh @18

 

struct ktubh, 82 bytes @20

struct ktubhxid, 8 bytes @20

ub2 ktubhseq @28

ub1 ktubhcnt @30

ub1 ktubhirb @31

ub1 ktubhicl @32

ub1 ktubhflg @33

ub2 ktubhidx[34] @34

 

ub1 freespace[346] @102

 

ub1 undodata[7740] @448

 

ub4 tailchk @8188

 

18) dump

- Block 의 내용을 실제 dump 해서 화면에 보여주는 명령어

- 자세한 내용을 알기 위해 /v 옵션을 함께 사용하는 경우가 많습니다.

- DBA, filename, file, block, offset 등 set 명령어로 설정된 값들을 다 조회 가능

 

- 다음은 1번 파일의 10번 블록을 offset 1-127 번까지 dump 를 수행한 내용입니다.

BBED> set width 100

WIDTH 100

 

BBED> dump /v dba 1,10 offset 0 count 128

File: /dev/raw/raw6 (1)

Block: 10 Offsets: 0 to 127 Dba:0x0040000a

-------------------------------------------------------

02a20000 0a004000 4e870200 00000104 l ......@.N.......

284e0000 00004800 27000000 36001f1f l (N....H.'...6...

0000e81f ec1ef01d f41cf81b fc1a001a l ................

04190818 0c171016 14151814 1c132012 l .............. .

24112810 2c0f300e 340d380c 3c0b400a l $.(.,.0.4.8.<.@.

44094808 4c075006 54055804 5c036002 l D.H.L.P.T.X.\.`.

64010000 00000000 00000000 00000000 l d...............

00000000 00000000 00000000 00000000 l ................

 

<16 bytes per line>

 

19) (p)rint

- 현재 작업중인 정보를 보여줍니다.

BBED> p

kcbh.type_kcbh

--------------

ub1 type_kcbh @0 0x02

 

BBED> p kcbh

struct kcbh, 20 bytes @0

ub1 type_kcbh @0 0x02

ub1 frmt_kcbh @1 0xa2

ub1 spare1_kcbh @2 0x00

ub1 spare2_kcbh @3 0x00

ub4 rdba_kcbh @4 0x0040000a

ub4 bas_kcbh @8 0x0002874e

ub2 wrp_kcbh @12 0x0000

ub1 seq_kcbh @14 0x01

ub1 flg_kcbh @15 0x04 (KCBHFCKV)

ub2 chkval_kcbh @16 0x4e28

ub2 spare3_kcbh @18 0x0000

 

BBED>

 

20) e(x)amine

- 이 명령어는 DBA, filename, file, block, offset 등의 값을 정해진 포멧형식으로 보여줍니다.

BBED> x

kcbh.type_kcbh @0

--------------

0x02

 

BBED> x /2

kcbh.type_kcbh @0

--------------

0x02 0xa2

 

BBED> x /b

kcbh.type_kcbh @0

--------------

0x02

 

BBED> x /h

kcbh.type_kcbh @0

--------------

0xa202

 

BBED> x /w

kcbh.type_kcbh @0

--------------

0x0000a202

 

BBED> x /l

kcbh.type_kcbh @0

--------------

0x0000a202

 

BBED> x /r

kcbh.type_kcbh @0

--------------

BBED-00402: operation only allowed for data/index blocks

 

- 옵션

/b : bytes 단위로 보여줌

/h : half-word 단위로 보여줌

/w : word 단위로 보여줌

/l : long 단위로 보여줌

/r : table/index row 를 보여줌

 

21) (f)ind

- 원하는 데이터를 찾아내는 기능

 

BBED> set file 7

FILE# 7

 

BBED> show

FILE# 7

BLOCK# 10

OFFSET 0

DBA 0x01c0000a (29360138 7,10)

FILENAME /dev/raw/raw30

BIFILE bifile.bbd

LISTFILE /home/oracle/filelist.log

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 100

COUNT 128

LOGFILE log.bbd

SPOOL No

 

BBED> find /c EW

File: /dev/raw/raw28 (6)

Block: 1 Offsets: 342 to 391 Dba:0x01800001

------------------------------------------------------------------------

45570000 00000000 00000000 00000000 00000000 00000000 00000600 00000000

00000000 00009671 982d0000 00000000 0000

 

<32 bytes per line>

 

BBED>

BBED> d /v dba 6,1 offset 342 count 50

File: /dev/raw/raw28 (6)

Block: 1 Offsets: 342 to 391 Dba:0x01800001

-------------------------------------------------------

45570000 00000000 00000000 00000000 l EW..............

00000000 00000000 00000600 00000000 l ................

00000000 00009671 982d0000 00000000 l .......q.-......

0000 l ..

 

<16 bytes per line>

 

BBED>

 

- find 명령어와 쓸 수 있는 옵션

/x : 16진수 값을 찾습니다.

/d : 10진수 값을 찾습니다.

/u : 부호없는 10진수 값을 찾습니다.

/o : 8진수 값을 찾습니다.

/c : 문자값을 찾습니다.

à find 명령어는 날짜와 숫자 값 검색은 지원하지 않습니다.

 

22) copy

- 이 명령어는 블록을 복사하는 명령어

 

 

BBED> copy dba 6,1 to dba 7,1

File: /dev/raw/raw30 (7)

Block: 1 Offsets: 342 to 391 Dba:0x01c00001

------------------------------------------------------------------------

45570000 00000000 00000000 00000000 00000000 00000000 00000600 00000000

00000000 00009671 982d0000 00000000 0000

 

<32 bytes per line>

 

BBED>

- 위 명령어는 6번 파일의 1번 블록을 7번 파일의 1번 블록으로 복사하라는 명령어

- 이 명령어는 백업 파일에서 특정 블록만을 운영 파일 등으로 복사해 올 때만 조심해서 사용해야 합니다.

 

 

 

 

 

 

 

 

 

BBED (Block Browser and Editor) 를 활용한

Block Recovery

 

1. BBED 를 통한 Delete 장애 복구

- 데이터가 변경된 block 을 찾아서 수정하는 실습

 

Step 1. 실습용 테이블 생성

No

Name

Price

01

APPLE

100

02

ORANGE

200

 

SQL> create table fruit(no number, name varchar2(10),price varchar2(10))

2 tablespace test1;

 

Table created.

 

SQL> select rowid,no,name,price

2 from fruit;

 

ROWID NO NAME PRICE

------------------ ---------- -------------------- ------------------------

AAACkzAAHAAAAGQAAA 1 APPLE 100

AAACkzAAHAAAAGQAAB 2 ORANGE 200

 

Step 2. Rowed 로 위 데이터가 들어있는 블록 주소 찾기

 

SQL> select dbms_rowid.rowid_block_number('AAACkzAAHAAAAGQAAA')

2 from fruit;

 

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAACKZAAHAAAAGQAAA')

-----------------------------------------------------------------------------

400

400

 

- 두 건의 데이터 모두 400번 블록에 들어가 있다는 것이 조회 됩니다.

 

- 이제 BBED 로 400번 블록을 확인해 보겠습니다.

BBED> set dba 7,400

DBA 0x01c00190 (29360528 7,400)

 

BBED> show

FILE# 7

BLOCK# 400

OFFSET 342

DBA 0x01c00190 (29360528 7,400)

FILENAME /dev/raw/raw30

BIFILE bifile.bbd

LISTFILE /home/oracle/filelist.log

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 100

COUNT 50

LOGFILE log.bbd

SPOOL No

 

BBED> find /c APPLE

File: /dev/raw/raw30 (7)

Block: 400 Offsets: 8179 to 8191 Dba:0x01c00190

------------------------------------------------------------------------

4150504c 45033130 300106b0 5e

 

<32 bytes per line>

BBED>

- 이제 ORANGE 도 겁색해 보겠습니다.

BBED> set offset 0

OFFSET 0

BBED> set dba 7.400

BBED-00205: illegal or out of range DBA (File 0, Block 7)

 

BBED> set offset 0

OFFSET 0

 

BBED> set dba 7,400

DBA 0x01c00190 (29360528 7,400)

 

BBED> find /c ORANGE

File: /dev/raw/raw30 (7)

Block: 400 Offsets: 8162 to 8191 Dba:0x01c00190

------------------------------------------------------------------------

4f52414e 47450332 30302c01 0302c102 05415050 4c450331 30300106 b05e

 

<32 bytes per line>

 

BBED> dump /v dba 7,400 offset 8162

File: /dev/raw/raw30 (7)

Block: 400 Offsets: 8162 to 8191 Dba:0x01c00190

-------------------------------------------------------

4f52414e 47450332 30302c01 0302c102 l ORANGE.200,.....

05415050 4c450331 30300106 b05e l .APPLE.100...^

 

<16 bytes per line>

- 위 내용을 보면 offset 8162 에 ORANGE, 8179에 APPLE 이 저장되어 있습니다.

- 또한 find 를 한 후 다시 찾기 위해서는 파일 포인터를 위로 올려주기 위해 set offset 0 으로 포인터를 옮겨 줘야 합니다.(파일시스템을 이해하신다면 무슨 말씀이신지 알 거라고 생각해요)

Step 3. APPLE 를 delete 한 후 commit 수행한 후 block 내용 다시 조회

- 터미널 1 작업 -

SQL> select * from fruit;

 

NO NAME PRICE

---------- -------------------- --------------------

1 APPLE 100

2 ORANGE 200

 

SQL> delete from fruit where no=1;

 

1 row deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from fruit;

 

NO NAME PRICE

---------- -------------------- --------------------

2 ORANGE 200

 

- 터미널 2 작업 -

- 위 작업을 수행 한 후 다른 터미널에서 BBED 를 실행해서 블록을 살펴 봅니다.

BBED> set offset 0

OFFSET 0

 

BBED> find /c APPLE

File: /dev/raw/raw30 (7)

Block: 400 Offsets: 8179 to 8191 Dba:0x01c00190

------------------------------------------------------------------------

4150504c 45033130 300106b0 5e

 

<32 bytes per line>

 

- 다음과 같이 아직 BLOCK 에는 APPLE 데이터가 삭제되지 않고 남아있는 것을 알 수 있습니다.

- 이유 ?

à DML 을 수행하면 오라클은 해당 블록에서 직접 데이터를 지우지 않고 해당 ROW 에 사용하지 않는다는 표시만 수행하기 때문이며 FREESPACE 에는 빈 곳으로 표시 됩니다.

à 이렇게 해당 데이터가 DELETE 되었다는 표시는 해당 ROW 의 Header 부분에 표시됩니다.

Row Header 는 Row flag, lock byte(ITL entry), Column Count 로 구성되는데 제일 먼저 있는 Row flag 부분은 해당 row 의 상태를 표시하는 1byte 로 구성이 됩니다.

 

 

Cluster

Key

Cluster

Table

Member

Head of

Row

Piece

Deleted

First data

piece

Last

Data

Piece

1st

Column

Las

Column

128

64

32

16

8

4

2

1

 

- 위 표의 값들을 계싼해서 Row Header 정보를 분석하면 해당 row 가 어떤 상태인지 알 수 있습니다.

- 일반 테이블에서 특정 row 가 삭제된다면 32 + 16 + 8 + 4 = 60 이 되어 16 진수로 0x3c 값이 됩니다.

- 위 결과를 통해 APPLE 는 offset 8179 에 있었는데 row header 8172 임을 알 수 있으며 row header 의 값이 0x3c 로 delete 된 것을 알 수 있습니다.

- 또 ORANGE 는 OFFSET 8162 에 있으며 ROW HEADER 는 8155 임을 알수 있으며 값이 0x2c로 데이터가 존재한다는 것도 알 수 있습니다.

 

Step 4. BBED 로 복구한 후 조회하기

- 터미널 1 작업 -

- 복구 원리는 8172의 0x3c 값을 0x2c 값으로 변경하고 저장하면 됩니다.

 

BBED> sum dba 7,400

Check value for File 7, Block 400:

current = 0x08d7, required = 0x08c7

 

BBED> sum dba 7,400 apply

Check value for File 7, Block 400:

current = 0x08c7, required = 0x08c7

 

BBED> p *kdbr[0]

rowdata[17]

-----------

ub1 rowdata[17] @8172 0x2c

- 터미널 2 작업 -

 

- 아직 복구 전 값이 나옵니다.

 

SQL> alter tablespace test1 offline;

 

Tablespace altered.

 

SQL> alter tablespace test1 online;

 

Tablespace altered.

 

SQL>

 

 

 

 

 

 

 

 

1. DBMS_REPAIR Package 를 이용한 Data Block Recovery
2. DBMS_REPAIR Package 를 이용한 Index Block Recovery

DBMS_REPAIR Package 를 이용한 Block Recovery

 

- DBMS_REPAIR Package? -

à 오라클 8i 버전부터 등장한 Block Corruption 을 발견하고 복구하는 패키지

이 패키지는 Table Block 과 Index Block 을 조사하여 문제가 있는 Block 을 수정해주는 Data corruption repair 패키지를 가지고 있으며 sys 계정으로 작업해야 합니다.

à Corrupted block 을 recover 해주는 것이 아닌 mark 작업만을 수행하여 해당 블록을 Access 하는 것을 막아 줌으로서 전체 Data file 을 사용가능 하게 해주는 것입니다.

 

- DBMS_REPAIR 의 제약 사항 및 한계점 -

à LOB 나 Cluster Index 는 지원하지 않습니다.

à DUMP_ORPHAN_KEYS 프로시저는 Bitmap Index, Function-Based Index 는 지원하지 않으며 3,950 bytes 이상은 지원하지 못합니다.

 

- DBMS_REPAIR 설정-

à Block Corruption 찾아내기

- 관련 패키지 (CHECK_OBJECT, FIX_CORRUPT_BLOCK)

- 위 패키지들을 사용하려면 ADMIN_TABLE 이 실행 되어야 합니다.

à REPAIR_TABLE 생성

SQL>conn / as sysdba;

Connected.

SQL> begin

2 dbms_repair.admin_tables(

3 table_name => 'REPAIR_TABLE',

4 table_type => dbms_repair.repair_table,

5 action => dbms_repair.create_action,

6 tablespace => 'USERS');

7 end;

8 /

 

PL/SQL procedure successfully completed.

 

- repair_table or dba_repair_table 모두 사용 가능합니다.

 

à Orphan-key-table 찾아내기

- 장애 난 테이블과 관련 있는 다른 Object 를 저장하는 Table : 관련 인덱스나 FK 등의 정보를 저장하는 곳.

- 테이블 검사를 하다가 관련있는 인덱스 등이 문제가 있을 수 있기 때문에 미리 생성해 둡니다.

SQL>

1 begin

2 dbms_repair.admin_tables(

3 table_name => 'ORPHAN_KEY_TABLE',

4 table_type => dbms_repair.orphan_table,

5 action => dbms_repair.create_action,

6 tablespace => 'USERS');

7* end;

PL/SQL procedure successfully completed.

 

à DB_Block_checking = true;

- 모든 블록을 체크하기 위한 파라미터

- Overhead 가 발생할 수 있음

- False 일 경우 System Tablespace 만 체크하고 나머지는 Skip 함

-alter system set dba_block_checking=true scope=spfile;

 

- DBMS_REPAIR 실행-

Step 1. db_blcok_checking=true 설정한 후 재 시작 수행

SQL>show parameter db_block_checking;

SQL>alter system set db_blcok_checking=true;

 

Step 2. Block Corruption 발생

- Test 용 Tablespace 생성

- Test1 을 생성합니다.

 

- test1 Tablespace 에 Table 생성 후 데이터를 입력 합니다.

 

- Raw Device 기반이기 때문에 offline 시킨 후 dd 명령어로 복사를 수행했습니다.

- 사용자 환경에 맞게 복사해 주세요

- WinSCP 란 어플리케이션을 이용하여 Windows 로 파일을 복사합니다.

- 이는 파일을 수정하기 위해 Windows 용 프로그램을 사용하기 위해서 그런 것 입니다. RHEL 용 프로그램으로 수정하셔도 상관 없습니다.

- 저는 Ultra Editor 를 사용하여 다음의 ASCII 코드를 찾아 다음의 16진수 메모리를 수정해 줬습니다.

 

 

- 그리곤 다시 원래 경로로 복사

 

- Test1 Tablespace 를 온라인 시키려 하니 복구가 필요하다고 나옵니다.

SQL>Recover tablespace test1;

SQL>alter tablespace test1 online;

- 그리곤 scott.tt910 테이블 데이터들을 조회하려고 하니 Block Corruption 이 발생했다고 나옵니다.

 

 

 

Step 3. DBMS_REPAIR 를 사용하여 에러를 찾습니다.

 

- 위 화면에서 장애 블록을 찾은 것을 확인 할 수 있습니다.

 

- 장애 블록에 대한 자세한 정보를 알아보기 위해서 다음과 같은 쿼리를 수행합니다.

 

 

Step 4. Corrupt 된 블록을 Fix 하겠습니다.

- 이 부분은 corrupt 된 블록을 찾아서 corrupt 되었다고 marking 하는 부분인데 check_object 부분에서 자동으로 해서 다시 안 하셔도 상관 없습니다.

SQL> set serveroutput on

SQL> declare n_fix int;

2 begin

3 n_fix:=0;

4 dbms_repair.fix_corrupt_blocks (

5 schema_name => 'SCOTT',

6 object_name => 'TT910',

7 object_type => dbms_repair.table_object,

8 repair_table_name => 'REPAIR_TABLE',

9 fix_count => n_fix);

10 dbms_output.put_line('fix_count:'||to_char(n_fix));

11 end;

12 /

fix_count:0 ß check_object 부분에서 해서 여기서는 0으로 나옵니다.

PL/SQL procedure successfully completed.

 

Fix 를 한다는 것은 해당 블록에 Corruption 이 발생하여 사용 못하니 더 이상 읽지도 쓰지도 말라는 표시를 한다는 뜻 입니다. 즉 블록 안에 있는 데이터를 복구하는 것이 아닌 Marking 작업만 수행한다는 뜻입니다.

Fix 를 하고 난 후에 해당 테이블에 Select 를 수행 하겠습니다.

- 여전히 해당 블록에 문제가 있다고 나오고 select 가 정상적으로 수행이 안됩니다. 이는 데이터 파일에 수많은 블록에 데이터가 들어있다고 가정 할 경우 1개의 block 에 corrupt 가 발생할 경우 전체 데이터를 읽어 오지 못하는 상황이 된다는 뜻입니다. 나머지 Data 를 살리기 위해서 다음의 작업을 수행 하시면 됩니다.

 

Step 5. Corrupt 된 Block 을 Skip 하도록 설정

1 begin

2 dbms_repair.skip_corrupt_blocks (

3 schema_name => 'SCOTT',

4 object_name => 'TT910',

5 object_type => dbms_repair.table_object,

6 flags => dbms_repair.skip_flag);

7* end;

PL/SQL procedure successfully completed.

SQL>

 

정상적으로 Select 가 수행 되는 것을 알 수 있지만 Block 에 존재했던 Data는 사라진 것을 알 수 있습니다.

- Skip corrupt 설정된 것을 확인하기 위해서는 아래의 쿼리를 수행하시면 됩니다.

DBVerify 실행하기

 

1. 특정 파일 검사하기

- Raw Device

$dbv file=/dev/raw/raw28

- ASM

$dbv file=/home/oracle/oradata/testdb/example.dbf

- 의미

Total Pages Examined : 6400

Total Pages Processed (Data) : 10

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 14

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 6376

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

 

Highest block SCN :

테스트 한 총 블록의 개수

테스트 한 총 테이블 블록 개수

문제가 있는 블록 개수

테이블이나 인덱스 블록 개수

문제가 있는 블록 개수

테이블이나 인덱스 외 다른 블록 개수

 

 

비어있는 블록 개수

문제가 있어서 Corrupt Marked 된 수

다른 사용자가 먼저 데이터 변경이 일어나 DBV를 하기위해 다시 읽은 블록 수

 

2. 특정 세그먼트만 검사하기

SQL> select sum(bytes)/1024/1024 as MB from dba_segments

2 where segment_name='TT70';

SQL> select t.ts#,s.header_file,s.header_block

2 from v$tablespace t,dba_segments s

3 where s.segment_name='TT70'

4 and t.name=s.tablespace_name;

SQL> !dbv userid=system/oracle segment_id=5.5.11

- 이를 이용하면 특정 Table 만 점검 할 수 있어 시간을 절약할 수 있습니다.

 

3. DML 도중 강제 OFFLINE 된 DATA FILE 점검하기

- session 1 à 특정 테이블을 업데이트 수행

- session 2 à 업데이트 수행중인 테이블 스페이스를 강제로 offline 시킴

- 해당 데이터파일과 해당 Segment 를 DBV 로 검사하기

 

 

 

 

터미널 1

 

터미널2

 

 

 

- Offline 된 것을 확인 할 수 있습니다.

 

- DBV 로 파일을 검사하면 이상이 없다고 나옵니다.

 

- 특정 세그먼트를 검사하려 하지만 offline 이기 때문에 오류가 발생하는 것을 확인 할 수 있습니다.

 

 

테이블스페이스를 복구 한 후 온라인 시킵니다.

- 다음을 통해서 알 수 있는 것은 분명히 Data file 에 문제가 발생하여 복구가 필요한 경우 였지만 DBV 로는 확인을 할 수 없었던 점 입니다. DBV 는 Block 에 직접적인 문제가 없는 경우에는 문제점을 찾지 못하는 경우도 발생 합니다.

RAC 복구 (Redo log file 전체 손상)

 

Case :

- Redo log file 전체 손상

- Archive log mode

- Begin Backup 존재

 

작업 순서

  1. 모든 노드에서 DB 정상 종료 후 Begin Backup 수행
  2. DB Open 후 Test 용 데이터 생성
  3. 장애 발생 후 Shutdown immediate 로 정상 종료
  4. Backup File 복원
  5. Archive file 복사
  6. 복구 수 resetlogs 로 DB open

 

Step 1. Begin Backup 수행

SQL> alter tablespace system begin backup;

SQL> !dd if=/dev/raw/raw6 of=/data/backup/open/raw6_system bs=8k

SQL> alter tablespace system end backup;

 

Step 2. DB Open 후 Test용 데이터 생성

- 데이터 삽입 후 commit 그리고 Switch 발생

 

-다시 데어터 삽입 후 commit 하지만 Switch 를 발생 안시킵니다.

 

 

Step 3 이제 redo log file 에 장애를 발생 시키겠습니다.

그리곤 정상 종료

SQL>shutdown immediate;

- 강제 종료 됩니다.

Stet 4. 백업 Data 파일 복원(open/close 상관 없음)

 

Step 5. Archive File 복사

- Archive 파일이 대단히 많아야 정상이지만 Backup 을 받은 후 Archive 파일을 정리 해준 것 같다.

 

Step 6. 복구 후 resetlogs 로 Open

 

- 3 을 제외한 나머지는 복구 완료 되었습니다.

- 당연한 얘기지만 log switch 가 발생되지 못했고 shutdown immediate 를 하여 정상 종료를 시도 했지만 terminated 되었기 때문에 archive log 파일이 생성되지 못했던 데이터에 대해서는 복구가 되질 않았습니다.

 

RAC (Archive mode 에서 장애 복구)

 

Case :

- Offline 안되는 테이블 스페이스 장애 발생 (Archive 파일이 필요한 경우) – system tablespace

- Backup 파일 존재

 

- 장애 발생 상황

- tt03 테이블을 system 테이블스페이스에 생성 후 Data를 입력 합니다.

- 데이터를 insert 한 후 log switch 를 발생 시킵니다.

- /dev/zero 파일로 덮어 씌웁니다.

- 잠시 후 인스턴스가 강제 종료되는 것을 alert log 를 통해 확인 할 수 있습니다.

- 복구

1. 클러스터를 확인하면 오류로 인해 강제 종룔 된 것을 확인 할 수 있습니다.

$crs_stat -t.

2. 노드 2 에서 아카이브 파일을 복사해 옵니다.

3. 복구를 수행합니다.

SQL>recover database;

- auto

SQL>alter database open;

- 양 쪽 노드 모두 Open 시킨 후 확인 합니다.

'Oracle > Backup&Recover' 카테고리의 다른 글

DBVerify 실행하기  (0) 2011.10.19
RAC 복구 (Redo log file 전체 손상)  (0) 2011.10.19
RAC (Archive mode 에서 장애 복구 2)  (0) 2011.10.18
RAC (Archive mode 에서 장애 복구 1)  (0) 2011.10.18
Raw Device 백업(Cold backup)  (0) 2011.10.18

RAC (Archive mode 에서 장애 복구 2)

 

Case :

- Offline 되는 테이블 스페이스 장애 발생 (Archive 파일이 필요한 경우)

- Backup 파일 존재

 

- 장애 발생 상황

- tt02 테이블을 ts_new 테이블스페이스에 생성 후 Data를 입력 합니다.

- 데이터를 insert 한 후 log switch 를 발생 시킵니다.

- /dev/zero 파일로 덮어 씌웁니다.

- alter tablespace ts_new offline immediate; 시킨 후 복구를 수행 합니다.

 

- 복구

1. 파일을 백업 파일로부터 복원 합니다.

2. 복구를 시도 하지만 실패 하는 것을 확인 할 수 있습니다.

3. 복구를 수행합니다.

SQL>recover tablespace ts_new;

- auto

SQL>alter tablespace ts_new online;

 

4. 만약 3번 과정에서 archive log 파일이 필요하다는 Message 가 나올 수 있다.

ORA-00308: cannot open archived log '/경로/xx.dbf' ß 이 Message 는 현재 노드에 Archive log 파일이 존재 하지 않기 때문에 발생하는 것으로 해당 노드에서 Archive log 파일을 현재 복구작업을 하는 노드의 log_archive_dest_x 경로로 복사해 와야 한다.

+ Recent posts