— b: 블럭번호

select segment_name, segment_type

from dba_extents

where file_id = f and

b between block_id and block_id + blocks – 1;

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;

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

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

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)

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.

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

from dba_extents

where file_id = 7 and

15 between block_id and block_id + blocks – 1;

SEGMENT_NAME          SEGMENT_TYPE

——————– ——————

T1                 TABLE

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

X

1002 rows selected.

SQL> select * from t1 where rowid =’AAARr8AAHAAAAAPAAA’;

C1

$ 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

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

Table dropped.

l  Imp test/test file=test.dmp log=test_imp.log tables=t1

Sql> select * from t1;

X

X

1002 rows selected.

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로 수행

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => ‘REPAIR_TABLE’,

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => ‘&tablespace_name’);

END;

/

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;

/

from REPAIR_TABLE;

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;

/

where table_name in (select distinct object_name from repair_table);

—- 추출된 index를 rebuild 해준다

–    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