ORACLE 11g新特性 DRA(Data Recovery Adviseor)

bjdcuaamzz

　　{ 参考官方文档 }
　　执行该工具先决条件：
　　The targetdatabase instance must be started. The databasemust be a single-instancedatabase and must not be a physical standby database.Make sure that at most oneRMAN session is runningthe REPAIRFAILURE command. The only exception is REPAIRFAILURE ... PREVIEW, which ispermitted in concurrent RMAN sessions.To performan automated repair, the Data Recovery Advisor mayrequire specific backups andarchived redo logs. If the files needed forrecoveryare not available, then therecovery will not be possible.
　　Data Recovery Advisor 是11g才有的一个工具。该 Advisor 有两种形式：命令行模式、OEM操作模式。
　　Afailureis a persistent datacorruption detected by the Health Monitor. Examples include physical andlogicaldata block corruptions and missing data files. Each failure hasafailurepriorityandfailure status. Thepriority can beCRITICAL,HIGH, orLOW. The status can beOPENorCLOSED.
　　You can run theLISTFAILUREcommandto show all known failures. If failures exist, then run theADVISE FAILUREcommandin the same session to determine manual and automated repairoptions.
　　
　　Failure Status
　　Every failure has afailure status:OPENorCLOSED.The status of a failure isOPENuntil the appropriate repairaction is invoked. The status changes toCLOSEDafter thefailure is repaired.
　　Every time you executeLISTFAILURE,Data Recovery Advisor revalidates allopen failures and closes failures that no longer exist. Thus, if you fixed somefailures as part of a separate procedure, or if the failures were transientproblems that disappeared by themselves, runningLISTFAILUREautomatically closes them.
　　You can useCHANGE FAILUREto change the status of an openfailure toCLOSEDifyouhave fixed it manually. However, it makes sense to useCHANGE FAILURE ...CLOSEDonly iffor some reason the failure was not closed automatically. If a failure stillexists when you useCHANGEtoclose it manually, then Data Recover Advisorre-creates it with a different failure ID when the appropriate data integritycheck is executed.
　　Failure Priority
　　Every failure hasafailure priority:CRITICAL,HIGH,orLOW. Data Recovery Advisor onlyassignsCRITICALorHIGHpriorityto diagnosed failures.
　　Failures withCRITICALpriority require immediateattention because they make thewhole database unavailable. For example, a diskcontaining a current control file may fail. Failures withHIGHpriority make a database partlyunavailable or unrecoverable and usually haveto be repaired quickly. Examplesinclude block corruptions and missing archived redo logs.
　　If a failure was assigned aHIGHpriority, but the failure haslittle impact on databaseavailability and recoverability, then you candowngrade the priority toLOW. ALOWpriority indicates that a failurecan be ignoreduntil more important failures are fixed.
　　By defaultLIST FAILUREdisplays only failures withCRITICALandHIGHpriority.You can use theCHANGEcommand to change the statusforLOWandHIGHfailures,but you cannot changethe status ofCRITICALfailures.The main reason for changing a priority toLOWis to reduce theLISTFAILUREoutput. If a failure cannot berevalidated at this time (for example, because of another failure), thenLIST FAILUREshows the failure as open.
　　
　　Failure Grouping
　　For clarity, Data Recovery Advisorgroups related failures together. For example, if 20 different blocks in a fileare corrupted, then these failures aregrouped under a single parent failure.Bydefault, Data Recovery Advisor lists information about the group of failures,although you can specify theDETAILoption to list information abouttheindividual subfailures.
　　A subfailure has the same format as afailure. You can get advice on a subfailure and repair it separately or in acombination with any other failure.
　　
　　Manual Actions and Automatic Repair Options
　　TheADVISE FAILUREcommand can present both manualand automatic repair options.dataRecovery Advisor categorizes manualactions as either mandatory or optional.
　　In some cases, the only possible actionsare manual. Suppose that no backups exist for a lost control file. In thiscase, the manual action is to executetheCREATE CONTROLFILEstatement. Data Recovery Advisorpresents this manual action as mandatory because no automatic repair isavailable. In contrast, suppose that RMAN backups exist for amissing datafile. In this case, theREPAIR FAILUREcommand can perform the repair automatically byrestoring and recovering the data file. An optional manual action would be torestore thedata file if it was unintentionally renamed or moved. Data RecoveryAdvisor suggests optional manual actions if they might prevent a more extremeform of repair such as data file restore and recovery.
　　In contrast to manual actions, automatedrepairs can be performed by Data Recovery Advisor. TheADVISE FAILUREcommandpresents an option ID foreach automated repair option and summarizes the action.
　　Data Recovery Advisorperformsfeasibility checks before recommending an automated repair. Forexample, Data Recovery Advisor checks that all backups and archivedredo logsneeded for media recovery are present and consistent. Data Recovery Advisor mayneed specific backups and archived redo logs. If the files needed for recoveryare not available, then recovery is not possible.
　　
　　命令行模式是通过在RMAN环境下执行相关命令，相关命令如下：
　　In the RMAN command-line interface, theData Recovery Advisor commands areLIST FAILURE,ADVISEFAILURE,REPAIR FAILURE, andCHANGE FAILURE.
　　Afailureis detectedeither automatically by the database or through a manual check such astheVALIDATEcommand. Youcan use theLIST FAILUREcommand to view problem statements forfailures and the effect ofthese failures on database operations. Each failure is uniquely identified by afailure number. In the same RMAN session, you can then use theADVISE FAILUREcommand to view repair options,which typically include both automated andmanual options.
　　After executingADVISE FAILURE, you can either repair failures manuallyor run theREPAIR FAILUREcommand to repair the failures automatically. Arepairisan action that fixes oneor more failures. Examples of repairs includeblockmedia recovery,data file media recovery, andOracleFlashback Database. When you choose an automatedrepair option,Data Recovery Advisor verifies the repair success and closes the relevantrepaired failures.
　　
　　RMAN> LIST FAILURE;
RMAN> ADVISE FAILURE;
RMAN> REPAIR FAILURE PREVIEW;
RMAN> REPAIR FAILURE;
　　List Failure- liststhe results of previously executed failure assessments. Revalidates existingfailures and closes them,if possible.
Advise Failure- presents manual and automatic repair options
Repair Failure- automatically fix failures by running optimalrepair option, suggested by ADVISE FAILURE. Revalidates existing failures whencompleted.
　　
　　EXAMPLE：
　　SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE  11.2.0.1.0   Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
　　
　　SQL> selectfile_name,file_id,tablespace_name,status,online_status from dba_data_files;

