ITPub博客

首页 > 数据库 > Oracle > 没有备份的情况下如何处理logical & physical corrupt block

没有备份的情况下如何处理logical & physical corrupt block

原创 Oracle 作者:oliseh 时间:2015-07-14 19:39:09 0 删除 编辑

###生成测试表和索引
create tablespace tsind datafile '/oradata06/testaaaaa/tsind_1.dbf' size 32m;
create table t0609_2 tablespace TS0608 as select *from all_users where 0=1;
insert into t0609_2 select * from all_users where rownum<2;
commit;
alter table t0609_2 minimize records_per_block;
delete t0609_2;
commit;
insert into t0609_2 select *from all_users;
commit;

SYS@tstdb1-SQL> desc t0609_2;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 USERNAME                                                                            NOT NULL VARCHAR2(30)
 USER_ID                                                                             NOT NULL NUMBER
 CREATED                                                                             NOT NULL DATE

set linesize 150 pagesize 30
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),username,user_id,created from t0609_2;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) USERNAME                          USER_ID CREATED
------------------------------------ ------------------------------------ ------------------------------ ---------- -----------------
                                   9                                  140 XS$NULL                        2147483638 20150130 23:25:24
                                   9                                  140 SYSADM11                               57 20150324 19:03:42
                                   9                                  141 SCOTT4                                 55 20150202 11:43:52
                                   9                                  141 FSYSTEM                                56 20150220 07:09:08
                                   9                                  142 SCOTT3                                 54 20150201 21:01:12
                                   9                                  142 SCOTT2                                 53 20150201 21:01:12
                                   9                                  143 XDB                                    52 20150130 23:22:39
                                   9                                  143 CTXSYS                                 49 20150130 22:32:12
                                   9                                  144 REMOTE_SCHEDULER_AGENT                 48 20150130 16:41:15
                                   9                                  144 ANONYMOUS                              41 20150130 16:21:49
                                   9                                  145 MGMT_VIEW                              39 20150109 21:13:05
                                   9                                  145 SYSMAN                                 37 20150109 21:09:40
                                   9                                  146 SCOTT                                  36 20141209 15:17:00
                                   9                                  146 PPIP                                   35 20141202 16:06:22
                                   9                                  147 APPQOSSYS                              31 20141110 21:28:49
                                   9                                  147 DBSNMP                                 30 20141110 21:28:47
                                   9                                  148 ORACLE_OCM                             21 20141110 21:18:49
                                   9                                  148 DIP                                    14 20141110 21:18:04
                                   9                                  149 OUTLN                                   9 20141110 21:16:14
                                   9                                  149 SYSTEM                                  5 20141110 21:16:12
                                   9                                  150 SYS                                     0 20141110 21:16:12

create index ind_t0609_2_uid on t0609_2(user_id) tablespace tsind;

////////////
// 方法1:使用dbms_repair.skip_corrupt_block跳过physical corrupt
////////////
---BBED physical corrupt block 9/140
set filename '/oradata06/testaaaaa/ts0608_1.dbf'
set block 140

BBED> print chkval_kcbh
ub2 chkval_kcbh                             @16       0x7fd8

BBED> modify /x 0x8888
 File: /oradata06/testaaaaa/ts0608_1.dbf (0)
 Block: 140              Offsets:   16 to  135           Dba:0x00000000
------------------------------------------------------------------------
 88880000 01000000 00008e7c 6381b7a5 0b920000 00023200 02400088 000a0000
 00010343 00c14286 2d6c0700 20020000 6381b7d8 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00010002 ffff0016 1f2d1f51
 1f510000 00021f2d 1f470000 00000000 00000000 00000000

 <32 bytes per line>

alter system flush buffer_cache;

---create admin_tables
exec dbms_repair.admin_tables(table_name=>'REPAIR_0609_2',table_type=>dbms_repair.REPAIR_TABLE,action=>dbms_repair.CREATE_ACTION,tablespace=>'XDBTS');


---check_object

