system表空间有坏块，求处理办法。ora-01578

[乘着叶片往前飞的蜗牛]

1.验证数据文件
[oracle@oracle-slave ~]$ dbv file=/oradisk/oradata/iddbsvr/system01.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 07:38:17 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /oradisk/oradata/iddbsvr/system01.dbf

DBV-00200: Block, dba 4195962, already marked corrupted

DBV-00200: Block, dba 4195978, already marked corrupted


DBVERIFY - Verification complete

Total Pages Examined : 81920
Total Pages Processed (Data) : 49691
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17925
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 254626859 (0.254626859)
我这个数据库的的system01.dbf文件有坏块，启动使用都没什么异常，查询表都没问题。再创建物化视图时才知道有错误。
2.创建一个异库表的本地物化视图时出错

3.查询业务系统运行日志，错误从做备份策略当天开始就出现了错误
我查了日志，从我做了备份以后开始就有错误了，rman的备份也都是成功的。这些备份能否用户恢复system表空间。日志显示9月4日开始就有错误
我是在3号晚上做得备份，备份之前把所有数据文件做了物理拷贝，我刚测试了物理拷贝的system01.dbf没问题，结果如下：
[oracle@oracle-slave iddbsvr]$ dbv file=../../oradata_20130903/iddbsvr/system01.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 08:00:24 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = ../../oradata_20130903/iddbsvr/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined : 81920
Total Pages Processed (Data) : 49690
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 12483
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1821
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17926
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 252052428 (0.252052428)


求指点一个安全的恢复方法，业务系统白天不能关闭，目前使用没发现其他异常，是不是应该早停止业务系统来做数据库的修复？

[惜分飞-Tel:[phone]]

1. 使用你上次cp的文件+归档日志做还原恢复 2. 定位坏块的内容，考虑重建坏块对象，如果是index rebuild --------------------------------------------------------------- qq号：107644445 BLOG：http://www.xifenfei.com

[乘着叶片往前飞的蜗牛]

在线等回复！！！

[康康125]

