坏块的解决
使用dbv工具检查一下数据文件：
[oracle@logserver logdw]$ dbv file=test02.dbf blocksize=8192
SQL> startup ;
ORACLE instance started.
Total System Global Area 1887350784 bytes
Fixed Size 2214456 bytes
Variable Size 1560282568 bytes
Database Buffers 301989888 bytes
Redo Buffers 22863872 bytes
Database mounted.
Database opened.
SQL> connect test/test
SQL> show user;
USER is "TEST"
SQL> set autotrace on ;
SQL> select count(id) from test_table;
select count(id) from test_table
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 16, block # 15)
ORA-01110: data file 16: '/u02/oradata/logdw/test02.dbf'
确定坏块的对象是什么?
SQL>set linesize 120
SQL>SELECT tablespace_name,
segment_type,
segment_name,
owner
FROM dba_extents
WHERE file_id = 9 AND 33858 BETWEEN block_id AND block_id + blocks - 1;
a. file# block# 可以从错误信息中得到
b. 如果是temp檔中出现坏块 是没有记录返回的
c. 通常容易出现坏块的对象有:
数据字典(system表空间) 回滚段临时段 联机日志 索引或者分区索引 表
------------索引坏块-----------------
如果返回的结果确定坏的是索引段只要把这个索相删除然后重建一下就可以了
drop index
create index
就可以了
----------------------------------
ERROR at line 1:
ORA-12801: error signaled in parallel query server P007
ORA-01578: ORACLE data block corrupted (file # 5, block # 17)
ORA-01110: data file 5: '/u01/oracle/oradata/orcl/cqwr_m1.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 2
-----------------使用RMAN修复ORA-01578--------------------------------
RMAN> blockrecover datafile 5 block 17;
RMAN> blockrecover datafile 16 block 16;
-- 或者组合起来
RMAN> blockrecover datafile 16 block 15 datafile 16 block 16;
RMAN> blockrecover datafile 16 block 15,16;
如果坏块很多，例如我们就要写很长很长的命令，此时可以试一下：
RMAN> blockrecover corruption list;
我们可以借助rman对数据库、文件或表空间进行“扫描”，检查出所有的坏块。
RMAN> backup validate database;
RMAN> backup validate tablespace test;
RMAN> backup validate datafile 16;
如果数据库比较大或者表空间也比较大，backup validate就会长时间占用大量I/O资源，因此范围应该尽量小。
RMAN> backup validate datafile 16;
Starting backup at 2010-06-23 10:42:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00016 name=/u02/oradata/logdw/test02.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
16 FAILED 0 7 23 175356605
File Name: /u02/oradata/logdw/test02.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 2 6
Other 0 10
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_30130.trc for details
Finished backup at 2010-06-23 10:42:26
只要让Oracle直到了哪些是坏块就好办了，是blockrecover即可修复坏块：
RMAN> blockrecover corruption list;
Starting recover at 2010-06-23 11:00:49
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 2 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2010-06-23 11:10:48
-----------------使用exp和imp尽力挽救数据-----------------
如果没有rman备份呢？以下主要参考盖国强的《Oracle中模拟及修复数据块损坏》
我们再用同样的方法编辑test01.dbf，定位到最后，将数据破坏。
可以预见test_table的数据已经损坏，索引没有损坏。
test$logdw@logdw SQL> select count(*) from test_table ;
select count(*) from test_table
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 15, block # 23)
ORA-01110: data file 15: '/u02/oradata/logdw/test01.dbf'
某些时候我们可以使用bbed 将坏块修正也是可以的
如果我们将任务定位为可以容忍数据丢失，希望能尽量挽救，减少数据丢失。
通过设置内部事件，设置在全表扫描时跳过损坏的数据块
系统级别
ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;
ALTER SYSTEM SET EVENTS='10231 trace name context off' ;
而后导出数据库
----------------------------------
session级别设定:
SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
(2) 创建一个临时表tmp_table把原始表中除坏块以外的资料都检索出来
SQL> CREATE TABLE alantest.tmp_table as select * from alantest.test_alan;
表已创建。
SQL> select count(*) from alantest.test_alan;
COUNT(*)
----------
6281
(3) 更改表名
SQL>alter table alantest.test_alan rename to alantest.test_alan_0607;
SQL>alter table alantest.tmp_table rename to alantest.test_alan;
-------------------------------------------------------------------------------------
exec DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,1,'USERS');--表数据
exec DBMS_REPAIR.ADMIN_TABLES('ORPHAN_TABLE',2,1,'USERS');--索引数据
检查坏块：dbms_repair.check_object ， 这里的schema_name是用户，比如我在sys下建立的表空间，这里就是sys
DECLARE
cc NUMBER;
BEGIN
DBMS_REPAIR.check_object (schema_name => 'CQWR',
object_name => 'T_LTE_CARD_INFO',
corrupt_count => cc);
DBMS_OUTPUT.put_line (a => TO_CHAR (cc));
END;
15
PL/SQL 过程已成功完成。
看到这里用dbms_repair.check，检查的结果corrupt_count＝15，有15个块损坏，和dbv的结果一致。
check完之后，在我们刚在创建的REPAIR_TABLE中查看块损坏信息：
SQL> SELECT * from repair_table
在这个table中，可以看到损坏的block的信息，这里的信息和我们用dbv得到的一致。
3.定位坏块：
dbms_repair.fix_corrupt_blocks
只有将坏块信息写入定义的REPAIR_TABLE后，才能定位坏块
DECLARE
cc NUMBER;
BEGIN
DBMS_REPAIR.fix_corrupt_blocks (schema_name => 'TEST1107',
object_name => 'TEST',
fix_count => cc);
DBMS_OUTPUT.put_line (a => TO_CHAR (cc));
END;
4.跳过坏块：
我们前面虽然定位了坏块，但是，如果我们访问table还是会得到错误信息。
这里需要用skip_corrupt_blocks来跳过坏块：
EXEC dbms_repair.skip_corrupt_blocks(schema_name => 'TEST1107',object_name => 'TEST',flags => 1);
SQL> select count(*) from test1107.test;
COUNT(*)
----------
4490
5.处理index上的无效键值；
DECLARE
cc NUMBER;
BEGIN
DBMS_REPAIR.dump_orphan_keys (schema_name => 'TEST1107',
object_name => 'I_TEST',
object_type => 2,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_TABLE',
key_count => CC);
END;
SELECT * FROM ORPHAN_TABLE;
22 rows selected
表示损失了22行数据
我们根据这个结果来考虑是否需要rebuild index.
6.重建freelist：rebuild_freelists
EXEC dbms_repair.rebuild_freelists(schema_name => 'TEST1107',object_name => 'TEST');
