从oracle 11gR1版本开始,oracle提供了数据库数据文件修复的建议方案,从而更好的让用户去选择恢复方法
这就显的越来越智能,只需要我们按照修复建议向导去做即可。
环境 Oracle Linux AS 5.5+oracle 11.2 (归档模式)
1、备份前的情况
SQL> conn fmismain/fmismain
Connected.
SQL> select count(*) from xtdw;
COUNT(*)
----------
29
SQL> select count(*) from xtdw3;
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/fmismain.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中的FMISMAIN.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/fmismain.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>
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
转载于:https://blog.51cto.com/19880614/1256783