需要坏块恢复 刚做了一个实验： 1）rman备份 RMAN> backup database; Starting backup at 18-SEP-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=36 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=00001 name=/u01/oracle/oradata/bj/system01.dbf input datafile file number=00002 name=/u01/oracle/oradata/bj/sysaux01.dbf input datafile file number=00005 name=/u01/oracle/oradata/bj/example01.dbf input datafile file number=00003 name=/u01/oracle/oradata/bj/undotbs01.dbf input datafile file number=00004 name=/u01/oracle/oradata/bj/users01.dbf channel ORA_DISK_1: starting piece 1 at 18-SEP-13 channel ORA_DISK_1: finished piece 1 at 18-SEP-13 piece handle=/oracle/rman/BJ_1071506218_10_1_20130918.bak tag=TAG20130918T013200 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 18-SEP-13 channel ORA_DISK_1: finished piece 1 at 18-SEP-13 piece handle=/oracle/rman/BJ_1071506218_11_1_20130918.bak tag=TAG20130918T013200 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-SEP-13 2）先把system表空间搞出坏块 ORA-01578: ORACLE data block corrupted (file # 1, block # 146) ORA-01110: data file 1: '/u01/oracle/oradata/bj/system01.dbf' Process ID: 3672 Session ID: 1 Serial number: 5 RMAN> blockrecover datafile 1 block 146; Starting recover at 18-SEP-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00001 channel ORA_DISK_1: reading from backup piece /oracle/rman/BJ_1071506218_10_1_20130918.bak channel ORA_DISK_1: piece handle=/oracle/rman/BJ_1071506218_10_1_20130918.bak tag=TAG20130918T013200 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:25 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 18-SEP-13 我这是模拟你的system表空间坏块 为了保险起见你可以现在测试库恢复试一试，这种方法。

[乘着叶片往前飞的蜗牛]

RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 2248146944 bytes Fixed Size 1220580 bytes Variable Size 754974748 bytes Database Buffers 1476395008 bytes Redo Buffers 15556608 bytes RMAN> alter database mount; database mounted RMAN> blockrecover datafile 1 block 1674; Starting blockrecover at 18-SEP-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=320 devtype=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00001 channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1 channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1 tag=TAG20130915T020145 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:08 failover to previous backup channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00001 channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/4/data.IDDBSVR.live.0.825905120.1 channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/4/data.IDDBSVR.live.0.825905120.1 tag=TAG20130912T020159 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:08 failover to previous backup channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00001 channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/3/data.IDDBSVR.live.0.825216917.1 channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/3/data.IDDBSVR.live.0.825216917.1 tag=TAG20130904T025126 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:16 failover to previous backup RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of blockrecover command at 09/18/2013 22:11:08 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 1 found to restore ---------------------------------------------------------------------------- 从备份一开始，system表中间就有坏块，好像没办法从备份中做块恢复。

[乘着叶片往前飞的蜗牛]

----------------------------------------------- 将原来dbv验证没坏块的物理拷贝，还原以后就有坏块。坏块数据已经被加到备份里了？ ---------------------------------------------- RMAN> restore datafile 1; Starting restore at 18-SEP-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oradisk/oradata/iddbsvr/system01.dbf channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1 channel ORA_DISK_1: restored backup piece 1 piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/0/data.IDDBSVR.live.0.826164326.1 tag=TAG20130915T020145 channel ORA_DISK_1: restore complete, elapsed time: 00:00:26 Finished restore at 18-SEP-13 RMAN> recover datafile 1; Starting recover at 18-SEP-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /oradisk/oradata/iddbsvr/system01.dbf channel ORA_DISK_1: reading from backup piece /oradisk/oradiskbak/iddbsvr/rmanbak/databak/2/data.IDDBSVR.live.1.826336975.1 channel ORA_DISK_1: restored backup piece 1 piece handle=/oradisk/oradiskbak/iddbsvr/rmanbak/databak/2/data.IDDBSVR.live.1.826336975.1 tag=TAG20130917T020104 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 starting media recovery media recovery complete, elapsed time: 00:00:18 Finished recover at 18-SEP-13 RMAN> alter database open; database opened RMAN> exit Recovery Manager complete. [oracle@oracle-slave iddbsvr]$ dbv file=/oradisk/oradata/iddbsvr/system01.dbf DBVERIFY: Release 10.2.0.1.0 - Production on Wed Sep 18 22:31:54 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /oradisk/oradata/iddbsvr/system01.dbf DBV-00200: Block, dba 4195962, already marked corrupted DBV-00200: Block, dba 4195978, already marked corrupted DBVERIFY - Verification complete Total Pages Examined : 81920 Total Pages Processed (Data) : 49691 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 12483 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1821 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 17925 Total Pages Marked Corrupt : 2 Total Pages Influx : 0 Highest block SCN : 254705434 (0.254705434) ---------------------------------------------- 使用restore datafile 1; 以后 system01.dbf就有坏块。 我除了导出全库，重做实例意外还有办法吗？

[乘着叶片往前飞的蜗牛]

[quote=引用 4 楼 xifenfei 的回复:] 1. 使用你上次cp的文件+归档日志做还原恢复 2. 定位坏块的内容，考虑重建坏块对象，如果是index rebuild --------- 坏块号和内容 --------------- SQL> select * from V$DATABASE_BLOCK_CORRUPTION; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 1 1674 1 252052885 LOGICAL 1 1658 1 252052881 LOGICAL SQL> select segment_type,owner||'.'||segment_name 2 from dba_extents 3 where 1 = file_id and 01674 between block_id and block_id+blocks -1; SEGMENT_TYPE ------------------ OWNER||'.'||SEGMENT_NAME -------------------------------------------------------------------------------- INDEX SYS.I_JOB_NEXT SQL> select segment_type,owner||'.'||segment_name from dba_extents where 1 = file_id and 1658 between block_id and block_id+blocks -1; SEGMENT_TYPE ------------------ OWNER||'.'||SEGMENT_NAME -------------------------------------------------------------------------------- TABLE SYS.JOB$

[惜分飞-Tel:[phone]]

INDEX SYS.I_JOB_NEXT 可以rebuild INDEX SYS.I_JOB_NEXT 是否可以重建，需要考虑下 整体来说，这两个东西问题不大

[乘着叶片往前飞的蜗牛]

好的，如果我备份的时候表空间数据正常，应该可以用这个方法。我先试试，多谢

[惜分飞-Tel:[phone]]

引用 9 楼 melody_lee 的回复:

我把 sys.job$ 表drop了 然后从其他客户系统里面把建表语句拿过来重建了表和索引。 添加了应有的user_jobs. 验证system01.dbf没有坏块。 但是使用 SQL> select * from V$DATABASE_BLOCK_CORRUPTION; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 1 1674 1 252052885 LOGICAL 1 1658 1 252052881 LOGICAL 还有有一样的坏块结果

Z 这个需要rman重新扫描，才会除掉

[乘着叶片往前飞的蜗牛]

我把 sys.job$ 表drop了 然后从其他客户系统里面把建表语句拿过来重建了表和索引。 添加了应有的user_jobs. 验证system01.dbf没有坏块。 但是使用 SQL> select * from V$DATABASE_BLOCK_CORRUPTION; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 1 1674 1 252052885 LOGICAL 1 1658 1 252052881 LOGICAL 还有有一样的坏块结果