FILE_NAME                     FILE_ID TABLESPACE_NAME      STATUS ONLINE_
-------------------------------------------------- ----------------------------------- --------- -------
/u01/app/oracle/oradata/ocpyjg/users01.dbf        4 USERS          AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/undotbs01.dbf       3 UNDOTBS1        AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/sysaux01.dbf        2 SYSAUX         AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/system01.dbf        1 SYSTEM         AVAILABLE SYSTEM
/u01/app/oracle/oradata/ocpyjg/example01.dbf       5 EXAMPLE         AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/tp1.dbf          6 TP1           AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/tp6.dbf          7 TP6           AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/tp7.dbf          9 TP7           AVAILABLE ONLINE
/u01/app/oracle/oradata/ocpyjg/rc_data.dbf        10 RC_DATA         AVAILABLE ONLINE
　　
　　SQL> createuser u_tp6 identified by u_tp6 default tablespace tp6;
　　 User created.

SQL> grant connect ,resource to u_tp6;
  Grant succeeded.

SQL> alter user u_tp6 account unlock;
  User altered.

SQL> conn u_tp6/u_tp6
  Connected.
SQL> select * from cat;
  no rows selected

SQL> create table tab_1(id number(1),name varchar2(20));
  Table created.

SQL> insert into tab_1 values(1,'yjg');
  1 row created.

SQL> commit;
  Commit complete.

SQL> select * from tab_1;
    ID NAME
---------- --------------------
    1 yjg
　　
