正在导出表 COMMON_PIC
EXP-00056:遇到ORACLE错误1578
ORA-01578:ORACLE数据块损坏(文件号11,块号265103)
ORA-01110:数据文件11:‘D:\ORACLE\ORADATA\PHOTO_INFO.DBF’
...
提示信息很明确,在导出COMMON_PIC表时,检测到11号数据文件中编号为265103的块损坏。
COMMON_PIC用来存放图片,包括BLOB类型的二进制数据。
先用DBV工具检查一下:
C:\> dbv file=d:\oracle\oradata\photo_info.dbf
blocksize=8192start=265000 end=265200
输出:
DBVERIFY: Release 9.2.0.1.0 -Production on 星期三 6月 29 13:21:14
2016
Copyright (c) 1982, 2002,Oracle Corporation. All rights reserved.
DBVERIFY - 验证正在开始 : FILE =d:\oracle\oradata\photo_info.dbf
DBVERIFY - 验证完成
检查的页总数 :201
处理的页总数(数据):0
失败的页总数(数据):0
处理的页总数(索引):0
失败的页总数(索引):0
处理的页总数(其它):200
处理的总页数 (段) : 0
失败的总页数 (段) : 0
空的页总数 :0
标记为损坏的总页数:0
汇入的页总数 :0
检查结果显示未发现问题,之后使用dbms_repair.check_object检查COMMON_PIC表也没有任何结果,尝试设置10231事件,让exp跳过坏块:
[sql] view plain copy
SQL> ALTER SYSTEM SETEVENTS=’10231 TRACE NAME CONTEXT FOREVER,
LEVEL 10’; 仍然无效。
为便于操作,打开PLSQL,用以下语句找出损坏数据块所在的段:
[sql] view plain copy
SELECT e.owner, e.segment_type, e.segment_name, e.tablespace_name, e.block_id, e.blocks FROM dba_extents e WHERE 265103 BETWEEN e.block_id AND (block_id +
blocks - 1);
输出:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER
SEGMENT_TYPE
SEGMENT_NAME
TABLESPACE_NAME
BLOCK_ID
BLOCKS
XC
LOBSEGMENT
SYS_LOB0000030307C00003$$
PHOTO_INFO
259081
8192
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
可以看到,坏块所在的段类型是LOBSEGMENT,继续执行以下语句确定坏块对应的列:
[sql] view plain copy
SELECT l.table_name, l.column_name FROM dba_lobs l WHERE l.segment_name =
'SYS_LOB0000030307C00003$$' AND owner =
'XC'; 输出:
-------------------------------------------------------------------
TABLE_NAME COLUMN_NAME
COMMON_PIC CONTENT
-------------------------------------------------------------------
执行以下语句,找出坏块对应的ROWID
--建表,存放块坏对应的行
[sql] view plain copy
CREATE TABLE corr_rows (row_id ROWID,error_code
NUMBER);
--使用dbms_lob包遍历COMMON_PIC表的CONTENT列,触发错误并将对应的ROWID和错误代码插入corr_rows表
[sql] view plain copy
DECLARE n NUMBER; ERROR_CODE NUMBER; corr_rows NUMBER :=
0; ora_1578 EXCEPTION; PRAGMA EXCEPTION_INIT(ora_1578,-1578); BEGIN FOR cursor_lob IN (SELECT ROWID rid,
CONTENT FROM XC.COMMON_PIC) LOOP BEGIN n := dbms_lob.instr(cursor_lob.CONTENT,
hextoraw('01234567')); EXCEPTION WHEN ora_1578 THEN corr_rows := corr_rows + 1; INSERT INTO corr_rows VALUES (cursor_lob.rid, 1578); COMMIT; WHEN OTHERS THEN ERROR_CODE := SQLCODE; corr_rows := corr_rows +
1; INSERT INTO corr_rows VALUES (cursor_lob.rid, ERROR_CODE); COMMIT; END; END LOOP; dbms_output.put_line('Corrupted rows: ' ||
corr_rows); END; / 输出:
Corrupted rows: 1
查询具体结果:
[sql] view plain copy
SELECT * FROM corr_rows; -----------------------------------------------------------------
ROW_ID ERR_CODE
AAAHZjAALAAAmDrAAX -1578
-----------------------------------------------------------------
结果显示只有1行的CONTENT列受到坏块影响,根据ROWID查询:
[sql] view plain copy
SELECT p.rowid, p.CONTENT FROM XC.COMMON_PIC p WHERE p.rowid =
'AAAHZjAALAAAmDrAAX';
输出:
-----------------------------------------------------------------
ROWID CONTENT
AAAHZjAALAAAmDrAAX
----------------------------------------------------------------
登录相关应用,可以验证本条记录对应的图片确实打开失败,由于确认图片有备份,因此这里把错误的字段清空,先保证exp能正常导出数据,待迁移完成后再恢复图片。
[sql] view plain copy
UPDATE TABLE XC.COMMON_PIC SET XC.CONTENT =
empty_blob() WHERE ROWID =
'AAAHZjAALAAAmDrAAX'; COMMIT;