今天在测试库上创建表的时候,报了个错误
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.