存在非空索引情况下的段头损坏的恢复

创建一个测试表：

SQL> create table goolen as select * from dba_objects;

Table created.

SQL> alter table goolen add constraint pk_objd primary key (object_id);
Table altered.

SQL> select count(*) from goolen;
  COUNT(*)
----------
     72139

--查看，goolen表的段头块是block 50

SQL> select header_file,header_block from dba_segments where segment_name='GOOLEN';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4           50

SQL> select file_id,block_id,blocks from dba_extents where segment_name='GOOLEN';
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4         48          8
         4         56          8
         4         64          8
         4         72          8
         4         80          8
         4         88          8
         4         96          8
         4        104          8
         4        112          8
         4        120          8
         4        128          8
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4        136          8
         4        144          8
         4        152          8
         4        160          8
         4        168          8
         4        256        128
         4        384        128
         4        512        128
         4        640        128
         4        768        128
         4        896        128
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4       1024        128
         4       1152        128


24 rows selected.

SQL> col file_name for a55
SQL> set lines 120
SQL> select file_id,file_name,bytes from dba_data_files;
   FILE_ID FILE_NAME                                                    BYTES
---------- ------------------------------------------------------- ----------
         4 /opt/app/oracle/oradata/goolen/users01.dbf                12713984
         3 /opt/app/oracle/oradata/goolen/undotbs01.dbf              78643200
         2 /opt/app/oracle/oradata/goolen/sysaux01.dbf              629145600
         1 /opt/app/oracle/oradata/goolen/system01.dbf              713031680

----使用bbed破坏段头块，模拟段头损坏

BBED> set file 4 block 50
        FILE#           4
        BLOCK#          50

BBED> map /v
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 50                                    Dba:0x01000032
------------------------------------------------------------
BBED-00400: invalid blocktype (00)

BBED> m /x 1234 offset 24
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 50               Offsets:   24 to  535           Dba:0x01000032
------------------------------------------------------------------------
 12340000

SQL> show user
USER is "SCOTT"
SQL> select count(*) from goolen;
  COUNT(*)
----------
     72139

SQL> alter system flush buffer_cache;
System altered.

SQL> select /*+ full(goolen)*/ count(*) from goolen;
select /*+ full(goolen)*/ count(*) from goolen
                                        *
ERROR at line 1:
ORA-08103: object no longer exists

SQL> execute dbms_space_admin.tablespace_verify('&tablespace_name')
Enter value for tablespace_name: USERS
BEGIN dbms_space_admin.tablespace_verify('USERS'); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> conn / as sysdba
Connected.
SQL> execute dbms_space_admin.tablespace_verify('&tablespace_name')
Enter value for tablespace_name: USERS
BEGIN dbms_space_admin.tablespace_verify('USERS'); END;
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 83
ORA-06512: at line 1

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_1678.trc
kcbzib: dump suspect buffer
buffer tsn: 4 rdba: 0x01000032 (4/50)
scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001
frmt: 0x02 chkval: 0x0000 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2               FROM dba_extents
  3              WHERE file_id =&file_id
  4     and &block_id between block_id and block_id + blocks -1;
Enter value for file_id: 4
old   3:             WHERE file_id =&file_id
new   3:             WHERE file_id =4
Enter value for block_id: 50
old   4:    and &block_id between block_id and block_id + blocks -1
new   4:    and 50 between block_id and block_id + blocks -1
no rows selected

SQL> SELECT owner, segment_name, segment_type, partition_name 
  2               FROM dba_segments
  3             WHERE header_file =&file_id
  4     and header_block=&block_id;
Enter value for file_id: 4
old   3:            WHERE header_file =&file_id
new   3:            WHERE header_file =4
Enter value for block_id: 50
old   4:    and header_block=&block_id
new   4:    and header_block=50

OWNER                          SEGMENT_NAME                        SEGMENT_TYPE       PARTITION_NAME
------------------------------ ----------------------------------- ------------------ ------------------------------

SCOTT                          GOOLEN                              TABLE

SQL> select index_name,index_type from dba_indexes where table_name='GOOLEN';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
PK_OBJD                        NORMAL

--使用rowid来抽取数据
SQL> insert into goolen_bak 
  2  select * from goolen where rowid in 
  3  （select /*+ index(goolen PK_OBJD)*/ rowid from goolen )
  4  ;

72139 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from goolen_bak;
  COUNT(*)
----------
     72139