實例講解Oracle9i數據壞塊的處理

　　筆者在一台生產用測試庫上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

　　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

From:http://tw.wingwit.com/Article/program/Oracle/201311/18869.html