13 Block Corruption and Repair
- 데이터 Block의 Corruption 발생 시 Recovery 방법
- DBVerify를 이용한 Block Recovery
- DBMS_REPAIR 이용한 Block Recovery
- BBED를 이용한 Block Recovery
13.1 DBVerify를 이용한 Block 관리
- DBVerify 유틸리티
- 오라클에서 제공하는 data file block과 Index file block, undo block 등을 점검해 주는 유틸리티
- Database가 Open 상태에서 사용(DB의 중단없이 점검 가능)
- 단, 점검 중인 데이터파일은 Read-Only가 되기 때문에 만약 점검 중일 때 DML 작업이 발생하면 잠시 작업이 중단되었다가 다시 실행
- 사용 문법: dbv file=file_name options
- 주요옵션들
| 옵션명 | 설명 |
|---|
| FILE | 점검해야 할 파일명 |
| END | 점검을 종료할 블록 번호, 기본값은 해당 파일의 마지막 블록 |
| BLOCKSIZE | 점검하기를 원하는 파일의 BLOCK 크기, 기본값은 2048(2k) |
| LOGFILE | 점검 결과를 저장할 파일명, 기본값은 'NONE'이며 검사 결과를 화면에 출력 |
| FEEDBACK | 0이상의 숫자로 설정할 수 있으며(기본값은 0초) 검사가 진행되는 동안에 화면에 .(dot)일 찍어서 진행 과정을 표시 |
| PARFILE | 파라미터 파일명 |
| USERID | ASM기반의 파일을 점검할 경우 ASM 인스턴스에 접속해야 하기 때문에 반드시 USERID를 사용 |
| SEGMENT_ID | 특정 세그먼트(TABLE, INDEX, UNDO)만 골라서 검색 가능, 9i이상에서만 사용 |
- DBV의 주요 특징들
- Block Level에서의 점검만 가능, Analyze table...validate structure와 같이 테이블과 인덱스간의 불일치같은 문제는 점검 못함
- 오직 데이터 파일만 점검, 즉 Redo log file이나 Control file은 점검할 수 없음
- ASM 파일까지 점검 가능, 단 ASM 인스턴스에 로그인할 수 있는 계정정보 입력 필요
- 특정 데이터 파일이 아닌 세그먼트 단위로 검사를 할 수 있음
- DBVerify 실행하기
- 특정 파일 검사하기
$ dbv file=/sw/oracle/oradata/TEST/example01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Dec 24 23:42:26 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /sw/oracle/oradata/TEST/example01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 44320 <<< 테스트한 총 블록 개수
Total Pages Processed (Data) : 12038 <<< 테스트한 총 테이블 블록 개수
Total Pages Failing (Data) : 0 <<< 문제가 있는 블록 개수
Total Pages Processed (Index): 2167 <<< 테스트한 총 인덱스 블록 개수
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3129 <<< 테이블이나 인덱스 외 다른 블록 개수
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 26986 <<< 비어있는 블록 개수
Total Pages Marked Corrupt : 0 <<< 문제가 있어서 corrupt marked된 블록 개수
Total Pages Influx : 0 <<< 다른 사용자가 먼저 데이터를 변경하고 있어서 DBV를 하기 위해 다시 읽은 블록 개수
Total Pages Encrypted : 0
Highest block SCN : 4618866 (0.4618866)
- 특정 세그먼트만 검사하기
SQL> SELECT T.TS#, S.HEADER_FILE, S.HEADER_BLOCK
FROM V$TABLESPACE T, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME='TEST01'
AND T.NAME=S.TABLESPACE_NAME;
TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
4 4 522 <<< segment_id
$ dbv userid=scott/tiger segment_id=4.4.522
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Dec 25 01:29:43 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : SEGMENT_ID = 4.4.522
DBVERIFY - Verification complete
Total Pages Examined : 311552
Total Pages Processed (Data) : 309106
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 791
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 1654
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1792249 (0.1792249)
- Database내 전체 데이터파일을 검사하는 dbv script
set feedback off
set head off
set echo off
set linesize 200
set pagesize 3000
spool /home/oracle/dbv.sh
SELECT '!dbv file='||name||' blocksize='||block_size||' logfile='||substr(name,instr(name,'/',-1,1)+1)||'.'||file#||'.log'
FROM V$DATAFILE
/
spool off
@/home/oracle/dbv.sh
SQL> @dbv.sh
!dbv file=/sw/oracle/oradata/TEST/system01.dbf blocksize=8192 logfile=system01.dbf.1.log
!dbv file=/sw/oracle/oradata/TEST/sysaux01.dbf blocksize=8192 logfile=sysaux01.dbf.2.log
!dbv file=/sw/oracle/oradata/TEST/undotbs01.dbf blocksize=8192 logfile=undotbs01.dbf.3.log
!dbv file=/sw/oracle/oradata/TEST/users01.dbf blocksize=8192 logfile=users01.dbf.4.log
!dbv file=/sw/oracle/oradata/TEST/example01.dbf blocksize=8192 logfile=example01.dbf.5.log
!dbv file=/sw/oracle/oradata/TEST/TSDBAD000.dbf blocksize=8192 logfile=TSDBAD000.dbf.6.log
!dbv file=/sw/oracle/oradata/TEST/TSDBAI000.dbf blocksize=8192 logfile=TSDBAI000.dbf.7.log
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Dec 25 02:31:24 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
(검사 결과 나오는 이 부분이 logfile로 지정한 부분에 저장)
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Dec 25 02:31:37 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
(이하 생략)
13.2 DBMS_REPAIR 패키지를 이용한 Block Recovery
- DBMS_REPAIR 패키지
- block corruption을 detecting하고 repair하는 패키지(8i부터)
- table block과 index block을 조사하여 문제가 있는 block을 수정할 있으며, sys계정으으로 작업해야 함
- 장애난 블록에 데이터가 있었다면 그 블록 안에 있던 내용은 전부 손실
{note:title=DBMS_REPAIR 패키지는 corrupt block를 repair하는 것이 아니다}
장애난 블록을 찾아내서 해당 blcok을 장애로 mark하고 더 이상 사용 안 하게 막아주고,
해당 블록 장애 때문에 진행 안 되던 작업을 계속 할 수 있도록 도와주는 역할을 한다.
{note} - DBMS_REPAIR 패키지 프로시저
| 프로시저 | 설명 |
|---|
| ADMIN_TABLE 프로시저 | Block Repair를 하기 위해 필요한 관리 작업(create, drop, purge) 제공, SYS Schema 소유로 테이블 생성되고 손상이 발생한 블록들의 리스트를 저장 |
| CHECK_OBJECT 프로시저 | Table이나 Index의 Block Corruption을 체크하고 문제가 있는 블록은 ADMIN_TABLE에서 만든 Repair table에 기록 |
| DUMP_ORPHAN_KEYS 프로시저 | Corrupted된 블록들이 테이블과 관련된 것이라면 ADMIN_TABLE에서 생성한 곳에 기록이 되지만 index와 관련 있는 블록들이라면 이 테이블에 기록 |
| FIX_CORRUPT_BLOCKS 프로서저 | CHECK_OBJECT로 발견된 corrupt된 block을 mark |
| REBUILD_FREELISTS 프로시저 | object의 freelists를 재생성 |
| SEGMENT_FIX_STATUS 프로시저 | ASSM 기능을 사용하고 있는 bitmap index가 corrupt되었다면 이 프로시저가 fix |
| SKIP_CORRUPT_BLOCKS 프로시저 | table이나 index scan할 때 기존에 mark된 corrupt block들은 확인하지 않고 skip |
- DBMS_REPAIR의 제약 사항 및 한계점
- LOB이나 cluster index는 지원하지 않는다.
- DUMP_ORPHAN_KEYS프로시저는 bitmap index, function-based index는 지원하지 않으며, 3,950bytes 이상은 지원하지 못한다.
- DBMS_REPAIR 시작하기
- ADMIN_TABLE 프로시저를 실행시켜 REPAIR_TABLE 생성하기
SQL> begin
dbms_repair.admin_tables(
table_name=>'REPAIR_TABLE',
table_type=>dbms_repair.repair_table,
action=>dbms_repair.create_action,
tablespace=>'USERS');
end;
/
PL/SQL procedure successfully completed.
SQL> desc repair_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
- ADMIN_TABLE 프로시저를 실행해서 ORPHAN-KEY-TABLE 생성하기
SQL>begin
dbms_repair.admin_tables(
table_name=>'ORPHAN_KEY_TABLE',
table_type=>dbms_repair.orphan_table,
action=>dbms_repair.create_action,
tablespace=>'USERS');
end;
/
PL/SQL procedure successfully completed.
SQL> desc orphan_key_table; <<< 장애가 발생한 테이블과 관련이 있는 다른 object를 저장하는 곳 (인덱스, FK 등)
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
- DB_BLOCK_CHECKING=TRUE
SQL> show parameter db_block_checking;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string FALSE <<< SYSTEM Tablespace만 체크
SQL> alter system set db_block_checking=true;
System altered.
SQL> show parameter db_block_checking;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string TRUE <<< 모든 블록을 체크, overhead 발생
- DBMS_REPAIR 실습하기
SQL>SELECT * FROM SCOTT.TT910;
NO NAME
---------- ----------
1 AA
2 BB
SQL> ALTER TABLESPACE TEST10 OFFLINE;
Tablespace altered
(test10.dbf에 block corruption을 만든 후)
SQL> ALTER TABLESPACE TEST10 ONLINE;
Tablespace altered.
SQL> SELECT * FROM SCOTT.TT910;
SELECT * FROM SCOTT.TT910
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 12)
ORA-01110: data file 8: '/sw/oracle/oradata/TEST/test10.dbf' <<< 장애 발생
SQL> set serveroutput on;
declare n_corrupt int;
begin
n_corrupt := 0;
dbms_repair.check_object(
schema_name=>'SCOTT',
object_name=>'TT910',
repair_table_name=>'REPAIR_TABLE',
corrupt_count=>n_corrupt);
dbms_output.put_line('장애블록수: '||to_char(n_corrupt));
10 end;
11 /
장애블록수: 4
PL/SQL procedure successfully completed.
SQL> SET LINE 200;
COL OBJECT_NAME FOR A15
COL CORRUPT_DESCRIPTION FOR A20
COL REPAIR_DESCRIPTION FOR A50
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE
;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION
--------------- ---------- ------------ ---------- -------------------- --------------------------------------------------
TT910 12 6148 TRUE mark block software corrupt
TT910 13 6148 TRUE mark block software corrupt
TT910 14 6148 TRUE mark block software corrupt
TT910 15 6148 TRUE mark block software corrupt
SQL> set serveroutput on;
declare n_fix int;
begin
n_fix:=0;
dbms_repair.fix_corrupt_blocks(
schema_name=>'SCOTT',
object_name=>'TT910',
object_type=>dbms_repair.table_object,
repair_table_name=>'REPAIR_TABLE',
fix_count=>n_fix);
dbms_output.put_line('fix_count: '||to_char(n_fix));
11 end;
12 /
fix_count: 0 <<< check_object 부분에서 해서 여기서는 0
PL/SQL procedure successfully completed.
SQL> begin
dbms_repair.skip_corrupt_blocks(
schema_name=>'SCOTT',
object_name=>'TT910',
object_type=>dbms_repair.table_object,
flags=>dbms_repair.skip_flag);
end;
/
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SCOTT.TT910;
no rows selected
SQL> SELECT OWNER, TABLE_NAME, SKIP_CORRUPT
FROM DBA_TABLES
WHERE OWNER='SCOTT'
;
OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
SCOTT TT910 ENABLED <<< corrupt된 블록 skip
SCOTT DEPT DISABLED
SCOTT EMP DISABLED
SCOTT BONUS DISABLED
SCOTT SALGRADE DISABLED
SCOTT F_ANIMAL DISABLED
SCOTT TEST4 DISABLED
SCOTT FMEM DISABLED
SCOTT TEST01 DISABLED
SCOTT TEST02 DISABLED
SCOTT FRUITS DISABLED
SCOTT TEST3 DISABLED
12 rows selected.
13.3 BBED를 이용한 Block Recovery
BBED 사용 주의
BBED를 사용할 경우 해당 데이터파일의 모든 데이터가 손상될 수 있으며 아예 복구를 하지 못할 수 있다.
또한, 이 방법은 잘못되었을 경우 Oracle사에서 어떤 것도 보증해 주지 않는다.
※ 지은이의 당부: 실무에서 반드시 필요한 경우를 제외하고는 절대로 금지
- Block Browser and Editor의 약자로 block을 탐색하고 수정하는 유틸리티
- BBED Link 후 실행하기(리눅스기반)
$make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /sw/oracle/product/11gR2//rdbms/lib/bbed
t/11gR2//lib
gcc: error: /sw/oracle/product/11gR2//rdbms/lib/ssbbded.o: No such file or directory
gcc: error: /sw/oracle/product/11gR2//rdbms/lib/sbbdpt.o: No such file or directory
make: *** [/sw/oracle/product/11gR2//rdbms/lib/bbed] Error 1
※ 10g에서 복사해야 할 파일들
$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/lib/ssbbded.o
$ORACLE_HOME/rdbms/mesg/bbedus.msb
$ORACLE_HOME/rdbms/mesg/bbedus.msg
$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
Linking BBED utility (bbed)
(이하 생략)
$ bbed <<< bbed 실행
Password: blockedit <<< 기본암호
BBED: Release 2.0.0.0.0 - Limited Production on Fri Dec 25 11:08:56 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>
| 옵션명 | 설명 |
|---|
| 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 | 파라미터 파일명 |
| 명령어 | 설명 |
|---|
| help all | BBED 관련 도움말 |
| set dba | 작업하고자 하는 데이터파일과 블록을 지정, 여기에 지정될 데이터파일은 listfile에 등록되어 있어야 함 |
| set filename | 작업을 원하는 데이터파일을 이름으로 지정 |
| set file | 작업을 원하는 데이터파을 번호로 지정 |
| set block | 현재 설정되어 있는 파일에서 작업을 원하는 block을 지정 |
| set offset | 현재 작업하기 원하는 offset 번호를 지정 |
| set blocksize | 현재 작업하는 파일의 blocksize를 지정 |
| set listfile | 작업을 수행할 파일의 목록이 적혀 있는 list file을 지정 |
| set width | 현재 보이는 화면의 폭을 지정 |
| set count | dump명령어 수행시 화면에 보여줄 data block의 byte 수를 지정 |
| set ibase | 내부적으로 사용되는 값들의 표현식을 지정(기본값은 10진수, 16진수나 8진수로 변경 가능) |
| set mode | BBED 수행 mode를 설정 |
| show | 현재 설정되어 있는 내용들을 보여줌 |
| info | 현재 작업 중인 파일을 보여줌 |
| map | 현재 작업 중인 블록에 대한 자세한 정보를 보여줌 |
| (d)ump | block의 내용을 실제 dump해서 화면에 보여주는 명령어 |
| (p)rint | 현재 작업 중인 정보를 보여줌 |
| e(x)amin | DBA, filename, file, block, offset 등의 값을 정해진 포맷 형식으로 보여줌 |
| (f)ind | 데이터 검색 |
| copy | 블록 복사 |
| (m)odify | 블록내용 변경 |
| sum | 블록의 checksum을 계산해 주고 틀릴 경우 올바른 값으로 저장 |
| push/pop | 특정 파일의 블록을 메모리로 가져와서 수정하거나 조회(push:메모리의 stack부분에 특정 블록을 강제로 적재 / pop:push명령어로 적재시킨 메모리를 해제) |
| revert | 변경된 내용을 BBED가 실행되기 전 값으로 rollback |
| undo | 가장 마지막에 실행했던 변경 내용을 rollback |
| verify | 특정 파일의 블록의 무결성을 검사 |
| corrupt | corrupt된 블록을 mark |
- BBED로 block corruption 장애 복구하기
- DBMS_REPAIR 패키지와 BBED 차이점
| DBMS_REPAIR 패키지 | 해당 블록 전체를 corruption 처리해서 skip, 즉 해당 블록 안에 있는 전체 내용이 손상 |
| BBED | corruption이 발생한 row만 corruption 처리, 즉 그 블록 안에 있는 데이터들을 복구 |
SQL> SELECT * FROM SCOTT.TT920;
NO NAME
---------- ----------
1 AAAAA <<< corrupt될 row:AAAAA → AAzAA
2 BBBBB
SQL> SELECT ROWID, NO, NAME FROM SCOTT.TT920;
ROWID NO NAME
------------------ ---------- ----------
AAAWGdAAIAAAAAWAAC 1 AAAAA
AAAWGdAAIAAAAAWAAD 2 BBBBB
SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAWGdAAIAAAAAWAAC') FROM SCOTT.TT920;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAWGDAAIAAAAAWAAC')
---------------------------------------------------
22
22
SQL> ALTER TABLESPACE TEST10 OFFLINE;
Tablespace altered.
(test10.dbf에 block corruption을 만든 후)
SQL> ALTER TABLESPACE TEST10 ONLINE;
Tablespace altered.
SQL> SELECT * FROM SCOTT.TT920;
SELECT * FROM SCOTT.TT920
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 22)
ORA-01110: data file 8: '/sw/oracle/oradata/TEST/test10.dbf'
$ vi filelist.log
1 /sw/oracle/oradata/TEST/users01.dbf 2714501120
2 /sw/oracle/oradata/TEST/undotbs01.dbf 377487360
3 /sw/oracle/oradata/TEST/sysaux01.dbf 1101004800
4 /sw/oracle/oradata/TEST/system01.dbf 891289600
5 /sw/oracle/oradata/TEST/example01.dbf 363069440
6 /sw/oracle/oradata/TEST/TSDBAD000.dbf 67108864
7 /sw/oracle/oradata/TEST/TSDBAI000.dbf 67108864
8 /sw/oracle/oradata/TEST/test10.dbf 262144
$ vi bbed.par
blocksize=8192
listfile=/home/oracle/filelist.log
mode=edit
$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Dec 25 12:20:04 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 8,22
DBA 0x02000016 (33554454 8,22)
BBED> corrupt block 22
Block marked media corrupt.
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x02000016
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0xff <<< corrupt block의 sequence number를 0xff로 설정, 이 값을 다른 사용 가능한 값으로 변경하면 block corruption이 취소
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xde42
ub2 spare3_kcbh @18 0x0000
BBED> m /x 01 dba 8,22 offset 14
File: /sw/oracle/oradata/TEST/test10.dbf (8)
Block: 22 Offsets: 14 to 525 Dba:0x02000016
------------------------------------------------------------------------
010442de 00000100 00009d61 01001dd8 48000000 00000200 32001000 00020300
19008a11 0000fe14 c0001804 33000080 000092d4 48000900 0900a511 0000120a
c000da03 17000420 000022d8 48000000 00000000 00000001 0400ffff 1a006e1f
621f661f 00000400 8f1f861f 7a1f6e1f 06006000 66000000 66002300 89000000
89000a00 93000000 93000000 93000000 93000000 93000000 93000000 93000000
93000300 881f701f 581f401f 281f101f d21e981e 631e291e f41dc11d 8e1d5b1d
241ded1c b61c831c 501c1a1c e31bac1b 751b411b 0c1bd31a 891a471a 0c1ad119
9d196319 2b19f418 be188718 4b181318 dd17a517 70173c17 0b17d716 9f166c16
37160216 cd159815 66152c15 f914c614 93146014 2d14fa13 c3139013 54131d13
ea12b412 80124912 1412de11 a0116611 3111f710 c2108f10 5c102910 0f03d802
a1028a0f 570f210f ea0eb30e 7c0e460e 120edd0d a40d5a0d 180d6602 2b02700c
360cfe0b c70b910b 5a0b1e0b f301b10a 880ae909 c2099b09 74094d09 2609ff08
d808b108 8b086508 3f081908 f307cc07 a5077c07 db06b406 8d066606 3f061806
f105ca05 a3057d05 57053105 0b05e504 be049704 70046004 50044004 30042004
10040004 f003e003 d003a203 74034603 af05d005 f105d806 f9066c02 140302c1
0202c104 02c10201 800a5645 52535f4d 414a4f52 02c16102 c1020180 ffff0180
<32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x02000016
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01 <<< corrupt block의 sequence number를 0x01로 변경
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xde42
ub2 spare3_kcbh @18 0x0000
BBED> p tailchk
ub4 tailchk @8188 0x000006ff <<< sequence number는 마지막 8bytes에 해당 row의 corruption 여부를 등록, 해당 row의 값을 0x000006ff로 설정
BBED> m /x 01060000 dba 8,22 offset 8188
File: /sw/oracle/oradata/TEST/test10.dbf (8)
Block: 22 Offsets: 8188 to 8191 Dba:0x02000016
------------------------------------------------------------------------
01060000 <<< 06ff를 0x10로 변경
<32 bytes per line>
BBED> sum dba 8,22 apply
Check value for File 8, Block 22:
current = 0xde42, required = 0xde42
SQL> SELECT * FROM SCOTT.TT920;
SELECT * FROM SCOTT.TT920
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 22)
ORA-01110: data file 8: '/sw/oracle/oradata/TEST/test10.dbf'
SQL> alter tablespace test10 offline;
Tablespace altered.
SQL> alter tablespace test10 online;
Tablespace altered.
SQL> SELECT * FROM SCOTT.TT920;
NO NAME
---------- ----------
1 AAzAA
2 BBBBB
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 222
Current log sequence 224
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2253824 bytes
Variable Size 327158784 bytes
Database Buffers 83886080 bytes
Redo Buffers 4247552 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/sw/oracle/oradata/TEST/users01.dbf'
SQL> recover datafile '/sw/oracle/oradata/TEST/users01.dbf'
ORA-00279: change 4777227 generated at 12/25/2015 12:51:50 needed for thread 1
ORA-00289: suggestion : /sw/oracle/fast_recovery_area/TEST/archivelog/2015_12_25/o1_mf_1_274_%u_.arc
ORA-00280: change 4777227 for thread 1 is in sequence #274
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/sw/oracle/fast_recovery_area/TEST/archivelog/2015_12_25/o1_mf_1_274_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/sw/oracle/oradata/TEST/users01.dbf'
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4778143 <<< 정상적인 checkpoint scn 정보
SQL> select change# from v$recover_file;
CHANGE#
----------
4777227 <<< users01.dbf의 문제되는 예전 scn 정보
BBED> set filename '/sw/oracle/oradata/TEST/system01.dbf'
FILENAME /sw/oracle/oradata/TEST/system01.dbf
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0048e89f <<< last change SCN
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x359b84d5 <<< time of the last change
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000146
ub4 kcrbabno @504 0x000001e5
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc <<< checkpoint count
ub4 kcvfhcpc @140 0x000001a6
BBED> p kcvfhccc <<< a checkpoint checker value, which is one less than kcvfhcpc
ub4 kcvfhccc @148 0x000001a5
BBED> set filename '/sw/oracle/oradata/TEST/users01.dbf'
FILENAME /sw/oracle/oradata/TEST/users01.dbf
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0048e50b
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x359b8266
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000112
ub4 kcrbabno @504 0x00000257
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000170
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x0000016f
BBED> m /x 9fe84800 dba 1,1 offset 484
BBED-00209: invalid number (9fe84800)
BBED> m /x 9fe8 dba 1,1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /sw/oracle/oradata/TEST/users01.dbf (1)
Block: 1 Offsets: 484 to 995 Dba:0x00400001
------------------------------------------------------------------------
9fe84800 00000000 66829b35 01000000 12010000 57020000 10004b78 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02000001 03000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset +2
OFFSET 486
BBED> m /x 4800
File: /sw/oracle/oradata/TEST/users01.dbf (1)
Block: 1 Offsets: 486 to 997 Dba:0x00400001
------------------------------------------------------------------------
48000000 00006682 9b350100 00001201 00005702 00001000 4b780200 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000d00
0d000d00 01000000 00000000 00000000 00000200 00010300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x d5849b35 dba 1,1 offset 492
BBED-00209: invalid number (d5849b35)
BBED> m /x d584 dba 1,1 offset 492
File: /sw/oracle/oradata/TEST/users01.dbf (1)
Block: 1 Offsets: 492 to 1003 Dba:0x00400001
------------------------------------------------------------------------
d5849b35 01000000 12010000 57020000 10004b78 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
00000000 00000000 00000000 02000001 03000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset +2
OFFSET 494
BBED> m /x 9b35
File: /sw/oracle/oradata/TEST/users01.dbf (1)
Block: 1 Offsets: 494 to 1005 Dba:0x00400001
------------------------------------------------------------------------
9b350100 00001201 00005702 00001000 4b780200 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000d00 0d000d00 01000000
00000000 00000000 00000200 00010300 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 70010000 dba 1, 1 offset 140
File: /sw/oracle/oradata/TEST/users01.dbf (1)
Block: 1 Offsets: 140 to 651 Dba:0x00400001
------------------------------------------------------------------------
70010000 85859b35 6f010000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000
00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 7ac92131 01000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 9fe84800 00000000
d5849b35 01000000 12010000 57020000 10004b78 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
<32 bytes per line>
BBED> m /x 6f010000 dba 1,1 offset 148
File: /sw/oracle/oradata/TEST/users01.dbf (1)
Block: 1 Offsets: 148 to 659 Dba:0x00400001
------------------------------------------------------------------------
6f010000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553
45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 7ac92131 01000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 9fe84800 00000000 d5849b35 01000000
12010000 57020000 10004b78 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000
<32 bytes per line>
BBED> sum dba 1,1 apply
Check value for File 1, Block 1:
current = 0x1c1b, required = 0x1c1b
SQL> recover datafile '/sw/oracle/oradata/TEST/users01.dbf';
Media recovery complete.
SQL> alter database open;
Database altered.