笔者在一台生产用测试库上SELECT一个表时出现ORA一个块损坏以前学习过块损坏怎么处理到还真没遇到过今天总算让我遇到了还是一台生产用测试库就不用很紧张了
数据库版本是Oraclei的RMAN有一个blockrecover命令可以在线修复坏块以下就是使用RMAN修复坏块的过程
SQL> conn owi/owi
Connected
SQL> select * from dpa_history;
select * from dpa_history
*
ERROR at line :
ORA: ORACLE data block corrupted (file # block # )
ORA: data file : /d/app/oracle/oradata/dpa/dpadbf
报ORA数据块损坏以下使用RMAN命令查询是否可以使用blockrecover命令恢复以及怎样恢复
使用rman登录catalog数据库
[ora@rmanserver ~]$ rman target sys/oracle@dpa catalog rman/rman
Recovery Manager: Release Production
Copyright (c) Oracle Corporation All rights reserved
connected to target database: DPA (DBID=)
connected to recovery catalog database
查找最近datafile 的全备份今天下午刚做了一次RMAN的全备份
RMAN> list backup of datafile ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
Full K DISK :: MAR
BP Key: Status: AVAILABLE Tag: TAGT
Piece Name: /d/fullbackup/_data__
List of Datafiles in backup set
File LV Type Ckp SCN Ckp Time Name
Full MAR /d/app/oracle/oradata/dpa/dpadbf
查找SCN 以后的archivelog是否有备份
RMAN> list backup of archivelog scn from
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
K DISK :: MAR
BP Key: Status: AVAILABLE Tag: TAGT
Piece Name: /d/fullbackup/_arch_
List of Archived Logs in backup set
Thrd Seq Low SCN Low Time Next SCN Next Time
MAR MAR
MAR MAR
查找sequence 以后的archivelog是否有备份
RMAN> list copy of archivelog from sequence ;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
A MAR /d/arch/_dbf
A MAR /d/arch/_dbf
查询online archive log
SQL> select sequence#membersarchivedstatus from v$log;
SEQUENCE# MEMBERS ARC STATUS
NO CURRENT
YES INACTIVE
YES INACTIVE
从以上查询中可以看出datafile 有一次最近的全备份有全备份以来的所有archivelogonline redo log
下面开始blockreocver其实命令很简单
RMAN> blockrecover datafile block ;
Starting blockrecover at MAR
allocated channel: ORA_DISK_
channel ORA_DISK_: sid= devtype=DISK
channel ORA_DISK_: restoring block(s)
channel ORA_DISK_: specifying block(s) to restore from backup set
restoring blocks of datafile
channel ORA_DISK_: restored block(s) from backup piece
piece handle=/d/fullbackup/_data__ tag=TAGT params=NULL
channel ORA_DISK_: block restore complete
starting media recovery
archive log thread sequence is already on disk as file /d/arch/_dbf
archive log thread sequence is already on disk as file /d/arch/_dbf
channel ORA_DISK_: starting archive log restore to default destination
channel ORA_DISK_: restoring archive log
archive log thread= sequence=
channel ORA_DISK_: restoring archive log
archive log thread= sequence=
channel ORA_DISK_: restored backup piece
piece handle=/d/fullbackup/_arch_ tag=TAGT params=NULL
channel ORA_DISK_: restore complete
media recovery complete
Finished blockrecover at MAR
再SELECT一下表DPA_HISTORY
SQL> select * from dpa_history;
PRODLINEID BARCODE PA
S*DB
S*DB
S*DB
S*DB
S*DB
S*DB
S*DB
S*DB
S*DB
