Oracle11新特性——備份恢復功能增強（九）

打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g在備份和恢復方面新增了很多的功能，無論是效能、功能性、安全性和可操作性方面都有了不同程度的提高。

這一篇介紹11g的新特性Data Recovery Advisor。

Oracle11新特性——備份恢復功能增強（一）：http://yangtingkun.itpub.net/post/468/412991

Oracle11新特性——備份恢復功能增強（二）：http://yangtingkun.itpub.net/post/468/414647

Oracle11新特性——備份恢復功能增強（三）：http://yangtingkun.itpub.net/post/468/414834

Oracle11新特性——備份恢復功能增強（四）：http://yangtingkun.itpub.net/post/468/414941

Oracle11新特性——備份恢復功能增強（五）：http://yangtingkun.itpub.net/post/468/416015

Oracle11新特性——備份恢復功能增強（六）：http://yangtingkun.itpub.net/post/468/423531

Oracle11新特性——備份恢復功能增強（七）：http://yangtingkun.itpub.net/post/468/426943

Oracle11新特性——備份恢復功能增強（八）：http://yangtingkun.itpub.net/post/468/427877

11g新增特性Data Recovery Advisor，這個內嵌工具可以自動檢測物理資料錯誤，可以給出建議修改方式，執行修復操作。

看一個簡單的例子，首先對測試表空間進行備份：

[oracle@yangtk ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Nov 9 00:35:19 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA11G (DBID=4026820313)

RMAN> list failure;

using target database control file instead of recovery catalog
no failures found that match specification

RMAN> backup tablespace users;

Starting backup at 09-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 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=00004 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf
channel ORA_DISK_1: starting piece 1 at 09-NOV-07
channel ORA_DISK_1: finished piece 1 at 09-NOV-07
piece handle=/data1/backup/1ij0irqj_1_1 tag=TAG20071109T003531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 09-NOV-07

Starting Control File and SPFILE Autobackup at 09-NOV-07
piece handle=/data1/backup/c-4026820313-20071109-00 comment=NONE
Finished Control File and SPFILE Autobackup at 09-NOV-07

RMAN> exit

Recovery Manager complete.

下面建立測試用表：

[oracle@yangtk ~]$ sqlplus yangtk/yangtk

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Nov 9 00:36:27 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_failure tablespace users as select * from dba_objects;

Table created.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)
2 from t_failure where rownum = 1;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 12

SQL> select count(*) from t_failure;

COUNT(*)
----------
68918

下面透過文字編輯工具如UltraEdit或vi，對資料檔案中表的內容直接進行修改。

修改之後，透過alter tablespace offline的方式，使得Oracle檢查到錯誤：

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
Process ID: 3144
Session ID: 118 Serial number: 241

SQL> select * from dba_tablespaces where tablespace_name = 'USERS';
ERROR:
ORA-03114: not connected to ORACLE

SQL> conn / as sysdba
Connected.
SQL> select status from dba_tablespaces where tablespace_name = 'USERS';

STATUS
---------
ONLINE

Oracle直接中止了程式，這說明Oracle已經檢查到了這個錯誤：

[oracle@yangtk datafile]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Nov 9 00:49:08 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA11G (DBID=4026820313)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7582 HIGH OPEN 09-NOV-07 One or more non-system datafiles are corrupt

果然，Oracle已經檢查到了錯誤，使用detail可以看到更詳細的資訊：

RMAN> list failure detail;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7582 HIGH OPEN 09-NOV-07 One or more non-system datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 7582
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7585 HIGH OPEN 09-NOV-07 Datafile 4: '/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf' is corrupt
Impact: Some objects in tablespace USERS might be unavailable

下面可以使用advise failure獲取恢復建議：

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7582 HIGH OPEN 09-NOV-07 One or more non-system datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 7582
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7585 HIGH OPEN 09-NOV-07 Datafile 4: '/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf' is corrupt
Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 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 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/oracle/diag/rdbms/ora11g_p/ora11g/hm/reco_1941598854.hm

下面可以透過repair failure來自動修復錯誤，如果對Oracle的repair failure不放心，還可以透過preview來檢查具體的恢復步驟：

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/oracle/diag/rdbms/ora11g_p/ora11g/hm/reco_1941598854.hm

contents of repair script:
# restore and recover datafile
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';

現在可以執行恢復了：

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/oracle/diag/rdbms/ora11g_p/ora11g/hm/reco_1941598854.hm

contents of repair script:
# restore and recover datafile
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';

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

sql statement: alter database datafile 4 offline

Starting restore at 09-NOV-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf
channel ORA_DISK_1: reading from backup piece /data1/backup/1ij0irqj_1_1
channel ORA_DISK_1: piece handle=/data1/backup/1ij0irqj_1_1 tag=TAG20071109T003531
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-NOV-07

Starting recover at 09-NOV-07
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 09-NOV-07

sql statement: alter database datafile 4 online
repair failure complete

恢復完成，最後再檢查一下failure的狀態：

RMAN> list failure;

no failures found that match specification

RMAN> list failure closed;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7585 HIGH CLOSED 09-NOV-07 Datafile 4: '/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf' is corrupt
Impact: Some objects in tablespace USERS might be unavailable
7582 HIGH CLOSED 09-NOV-07 One or more non-system datafiles are corrupt

這個新功能使得系統恢復變得更加容易。

這裡介紹的是Data Recovery Advisor的命令列介面，這個功能還提供一個圖形化的介面，整合在OEM中，這裡就不描述了。