variable v_corrupt_count number;
exec DBMS_REPAIR.CHECK_OBJECT (schema_name=>'SYS',object_name=>'T0609_2',object_type=>dbms_repair.TABLE_OBJECT,repair_table_name=>'REPAIR_0609_2',corrupt_count=>:v_corrupt_count);
print :v_corrupt_count

V_CORRUPT_COUNT
---------------
              1


---repair_表内容,已被标记为corrupt

col REPAIR_DESCRIPTION format a30
col OBJECT_NAME format a10
set linesize 140
select object_id,relative_file_id,block_id,object_name,repair_description,marked_corrupt,check_timestamp,fix_timestamp from dba_REPAIR_0609_2;

 OBJECT_ID RELATIVE_FILE_ID   BLOCK_ID OBJECT_NAM REPAIR_DESCRIPTION             MARKED_COR CHECK_TIMESTAMP   FIX_TIMESTAMP
---------- ---------------- ---------- ---------- ------------------------------ ---------- ----------------- -----------------
     36476                9        140 T0609_2    mark block software corrupt    TRUE       20150609 15:58:10


---此时dump block 9/140的结果是:

Block dump from disk:
buffer tsn: 15 rdba: 0x0240008c (9/140)
scn: 0x0b92.6381b7d8 seq: 0x01 flg: 0x06 tail: 0xb7d80601
frmt: 0x02 chkval: 0x8888 type: 0x06=trans data
Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x000000011085DA00 to 0x000000011085DA14
11085DA00 06A20000 0240008C 6381B7D8 0B920106  [.....@..c.......]
11085DA10 88880000                             [....]
Hex dump of corrupt block


---因为执行完check_object后block已经被marked corrupt,所以再次执行fix_corrupt_blocks返回number of fixed block is 0

variable v_fix_count number;
exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS(schema_name=>'SYS',object_name=>'T0609_2',object_type=>DBMS_REPAIR.TABLE_OBJECT,repair_table_name=>'REPAIR_0609_2',fix_count=>:v_fix_count);
print :v_fix_count

V_FIX_COUNT
-----------
          0


---FTS访问返回0 rows,无论结果集是否包含corrupt block里的记录

SYS@tstdb1-SQL> select /*+ full(t0609_2) */ * from t0609_2;
select /*+ full(t0609_2) */ * from t0609_2
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 140)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'

SQL> select /*+ full(t0609_2) */ * from t0609_2 where user_id<60;
select /*+ full(t0609_2) */ * from t0609_2 where user_id<60
                                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 140)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'

SYS@tstdb1-SQL> select /*+ full(t0609_2) */ * from t0609_2 where user_id<30;
select /*+ full(t0609_2) */ * from t0609_2 where user_id<30
                                   *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 140)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'

---index访问时如果结果集不包含corrupt block里的记录,能正常返回,否则可能会部分rows或者0 rows
***结果集不在corrupt,正常返回所有满足条件的rows
SYS@tstdb1-SQL> select /*+ index(t0609_2,ind_t0609_2_uid) */ * from t0609_2 where user_id<57;

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
SYS                                     0 20141110 21:16:12
SYSTEM                                  5 20141110 21:16:12
OUTLN                                   9 20141110 21:16:14
DIP                                    14 20141110 21:18:04
ORACLE_OCM                             21 20141110 21:18:49
DBSNMP                                 30 20141110 21:28:47
APPQOSSYS                              31 20141110 21:28:49
PPIP                                   35 20141202 16:06:22
SCOTT                                  36 20141209 15:17:00
SYSMAN                                 37 20150109 21:09:40
MGMT_VIEW                              39 20150109 21:13:05
ANONYMOUS                              41 20150130 16:21:49
REMOTE_SCHEDULER_AGENT                 48 20150130 16:41:15
CTXSYS                                 49 20150130 22:32:12
XDB                                    52 20150130 23:22:39
SCOTT2                                 53 20150201 21:01:12
SCOTT3                                 54 20150201 21:01:12
SCOTT4                                 55 20150202 11:43:52
FSYSTEM                                56 20150220 07:09:08

