打算写一系列的文章介绍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

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

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

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中，这里就不描述了。

ITPub博客

Oracle11新特性——备份恢复功能增强（九）

最新文章