RMAN中有一个恢复顾问可以针对不是非常棘手的数据库恢复故障提供针对性建议。此助手有4中模式:1.显示故障详细信息 2.提供故障处理建议 3.运行修复故障命令 4.更改故障状态
恢复丢失的数据文件
1.重命名一个数据文件,模拟数据文件丢失
[oracle@zabbix@qc ~]$ mv /oracle/oradata/yang/users01.dbf /oracle/oradata/yang/users01.dbf.bak
[oracle@zabbix@qc ~]$ sqlp
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 21 18:57:58 2015
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, OLAP, Data Mining and Real Application Testing options
sys@YANG> shutdown abort
ORACLE instance shut down.
sys@YANG> startup mount
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
sys@YANG> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/oradata/yang/users01.dbf'
sys@YANG> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2.用Data Recovery Advisor进行恢复
这个例子演示的比较简单,在数据库告警日志中就提示的很明确,某个数据文件找不到了。用Data Recovery Advisor看下如何进行恢复的。
[oracle@zabbix@qc ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 21 18:59:41 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: YANG (DBID=3607655636, not open)
#list failure 看看发生了什么不对劲的地方。
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
770422 HIGH OPEN 21-DEC-15 One or more non-system datafiles are missing
#列出这个错误代码代表的详细信息
RMAN> list failure 770422 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
770422 HIGH OPEN 21-DEC-15 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 770422
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
770725 HIGH OPEN 21-DEC-15 Datafile 4: '/oracle/oradata/yang/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
#提供下处理建议
RMAN> advise failure 770422;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
770422 HIGH OPEN 21-DEC-15 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=189 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=221 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /oracle/oradata/yang/users01.dbf was unintentionally renamed or moved, restore it
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: /oracle/diag/rdbms/yang/yang/hm/reco_454714551.hm
#查看处理建议是什么
[oracle@zabbix@qc ~]$ cat /oracle/diag/rdbms/yang/yang/hm/reco_454714551.hm
# restore and recover datafile
restore datafile 4;
recover datafile 4;
[oracle@zabbix@qc ~]$
#对故障处理建议进行预演
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/yang/yang/hm/reco_454714551.hm
contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;
#如果接受处理建议把preview去掉就可以了。
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/yang/yang/hm/reco_454714551.hm
contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 21-DEC-15
using channel ORA_DISK_1
using channel ORA_DISK_2
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 /oracle/oradata/yang/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/YANG/backupset/2015_12_21/o1_mf_nnndf_TAG20151221T185253_c7hpj8dp_.bkp
channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/YANG/backupset/2015_12_21/o1_mf_nnndf_TAG20151221T185253_c7hpj8dp_.bkp tag=TAG20151221T185253
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 21-DEC-15
Starting recover at 21-DEC-15
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-DEC-15
repair failure complete
#还原恢复完毕后询问是不是要打开数据库。选择yes
Do you want to open the database (enter YES or NO)? yes
database opened
RMAN>