***结果集部分包含在corrupt blocks,返回non-corrupt block所含rows的情况
SYS@tstdb1-SQL> select /*+index(t0609_2,ind_t0609_2_uid) */ * from t0609_2 where user_id<60;

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
SYS                                     0 20141110 21:16:12
SYSTEM                                  5 20141110 21:16:12
OUTLN                                   9 20141110 21:16:14
DIP                                    14 20141110 21:18:04
ORACLE_OCM                             21 20141110 21:18:49
DBSNMP                                 30 20141110 21:28:47
APPQOSSYS                              31 20141110 21:28:49
PPIP                                   35 20141202 16:06:22
SCOTT                                  36 20141209 15:17:00
SYSMAN                                 37 20150109 21:09:40
MGMT_VIEW                              39 20150109 21:13:05
ANONYMOUS                              41 20150130 16:21:49
REMOTE_SCHEDULER_AGENT                 48 20150130 16:41:15
CTXSYS                                 49 20150130 22:32:12
XDB                                    52 20150130 23:22:39
ERROR:
ORA-01578: ORACLE data block corrupted (file # 9, block # 140)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'

***结果集部分包含在corrupt blocks,返回0 rows的情况
SYS@tstdb1-SQL> select /*+ index(t0609_2,ind_t0609_2_uid) */ * from t0609_2 where user_id>50;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 9, block # 140)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'

---创建orphan_ table
exec dbms_repair.admin_tables(table_name=>'ORPHAN_IND_0609_2',table_type=>dbms_repair.ORPHAN_TABLE,action=>dbms_repair.CREATE_ACTION,tablespace=>'XDBTS');


---使用DBMS_REPAIR.DUMP_ORPHAN_KEYS检索出index中有多少条目对应于corrupt block里的记录

variable v_ind_corrupt number;
EXEC DBMS_REPAIR.DUMP_ORPHAN_KEYS (SCHEMA_NAME => 'SYS',OBJECT_NAME => 'IND_T0609_2_UID',OBJECT_TYPE => dbms_repair.index_object,REPAIR_TABLE_NAME => 'REPAIR_0609_2',ORPHAN_TABLE_NAME=> 'ORPHAN_IND_0609_2',KEY_COUNT =>:v_ind_corrupt);
print :v_ind_corrupt

V_IND_CORRUPT
-------------
            2

---ORPHAN_IND_0609_2表中记录了corrupt block里的rowid
col index_name format a15
col table_name format a8
col keyrowid format a20
col key format a25
set linesize 140
select index_name,index_id,table_name,table_id,keyrowid,key,dump_timestamp from ORPHAN_IND_0609_2;
INDEX_NAME        INDEX_ID TABLE_NA   TABLE_ID KEYROWID             KEY                       DUMP_TIMESTAMP
--------------- ---------- -------- ---------- -------------------- ------------------------- -----------------
IND_T0609_2_UID      36477 T0609_2       36476 AAAI58AAJAAAACMAAA   *BAAAAAAGxRYwMSUn/g       20150609 16:56:39
IND_T0609_2_UID      36477 T0609_2       36476 AAAI58AAJAAAACMAAB   *BAAAAAACwTr+             20150609 16:56:39

---在无法recover corrupt block的情况下可以使用skip_corrupt_blocks跳过
exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'SYS',object_name=>'T0609_2',object_type=>DBMS_REPAIR.TABLE_OBJECT,flags=>dbms_repair.SKIP_FLAG);


***注意skip_corrupt_block的动作是可逆的,可以使用NOSKIP_FLAG来重现ORA-01578

exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'SYS',object_name=>'T0609_2',object_type=>DBMS_REPAIR.TABLE_OBJECT,flags=>dbms_repair.NOSKIP_FLAG);
           
SYS@tstdb1-SQL> select owner,table_name,skip_corrupt from dba_tables where table_name='T0609_2';

OWNER                          TABLE_NAME                     SKIP_COR
------------------------------ ------------------------------ --------
SYS                            T0609_2                        ENABLED

