1 개요 및 목적
- Blcok corruption이 발생으로 인한 오류 ORA-01578 발생원인과 종류 및 확인, 처리방법에 이문서의 목적을 둔다.
1.1 Block Corruption 종류
1.1.1 Logical Corruption
- 메모리 블록의 손상
- ORA-600 에러중 첫번째 인수가 3398 발생
1.1.2 Physical Corruption
- 물리적 블록의 손상
- ORA-1579 에러발생
- ORA-600 [3339]와 ORA-600[4519] 는 메모리와 디스크 손상 모두와 연관
2 BLOCK CORRUPTION 발생원인
- Disk 손상으로 인한 문제 발생
- 메모리상에서 이미 손상된 블록을 디스크상에 그대로 기록된 경우
- 블록이 데이터파일상의 기록될 위치를 잘못찾아 손상된경우(write blocks out of sequence)
- I/O controller 문제
- 동일한 디바이스에서 다른 블록을 읽어온경우 (O/S, H/W 문제인 경우 많음)
- Oracle이나 O/S bug
3 Block corruption 확인
- 손상된 블록 access시에 에러발생
| select * from t1; ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # x, block #xxxx) |
- dbv 확인
| dbv file=test1_01.dbf blocksize=8192 |
- Corruption된 블록의 object 확인
| — f: 화일번호
— b: 블럭번호 select segment_name, segment_type from dba_extents where file_id = f and b between block_id and block_id + blocks – 1; |
4 RECOVERY SCENARIO
4.1 RMAN을 이용한 RECOVERY
4.1.1 환경구성
| SQL> create tablespace test1 datafile ‘/u01/oradata/ORA/test1_01.dbf’ size 1M;
SQL> alter user test identified by test default tablespace test1; SQL> insert into t1 select * from dual connect by level <=1000; SQL> commit; SQL> alter system switch logfile; |
4.1.2 Rman backup 수행
| RMAN> report schema;
Report of database schema List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name —- ——– ——————– ——- ———————— 5 0 MGMT_TABLESPACE *** /u01/app/oracle/OracleHome/oms10g/oradata/mgmt.dbf 6 0 MGMT_ECM_DEPOT_TS *** /u01/app/oracle/OracleHome/oms10g/oradata/mgmt_ecm_depot1.dbf 7 1 TEST1 *** /u01/oradata/ORA/test1_01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name —- ——– ——————– ———– ——————– 1 20 TEMP 32767 /u01/oradata/ORA/temp01.dbf
RMAN> backup tablespace test1; Starting backup at 02-APR-10 piece handle=/u01/recovery/ORA/backupset/2010_04_02/o1_mf_nnndf_TAG20100402T150042_5vc20b5t_.bkp tag=TAG20100402T150042 comment=NONE Finished backup at 02-APR-10 RMAN> list backup RMAN> list backupset 11; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ——- —- — ———- ———– ———— ————— 11 Full 152.00K DISK 00:00:00 02-APR-10 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20100402T150042 Piece Name: /u01/recovery/ORA/backupset/2010_04_02/o1_mf_nnndf_TAG20100402T150042_5vc20b5t_.bkp List of Datafiles in backup set 11 File LV Type Ckp SCN Ckp Time Name —- — —- ———- ——— —- 7 Full 1621908 02-APR-10 /u01/oradata/ORA/test1_01.dbf |
4.1.3 BLOCK CORRUPT 작업
| l Segment 정보확인
select owner, segment_name, segment_type, header_file, header_block, blocks, tablespace_name from dba_segments where owner =’TEST’ and segment_name =’T1′;
l Data file 정보 select file#, ts#, rfile#, status, enabled, name, blocks, block_size from v$datafile where file#= 7 ;
l Corrupt 할 block 정보 확인 (15번 block corrution 예정) select c1, rowid, dbms_rowid.rowid_relative_fno(rowid) fno#, dbms_rowid.rowid_block_number(rowid) blk# from t1;
l 15번째 block corruption 한다 $ dd if=/dev/zero of=/u01/oradata/ORA/test1_01.dbf seek=15 count=1 bs=8192 conv=notrunc 1+0 records in 1+0 records out $ SQL> alter system flush buffer_cache; buffer cache를 flush 한다 System altered. SQL> select * from t1; select * from t1 * ERROR at line 1: ORA-08103: object no longer exists SQL> select * from t1 where rowid =’AAARr8AAHAAAAAMABd’; 12번 block rowid로 확인 C1 ———- X SQL> select * from t1 where rowid =’AAARr8AAHAAAAAPAAA’; 15번 rowid로 확인 select * from t1 where rowid =’AAARr8AAHAAAAAPAAA’ * ERROR at line 1: ORA-08103: object no longer exists
|
4.1.4 Block corruption 확인
4.1.4.1 Dbv를 이용한 확인
| $ dbv file=test1_01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 – Production on Fri Apr 2 16:40:08 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY – Verification starting : FILE = test1_01.dbf Page 15 is marked corrupt Corrupt block relative dba: 0x01c0000f (file 7, block 15) Completely zero block found during dbv: DBVERIFY – Verification complete Highest block SCN : 1621880 (0.1621880) |
4.1.4.2 dbms_repair package를 활용한 확인작업
| l 손상된 블록의 정보를 보관한 table, view 생성
SQL> exec dbms_repair.admin_tables(‘ REPAIR_TABLE’, table name DBMS_REPAIR.REPAIR_TABLE, table type DBMS_REPAIR.CREATE_ACTION, 작업 ‘USERS’); 테이블스페이스 PL/SQL procedure successfully completed. SQL> select * from tab where tname like ‘%REPAIR%’; TNAME TABTYPE CLUSTERID —————————— ——- ———- REPAIR_TABLE TABLE DBA_REPAIR_TABLE VIEW l 손상된 블록을 가진 테이블의 index 를 위한 테이블생성 SQL> exec dbms_repair.admin_tables( ‘ORPHAN_KEY_TABLE’, DBMS_REPAIR.ORPHAN_TABLE, DBMS_REPAIR.CREATE_ACTION ,’USERS’); PL/SQL procedure successfully completed. SQL> select * from tab where tname like ‘%ORPHAN%’; TNAME TABTYPE CLUSTERID —————————— ——- ———- ORPHAN_KEY_TABLE TABLE DBA_ORPHAN_KEY_TABLE VIEW l 손상된 테이블 SQL> SET SERVEROUTPUT ON SQL> VAR CORRUPT_COUNT NUMBER SQL> BEGIN 2 DBMS_REPAIR.CHECK_OBJECT 3 ( 4 ‘TEST’,’T1′,REPAIR_TABLE_NAME=>’REPAIR_TABLE’ 5 ,CORRUPT_COUNT=>:CORRUPT_COUNT); 6 end; 7 / BEGIN * ERROR at line 1: ORA-00600: internal error code, arguments: [4555], [0], [], [], [], [], [], [] ORA-06512: at “SYS.DBMS_REPAIR”, line 293 ORA-06512: at line 2 l 손상되지 않은 테이블 SQL> set serveroutput on DECLARE num_corrupt INT; BEGIN num_corrupt := 0; DBMS_REPAIR.CHECK_OBJECT ( SCHEMA_NAME => ‘&schema_name’, OBJECT_NAME => ‘&object_name’, REPAIR_TABLE_NAME => ‘REPAIR_TABLE’, corrupt_count => num_corrupt); DBMS_OUTPUT.PUT_LINE(‘number corrupt: ‘ || TO_CHAR (num_corrupt)); END; /SQL> 2 3 4 5 6 7 8 9 10 11 Enter value for schema_name: TEST old 5: SCHEMA_NAME => ‘&schema_name’, new 5: SCHEMA_NAME => ‘TEST’, Enter value for object_name: T2 old 6: OBJECT_NAME => ‘&object_name’, new 6: OBJECT_NAME => ‘T2’, number corrupt: 0 PL/SQL procedure successfully completed. |
4.1.4.3 RMAN을 이용한 확인
| RMAN> backup validate check logical database skip offline; offline datafile skip
Starting backup at 05-APR-10 input datafile fno=00007 name=/u01/oradata/ORA/test1_01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 SQL> select * from v$database_block_corruption; no rows selected validation check(RMAN) 작업전 SQL> SQL> / validation check(RMAN) 작업 후 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ———- ———- ———- —————— ——— 7 15 1 0 ALL ZERO |
4.1.5 RECOVERY
4.1.5.1 데이터 확인
- 위에서 7번파일의 15번째 블록이 손상된 것을 확인할수 있다.
- 과연 그곳에 데이터가 존재하는지는 확인해보자
| select segment_name, segment_type
from dba_extents where file_id = 7 and 15 between block_id and block_id + blocks – 1;
SEGMENT_NAME SEGMENT_TYPE ——————– —————— T1 TABLE |
4.1.5.2 데이터 복구
- Corrutpion된 block이 table이란 것을 위에서 확인했다
- Rman으로 복구작업을 진행한다.
| RMAN> blockrecover datafile 7 block 15;
Starting blockrecover at 06-APR-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=148 devtype=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00007 channel ORA_DISK_1: reading from backup piece /u01/recovery/ORA/backupset/2010_04_02/o1_mf_nnndf_TAG20100402T150042_5vc20b5t_.bkp channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/u01/recovery/ORA/backupset/2010_04_02/o1_mf_nnndf_TAG20100402T150042_5vc20b5t_.bkp tag=TAG20100402T150042 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished blockrecover at 06-APR-10 |
4.1.6 Recovery data 확인
| Sql> select * from t1;
X 1002 rows selected. SQL> select * from t1 where rowid =’AAARr8AAHAAAAAPAAA’; C1 |
4.2 Export backup을 이용한 Recovery
4.2.1 Export/import를 이용한 recovery
| l 정상적인 data export 작업 수행
$ exp test/test file=test_exp.dmp log=test_exp.log . . exporting table SYS_EXPORT_SCHEMA_01 11 rows exported . . exporting table T1 1002 rows exported . . exporting table T2 0 rows exported
l Datapump를 이용한 export Export: Release 10.2.0.1.0 – Production on Tuesday, 06 April, 2010 12:48:43 . . exported “TEST”.”T1″ 19.58 KB 1002 rows . . exported “TEST”.”T2″ 0 KB 0 rows
|
4.2.2 Block corruption 작업수행
- 1.3.과 같은 방법을 이용해서 block corruption을 수행한다.
- 1.4와 같은 방법으로 blcok corruption을 확인한다
4.2.3 Export 재수행
- T1 테이블이 정상적으로 export 되지 않는다.
| l TEST_corruption.dmp로 export 작업 수행
EXP-00091: Exporting questionable statistics. . . exporting table T1 EXP-00056: ORACLE error 8103 encountered ORA-08103: object no longer exists . . exporting table T2 0 rows exported . exporting synonyms |
4.2.4 Import를 통한 T1 테이블 Recovery
| SQL> drop table test.t1; 정상적으로 backup 받아진 파일 확인후 작업
Table dropped. l Imp test/test file=test.dmp log=test_imp.log tables=t1 Sql> select * from t1; X X 1002 rows selected. |
- Export 받기전에 DATA만 복구 가능함
- DATA가 변경이 없는경우에 사용가능
5 사용하지않는 block corruption 발생
- 백업후 기존의 T1테이블 삭제, 새로운 T1테이블 생성후 새로운 데이터 입력
- Block corruption을 발생시킨다..(데이터가 들어있지 않은 블록)
5.1.1 확인 및 수행 순서
| l 새로운 데이터 입력
l Block corrution 작업 l Dbv 확인 $ dbv file=test1_01.dbf blocksize=8192 DBVERIFY – Verification starting : FILE = test1_01.dbf Page 15 is marked corrupt Corrupt block relative dba: 0x01c0000f (file 7, block 15)
l Table인지 index인지 확인한다 select segment_name, segment_type from dba_extents where file_id = 7 and 15 between block_id and block_id + blocks – 1; l Index시 Rebuild 작업수행 l Table시 복구작업수행 l 데이터가 없을시 exp -> imp로 수행 |
6 Event(10210, 10211, 10231) 사용
- 8i 이전버전에서 block skip할 때 사용한다.
- Block corrupt으로 인해 table 전체를 읽지 못할 때
- Corruption 된 블록의 데이터가 필요치 않을경우
7 dbms_repair (8i 이후버전)
7.1 rapair table 생성
| BEGIN
DBMS_REPAIR.ADMIN_TABLES ( TABLE_NAME => ‘REPAIR_TABLE’, TABLE_TYPE => dbms_repair.repair_table, ACTION => dbms_repair.create_action, TABLESPACE => ‘&tablespace_name’); END; / |
7.2 block cuttuption schema, objcet 입력 (정보수집)
| set serveroutput on
DECLARE num_corrupt INT; BEGIN num_corrupt := 0; DBMS_REPAIR.CHECK_OBJECT ( SCHEMA_NAME => ‘&schema_name’, OBJECT_NAME => ‘&object_name’, REPAIR_TABLE_NAME => ‘REPAIR_TABLE’, corrupt_count => num_corrupt); DBMS_OUTPUT.PUT_LINE(‘number corrupt: ‘ || TO_CHAR (num_corrupt)); END; / |
- 수집된 정보 확인
| select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;
|
7.3 Block fixed
- Table block fix
| DECLARE num_fix INT;
BEGIN num_fix := 0; DBMS_REPAIR.FIX_CORRUPT_BLOCKS ( SCHEMA_NAME => ‘&schema_name’, OBJECT_NAME=> ‘&object_name’, OBJECT_TYPE => dbms_repair.table_object, REPAIR_TABLE_NAME => ‘REPAIR_TABLE’, FIX_COUNT=> num_fix); DBMS_OUTPUT.PUT_LINE(‘num fix: ‘ || to_char(num_fix)); END; / |
- table repair후 full scan 에러 지속시 인덱스 정보 확인
| Select index_name from dba_indexes
where table_name in (select distinct object_name from repair_table); |
- curruption data index 정보수집
| var key_count number SQL> begin 2 dbms_repair.dump_orphan_keys( 3 schema_name=>”, 4 object_name=>”, 5 object_type=>DBMS_REPAIR.INDEX_OBJECT, 6 repair_table_name=>’REPAIR_TABLE’, 7 orphan_table_name=>’ORPHAN_KEY_TABLE’, 8 key_count=>:key_count); 9 end; 10 / |
- curruption data index 정보확인
| select SCHEMA_NAME, INDEX_NAME, INDEX_ID, TABLE_NAME, KEYROWID from orphan_key_table;
—- 추출된 index를 rebuild 해준다 |
8 ORACLE BUG 로 인한 block corruption
| n Bug 4655520 Block corrupted during write not noticed 에서보면 아래 버젼에서 block corruption 이 발생
– 9.2.0.6 – 9.2.0.7 – 10.1.0.5 – 10.2.0.2 l 이것은 아래버전에서 fix되었습니다. – 9.2.0.8 (Server Patch Set) – 10.1.0.6 (Server Patch Set) – 10.2.0.3 (Server Patch Set)11g (Future version) n Bug 4411228 – Block corruption with mixture of file system and RAW files 발생가능한 버전 – 9.2.0.5 – 9.2.0.7 – 10.1.0.5 – 10.2.0.2 l fix된 버전 – 9.2.0.8 (Server Patch Set) – 10.1.0.6 (Server Patch Set) – 10.2.0.3 (Server Patch Set) – 11g (Future version) n Bug 5061091 Block recovery corrupts undo header when log_parallelism > 1 발생가능 버전 – 9.2.0.6 – 10.1.0.5 – 10.2.0.2 l fix된 버전 – 10.1.0.6 (Server Patch Set) – 10.2.0.3 (Server Patch Set) – 11g (Future version |