oracle数据库查询遇到ora-1578错误是怎么回事？ ora-01578 "ORACLE data block corrupted ORACLE 数据文件有坏块，报错：ORA-01578:OR...
Oracle\u6570\u636e\u5e93\u9047\u5230ORA-01578\u574f\u5757\u9519\u8bef\u7684\u539f\u56e0\u662f\u4ec0\u4e48\uff0c\u6c42\u89e3\u91ca\uff1f\u3010Oracle\u6570\u636e\u6062\u590d\u3011ORA-01578\u9519\u8bef\u89e3\u6790
\u4e00\u822c\u8ba4\u4e3aORA-1578\u662f \u6570\u636e\u5757\u7269\u7406\u574f\u5757/\u635f\u574f\u7684\u4e00\u79cd\uff0c\u4e0d\u540c\u4e8e\u903b\u8f91\u635f\u574f/\u574f\u5757\uff0c\u5176\u9519\u8bef\u4fe1\u606f\u5982\u4e0b\uff1a
[oracle@oel8 dirdat]$ oerr ora 1578
01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"
// *Cause: The data block indicated was corrupted, mostly due to software
// errors.
// *Action: Try to restore the segment containing the block indicated. This
// may involve dropping the segment and recreating it. If there
// is a trace file, report the errors in it to your ORACLE
// representative
\u8fd9\u4e2a1578\u4e00\u822c \u4f1a\u4f34\u968fORA-1110\u51fa\u73b0\uff0c\u4e00\u65e6ORACLE\u8bfb\u53d6\u5230\u5b58\u5728\u635f\u574f\u7684\u5757\u5c31\u4f1a\u62a5\u51fa:
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 11, block # 34) \u8fd9\u91cc\u7684FILE#\u662f RFN
ORA-01110: data file 6:'/home/oracle/corrclass.dbf' \u8fd9\u91cc\u7684\u662fAFN
\u65ad\u88c2\u7684\u6570\u636e\u5757Fractured Block\uff1a
Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380e573
last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
consistency value in tail: 0x00780601
check value in block header: 0x8739, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data
\u6216\u8005\u9519\u8bef\u7684\u6821\u9a8c\u548cchecksum
Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
Bad check value found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380a58f
last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
consistency value in tail: 0xc5ee0601
check value in block header: 0x68a7, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data
\u6216\u8005\u9519\u8bef\u7684\u5757\u5934
Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
Bad header found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0d805b08
last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
consistency value in tail: 0x08e30601
check value in block header: 0x2a6e, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0d805a89 (file 54, block 23177) found valid data
\u4e00\u4e9b\u53ef\u80fd\u5f15\u8d77ORA-1578\u7684BUG \u5217\u8868\u5982\u4e0b\uff1a
\u5982\u679c\u81ea\u5df1\u641e\u4e0d\u5b9a\u53ef\u4ee5\u627eASKMACLEAN\u4e13\u4e1a\u6570\u636e\u5e93\u4fee\u590d\u56e2\u961f\u6210\u5458\u5e2e\u60a8\u6062\u590d!
Oracle DBA\u795e\u5668\uff1aPRM-DUL\u707e\u96be\u6062\u590d\u5de5\u5177\uff0cSchema\u7ea7\u522b\u6570\u636e\u6062\u590d\u3002PRM-DULFor Oracle Database \u2013 schema\u7ea7\u522boracle\u6570\u636e\u5e93\u6570\u636e\u6062\u590d\u7279\u6027 \uff0cPRM-DUL\u5373ParnassusData Recovery Manager\u662f\u4f01\u4e1a\u7ea7\u522bOracle\u6570\u636e\u5e93\u707e\u96be\u6062\u590d\u5de5\u5177\u3002PRM\u53ef\u4ee5\u5728\u65e0\u5907\u4efd\u7684\u60c5\u51b5\u4e0b\u6062\u590d\u88abtruncated\u6389\u7684\u8868\uff0c\u4e5f\u53ef\u4ee5\u6062\u590d\u65e0\u6cd5\u6253\u5f00\u7684Oracle\u6570\u636e\u5e93(Alter Database Open\u5931\u8d25)\u4e2d\u7684\u6570\u636e\u3002
如果不能自行解决该问题，那么也可以联系MACLEAN专业数据库修复团队。ORA-01578错误是Oracle中常见的物理坏块讹误(Corruption)错误，从10g以后在拥有完整备份和归档日志的情况下可以通过blockrecover/recover命令在线恢复该坏块，前提是数据块所在磁道在物理上仍可用。
以下是一个在没有充分备份情况下的ORA-01578错误的解决，前提是能够容忍坏块所在数据的丢失：
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;
BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 870212)
ORA-01110: data file 4:
'/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 15188
ORA-06512: at "SYS.DBMS_STATS", line 15530
ORA-06512: at "SYS.DBMS_STATS", line 15674
ORA-06512: at "SYS.DBMS_STATS", line 15638
ORA-06512: at line 1
使用RMAN blockreocver命令试图修改该物理坏块：
RMAN> blockrecover datafile 4 block 870212;
Starting blockrecover at 08-NOV-12
channel ORA_DISK_1: restored block(s) from backup piece 1
piece
handle=/s01/flash_recovery_area/G10R25/backupset/2012_08_06/o1_mf_nnndf_TAG20120806T075500_81zd4njn_.bkp
tag=TAG20120806T075500
channel ORA_DISK_1: block restore complete, elapsed time: 00:01:16
starting media recovery
archive
log thread 1 sequence 467 is already on disk as file
/s01/flash_recovery_area/G10R25/archivelog/2012_10_31/o1_mf_1_467_893571cm_.arc
archive
log thread 1 sequence 468 is already on disk as file
/s01/flash_recovery_area/G10R25/archivelog/2012_10_31/o1_mf_1_468_893pc84l_.arc
archive
log thread 1 sequence 469 is already on disk as file
/s01/flash_recovery_area/G10R25/archivelog/2012_11_01/o1_mf_1_469_894zsbym_.arc
archive
log thread 1 sequence 470 is already on disk as file
/s01/flash_recovery_area/G10R25/archivelog/2012_11_01/o1_mf_1_470_896b944y_.arc
4_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 11/08/2012 06:19:40
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 466 lowscn 27762151 found to restore
RMAN-06025: no backup of log thread 1 seq 465 lowscn 27762145 found to restore
RMAN-06025: no backup of log thread 1 seq 464 lowscn 27762142 found to restore
由于缺少必要的归档日志导致blockrecover无法成功，需要另想办法。
首先确认该数据块属于哪个SEGMENT，如果是INDEX那么完全可以重建也不会丢失数据，但是如果是表数据则需要容忍丢失该坏块内的数据：
SQL> col tablespace_name for a20
SQL> col segment_type for a10
SQL> col segment_name for a20
SQL> col owner for a8
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = &fileid
4 and &blockid between block_id AND block_id + blocks - 1;
Enter value for fileid: 4
old 3: WHERE file_id = &fileid
new 3: WHERE file_id = 4
Enter value for blockid: 870212
old 4: and &blockid between block_id AND block_id + blocks - 1
new 4: and 870212 between block_id AND block_id + blocks - 1
TABLESPACE_NAME SEGMENT_TY OWNER SEGMENT_NAME
-------------------- ---------- -------- --------------------
USERS TABLE SYS CORRUPT_ME
SQL> select count(*) from CORRUPT_ME;
select count(*) from CORRUPT_ME
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 870212)
ORA-01110: data file 4:
'/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf'
SQL> analyze table corrupt_me validate structure;
analyze table corrupt_me validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_19749.trc
Corrupt block relative dba: 0x010d4744 (file 4, block 870212)
Bad header found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x000d4744
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000006ff
check value in block header: 0x6323
computed block checksum: 0x0
Reread of rdba: 0x010d4744 (file 4, block 870212) found same corrupted data
*** 2012-11-08 06:23:12.564
table scan: segment: file# 4 block# 870211
skipping corrupt block file# 4 block# 870212
*** 2012-11-08 06:23:36.955
table scan: segment: file# 4 block# 870211
skipping corrupt block file# 4 block# 870212
skipping corrupted block at rdba: 0x010d4744
下面使用10231 level 10事件来避免发生ORA-01578错误，并将原坏块表复制出来:
SQL> alter session set events '10231 trace name context forever,level 10';
Session altered.
SQL> select count(*) from CORRUPT_ME;
COUNT(*)
----------
50857
SQL> create table corrupt_me_copy tablespace users as select * from CORRUPT_ME;
Table created.
SQL> analyze table corrupt_me_copy validate structure;
Table analyzed.
之后仅需要将新表rename为旧表，并重建索引即可：
SQL> alter table corrupt_me rename to corrupt_me_copy1;
Table altered.
SQL> alter table corrupt_me_copy rename to corrupt_me;
Table altered.
SQL> rebuild indexs
绛旓細and浼樺厛绾уぇ浜or銆傚姞鎷彿鍒欎紭鍏堟墽琛宱r锛屽悗鎵цand锛涗笉鍔犳嫭鍙凤紝浼氬厛鎵цand锛屽啀鎵цor锛屾墍浠鏌ヨ缁撴灉涓嶅悓銆備妇渚嬶細鏁版嵁搴瀛樺湪鏁版嵁锛歍homas Carter William Carter Thomas King 鎵ц锛歋ELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')AND LastName='Carter'缁撴灉涓猴細Thomas Carter ...
绛旓細锛1锛 鐢║NION鏇挎崲OR (閫傜敤浜庣储寮曞垪)閫氬父鎯呭喌涓, 鐢║NION鏇挎崲WHERE瀛愬彞涓殑OR灏嗕細璧峰埌杈冨ソ鐨勬晥鏋. 瀵筥__绱㈠紩鍒椾娇鐢∣R灏嗛犳垚鍏ㄨ〃鎵弿. 娉ㄩ噸, 浠ヤ笂瑙勫垯鍙拡瀵瑰涓储寮曞垪鏈夋晥. 鍋囧鏈塩olumn 娌℃湁琚储寮, 鏌ヨ鏁堢巼鍙兘浼氬洜涓轰綘娌℃湁閫夋嫨OR鑰岄檷浣. 鍦ㄤ笅闈㈢殑渚嬪瓙涓, LOC_ID 鍜孯EGION涓婇兘寤烘湁绱㈠紩.楂樻晥...
绛旓細ORA-00600: internal error code, arguments: [kfrValAcd30]鍙兘鏄痓ug鎴栬呯‖浠舵晠闅滃鑷.鍩轰簬瀹㈡埛鐨勬儏鍐,鏈澶у彲鑳藉氨鏄敱浜庣‖浠舵晠闅滃鑷碼sm 纾佺洏缁勭殑acd鏃犳硶姝ｅ父杩涜,浠庤屾棤娉昺ount鎴愬姛.濡傛灉杩愭皵濂,閫氳繃kfed鐩稿叧淇鍙互姝ｅ父mount鎴愬姛,杩愭皵涓嶅ソ鍙互閫氳繃搴曞眰杩涜鎭㈠鏁版嵁鏂囦欢,浠庤屾渶澶ч檺搴︽仮澶嶆暟鎹.鍙傝冪被浼兼枃绔狅細...
绛旓細鍙互鐢╱nion锛屾瘮濡俿elect 鍐呭 from user where name='寮犱笁' union select 鍐呭 from user where name='鏉庡洓'锛岀浉褰撲簬select 鍐呭 from user where name='寮犱笁' or name='鏉庡洓' 锛屽洜涓簎nion浼氱敤鍒扮储寮曪紝涓嶇煡閬撲綘杩欎釜琛ㄦ湁娌℃湁绱㈠紩锛岃〃鐨鏁版嵁澶氬ぇ锛
绛旓細鍏堣瘯璇曚笉瑕乤nd a_devtestdt is null 杩欏彞銆鏌ョ湅涓ゆ浠ｇ爜缁撴灉銆傚啀璇曡瘯涓嬮潰 select dept from A where (status='[D2]鏂规璁捐闃舵' or status='[D2]鏂规瀹℃牳涓' or status ='[D2]寮鍙戜腑' or status='[D2]寮鍙戝畬鎴' or status='[D2]鍗曞厓娴嬭瘯杩涜涓')and a_devtestdt is null --- ...
绛旓細浣犵悊瑙ｉ敊浜or锛宱r鏄弧瓒冲叾涓竴涓垨涓や釜鏉′欢锛屽嵆浣跨涓涓潯浠朵笉鎴愮珛锛岀浜屼釜鏉′欢涔熻鍒ゆ柇锛屽洜涓烘湁鍙兘绗簩涓潯浠舵槸婊¤冻鐨勩傝宎nd鏄涓や釜鏉′欢鍚屾椂鎴愮珛锛屼綔鐢ㄦ槑鏄惧拰or涓嶄竴鏍枫
绛旓細瑕佷紭鍖or鏌ヨ锛屽彧瑕佷綘鐭ラ亾涓鐐癸細sql or鏌ヨ鏄乏鎴柇鐨勶紝涔熷氨鏄寚瑕乷r绗﹀悎鏉′欢锛屽氨涓嶅啀璧板悗闈㈢殑or checking浜 鏄庣櫧浜嗚繖涓紝鍞竴鐨勪紭鍖栨柟妗堝氨鏄 鎶婅秺鑳藉懡涓殑or璇彞鏀惧埌鏈鍓嶉潰锛屽鍔犲墠闈㈠噺灏戝悗闈㈢殑or 閫昏緫鍒ゆ柇
绛旓細and:oracle 鍏鏌ユ壘婊¤冻and 涔嬪墠鐨勶紝鐒跺悗鎵弿and涔嬪悗鐨勬潯浠躲傚ぇ姒傞渶瑕佹壂鎻忎袱閬嶆垨鑰呬互涓娿俹r:閭ｄ箞鎵弿娆℃暟鏇村浜嗐傛弧瓒硂r涔嬪墠锛屾壂鎻忓叏琛ㄤ竴娆★紱婊¤冻or涔嬪悗锛屼竴娆★紱鍚屾椂婊¤冻鍙堜竴娆°倁nion锛氬彧鏄仈鍚鏌ヨ鑰屽凡锛屾牴鎹储寮曟潵鎵撅紝鎸哄揩鐨勶紝鍑犱箮鑰楄垂涓嶄簡澶氬皯鏃堕棿銆傛噦鐢ㄥ氨琛屼簡锛岃繕闇瑕佺煡閬撳簳灞傚晩锛岃繖涓亣濡備綘鐨勪笓涓氫笉鏄...
绛旓細=鎴<>鎿嶄綔绗︼紝鍚﹀垯灏嗗紩鎿庢斁寮冧娇鐢ㄧ储寮曡岃繘琛屽叏琛ㄦ壂鎻忋4.搴斿敖閲忛伩鍏嶅湪 where 瀛愬彞涓娇鐢 or 鏉ヨ繛鎺ユ潯浠讹紝鍚﹀垯灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細select id from t where num=10 or num=20鍙互杩欐牱鏌ヨ锛歴elect id from t where num=10union allselect id from t where num=205.in 鍜...
绛旓細Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1596.trc:ORA-00704: 寮曞绋嬪簭杩涚▼澶辫触 ORA-39700: 蹇呴』鐢 UPGRADE 閫夐」鎵撳紑鏁版嵁搴 Tue Dec 10 09:33:57 2013 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Tu...