---测试一下跳过corrupt之后的全表以及索引扫描
**以下FTS访问均没有ORA-01578出现
select /*+ full(t0609_2) */ * from t0609_2;
select /*+ full(t0609_2) */ * from t0609_2 where user_id<60;
select /*+ full(t0609_2) */ * from t0609_2 where user_id<30;

**以下INDEX访问也没有ORA-01578出现
select /*+ index(t0609_2,ind_t0609_2_uid) */ * from t0609_2 where user_id<57;
select /*+ index(t0609_2,ind_t0609_2_uid) */ * from t0609_2 where user_id<60;
select /*+ index(t0609_2,ind_t0609_2_uid) */ * from t0609_2 where user_id>50;

dbms_repair.skip_corrupt_blocks虽然能够方便的跳过corrupt block,但有可能带来index访问和FTS访问结果不一致的情况,同样的一条查询语句第一条走索引,第二条走全表扫描,得到的结果却不一样
SYS@tstdb1-SQL> select /*+ index(t0609_2,ind_t0609_2_uid) */ user_id from t0609_2 where user_id>56;

   USER_ID
----------
        57
2147483638

SYS@tstdb1-SQL> select /*+ full(t0609_2) */ user_id from t0609_2 where user_id>56;

no rows selected


为了避免产生不一致的结果,需要重建索引,重建索引之前可以选择将保存在索引中的键值先保存下来以尽可能挽回较多的数据,例如:

select /*+ index(t0609_2,ind_t0609_2_uid) */ user_id from t0609_2 minus select /*+ full(t0609_2) */ user_id from t0609_2;
   USER_ID
----------
        57
2147483638

---重建索引(注意必须在skip corrupt block的情况下才能重建索引,否则也会有ORA-01578)
exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'SYS',object_name=>'T0609_2',object_type=>DBMS_REPAIR.TABLE_OBJECT,flags=>dbms_repair.NOSKIP_FLAG);

SYS@tstdb1-SQL> alter index ind_t0609_2_uid rebuild online;
alter index ind_t0609_2_uid rebuild online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 140)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'

exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'SYS',object_name=>'T0609_2',object_type=>DBMS_REPAIR.TABLE_OBJECT,flags=>dbms_repair.SKIP_FLAG);

SYS@tstdb1-SQL> alter index ind_t0609_2_uid rebuild online;

Index altered.

SYS@tstdb1-SQL> select /*+ index(t0609_2,ind_t0609_2_uid) */ user_id from t0609_2 where user_id>56;

no rows selected

SYS@tstdb1-SQL> select /*+ full(t0609_2) */ user_id from t0609_2 where user_id>56;

no rows selected


////////////
// 方法2:使用dbms_repair.skip_corrupt_block跳过logical corrupt,和physical corrupt不同需要先使用fix_corrupt_blocks将logical corrupt block标记为soft corrupt block
////////////
###准备测试表
create tablespace ts0610 datafile '/oradata06/testaaaaa/ts0610_1.dbf' size 32m;

create table scott.t0610_1 tablespace ts0610 as select * from all_users;

alter system flush buffer_cache;

set linesize 120 pagesize 30
select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.t0610_1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                  12                                  131

###BBED修改kdbhnrow,从21改为40
BBED> set filename '/oradata06/testaaaaa/ts0610_1.dbf'
        FILENAME        /oradata06/testaaaaa/ts0610_1.dbf

BBED> set block 131
        BLOCK#          131

BBED> print kdbhnrow
sb2 kdbhnrow                                @126      21

BBED> set offset 126
        OFFSET          126

BBED> examine /h
kdbh.kdbhnrow                               @126    
-------------
 0x0015

BBED> modify /x 0x0028

BBED> sum apply
Check value for File 0, Block 131:
current = 0x0b22, required = 0x0b22

BBED> print kdbhnrow
sb2 kdbhnrow                                @126      40

###select不受影响
SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0610_1;

  COUNT(*)
----------
        21


###使用dbms_repair.check_object检测到有logical corrupt block

exec dbms_repair.admin_tables(table_name=>'REPAIR_0610_1',table_type=>dbms_repair.REPAIR_TABLE,action=>dbms_repair.CREATE_ACTION,tablespace=>'XDBTS');

