处理数据块损坏

最新推荐文章于 2023-09-20 07:52:29 发布

ciliting2867

最新推荐文章于 2023-09-20 07:52:29 发布

阅读量107

点赞数

今天在测试环境做测试的时候，遇到如下错误：

SQL> select count(*) from test1;
select count(*) from test1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 26759)
ORA-01110: data file 2: '/oracle/oradata/prod/users02.dbf'

看了eygle的一篇文章，进行处理，步骤如下：

1. 使用DBV检查数据文件

[oracle@ora-as4 prod]$ dbv file=users02.dbf blocksize=8192

DBVERIFY: Release 9.2.0.8.0 - Production on Sun May 4 20:28:54 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = users02.dbf
Page 26759 is influx - most likely media corrupt
***
Corrupt block relative dba: 0x00806887 (file 2, block 26759)
Fractured block found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0x00806887
last change scn: 0x0000.004e4e08 seq: 0x2 flg: 0x04
consistency value in tail: 0x000006fd
check value in block header: 0xc210, computed block checksum: 0x4ef7
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

DBVERIFY - Verification complete

Total Pages Examined         : 262144
Total Pages Processed (Data) : 147704
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 19374
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 29
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 95036
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Highest block SCN            : 5850423 (0.5850423)

2. 考虑到没有备份数据，只能丢弃坏块里面的数据了。

尝试导出该表，导出失败，如下：

[oracle@ora-as4 oracle]$ exp scott/tiger tables=test1 file=test1.dmp statistics=none log=test1.log

Export: Release 9.2.0.8.0 - Production on Sun May 4 20:38:28 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHT16BIG5 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST1
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 2, block # 26759)
ORA-01110: data file 2: '/oracle/oradata/prod/users02.dbf'
Export terminated successfully with warnings.

3.查询损坏数据块上的数据对象

SQL> select tablespace_name,segment_type,owner,segment_name from dba_extents
2 where file_id=2 and 26759 between block_id and block_id+blocks;

TABLESPACE SEGMENT_TYPE       OWNER                          SEGMENT_NAME
---------- ------------------ -----------------------------                      --------------------
USERS      TABLE              SCOTT                                             TEST1

4.设置内部事件，使exp跳过这些损坏的block

SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10';

System altered.

这是一个内部事件，设置在全表扫描时跳过损坏的数据块.

5. 再次执行export

[oracle@ora-as4 ora9208]$ exp scott/tiger tables=test1 file=test1.dmp statistics=none log=test1.log

Export: Release 9.2.0.8.0 - Production on Sun May 4 20:50:09 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHT16BIG5 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST1 2964221 rows exported
Export terminated successfully without warnings.

成功导出。

6.将该表drop，并重新import。

SQL> conn scott/tiger
Connected.
SQL> drop table test1;

Table dropped.

SQL> !
[oracle@ora-as4 oracle]$ cd /ora9208
[oracle@ora-as4 ora9208]$ imp scott/tiger tables=test1 file=test1.dmp

Import: Release 9.2.0.8.0 - Production on Sun May 4 20:53:49 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHT16BIG5 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table "TEST1" 2964221 rows imported
Import terminated successfully without warnings.

7. 再次进行查询。

SQL> show user
USER is "SCOTT"
SQL> select count(*) from test1;

COUNT(*)
----------
2964221

8.重新验证数据文件

[oracle@ora-as4 prod]$ dbv file=users02.dbf blocksize=8192

DBVERIFY: Release 9.2.0.8.0 - Production on Sun May 4 20:59:22 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = users02.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 262144
Total Pages Processed (Data) : 147534
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 19547
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 27
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 95036
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 5856599 (0.5856599)