重庆思庄Oracle、Redhat认证学习论坛

标题: 跳过损坏块的行 [打印本页]

作者: 郑全 时间: 2016-8-20 12:09
标题: 跳过损坏块的行
有些时候,我们的表中有损害的块,又没有开归档,就是没有0级备份,这个时候,我们没有办法使用块介质恢复技术来处理,那么,这个时候,如果损坏的行不多,我们可以通过跳过损害的行来解决.代价当然是那些损坏的行,再也找不到了.

下面是一个例子:

create table emp tablespace ts_bc as select * from hr.employees;

select EMPLOYEE_ID,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','EMP'), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) from emp;
... ...

      183                                  4                                              1027                                  11
      184                                  4                                              1027                                  11
      185                                  4                                              1027                                  11
      186                                  4                                              1027                                  11
      187                                  4                                              1027                                  11
      188                                  4                                              1027                                  11
      189                                  4                                              1027                                  12
EMPLOYEE_ID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','EMP') DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
----------- ------------------------------------ --------------------------------------------------- ------------------------------------
      190                                  4                                              1027                                  12
      191                                  4                                              1027                                  12
      192                                  4                                              1027                                  12
      193                                  4                                              1027                                  12
      194                                  4                                              1027                                  12
      195                                  4                                              1027                                  12
      196                                  4                                              1027                                  12
      197                                  4                                              1027                                  12

模拟损坏11块.
RMAN>  recover datafile 1027 block 11 clear;

再去查询 emp,抱错了.

SQL> select * from emp;
select * from emp
            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1027, block # 11)
ORA-01110: data file 1027: '+DATA/sztech1/datafile/ts_bc.1288.920372761'

如果我们这时没有开归档 ,就只有跳过了:

SQL> alter system  set events='10231 trace name context forever,level 10';

再去执行,就可以把未损坏得纪录取出来:

SQL> select employee_id,last_name from emp;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
      189 Dilly
      190 Gates
      191 Perkins
      192 Bell
      193 Everett
      194 McCain
      195 Jones
      196 Walsh
      197 Feeney
9 rows selected.
SQL>

这个正好是上面块为12的9行纪录.

SQL> select EMPLOYEE_ID,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','EMP'), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) from emp where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=12;
EMPLOYEE_ID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','EMP') DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
----------- ------------------------------------ --------------------------------------------------- ------------------------------------
      189                                  4                                              1027                                  12
      190                                  4                                              1027                                  12
      191                                  4                                              1027                                  12
      192                                  4                                              1027                                  12
      193                                  4                                              1027                                  12
      194                                  4                                              1027                                  12
      195                                  4                                              1027                                  12
      196                                  4                                              1027                                  12
      197                                  4                                              1027                                  12
9 rows selected.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)