New Feature of Oracle11g -- RMANDataRecoveryAdvisor (DRA) Description

1. Data Recovery Advisor (DRA) Description 1.1 DRA description

DRA automatically collects data fault information when an error occurs. In addition, it can check faults in advance. In this mode, it can detect and analyze data faults before the database process detects corruption and sends error messages (note that repair is always done under human control ).

Data faults may be very serious. For example, if the latest log file is missing, the database cannot be started. Some data faults (such as block damages in data files) are not catastrophic because they do not stop the database or prevent you from starting an Oracle instance. The data recovery guide can handle either of the following situations: you cannot start the database (because necessary database files are missing or these database files are inconsistent or damaged ), another situation is that the file is damaged during the running.

The first choice for solving serious Data faults is to first transfer the fault to the standby database (provided that the fault is configured under Data Guard), so that the user can restore the connection as soon as possible. The primary cause of Data failure needs to be repaired, but fortunately, this operation will not affect users.

1.2 automatic diagnosis Workflow

The automatic diagnosis workflow in Oracle Database11g can perform workflow steps for you. With data recovery instructions, you only need to start suggestions and repair.

1. The health status monitor automatically performs the check and records the fault and fault symptom as the "search result" to the automatic diagnosis archives (ADR.

2. The data recovery guide combines the search result with the fault. It lists the previously executed evaluation results, including the severity (severe or high) of the fault ).

3. If you require the system to provide fault repair suggestions, the data recovery guide will map the fault to the automatic and manual repair options, check the basic feasibility, and provide you with repair suggestions.

4. You can choose to manually perform repair or request data recovery guidance to perform this operation for you.

5. In addition to the main "passive" checks automatically executed by the health status monitor and data recovery guide, Oracle also recommends using the VALIDATE command for "preventive" checks.

1.3 steps for using DRA in RMAN

Oracle OEMs are already very intelligent, and these operations can be performed on OEMs. Here we only look at the steps implemented using the RMAN command.

If you suspect or know that the database is faulty, you can use the list failure command to obtain information about these faults. You can list all faults or some faults and limit the output in multiple ways. The fault is uniquely identified by the fault number. Note that these fault numbers are not consecutive, so the interval between them is meaningless.

The advise failure command displays the recommended repair options for the specified fault. It prints the input fault summary and implicitly closes all opened faults that have been fixed. When no option is used, the default behavior is to provide suggestions for all failures recorded in ADR with a priority of CRITICAL and HIGH.

In the same RMAN Session, the repair failure command is used after the advise failure command. By default, this command uses a recommended Repair Option When the ADVISEFAILURE command was last executed in the current session. If no REPAIR options are available, the repair failure command starts the implicit advise failure command. After the repair is completed, the command will close the fault.

The change failure command changes the fault priority or closes one or more failures. Only HIGH or LOW fault priority can be changed. After the fault is fixed, the open fault is closed implicitly. However, you can also explicitly disable the fault.

1.3.1 list data faults

The rman listfailure command can list faults. If the target instance uses the recovery directory, it can be in STARTED mode; otherwise, it must be in MOUNTED mode. The list failure command does not start a new fault diagnosis check; it lists the previous evaluation results. Repeat the list failure command to verify all existing faults.

If the database diagnoses new faults (between command execution), these new faults are displayed.

If the user manually repairs a fault or the temporary fault disappears, the data recovery guide will delete the fault from the list failure output.

Syntax description:

(1) failnum: displays the number of failures in the repair options.

(2) ALL: lists ALL priority faults.

(3) CRITICAL: Lists faults with a priority of CRITICAL and in the OPEN state. These faults make the entire database unavailable (such as missing control files), so you need to pay attention immediately.

(4) HIGH: Lists faults with a HIGH priority and in the OPEN state. These faults make part of the database unavailable or unrecoverable, so they should be repaired as soon as possible (such as missing archived redo logs ).

(5) LOW: Lists faults with LOW priority and OPEN status. Low-priority faults can be repaired after more important faults are fixed.

