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

{ 参考官方文档 }

执行该工具先决条件：

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 running the 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 forrecovery are not available, then therecovery will not be possible.

Data Recovery Advisor  是11g才有的一个工具。该 Advisor 有两种形式：命令行模式、OEM操作模式。

A failure is a persistent datacorruption detected by the Health Monitor. Examples include physical andlogical data block corruptions and missing data files. Each failure hasa failure priority and failure status. Thepriority can be CRITICAL, HIGH, or LOW. The status can be OPEN or CLOSED.

You can run the LIST FAILURE commandto show all known failures. If failures exist, then run the ADVISE FAILURE commandin the same session to determine manual and automated repair options.

 

Failure Status

Every failure has a failure status: OPEN or CLOSED. The status of a failure is OPEN until the appropriate repairaction is invoked. The status changes to CLOSEDafter the failure is repaired.

Every time you execute LIST FAILURE, 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, running LIST FAILURE automatically closes them.

You can use CHANGE FAILURE to change the status of an openfailure to CLOSED if youhave fixed it manually. However, it makes sense to use CHANGE FAILURE ...CLOSED only iffor some reason the failure was not closed automatically. If a failure stillexists when you use CHANGE to close it manually, then Data Recover Advisorre-creates it with a different failure ID when the appropriate data integritycheck is executed.

Failure Priority

Every failure has a failure priority: CRITICAL, HIGH, or LOW. Data Recovery Advisor onlyassigns CRITICAL or HIGH priority to diagnosed failures.

Failures with CRITICAL priority require immediateattention because they make the whole database unavailable. For example, a diskcontaining a current control file may fail. Failures with HIGH priority make a database partlyunavailable or unrecoverable and usually have to be repaired quickly. Examplesinclude block corruptions and missing archived redo logs.

If a failure was assigned a HIGH priority, but the failure haslittle impact on database availability and recoverability, then you candowngrade the priority to LOW. A LOW priority indicates that a failure can be ignoreduntil more important failures are fixed.

By default LIST FAILURE displays only failures with CRITICAL and HIGH priority. You can use the CHANGE command to change the statusfor LOW and HIGH failures, but you cannot changethe status of CRITICAL failures.The main reason for changing a priority to LOW is to reduce the LIST FAILURE output. If a failure cannot berevalidated at this time (for example, because of another failure), then LIST FAILURE shows 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 are grouped under a single parent failure.Bydefault, Data Recovery Advisor lists information about the group of failures,although you can specify the DETAIL option to list information aboutthe individual 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

The ADVISE FAILURE command can present both manualand automatic repair options. data Recovery 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 execute theCREATE CONTROLFILE statement. Data Recovery Advisorpresents this manual action as mandatory because no automatic repair isavailable. In contrast, suppose that RMAN backups exist for a missing datafile. In this case, the REPAIR FAILURE command can perform the repair automatically byrestoring and recovering the data file. An optional manual action would be torestore the data 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. The ADVISE FAILURE command presents an option ID foreach automated repair option and summarizes the action.

Data Recovery Advisorperforms feasibility checks before recommending an automated repair. Forexample, Data Recovery Advisor checks that all backups and archived redo 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 are LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE, and CHANGE FAILURE.

A failure is detectedeither automatically by the database or through a manual check such asthe VALIDATE command. Youcan use the LIST FAILURE command to view problem statements for failures 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 the ADVISE FAILURE command to view repair options,which typically include both automated and manual options.

After executing ADVISE FAILURE, you can either repair failures manuallyor run the REPAIR FAILURE command to repair the failures automatically. A repair isan action that fixes one or more failures. Examples of repairs include blockmedia recovery, data file media recovery, and OracleFlashback Database. When you choose an automated repair 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