variable v_corrupt_count number;
exec DBMS_REPAIR.CHECK_OBJECT (schema_name=>'SYS',object_name=>'T0609_2',object_type=>dbms_repair.TABLE_OBJECT,repair_table_name=>'REPAIR_0610_1',corrupt_count=>:v_corrupt_count);
print :v_corrupt_count

V_CORRUPT_COUNT
---------------
              1

---DBA_REPAIR_0610_1里的marked_corrupt=FALSE表示该block尚未被标记为soft corrupt
col REPAIR_DESCRIPTION format a30
col OBJECT_NAME format a10
set linesize 140
select object_id,relative_file_id,block_id,object_name,repair_description,marked_corrupt,check_timestamp,fix_timestamp from DBA_REPAIR_0610_1;
 OBJECT_ID RELATIVE_FILE_ID   BLOCK_ID OBJECT_NAM REPAIR_DESCRIPTION             MARKED_COR CHECK_TIMESTAMP   FIX_TIMESTAMP
---------- ---------------- ---------- ---------- ------------------------------ ---------- ----------------- -----------------
     36543               12        131 T0610_1    mark block software corrupt    FALSE      20150610 20:59:35         


###为避免logical corrupt的影响扩散,使用dbms_repair.fix_corrupt_blocks标记为soft corrupt block

variable v_fix_count number;
exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS(schema_name=>'SCOTT',object_name=>'T0610_1',object_type=>DBMS_REPAIR.TABLE_OBJECT,repair_table_name=>'REPAIR_0610_1',fix_count=>:v_fix_count);
print :v_fix_count
V_FIX_COUNT
-----------
          1

---marked_corrupt=TRUE
SYS@tstdb1-SQL> select object_id,relative_file_id,block_id,object_name,repair_description,marked_corrupt,check_timestamp,fix_timestamp from DBA_REPAIR_0610_1;

 OBJECT_ID RELATIVE_FILE_ID   BLOCK_ID OBJECT_NAM REPAIR_DESCRIPTION             MARKED_COR CHECK_TIMESTAMP   FIX_TIMESTAMP
---------- ---------------- ---------- ---------- ------------------------------ ---------- ----------------- -----------------
     36543               12        131 T0610_1    mark block software corrupt    TRUE       20150610 20:59:35 20150610 21:07:37


---marked corrupt后再次访问该表出现了ORA-01578

SYS@tstdb1-SQL> select * from scott.t0610_1;
select * from scott.t0610_1
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 131)
ORA-01110: data file 12: '/oradata06/testaaaaa/ts0610_1.dbf'

###从dbv的输出也可以看出一个block被标记为corrupt
oracle@jq570322b:/home/oracle>dbv file='/oradata06/testaaaaa/ts0610_1.dbf'

DBVERIFY: Release 11.2.0.3.0 - Production on Wed Jun 10 21:10:30 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oradata06/testaaaaa/ts0610_1.dbf

DBV-00200: Block, DBA 50331779, already marked corrupt


DBVERIFY - Verification complete

Total Pages Examined         : 4096
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 130
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3965
Total Pages Marked Corrupt   : 1      <----Marked Corrupt=1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1669611986 (2962.1669611986)
    
###dump block 12/131发现seq: 0xff
Block dump from disk:
buffer tsn: 18 rdba: 0x03000083 (12/131)
scn: 0x0b92.63844acd seq: 0xff flg: 0x04 tail: 0x4acd06ff
frmt: 0x02 chkval: 0xf6df type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

---在无法recover corrupt block的情况下可以使用skip_corrupt_blocks跳过
exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'SCOTT',object_name=>'T0610_1',object_type=>DBMS_REPAIR.TABLE_OBJECT,flags=>dbms_repair.SKIP_FLAG);


---跳过以后不报ORA-01578错误了

SYS@tstdb1-SQL> select * from scott.t0610_1;

no rows selected

