repair failure修复坏块

最新推荐文章于 2023-09-05 15:08:37 发布

原创最新推荐文章于 2023-09-05 15:08:37 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个Oracle数据库中损坏数据块的修复过程。通过备份数据库、故意破坏数据块并使用RMAN工具进行修复，验证了repairfailure命令的有效性。讨论了可能的失败原因，并提供了详细的步骤说明。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

刚刚逛论坛，看到一个网友说他dd破坏的块用repair failure修复不了，我这里也实验一把试试

测试之前先做一个备份
RMAN> backup database;
Starting backup at 12-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 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=/opt/app/oracle/oradata/goolen/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/goolen/sysaux01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/goolen/undotbs01.dbf
input datafile file number=00006 name=/opt/app/oracle/oradata/goolen/goolen01.dbf
channel ORA_DISK_1: starting piece 1 at 12-MAR-14
channel ORA_DISK_1: finished piece 1 at 12-MAR-14
piece handle=/opt/app/oracle/product/11.2.0/dbs/1rp3029l_1_1 tag=TAG20140312T112124 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-MAR-14
channel ORA_DISK_1: finished piece 1 at 12-MAR-14
piece handle=/opt/app/oracle/product/11.2.0/dbs/1sp302ao_1_1 tag=TAG20140312T112124 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-MAR-14

创建一个测试表
SQL> create table scott.rcsy tablespace goolen as select empno,ename from scott.emp where rownum <=2;
Table created.

SQL> set lines 120
SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT' and table_name='RCSY';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT RCSY GOOLEN

SQL> col file_name for a55
SQL> select file_name,file_id,TABLESPACE_NAME from dba_data_files where tablespace_name='GOOLEN';
FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------------------------------- ---------- ------------------------------
/opt/app/oracle/oradata/goolen/goolen01.dbf 6 GOOLEN

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.rowid_block_number(rowid) block# from scott.rcsy;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) BLOCK#
------------------------------------ ----------
6 51
6 51
现在我们来破坏6号文件的51号块
[oracle@localhost xiaoming]$ dd if=/dev/zero of=/opt/app/oracle/oradata/goolen/goolen01.dbf count=1 seek=51 bs=8192 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.9785e-05 s, 206 MB/s

SQL> select * from scott.rcsy;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN

SQL> alter system flush buffer_cache;
System altered.

再次查询报错，有坏块
SQL> select * from scott.rcsy;
select * from scott.rcsy
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 51)
ORA-01110: data file 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf'

使用rman来恢复这个坏块
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
8102 HIGH OPEN 12-MAR-14 Datafile 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf' contains one or more corrupt blocks

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
8102 HIGH OPEN 12-MAR-14 Datafile 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Perform block media recovery of block 51 in file 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/goolen/goolen/hm/reco_697173025.hm

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/goolen/goolen/hm/reco_697173025.hm
contents of repair script:
# block media recovery
recover datafile 6 block 51;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting recover at 12-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/product/11.2.0/dbs/1rp3029l_1_1
channel ORA_DISK_1: piece handle=/opt/app/oracle/product/11.2.0/dbs/1rp3029l_1_1 tag=TAG20140312T112124
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-MAR-14
repair failure complete

RMAN> exit

Recovery Manager complete.

SQL> alter system flush buffer_cache;
System altered.

SQL> select * from scott.rcsy;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN

可以看到我们这里的测试是可以恢复的，我估计是他之前的备份集有问题，或者恢复完后没有flush buffercache，查询还是有可能报错的