(6) CLOSED: only the CLOSED faults are listed.

(7) exclude failure: the specified fault number is excluded from the list.

(8) DETAIL: Lists faults by expanding the merged faults. For example, if multiple blocks in a file are corrupted, the DETAIL option lists each block corruption.

For example:

RMAN> list failure;

RMAN> list failure detail;

1.3.2 repair suggestions

The rman advise failure command displays the recommended repair options for the specified fault. If you execute this command from Enterprise Manager, Data Guard displays a Repair Option (however, if you execute this command directly from the RMAN command line, Data Guard does not display the repair option ). The advise failure command prints the input fault summary. This command will implicitly close all opened faults that have been fixed.

When no option is used, the default behavior is to provide suggestions for all faults whose priority is CRITICAL and HIGH in the automatic diagnostic data portfolio (ADR. If a new fault is recorded in the ADR after the last list failure command is executed, the command contains a WARNING before providing suggestions for all CRITICAL and HIGH faults.

Perform Two common repair options: No data loss repair and data loss repair.

DRA generates a script when generating the Automatic Repair Option to show how the RMAN plan to fix the fault. If you do not want the data recovery guide to automatically fix the fault, you can execute manual repair from this script. The operating system (OS) Position of the script is displayed at the end of the command output. You can check the script and customize it (if needed). You can also manually execute the script (for example, when the audit clue requires manual operations ).

The rmanadvise failure command has the following purposes:

(1) display the input fault list Overview

(2) Includes warnings (if a new fault occurs in the ADR)

(3) display manual Checklist

(4) List A recommended Repair Option

(5) generate a repair script (for automatic or manual repair)

...

Repair script:

/U01/app/oracle/diag/rdbms/orcl/hm/reco_2979128860.hm

RMAN>

Syntax:

ADVISE FAILURE

[ALL | CRITICAL | HIGH | LOW | failnum [, failnum,…] ]

[Exclude failure failnum [, failnum,…] ]

1.3.3 execute repair

This command should be used after the advise failure command in the same RMAN session. By default (no option), this command uses a recommended Repair Option for the last execution of advise failure in the current session. If no repair options are available, the REPAIRFAILURE command starts the implicit advise failure command.

By default, you need to confirm whether to execute this command, because it may take a lot of time to complete the changes. During the repair process, the output of this command indicates that the repair process is being executed.

After the repair is completed, the command will close the fault.

Syntax:

REPAIR FAILURE

[PREVIEW]

[NOPROMPT]

You cannot run multiple concurrent repair sessions. However, concurrent REPAIR... PREVIEW session.

(1) PREVIEW: if the repair is not executed, the RMAN script generated previously contains all the repair operations and comments is displayed.

(2) NOPROMPT indicates that confirmation is not required.

Troubleshooting example

RMAN> repair failure preview;

RMAN> repair failure;

The rmanrepair failure command has the following purposes:

(1) follow the advise failure command

(2) repair the specified fault

(3) Disable repaired faults

1.4 classification (and closure) faults

The change failure command is used to CHANGE the fault priority or disable one or more failures.

Syntax:

CHANGE FAILURE

{ALL | CRITICAL | HIGH | LOW | failnum [, failnum,…] }

[Exclude failure failnum [, failnum,…] ]

{PRIORITY {CRITICAL | HIGH | LOW} |

CLOSE}-change the fault status to Disabled

[NOPROMPT]-user confirmation not required

You can only change the fault priority from HIGH to LOW and from LOW to HIGH. An error occurs when you change the CRITICAL priority. (One reason for changing the fault priority from HIGH to LOW is to prevent the fault from being displayed in the default output LIST of the list failure command. For example, if a block corruption has a HIGH priority, you may want to temporarily change it to LOW when the block is in a rarely used tablespace .)

After the fault is fixed, the open fault is closed implicitly. However, you can also explicitly disable the fault. This requires re-evaluation of all other open faults, because some of these faults will become irrelevant due to the fault closure.

By default, this command requires the user to confirm the change request.

1.5 view related to DRA

Query the dynamic data dictionary View:

(1) V $ IR_FAILURE: LIST of all faults, including closed faults (result of list failure command)

(2) V $ IR_MANUAL_CHECKLIST: manually recommended list (result of the advise failure command)

(3) V $ IR_REPAIR: repair list (result of advise failure command)

(4) V $ IR_FAILURE_SET: cross reference of the fault and suggestion identifier

1.6 preventive check

For very important databases, other preventive checks may be required (which can be performed during off-peak hours every day ).

You can schedule regular health checks by using the health monitor or the RMANVALIDATE command. Generally, if a passive check detects a fault in the database component, you may need to perform a more comprehensive check on the affected component.

The rman validate database command is used to call health checks on the DATABASE and its components. It extends the existing validate backupset command. All problems detected during verification are displayed, which in turn enable fault assessment. If a fault is detected, the fault is recorded in the ADR as the search result. You can use the list failure command to view all faults recorded in the archive.

The VALIDATE command supports verification of a single backup set and data block. During physical damage, the database cannot identify blocks at all. In the case of logical corruption, the block content is logically inconsistent. By default, the VALIDATE command only checks for physical damages. You can also specify check logical to CHECK for LOGICAL corruption.

Block damage can be divided into block damage and block damage. When the block is damaged, the block itself is damaged, either physically or logically. In inter-block damage, block-to-block damage can only be logical damage. The VALIDATE command only checks whether the block is damaged.

Call the preventive health check for the database and its components:

(1) health status monitor or rman validate database Command

(2) Check for logical and physical damages

(3) record the search results in ADR

Ii. DRA example 2.1 create a fault

SQL> select file_name fromdba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/U01/app/oracle/oradata/anqing/dave01.dbf

/U01/app/oracle/oradata/anqing/users01.dbf

/U01/app/oracle/oradata/anqing/undotbs01.dbf

/U01/app/oracle/oradata/anqing/sysaux01.dbf

/U01/app/oracle/oradata/anqing/system01.dbf

/U01/app/oracle/oradata/anqing/example01.dbf

-- Destroy the users01.dbf data file:

[Oracle @ dave anqing] $ pwd

/U01/app/oracle/oradata/anqing

[Oracle @ dave anqing] $ ll users01.dbf

-Rw-r ----- 1 oracle oinstall 34086912 10 ?? Users01.dbf

[Oracle @ dave anqing] $ echo> users01.dbf

[Oracle @ dave anqing] $ ll users01.dbf

-Rw-r ----- 1 oracle oinstall 1 10 ?? Users01.dbf

-- Create a table in the users tablespace: anqing

SQL> create table anqing (id number) tablespace users;

Create table anqing (id number) tablespaceusers

*

ERROR at line 1:

ORA-01115: IO error reading block fromfile (block #)

ORA-01110: data file 4: '/u01/app/oracle/oradata/anqing/users01.dbf'

ORA-27072: File I/O error.

Additional information: 4

Additional information: 3

-- The error message is written into the ADR for verification:

Adrci> show homepath

ADR Homes:

Diag/rdbms/dave

Diag/tnslsnr/dave/listener

Adrci> set homepath diag/rdbms/dave

Adrci> show alert-tail

...

22:53:39. 334000 + 08: 00

Starting background process SMCO

SMCO started with pid = 33, OS id = 11016

2012-10-11. 34. 746000 + 08: 00

Checker run found 1 newpersistent data failures

2.2 RMAN LIST FAILURE

[Oracle @ dave ~] $ Rman target/

Recovery Manager: Release 11.2.0.3.0-Production on Thu Oct 11 23:06:52 2012

Copyright (c) 1982,201 1, Oracle and/or itsaffiliates. All rights reserved.

Connected to target database: DAVE (DBID = 856255083)

RMAN> list failure;

Using target database control file insteadof recovery catalog

List of Database Failures

======================================

Failure ID Priority Status Time Detected Summary

-----------------------------------------------

282 high open 11-OCT-12 One or morenon-system datafiles are running upt

Note:

The Listfailure command displays any open failures and gives priority to critical or high failure. If there is no similar failure, low failures will be displayed.

2.3 RMAN ADVISE FAILURE

RMAN> advise failure;

List of Database Failures

======================================

Failure ID Priority Status Time Detected Summary

-----------------------------------------------

282 high open 11-OCT-12 One or more non-system datafiles areinvalid upt

Analyzing automatic repair options; thismay take some time

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID = 18 device type = DISK

Analyzing automatic repair options complete

Mandatory Manual Actions

======================================

No manual actions available

Optional Manual Actions

======================================

No manual actions available

Automatic Repair Options

======================================

Option Repair Description

------------------------

1 Restore and recover datafile 4

Strategy: The repair complete media recovery with no data loss

Repair script:/u01/app/oracle/diag/rdbms/dave/hm/reco_1992614502.hm

-- A valid RMAN backup can be restored. Otherwise, a restore cannot be performed.

-- The recovery script can be executed manually or automatically:

[Oracle @ dave ~] $ Cat/u01/app/oracle/diag/rdbms/dave/hm/reco_1992614502.hm

# Restore and recover datafile

Restore datafile 4;

Recover datafile 4;

SQL 'alter database datafile 4 online ';

2.4 RMAN REPAIR FAILURE

RMAN> repair failure preview;

Strategy: The repair events des completemedia recovery with no data loss

Repair script:/u01/app/oracle/diag/rdbms/dave/hm/reco_1992614502.hm

Contents of repair script:

# Restore and recover datafile

Restore datafile 4;

Recover datafile 4;

SQL 'alter database datafile 4 online ';

-- By default, the repair failure command prompts the user to confirm the repair. Here we use noprompt to skip verification:

RMAN> repair failure noprompt;

Strategy: The repair events des completemedia recovery with no data loss

Repair script:/u01/app/oracle/diag/rdbms/dave/hm/reco_1992614502.hm

Contents of repair script:

# Restore and recover datafile

Restore datafile 4;

Recover datafile 4;

SQL 'alter database datafile 4 online ';

Executing repair script

Starting restore at 11---12

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafilebackup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile00004 to/u01/app/oracle/oradata/anqing/users01.dbf

Channel ORA_DISK_1: reading from backuppiece/u01/backup/ave_lev0_0bnnh6co_1_201721021011

Channel ORA_DISK_1: piecehandle =/u01/backup/ave_lev0_0bnnh6co_0000000021021011 tag = DAVE_LEV0

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 11-OCT-12

Starting recover at 11---12

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete, elapsed time: 00: 00: 01

Finished recover at 11-OCT-12

SQL statement: alter database datafile 4 online

Repair failure complete

Database opened

RMAN>

2.5 RMAN CHANGE FAILURE

The Change failure command can Change the failure level. In section 2.2, our failure level is high. If there is a failure, we can lower the level of it before we fix it, or if we do not want to fix it for the time being, without affecting the normal use of the system.

For example:

RMAN> CHANGEFAILURE 282 priority low;

2.6 verify VALIDATE

# Check for physical upload uption of alldatabase files.

Validate database;

# Check for physical and logical upload uptionof a tablespace.

Validate check logical tablespace users;

# Check for physical and logical upload uptionof a datafile.

Validate check logical datafile 4;

# Check for physical upload uption of allarchived redo logs files.

Validate archivelog all;

# Check for physical and logical upload uptionof the controlfile.

Validate check logical current controlfile;

# Check for physical and logical upload uptionof a specific backupset.

Validate check logical backupset 3;

# Check for physical upload uption of files tobe backed up.

Backup validate database archivelog all;

# Check for physical and logical upload uptionof files to be backed up.

Backup validate check logical databasearchivelog all;

# Check for physical upload uption of files tobe restored.

Restore validate database;

# Check for physical and logical upload uptionof files to be restored.

Restore validate check logical database;