////////////
// 方法3:使用rowid scan的方式从含有physical/logical corrupt block的表里抽出其它完好的数据
////////////
###sys.t0609_2表
SYS@tstdb1-SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),username,user_id,created from t0609_2;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) USERNAME                          USER_ID CREATED
------------------------------------ ------------------------------------ ------------------------------ ---------- -----------------
                                  10                                  155 FSYSTEM                                56 20150220 07:09:08
                                  10                                  155 SCOTT4                                 55 20150202 11:43:52
                                  10                                  156 XDB                                    52 20150130 23:22:39
                                  10                                  156 CTXSYS                                 49 20150130 22:32:12
                                  10                                  157 REMOTE_SCHEDULER_AGENT                 48 20150130 16:41:15
                                  10                                  157 ANONYMOUS                              41 20150130 16:21:49
                                  10                                  158 SYSADM11                               57 20150324 19:03:42
                                  10                                  158 XS$NULL                        2147483638 20150130 23:25:24
                                  10                                  159 ORACLE_OCM                             21 20141110 21:18:49
                                  10                                  159 DIP                                    14 20141110 21:18:04
                                  10                                  160 OUTLN                                   9 20141110 21:16:14
                                  10                                  160 SYSTEM                                  5 20141110 21:16:12
                                  10                                  161 SYS                                     0 20141110 21:16:12
                                  10                                  161 MGMT_VIEW                              39 20150109 21:13:05
                                  10                                  162 SYSMAN                                 37 20150109 21:09:40
                                  10                                  162 SCOTT                                  36 20141209 15:17:00
                                  10                                  163 PPIP                                   35 20141202 16:06:22
                                  10                                  163 APPQOSSYS                              31 20141110 21:28:49
                                  10                                  164 DBSNMP                                 30 20141110 21:28:47

###破坏block 10/160
RMAN> recover datafile 10 block 160 clear;

Starting recover at 20150610 21:28:29
using channel ORA_DISK_1
Finished recover at 20150610 21:28:29

SYS@tstdb1-SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),username,user_id,created from t0609_2;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 10, block # 160)
ORA-01110: data file 10: '/oradata06/testaaaaa/ts0609_1.dbf'

###构造能够绕过block 10/160的rowid range
***low range of rowid: 10/160 row_number=0
SYS@tstdb1-SQL> select data_object_id from dba_objects where object_name='T0609_2';

DATA_OBJECT_ID
--------------
         36541

select DBMS_ROWID.ROWID_CREATE(rowid_type=>1,object_number=>36541,relative_fno=>10,block_number=>160,row_number=>0) low_rid from dual;
LOW_RID
------------------
AAAI69AAKAAAACgAAA

***high range of rowid: 10/161 row_number=0

select DBMS_ROWID.ROWID_CREATE(rowid_type=>1,object_number=>36541,relative_fno=>10,block_number=>161,row_number=>0) high_rid from dual;
HIGH_RID
------------------
AAAI69AAKAAAAChAAA


###将完好的记录select出来

select * from t0609_2 where rowid<'AAAI69AAKAAAACgAAA'
union all
select * from t0609_2 where rowid>='AAAI69AAKAAAAChAAA';

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
FSYSTEM                                56 20150220 07:09:08
SCOTT4                                 55 20150202 11:43:52
XDB                                    52 20150130 23:22:39
CTXSYS                                 49 20150130 22:32:12
REMOTE_SCHEDULER_AGENT                 48 20150130 16:41:15
ANONYMOUS                              41 20150130 16:21:49
SYSADM11                               57 20150324 19:03:42
XS$NULL                        2147483638 20150130 23:25:24
ORACLE_OCM                             21 20141110 21:18:49
DIP                                    14 20141110 21:18:04
SYS                                     0 20141110 21:16:12
MGMT_VIEW                              39 20150109 21:13:05
SYSMAN                                 37 20150109 21:09:40
SCOTT                                  36 20141209 15:17:00
PPIP                                   35 20141202 16:06:22
APPQOSSYS                              31 20141110 21:28:49
DBSNMP                                 30 20141110 21:28:47

17 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1733252/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1644804