　　[oracle@yjgocpocpyjg]$ ll
total 1893548
-rw-r----- 1 oracle oinstall  9748480 May 15 11:02 control01.ctl
-rw-r----- 1 oracle oinstall  9748480 May 15 11:02 control02.ctl
-rw-r----- 1 oracle oinstall  9748480 Apr 19 13:24 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 May 15 11:02 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 May 15 11:02 rc_data.dbf
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo03.log
-rw-r----- 1 oracle oinstall 618668032 May 15 11:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 744497152 May 15 11:02 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 May 15 10:43 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 15 11:02 tp1.dbf
-rw-r----- 1 oracle oinstall 10493952 May 15 11:02 tp6.dbf
-rw-r----- 1 oracle oinstall 10493952 May 15 11:02 tp7.dbf
-rw-r----- 1 oracle oinstall 110108672 May 15 11:02 undotbs01.dbf
-rw-r----- 1 oracle oinstall  5251072 May 15 11:02 users01.dbf
[oracle@yjgocp ocpyjg]$ rm -rf tp6.dbf;
[oracle@yjgocp ocpyjg]$ ll
total 1883284
-rw-r----- 1 oracle oinstall  9748480 May 15 11:12 control01.ctl
-rw-r----- 1 oracle oinstall  9748480 May 15 11:12 control02.ctl
-rw-r----- 1 oracle oinstall  9748480 Apr 19 13:24 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 May 15 11:02 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 May 15 11:02 rc_data.dbf
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 15 11:12 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo03.log
-rw-r----- 1 oracle oinstall 618668032 May 15 11:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall 744497152 May 15 11:07 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 May 15 10:43 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 15 11:02 tp1.dbf
-rw-r----- 1 oracle oinstall 10493952 May 15 11:02 tp7.dbf
-rw-r----- 1 oracle oinstall 110108672 May 15 11:11 undotbs01.dbf
-rw-r----- 1 oracle oinstall  5251072 May 15 11:02 users01.dbf
　　
　　SQL> createtable tab_3 as select * from tab_1;
create table tab_3 as select * from tab_1
                 *
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/app/oracle/oradata/ocpyjg/tp6.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select * from tab_1;
select * from tab_1
       *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/ocpyjg/tp6.dbf'
　　说明7号文件已经对数据库的运行产生了影响
　　
RMAN> list failure;

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

Failure ID Priority Status  Time Detected Summary
---------- -------- --------- ------------- -------
562    HIGH   OPEN  15-MAY-13   One or more non-system datafiles are missing

RMAN> advise failure;

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

Failure ID Priority Status  Time Detected Summary
---------- -------- --------- ------------- -------
562    HIGH   OPEN  15-MAY-13   One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /u01/app/oracle/oradata/ocpyjg/tp6.dbf was unintentionallyrenamed or moved, restore it
2. If you have an export of tablespace TP6, then drop and re-create thetablespace and import the data.
3. Contact Oracle Support Services if the preceding recommendations cannotbe used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available
　　没有给出修复脚本，根据建议手动恢复；
　　RMAN> restoredatafile 7;

Starting restore at 15-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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 00007 to/u01/app/oracle/oradata/ocpyjg/tp6.dbf
channel ORA_DISK_1: reading from backup piece/u01/app/oracle/rman_backup/OCPYJG_30
channel ORA_DISK_1: piece handle=/u01/app/oracle/rman_backup/OCPYJG_30tag=TAG20130514T171018
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-MAY-13

RMAN> recover datafile 7;
　　
Starting recover at 15-MAY-13
using channel ORA_DISK_1

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

Finished recover at 15-MAY-13
　　
　　[oracle@yjgocpocpyjg]$ ll
total 1893548
-rw-r----- 1 oracle oinstall  9748480 May 15 15:48 control01.ctl
-rw-r----- 1 oracle oinstall  9748480 May 15 15:48 control02.ctl
-rw-r----- 1 oracle oinstall  9748480 Apr 19 13:24 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 May 15 11:02 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 May 15 11:02 rc_data.dbf
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 15 15:47 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 15 11:02 redo03.log
-rw-r----- 1 oracle oinstall 618668032 May 15 15:45 sysaux01.dbf
-rw-r----- 1 oracle oinstall 744497152 May 15 15:47 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 May 15 10:43 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 15 11:02 tp1.dbf
-rw-r----- 1 oracle oinstall 10493952 May 15 15:47 tp6.dbf
-rw-r----- 1 oracle oinstall 10493952 May 15 11:02 tp7.dbf
-rw-r----- 1 oracle oinstall 110108672 May 15 15:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall  5251072 May 15 11:02 users01.dbf
　　
　　SQL> select *from tab_1;


ID NAME
---------- --------------------
1 yjg
　　说明影响已经消除
　　RMAN> listfailure;

no failures found that match specification