DB:Oracle 11.2.0.4.0
已创建示例表空间CCDATA,schema为cc,数据文件o1_mf_ccdata_bn8l5x7l_.dbf
找到该表空间中的一个表CUSTACCOUNTS,看看它的block_id范围
点击(此处)折叠或打开
- select owner, segment_name, segment_type,
- extent_id, file_id, block_id, blocks
- from dba_extents
- where owner='CC' and segment_name='CUSTACCOUNTS'
OWNER | SEGMENT_NAME | SEGMENT_TYPE | EXTENT_ID | FILE_ID | BLOCK_ID | BLOCKS |
CC | CUSTACCOUNTS | TABLE | 0 | 5 | 896 | 128 |
CC | CUSTACCOUNTS | TABLE | 1 | 5 | 1152 | 128 |
CC | CUSTACCOUNTS | TABLE | 2 | 5 | 1408 | 128 |
CC | CUSTACCOUNTS | TABLE | 3 | 5 | 1664 | 128 |
CC | CUSTACCOUNTS | TABLE | 4 | 5 | 1792 | 128 |
CC | CUSTACCOUNTS | TABLE | 5 | 5 | 2048 | 128 |
CC | CUSTACCOUNTS | TABLE | 6 | 5 | 2304 | 128 |
CC | CUSTACCOUNTS | TABLE | 7 | 5 | 2560 | 128 |
CC | CUSTACCOUNTS | TABLE | 8 | 5 | 2688 | 128 |
CC | CUSTACCOUNTS | TABLE | 9 | 5 | 2944 | 128 |
CC | CUSTACCOUNTS | TABLE | 10 | 5 | 3200 | 128 |
CC | CUSTACCOUNTS | TABLE | 11 | 5 | 3456 | 128 |
CC | CUSTACCOUNTS | TABLE | 12 | 5 | 3712 | 128 |
使用UE打开数据文件。怎么确定block_id=1000在数据文件的哪个位置呢。块大小8192字节,8192×1000=8192,000,转换成16进制为007D 0000。随意改这一行第一个数字为FF,保存退出。
注:以上操作要在Oracle关闭状态下操作,别且要做好备份。
打开数据库,试试查找该表。
点击(此处)折叠或打开
- SQL> select * from CUSTACCOUNTS;
CAID CAVALIDFROM CAVALIDTO
---------- ------------------- -------------------
CANAME
----------------------------------------------------------------
CAPIN
----------------------------------------------------------------
CALASTLOGIN CAFAILEDLOGINS CALASTFAILEDLOGIN
------------------- -------------- -------------------
10763 1970-01-01 00:00:00 4312-12-31 23:59:59
0000000000010763
1234
1970-01-01 00:00:00 0 1970-01-01 00:00:00
--查询进行了一段时间后,报错
ERROR:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1000)
ORA-01110: data file 5: '/oradata/APR/datafile/o1_mf_ccdata_bn8l5x7l_.dbf'
这时,如果有RMAN备份,恢复很简单
点击(此处)折叠或打开
- RMAN> blockrecover datafile 5 block 1000;
坏块检查
当前已出现的坏块记录在视图v$database_block_corruption
点击(此处)折叠或打开
- select * from v$database_block_corruption
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------
5 1000 1 0 CORRUPT
点击(此处)折叠或打开
- [oracle@apr datafile]$ dbv file=o1_mf_ccdata_bn8l5x7l_.dbf
-
- DBVERIFY: Release 11.2.0.4.0 - Production on Sat May 2 16:54:32 2015
-
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
- DBVERIFY - Verification starting : FILE = /oradata/APR/datafile/o1_mf_ccdata_bn8l5x7l_.dbf
- Page 10000 is marked corrupt
- Corrupt block relative dba: 0x01402710 (file 5, block 10000)
- Bad header found during dbv:
- Data in bad block:
- type: 255 format: 2 rdba: 0x01402710
- last change scn: 0x0000.0011261f seq: 0x1 flg: 0x06
- spare1: 0x0 spare2: 0x0 spare3: 0x0
- consistency value in tail: 0x261f0601
- check value in block header: 0x7764
- computed block checksum: 0xf9
-
-
-
- DBVERIFY - Verification complete
-
- Total Pages Examined : 222848
- Total Pages Processed (Data) : 103647
- Total Pages Failing (Data) : 0
- Total Pages Processed (Index): 54090
- Total Pages Failing (Index): 0
- Total Pages Processed (Other): 2756
- Total Pages Processed (Seg) : 0
- Total Pages Failing (Seg) : 0
- Total Pages Empty : 62354
- Total Pages Marked Corrupt : 1
- Total Pages Influx : 0
- Total Pages Encrypted : 0
- Highest block SCN : 1135578 (0.1135578)
根据罗敏的《感悟Oracle核心技术》,推荐用ROWID扫描方法抢救数据。
//2016.04.08更新
首先需要定位到坏块属于哪个数据库对象。
点击(此处)折叠或打开
- select owner,segment_name,segment_type,block_id,blocks from dba_extents
where file_id = 5
and block_id 10000;
SELECT dbms_rowid.rowid_create(1, , , , 0) LOW_RID from DUAL;
最大ROW_ID
SELECT dbms_rowid.rowid_create(1, , , +1, 0) HI_RID from DUAL;
点击(此处)折叠或打开
- SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER='CC' AND OBJECT_NAME='CUSTACCOUNTS';
OBJECT_ID
----------
87524
点击(此处)折叠或打开
- SQL> SELECT dbms_rowid.rowid_create(1, 87524, 5, 1000, 0) LOW_RID from DUAL;
- LOW_RID
------------------------------------
AAAVXkAAFAAAAPoAAA - SQL> SELECT dbms_rowid.rowid_create(1, 87524, 5, 1001, 0) HI_RID from DUAL;
- HI_RID
------------------------------------
AAAVXkAAFAAAAPpAAA
点击(此处)折叠或打开
- --将坏块所在的表转存到另一“安全”的表空间
- CREATE TABLE ccbak.CUSTACCOUNTS_BAK
- TABLESPACE ccbak
- AS SELECT /*+ ROWID(A) */ * FROM CC.CUSTACCOUNTS A
- WHERE ROWID < 'AAAVXkAAFAAAAPoAAA';
- --继续
- INSERT INTO ccbak.CUSTACCOUNTS_BAK
- SELECT /*+ ROWID(A) */ * FROM CC.CUSTACCOUNTS A
- WHERE ROWID >= 'AAAVXkAAFAAAAPpAAA';
- commit;
点击(此处)折叠或打开
- drop table CC.CUSTACCOUNTS;
- purge recyclebin;
点击(此处)折叠或打开
- SQL> create directory ccdir as '/home/oracle/ccdir';
- [oracle@apr ~]$ expdp system/oracle@apr schemas=cc dumpfile=cc.dmp DIRECTORY=ccdir
点击(此处)折叠或打开
- SQL> drop tablespace ccdata including contents AND datafiles;
点击(此处)折叠或打开
- SQL> create tablespace ccdata datafile size 1G autoextend on;
-
- SQL> alter user cc default tablespace ccdata;
点击(此处)折叠或打开
- impdp system/oracle@apr DIRECTORY=ccdir DUMPFILE=cc.dmp SCHEMAS=cc
注:ROWID扫描方法适用于用户表和分区表段内的坏块,对段头和系统表坏块不适用。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1619107/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-1619107/