使用Data Recovery Advisor診斷和修復failures

今天在rman 上面敲了半天，晚上實在扛不住太累了。看了集電影回來準備敲敲sql。因為我今天把我修改的亂七八糟的hr schema 給drop 了。所以打算用指令碼建立回來。出現了出現了資料庫沒有開啟的錯誤，原來資料庫沒有open，那就open 吧，結果出現了下面的錯誤。

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle11g/oradata/oracl/system01.dbf'

ERROR at line 1:

ORA-01109: database not open

在rman 試一下看能夠開啟資料庫，其實我知道肯定是open 不起來的，但是我還是決定要來這邊試一試（這種僥倖心理不好）。open 不起來再來恢復。

[oracle@zeng ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Apr 21 22:16:07 2012

connected to target database: ORACL (DBID=1821380921, not open)

RMAN> startup force

Oracle instance started

database mounted

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of startup command at 04/21/2012 22:16:56

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle11g/oradata/oracl/system01.dbf'

現在來使用list failure 命令來檢視下更詳細的錯誤原因。該命令可以列出failures的優先順序critical(致命),high（高）,low（低），和failures 的狀態open（未解決的），closed（已經關閉的）。

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

1182 CRITICAL OPEN 21-APR-12 System datafile 1: '/opt/oracle11g/oradata/oracl/system01.dbf' needs media recovery

1185 HIGH OPEN 21-APR-12 One or more non-system datafiles need media recovery

從list failure 的輸出我們可以知道系統表空間的資料檔案system01.dbf 需要執行介質恢復。一個或多個非系統表空間資料檔案也需要執行介質恢復。下面使用advise failure 來看看Data Recovery Advisor 工具對這些failures 的修復提供了那些建議。

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

1182 CRITICAL OPEN 21-APR-12 System datafile 1: '/opt/oracle11g/oradata/oracl/system01.dbf' needs media recovery

1185 HIGH OPEN 21-APR-12 One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If you restored the wrong version of data file /opt/oracle11g/oradata/oracl/system01.dbf, then replace it with the correct one

2. If you restored the wrong version of data file /opt/oracle11g/oradata/oracl/sysaux01.dbf, then replace it with the correct one

3. If you restored the wrong version of data file /opt/oracle11g/oradata/oracl/undotbs01.dbf, then replace it with the correct one

4. If you restored the wrong version of data file /opt/oracle11g/oradata/oracl/users01.dbf, then replace it with the correct one

5. If you restored the wrong version of data file /opt/oracle11g/oradata/tbs.dbf, then replace it with the correct one

Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Recover database

Strategy: The repair includes complete media recovery with no data loss

Repair script. /opt/oracle11g/diag/rdbms/oracl/oracl/hm/reco_1277731850.hm

強大的Data Recovery Advisor 提供了修復failures 的措施。下面來

嘗試使用 repair failure 來修復failures。之所以說是嘗試是因為Data Recovery Advisor不一定能夠修復所有的錯誤，當執行完修復操作以後，Data Recovery Advisor 會重新評估所有存在的錯誤，以確定他們是否被修復，因為當修復一個failure 的時候可能會自動的修復由於該failure 引起的其他failures。如果在修復的過程中出現了錯誤，會觸發

一次新的評估以查詢錯誤並修復。因為Data Recovery Advisor並不是萬能的所以他也會做出誤判導致不能對症下藥出現錯誤，所以需要“反省一次”。

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /opt/oracle11g/diag/rdbms/oracl/oracl/hm/reco_1277731850.hm

contents of repair script.:

# recover database

recover database;

#repair failure 在執行實際修復操作之前需要使用者確認。

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

executing repair script

Starting recover at 21-APR-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /opt/oracle11g/oradata/oracl/system01.dbf

destination for restore of datafile 00002: /opt/oracle11g/oradata/oracl/sysaux01.dbf

destination for restore of datafile 00003: /opt/oracle11g/oradata/oracl/undotbs01.dbf

destination for restore of datafile 00004: /opt/oracle11g/oradata/oracl/users01.dbf

channel ORA_DISK_1: reading from backup piece /back/oracleback/incr_ORACL781201228281

channel ORA_DISK_1: piece handle=/back/oracleback/incr_ORACL781201228281 tag=TAG20120421T162028

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00005: /opt/oracle11g/oradata/tbs.dbf

channel ORA_DISK_1: reading from backup piece /back/oracleback/incr_ORACL781201284291

channel ORA_DISK_1: piece handle=/back/oracleback/incr_ORACL781201284291 tag=TAG20120421T162028

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /opt/oracle11g/oradata/oracl/system01.dbf

destination for restore of datafile 00002: /opt/oracle11g/oradata/oracl/sysaux01.dbf

destination for restore of datafile 00003: /opt/oracle11g/oradata/oracl/undotbs01.dbf

destination for restore of datafile 00004: /opt/oracle11g/oradata/oracl/users01.dbf

channel ORA_DISK_1: reading from backup piece /back/oracleback/incr_test0vn90cbk

channel ORA_DISK_1: piece handle=/back/oracleback/incr_test0vn90cbk tag=TAG20120421T162939

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00005: /opt/oracle11g/oradata/tbs.dbf

channel ORA_DISK_1: reading from backup piece /back/oracleback/incr_test10n90cdb

channel ORA_DISK_1: piece handle=/back/oracleback/incr_test10n90cdb tag=TAG20120421T162939

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 21-APR-12

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

以上是rman 執行介質恢復時候的資訊輸出，末尾詢問你是否要open 資料庫。

今日心得：

1、解決問題只是做對了一半，還需要找出問題的原因加以研究，以防後患。我這裡是找不出這個問題的原因了。因為我今天用rman 執行了很多備份和恢復的練習，中途太累又看了一集電影。在返回rman中繼續時，已經暈頭轉向搞不起是那些操作導致了不能open資料庫。

2、學習不要把自己搞的太累，要注意方法。像我今天把自己搞的腦袋那麼亂，那麼累，有些失敗。

使用Data Recovery Advisor診斷和修復failures

相關文章