ORA-01578: ORACLE data block corrupted

今天在测试库上创建表的时候，报了个错误
SQL> create table abc as select * from dba_objects;
create table abc as select * from dba_objects
                                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 298)
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'

SQL> alter database datafile 4 resize 10m;
alter database datafile 4 resize 10m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> alter database datafile 4 resize 20m;
Database altered.

SQL> create table abc as select * from dba_objects;
Table created.

SQL> create unique index ind_123 on abc(object_id);
create unique index ind_123 on abc(object_id)
                               *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 298)
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'

---dbv检测结果：
[oracle@localhost ~]$ dbv file=/opt/app/oracle/oradata/goolen/users01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Wed Dec 25 15:57:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/goolen/users01.dbf
Page 298 is influx - most likely media corrupt
Corrupt block relative dba: 0x0100012a (file 4, block 298)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0100012a
 last change scn: 0x0000.0012c00b seq: 0x2a flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc00b0602
 check value in block header: 0x2f3e
 computed block checksum: 0x28


DBVERIFY - Verification complete
Total Pages Examined         : 2720
Total Pages Processed (Data) : 2074
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 115
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 203
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 327
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Total Pages Encrypted        : 0
Highest block SCN            : 1288820 (0.1288820)

---仔细看看就会注意到tailchk=bas_kcbh低2字节(c00b)+type_kcbh(06)+seq_kcbh(2a),而这里是tail: 0xc00b0602

---接下来我们修改一下tailchk的值
BBED> set file 4 block 298
        FILE#           4
        BLOCK#          298

BBED> show 
        FILE#           4
        BLOCK#          298
        OFFSET          0
        DBA             0x0100012a (16777514 4,298)
        FILENAME        /opt/app/oracle/oradata/goolen/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE        filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           12
        LOGFILE         log.bbd
        SPOOL           No

BBED> p tailchk
tailchk
-------
ub4 tailchk                                 @8188     0xc00b0602

BBED> p seq_kcbh
kcbh.seq_kcbh
-------------
ub1 seq_kcbh                                @14       0x2a

BBED> m /x 2a offset 8188
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 298              Offsets: 8188 to 8191           Dba:0x0100012a
------------------------------------------------------------------------
 2a060bc0 

 <32 bytes per line>

BBED> sum apply;
Check value for File 4, Block 298:
current = 0x2f3e, required = 0x2f3e

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system flush buffer_cache;
System altered.

SQL> exit

---dbv再检测的时候，已经正常
[oracle@localhost ~]$ dbv file=/opt/app/oracle/oradata/goolen/users01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Wed Dec 25 16:16:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/goolen/users01.dbf

DBVERIFY - Verification complete
Total Pages Examined         : 2720
Total Pages Processed (Data) : 2075
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 115
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 203
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 327
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1289197 (0.1289197)

---但是在创建索引的时候还是报错：
SQL> conn scott/tiger
Connected.
SQL> create unique index ind_123 on abc(object_id);
create unique index ind_123 on abc(object_id)
                               *
ERROR at line 1:
ORA-08103: object no longer exists

SQL> select /*+full(abc)*/ count(*) from abc;
  COUNT(*)
----------
     72058


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-01578: ORACLE data block corrupted (file # 4, block # 298)
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
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_19323.trc
 
kcbzib: dump suspect buffer
buffer tsn: 4 rdba: 0x0100012a (4/298)
scn: 0x0000.0012c00b seq: 0x2a flg: 0x04 tail: 0xc00b062a
frmt: 0x02 chkval: 0x2f3e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

---我们发现，还是file 4，block 298有问题

SQL> select segment_name, segment_type, owner
  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: 298
old   4:   and &block_id between block_id and block_id + blocks - 1
new   4:   and 298 between block_id and block_id + blocks - 1

no rows selected

SQL> select *
  2  from dba_free_space
  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: 298
old   4: and &block_id between block_id and block_id + blocks - 1
new   4: and 298 between block_id and block_id + blocks - 1

no rows selected

SQL> SELECT owner, segment_name, segment_type, partition_name 
             FROM dba_segments
           WHERE header_file =4
   and header_block=298;
OWNER                          SEGMENT_NAME SEGMENT_TYPE       PARTITION_NAME
------------------------------ -------------------- ------------------ ------------------------------
SCOTT                          4.298 TEMPORARY
 
SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2213696 bytes
Variable Size            1040189632 bytes
Database Buffers          520093696 bytes
Redo Buffers                7512064 bytes
Database mounted.
Database opened.

---重启了实例，还是有这个
SQL> SELECT owner, segment_name, segment_type, partition_name 
             FROM dba_segments
           WHERE header_file =4
   and header_block=298;
OWNER                          SEGMENT_NAME SEGMENT_TYPE       PARTITION_NAME
------------------------------ -------------------- ------------------ ------------------------------
SCOTT                          4.298 TEMPORARY


8 rows selected.

---试着替换一下这个块
SQL> conn scott/tiger
Connected.
SQL> create table t2 tablespace users as select * from abc where 1=2;
Table created.
SQL> insert into t2 select * from abc where rownum<2;
1 row created.
SQL> commit;
Commit complete.
SQL> select 
  2  dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location,
  3  count(*) 
  4  from t2 
  5  group by dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid);

