使用DRA诊断问题并提出建议

DRA，即数据恢复指导（Data Recovery Advisor，很多书也称数据恢复顾问），它是一个诊断和修复数据库问题的工具。共有两个接口：RMAN应用程序和Enterprise Manager。DRA能够生成脚本来修复数据文件和（在某些环境下）控制文件受到的损坏：它不提供有关服务器参数文件或联机重做日志文件问题的建议。DRA依赖于自动诊断知识库（ADR，Automatic Diagnostic Repository）和Health Monitor。


[jeff@rhel55 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 3 10:29:05 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
SQL> startup
SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     28
Current log sequence           30

SQL> shutdown immediate;
SQL> startup mount;
SQL> quit
[jeff@rhel55 ~]$ mv /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/jeff/sysaux01.dbf#模拟数据库引发一个错误，然后使用DRA报告
[jeff@rhel55 ~]$ rman target /
RMAN> list backup of tablespace sysaux;
RMAN> backup as backupset tablespace sysaux;
RMAN> shutdown immediate;
RMAN> exit;

Recovery Manager complete.

[jeff@rhel55 ~]$ sqlplus /nolog
SQL> conn / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area  636100608 bytes
Fixed Size                  1338392 bytes
Variable Size             478151656 bytes
Database Buffers          150994944 bytes
Redo Buffers                5615616 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

SQL> quit
[jeff@rhel55 ~]$ rman target /
RMAN> list failure;
RMAN> advise failure;
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
19482      HIGH     OPEN      03-OCT-12     One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
19482      HIGH     OPEN      03-OCT-12     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=17 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=22 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=23 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/orcl/sysaux01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      NOARCHIVELOG mode restore datafile 2
  Strategy: The repair includes complete media recovery with no data loss
  Repair script. /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2495954950.hm

RMAN> exit


Recovery Manager complete.

[jeff@rhel55 ~]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2495954950.hm
 # NOARCHIVELOG mode restore datafile
   restore datafile 2;
   recover datafile 2;
[jeff@rhel55 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 3 09:52:05 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1302004769, not open)

RMAN> restore datafile 2;

Starting restore at 03-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=17 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=20 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=18 device type=DISK

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 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupse                                       t/2012_10_03/o1_mf_nnndf_TAG20121003T094211_86q5znqo_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_03/                                       o1_mf_nnndf_TAG20121003T094211_86q5znqo_.bkp tag=TAG20121003T094211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 03-OCT-12

RMAN> recover datafile 2;

Starting recover at 03-OCT-12
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 03-OCT-12

RMAN> startup force

Oracle instance started
database mounted
database opened

Total System Global Area     636100608 bytes

Fixed Size                     1338392 bytes
Variable Size                478151656 bytes
Database Buffers             150994944 bytes
Redo Buffers                   5615616 bytes

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     /u01/app/oracle/oradata/orcl/system01.dbf
2    940      SYSAUX               ***     /u01/app/oracle/oradata/orcl/sysaux01.dbf
3    1035     UNDOTBS1             ***     /u01/app/oracle/oradata/orcl/undotbs01.dbf
4    231      USERS                ***     /u01/app/oracle/oradata/orcl/users01.dbf
5    100      EXAMPLE              ***     /u01/app/oracle/oradata/orcl/example01.dbf
6    200      RMAN_TBS             ***     /u01/app/oracle/oradata/orcl/rman_tbs.dbf
7    100      STORE_TBS            ***     /u01/app/oracle/oradata/store_tbs.dbf
8    100      FSDATA               ***     /u01/app/oracle/oradata/orcl/fsdata01.dbf
9    100      USERS                ***     /u01/app/oracle/oradata/users02.dbf
10   100      TOOLS_BF             ***     /u01/app/oracle/oradata/tools_bf01.dbf
11   5        TBS1                 ***     /u01/app/oracle/product/11.0.2/db_1/dbs/tbs01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    62       TEMP                 32767       /u01/app/oracle/oradata/orcl/temp01.dbf

RMAN>exit

Recovery Manager complete.


当然，这也可以用Enterprise Manager来做，过程都差不多的，下面是我恢复的结果：

Recovery Results

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 3 12:05:42 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: ORCL (DBID=1302004769, not open) using target database control file instead of recovery catalog RMAN> echo set on RMAN> REPAIR FAILURE USING REPAIRID 20027 NO OPEN DATABASE NOPROMPT; Strategy: The repair includes complete media recovery with no data loss Repair script. /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1198328248.hm contents of repair script.: # NOARCHIVELOG mode restore datafile restore datafile 2; recover datafile 2; executing repair script Starting restore at 03-OCT-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=18 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=20 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=17 device type=DISK 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 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_03/o1_mf_nnndf_TAG20121003T094211_86q5znqo_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_03/o1_mf_nnndf_TAG20121003T094211_86q5znqo_.bkp tag=TAG20121003T094211 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:15 Finished restore at 03-OCT-12 Starting recover at 03-OCT-12 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery media recovery complete, elapsed time: 00:00:16 Finished recover at 03-OCT-12 repair failure complete RMAN> exit; Recovery Manager complete.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27795363/viewspace-745571/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27795363/viewspace-745571/