maycn

0 积分	1 好友	1 主题

发消息

[Exadata硬件] ORA-01578错误，块损坏，使用repair failure无法恢复问题。

1^#

发表于 2014-3-12 00:08:36 | 查看: 4281| 回复: 6

本帖最后由 maycn 于 2014-3-12 21:25 编辑

我测试的环境是，RatHat 6.3下。oracle 11gr2  单机安装的。

现测试block恢复的 repair failure恢复方法，发现无法恢复。看了alert.log没有发现为什么，首先说本人初级。希望多多指导。

我们的表情况：
SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT' and table_name='RCSY';

OWNER          TABLE_NAME       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT          RCSY       USERS

SQL> select file_name,file_id,TABLESPACE_NAME from dba_data_files where tablespace_name='USERS';

FILE_NAME
----------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/oracle/oradata/updba/users01.dbf
   4 USERS

select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.rowid_block_number(rowid) block# from scott.rcsy;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)    BLOCK#
------------------------------------ ----------
   4       535
   4       535

测试，我dd掉了4号文件的535这个块。

[oracle@dns ~]$ dd if=/dev/zero of=/u01/oracle/oradata/updba/users01.dbf count=1 seek=535 bs=8192 conv=notrunc

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from rcsy;
select * from rcsy;
select * from rcsy
            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 535)
ORA-01110: data file 4: '/u01/oracle/oradata/updba/users01.dbf'

根据报错信息，已经告诉我们了,4号文件535block有问题。

下面恢复
RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
462       HIGH    OPEN    12-MAR-14    Datafile 4: '/u01/oracle/oradata/updba/users01.dbf' contains one or more corrupt blocks

已经发现这个文件。
RMAN> advise failure;
========================
Option Repair Description
------ ------------------
1    Perform block media recovery of block 535 in file 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/oracle/diag/rdbms/updba/updba/hm/reco_3300706224.hm

查看oracle提供的这个脚本

[root@dns Desktop]# cat /u01/oracle/diag/rdbms/updba/updba/hm/reco_3300706224.hm
# block media recovery
recover datafile 4 block 535;
[root@dns Desktop]#

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/diag/rdbms/updba/updba/hm/reco_3300706224.hm

contents of repair script:
# block media recovery
recover datafile 4 block 535;

Do you really want to execute the above repair (enter YES or NO)? y
executing repair script

Starting recover at 12-MAR-14
using channel ORA_DISK_1
searching flashback logs for block images until SCN 1201350
finished flashback log search, restored 0 blocks                -----,恢复0块

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/UPDBA/backupset/2014_03_12/o1_mf_nnndf_TAG20140312T030951_9kyqw0fb_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/UPDBA/backupset/2014_03_12/o1_mf_nnndf_TAG20140312T030951_9kyqw0fb_.bkp tag=TAG20140312T030951
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 ----block恢复完成。

starting media recovery                                        ----开始介质恢复
some blocks not recovered: See trace file for details 有些block没有恢复？？？
media recovery complete, elapsed time: 00:00:03

Finished recover at 12-MAR-14
repair failure complete

查看表，确认，
select * from rcsy
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 535)
ORA-01110: data file 4: '/u01/oracle/oradata/updba/users01.dbf'

没有能够恢复，为什么？没有弄明白，需要我才上传跟踪文件。

实在不知道，麻烦各位帮忙分析一下，谢谢。