/*
说明：oracle 11g才拥有数据库恢复专家功能
实验目的：使用数据库恢复专家恢复数据库
实验步骤：
- tablespace--app_data
- backup---datafile-app_data_01.dbf-rman
- 破坏
- recover-data recover advisor-rman
*/
1、准备实验环境
创建表空间APP_DATA
创建用户TEST
SQL> CREATE TABLESPACE APP_DATA DATAFILE 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M; Tablespace created. SQL> CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE APP_DATA; User created. SQL> GRANT CONNECT,RESOURCE TO TEST; Grant succeeded. SQL> CONN TEST/TEST; Connected. SQL> select * from tab; no rows selected
2、使用RMAN，进行数据文件备份
RMAN> report schema; Report of database schema for database with db_unique_name ARCERZHANGDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF 2 720 SYSAUX *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF 3 300 UNDOTBS1 *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\UNDOTBS01.DBF 4 38 USERS *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\USERS01.DBF 5 100 EXAMPLE *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\EXAMPLE01.DBF 6 100 ASSM *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\ASSM_1.DBF 7 100 MSSM *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\MSSM_1.DBF 8 200 APP_DATA *** D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 29 TEMP 32767 D:\APP\MARYHU\ORADATA\ARCERZHANGDB\TEMP01.DBF
run{ allocate channel d1 type disk format 'D:\app\MaryHu\oradata\backup\datafile_app_data_01_%s_%p.bak'; backup datafile 8; }
RMAN> run{ 2> allocate channel d1 type disk format 'D:\app\MaryHu\oradata\backup\datafile_%s_%p.bus'; 3> backup datafile 8; 4> } released channel: ORA_DISK_1 allocated channel: d1 channel d1: SID=70 device type=DISK Starting backup at 05-JUN-13 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00008 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF channel d1: starting piece 1 at 05-JUN-13 channel d1: finished piece 1 at 05-JUN-13 piece handle=D:\APP\MARYHU\ORADATA\BACKUP\DATAFILE_5_1.BUS tag=TAG20130605T132340 comment=NONE channel d1: backup set complete, elapsed time: 00:00:01 Finished backup at 05-JUN-13 released channel: d1
3、关闭数据库，然后删除数据库文件app_data_01.dbf，然后再重新启动数据库，将会报错
C:\Users\MaryHu>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 5 13:26:55 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 778387456 bytes Fixed Size 1374808 bytes Variable Size 377488808 bytes Database Buffers 394264576 bytes Redo Buffers 5259264 bytes Database mounted. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF'
4、开始使用数据库恢复专家，开展恢复工作
C:\Users\MaryHu>rman target SYS/arcerzhang_db168@DB168 NOCATALOG Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 13:33:04 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ARCERZHA (DBID=3117207478, not open) using target database control file instead of recovery catalog RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 122 HIGH OPEN 05-JUN-13 One or more non-system datafiles are missing
RMAN> advise failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 122 HIGH OPEN 05-JUN-13 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=63 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF was unintentionally renamed or moved, restore it Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 8 Strategy: The repair includes complete media recovery with no data loss Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_2686889394.hm
数据库恢复专家给出的修复建议脚本:
d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_2686889394.hm
# restore and recover datafile restore datafile 8; recover datafile 8
同样，我们也可以通过动态性能视图查看数据库修复专家给出修复建议信息
C:\Users\MaryHu>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 5 13:43:36 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> desc v$ir_repair; Name Null? Type ----------------------------------------- -------- ---------------------------- REPAIR_ID NUMBER ADVISE_ID NUMBER SUMMARY VARCHAR2(32) RANK NUMBER TIME_DETECTED DATE EXECUTED DATE ESTIMATED_DATA_LOSS VARCHAR2(20) DETAILED_DESCRIPTION VARCHAR2(1024) REPAIR_SCRIPT VARCHAR2(512) ESTIMATED_REPAIR_TIME NUMBER ACTUAL_REPAIR_TIME NUMBER STATUS VARCHAR2(7) SQL> select repair_id,advise_id,summary,rank,status from v$ir_repair; REPAIR_ID ADVISE_ID SUMMARY RANK STATUS ---------- ---------- -------------------------------- ---------- ------- 142 141 NO DATA LOSS 1 NOT RUN SQL> desc v$ir_manula_checklist; ERROR: ORA-04043: object v$ir_manula_checklist does not exist SQL> desc v$ir_manual_checklist; Name Null? Type ----------------------------------------- -------- ---------------------------- ADVISE_ID NUMBER RANK NUMBER REQUIRED VARCHAR2(3) MESSAGE VARCHAR2(1024) SQL> col message format a50; SQL> select * from v$ir_manual_checklist; ADVISE_ID RANK REQ MESSAGE ---------- ---------- --- -------------------------------------------------- 141 0 NO If file D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DAT A_01.DBF was unintentionally renamed or moved, res tore it
开始使用数据库恢复专家，对数据库进行恢复操作
C:\Users\MaryHu>rman target SYS/arcerzhang_db168@DB168 NOCATALOG Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 13:52:32 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ARCERZHA (DBID=3117207478, not open) using target database control file instead of recovery catalog RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 122 HIGH OPEN 05-JUN-13 One or more non-system datafiles are missing RMAN> advise failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 122 HIGH OPEN 05-JUN-13 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=63 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF was unintentionally renamed or moved, restore i Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 8 Strategy: The repair includes complete media recovery with no data loss Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_3363844532.hm RMAN> repair failure preview; Strategy: The repair includes complete media recovery with no data loss Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_3363844532.hm contents of repair script: # restore and recover datafile restore datafile 8; recover datafile 8; RMAN> repair failure; Strategy: The repair includes complete media recovery with no data loss Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_3363844532.hm contents of repair script: # restore and recover datafile restore datafile 8; recover datafile 8; Do you really want to execute the above repair (enter YES or NO)? YES executing repair script Starting restore at 05-JUN-13 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 00008 to D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF channel ORA_DISK_1: reading from backup piece D:\APP\MARYHU\ORADATA\BACKUP\DATAFILE_5_1.BUS channel ORA_DISK_1: piece handle=D:\APP\MARYHU\ORADATA\BACKUP\DATAFILE_5_1.BUS tag=TAG20130605T132340 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 Finished restore at 05-JUN-13 Starting recover at 05-JUN-13 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 05-JUN-13 repair failure complete Do you want to open the database (enter YES or NO)? YES database opened
注意：list failure;advise failure;repair failure;命令一定要处于同一个session当中，否则会出错.
修复完成之后，查看已经关闭级别的错误信息，即可查到刚才被修复的记录信息
RMAN> list failure closed; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 5 CRITICAL CLOSED 22-JAN-13 System datafile 1: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF' needs media recovery 2 CRITICAL CLOSED 22-JAN-13 Control file needs media recovery 125 HIGH CLOSED 05-JUN-13 Datafile 8: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF' is missing Impact: Some objects in tablespace APP_DATA might be unavailable 122 HIGH CLOSED 05-JUN-13 One or more non-system datafiles are missing 11 HIGH CLOSED 22-JAN-13 Datafile 2: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF' needs media recovery Impact: Some objects in tablespace SYSAUX might be unavailable 8 HIGH CLOSED 22-JAN-13 One or more non-system datafiles need media recovery
再次执行list failure命令，便不会再出现错误信息
RMAN> list failure; no failures found that match specification