【Oracle 11g新特性】data recover Advisor自动恢复建议

从oracle 11gR1版本开始，oracle提供了数据库数据文件修复的建议方案，从而更好的让用户去选择恢复方法
这就显的越来越智能，只需要我们按照修复建议向导去做即可。

环境 Oracle Linux AS 5.5+oracle 11.2 （归档模式)

1、备份前的情况

SQL> conn fmcs/fmcs
Connected.
SQL> select count(*) from xtcs;
COUNT(*)
----------
29
SQL> select count(*) from xtcs3;
COUNT(*)
----------
30
SQL>

2、rman备份

[oracle@ora1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 22 09:13:05 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4120198168)
RMAN> backup database format '/soft/bak/%U.full_bak';
Starting backup at 22-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
including current control file in backup set
input datafile file number=00001 name=+DGDATGA/ora11g/datafile/system.260.723998053
input datafile file number=00002 name=+DGDATGA/ora11g/datafile/sysaux.259.723998085
input datafile file number=00003 name=+DGDATGA/ora11g/datafile/undotbs1.258.723998109
input datafile file number=00005 name=+DGDATGA/ora11g/datafile/fmcs.266.724020873
input datafile file number=00004 name=+DGDATGA/ora11g/datafile/users.256.723998149
channel ORA_DISK_1: starting piece 1 at 22-JUL-10
channel ORA_DISK_1: finished piece 1 at 22-JUL-10
piece handle=/soft/bak/01ljdn3b_1_1.full_bak tag=TAG20100722T091419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:07
Finished backup at 22-JUL-10

3.关闭数据库，误删除一个数据库的数据文件

手工删除asm中的FMCS.266.724020873文件

3.启动数据库时报错，提示找不到FMISMAIN.266.724020873数据文件
[oracle@ora1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 22 09:19:16 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 481259520 bytes
Fixed Size 1337352 bytes
Variable Size 264243192 bytes
Database Buffers 209715200 bytes
Redo Buffers 5963776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DGDATGA/ora11g/datafile/fmismain.266.724020873'

4.advise failure (data recover Advisor)

[oracle@ora1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 22 09:39:42 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4120198168, not open)
RMAN> list；
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "all, archivelog, backed, backuppiece, backupset, backup, completed, controlfilecopy, copy, datafilecopy, db_unique_name, device, expired, failure, foreign, global, incarnation, like, proxy, recoverable, restore, script, tag"
RMAN-01007: at line 2 column 1 file: standard input
RMAN>
RMAN> ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure, connect, convert, copy, create, crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list, mount, open, print, quit, recover, register, release, repair, replace, report, reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, startup, switch, transport, unregister, upgrade, validate, {, "
RMAN-01007: at line 1 column 1 file: standard input

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
22 HIGH OPEN 22-JUL-10 One or more non-system datafiles are missing

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
22 HIGH OPEN 22-JUL-10 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file +DGDATGA/ora11g/datafile/fmismain.266.724020873 was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script. /oracle/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2728654069.hm

RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script. /oracle/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2728654069.hm
contents of repair script.:
# restore and recover datafile
restore datafile 5;
recover datafile 5;

查看修复脚本：

[oracle@ora1 ~]$ more /oracle/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2728654069.hm
# restore and recover datafile
restore datafile 5;
recover datafile 5;

RMAN> REPAIR FAILURE NOPROMPT;

恢复分三步走：

第一步，查看
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
22 HIGH OPEN 22-JUL-10 One or more non-system datafiles are missing

第二步，建议
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
22 HIGH OPEN 22-JUL-10 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file +DGDATGA/ora11g/datafile/fmismain.266.724020873 was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script. /oracle/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_634924069.hm

第三步，修复。
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script. /oracle/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_634924069.hm
contents of repair script.:
# restore and recover datafile
restore datafile 5;
recover datafile 5;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 22-JUL-10
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 00005 to +DGDATGA/ora11g/datafile/fmcs.266.724020873
channel ORA_DISK_1: reading from backup piece /soft/bak/01ljdn3b_1_1.full_bak
channel ORA_DISK_1: piece handle=/soft/bak/01ljdn3b_1_1.full_bak tag=TAG20100722T091419
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:13
Finished restore at 22-JUL-10
Starting recover at 22-JUL-10
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-JUL-10
repair failure complete
Do you want to open the database (enter YES or NO)? YES
database opened

RMAN>