LOCATION  COUNT(*)
--------------------
4_1455 1

SQL> delete from t2;
1 row deleted.

SQL> commit;
Commit complete.

SQL> select to_char(data_object_id,'XXXXXXXX') from dba_objects where owner='SCOTT' and object_name='T1';
no rows selected


SQL> select to_char(data_object_id,'XXXXXXXX') from dba_objects where owner='SCOTT' and object_name='ABC';
TO_CHAR(D
---------
    12075

SQL> select to_char(data_object_id,'XXXXXXXX') from dba_objects where owner='SCOTT' and object_name='T2';
TO_CHAR(D
---------
    120DD

SQL> alter system flush buffer_cache;
System altered.

---copy block 1455号块，覆盖298号块，因为都是空块，所以只需要修改data_object_id（offset 24）和rdba（offset 4）地址就行
BBED> copy file 4 block 1455 to file 4 block 298
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 298              Offsets:    0 to  511           Dba:0x0100012a
------------------------------------------------------------------------
 06a20000 af050001 8db81300 00000106 3aff0000 01001200 dd200100 8bb81300 
 0000e81f 02003200 a8050001 07001700 e5020000 4b0bc000 d4002100 00800000 
 7eb81300 0a000200 3d030000 c707c000 04010e00 01204b00 8db81300 00000000 

BBED> p offset 4
kcbh.rdba_kcbh
--------------
ub4 rdba_kcbh                               @4        0x010005af

BBED> m /x 2a01 offset 4
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 298              Offsets:    4 to  515           Dba:0x0100012a
------------------------------------------------------------------------
 2a010001 8db81300 00000106 3aff0000 01001200 dd200100 8bb81300 0000e81f 

BBED> p offset 4
kcbh.rdba_kcbh
--------------
ub4 rdba_kcbh                               @4        0x0100012a

BBED> p offset 24
ktbbh.ktbbhsid.ktbbhod1
-----------------------
ub4 ktbbhod1                                @24       0x000120dd

BBED> m /x 7520 offset 24
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 298              Offsets:   24 to  535           Dba:0x0100012a
------------------------------------------------------------------------
 75200100 8bb81300 0000e81f 02003200 a8050001 07001700 e5020000 4b0bc000 
 d4002100 00800000 7eb81300 0a000200 3d030000 c707c000 04010e00 01204b00 

BBED> p offset 24
ktbbh.ktbbhsid.ktbbhod1
-----------------------
ub4 ktbbhod1                                @24       0x00012075

BBED> sum apply;
Check value for File 4, Block 298:
current = 0xfb17, required = 0xfb17

SQL> create unique index ind_123 on abc(object_id);
create unique index ind_123 on abc(object_id)
                               *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 298)
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'

---发现还是不行，对于这种情况，不知道怎么去解决
也通过hout.sql脚本和hcheck3.sql检查过数据字典，发现也没有问题
最后只能move 表，替换表空间解决了

SQL> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/goolen/users01.dbf
/opt/app/oracle/oradata/goolen/undotbs01.dbf
/opt/app/oracle/oradata/goolen/sysaux01.dbf
/opt/app/oracle/oradata/goolen/system01.dbf

SQL> create tablespace goolen datafile '/opt/app/oracle/oradata/goolen/goolen01.dbf' size 1m autoextend on maxsize 1G;
Tablespace created.
 
SQL> select index_name,status from dba_indexes where owner='SCOTT';
INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        VALID

SQL> select 'alter table '||table_name||' move tablespace goolen;' from dba_tables where owner='SCOTT';
'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEGOOLEN;'
------------------------------------------------------------------
alter table DEPT move tablespace goolen;
alter table EMP move tablespace goolen;
alter table SALGRADE move tablespace goolen;
alter table ABC move tablespace goolen;
alter table T2 move tablespace goolen;
alter table BONUS move tablespace goolen;

6 rows selected.

SQL> alter table DEPT move tablespace goolen;
alter table DEPT move tablespace goolen
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn scott/tiger
Connected.
SQL> alter table DEPT move tablespace goolen;
Table altered.

SQL> alter table EMP move tablespace goolen;
alter table SALGRADE move tablespace goolen;
alter table ABC move tablespace goolen;
alter table T2 move tablespace goolen;

Table altered.
SQL> 
Table altered.
SQL> alter table BONUS move tablespace goolen;
Table altered.
SQL> 
Table altered.
SQL> 
Table altered.

SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

SQL> alter database default tablespace goolen;

Database altered.

SQL> drop tabespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='DEPT';
TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
DEPT                           PK_DEPT                        P

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='EMP';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
EMP                            FK_DEPTNO                      R

SQL> alter table emp drop constraint FK_DEPTNO;
Table altered.

SQL> alter table dept drop constraint pK_DEPT;
Table altered.

SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.

SQL> create unique index ind_123 on abc(object_id);

Index created.