Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to understand Oracle Data Recovery Advisor. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

For DBA, data backup and restore are the basic skills of daily work. Today, with the development of Oracle, automation and intelligence is an important development direction. The database can automatically diagnose problems and fix errors, which may not be far away from us.

Data backup and restore take up a lot of space in the process of Oracle DBA learning. From cold backup to hot backup, from full recovery to incomplete recovery, this process involves a lot of technical details and knowledge points. Some DBA are initially exposed to these concepts and operations and are easily messed up. The Data Recovery Advisor launched by Oracle 11g provides us with a choice of automatic diagnosis and repair of the database.

1 、 Advisor

We have been exposed to all kinds of Advisor since 9i and 10g. From memory sizes, such as SGA, Buffer Cache, and PGA, to storage segment structures Segment Space Advisor and SQL Tuning Advisor,Oracle are trying to build an Advisor Framework.

Most of the Advisor of Oracle has the characteristics of "running in the background and automatic suggestion". These Advisor are often bound to Oracle automatic background jobs, where one or more background processes automatically collect information and diagnose, and finally generate diagnostic recommendations.

It should be said that Advisor is an important strategic step for Oracle to move towards automation and intelligence. Data Recovery Advisor introduced in this article is an important Advisor component used in the field of data recovery.

Data Recovery Advisor (hereinafter referred to as DRA) is a built-in (Build-In) tool of Oracle for reporting data errors, corruption, and recommendations for repair. For example, DRA can automatically find the current bad blocks and look at the backup database (RMAN) to give repair suggestions and statements. DRA can even do "one-click" recovery, hit a repair command, and automatically execute the repair script to release the error.

DRA is used by binding with the Oracle classic backup and restore tool RMAN. DRA automatically checks the database status and collects data in the background. Once an error is found, it will automatically prompt for repair suggestions. DRA can currently work in two ways, one is a database startup barrier, such as an error in the startup process. The other is an obstacle to the running process, such as abnormal corruption of the running database (such as the data file is deleted in the background).

Currently, DRA can support both the User interface and the command line. In OEM, we click on the repair link to view or solve the problem directly. On the command line, we can use the command of RMAN to do the processing.

2. Environmental preparation

As the saying goes, "A skillful wife cannot make bricks without rice". It should be noted that DRA is an automatic assistant tool, and for DBA, it is an assistant of standard operation, not a "panacea" that turns stone into gold. DRA data recovery is based on the original backup and restore system of Oracle, which does not introduce any special functions. In other words, DRA is powerless when fatal errors occur in a non-archived, non-backup, non-redundant database.

That is to say, the backup still needs to be done. Let's first make a full backup before the experiment. Select Oracle 11g to experiment and turn on the archiving mode.

[oracle@bspdev ~] $sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 6 06:09:29 2013

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

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup mount

ORACLE instance started.

Total System Global Area 849530880 bytes

(for reasons of space, there are omissions. )

Redo Buffers 5132288 bytes

Database mounted.

-- check to see if the archive mode

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 172

Next log sequence to archive 174

Current log sequence 174

Use RMAN to make a full backup.

SQL > alter database open

Database altered.

[oracle@bspdev ~] $rman nocatalog

Recovery Manager: Release 11.2.0.1.0-Production on Fri Sep 6 06:14:28 2013

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

RMAN > connect target /

Connected to target database: WILSON (DBID=3906514064)

Using target database control file instead of recovery catalog

RMAN > backup database plus archivelog delete input

Starting backup at 06-SEP-13

Current log archived

Skip the details.

In RMAN, we can use list failure all; 's DRA command to view the list of errors that currently exist.

RMAN > list failure all

No failures found that match specification

Next, we select the startup process and the running process of two scenarios for recovery demonstration.

3. Demonstration of database failure in startup process

Database errors during startup account for a considerable proportion of database errors. DRA can help us solve problems in this process.

First of all, let's create a problem. Currently, there are two control files, which are backups of each other.

SQL > col name for A100

SQL > select name from v$controlfile

NAME

/ u01/oradata/WILSON/controlfile/o1_mf_7xt44jkr_.ctl

/ u01/flash_recovery_area/WILSON/controlfile/o1_mf_7xt44kbv_.ctl

Accidentally interrupt the system and delete a control file.

SQL > conn / as sysdba

Connected.

SQL > shutdown abort

ORACLE instance shut down.

[oracle@bspdev ~] $cd / u01/oradata/WILSON/controlfile/

[oracle@bspdev controlfile] $ls-l

Total 9856

-rw-r- 1 oracle oinstall 10076160 Sep 6 06:36 o1_mf_7xt44jkr_.ctl

[oracle@bspdev controlfile] $mv o1_mf_7xt44jkr_.ctl o1_mf_7xt44jkr_.ctl.bak

[oracle@bspdev controlfile] $ls-l

Total 9856

-rw-r- 1 oracle oinstall 10076160 Sep 6 06:36 o1_mf_7xt44jkr_.ctl.bak

When you start again, the database is bound to report an error.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 616566352 bytes

Database Buffers 226492416 bytes

Redo Buffers 5132288 bytes

ORA-00205: error in identifying control file, check alert log for more info

Failed to locate the control file. The content was found in alert log.

MMNL started with pid=16, OS id=4418

Starting up 1 shared server (s)...

ORACLE_BASE from environment = / U01

Fri Sep 06 07:06:42 2013

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: controlfile:'/ u01Universe oradataUnixxt44jkrand.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

Fri Sep 06 07:06:44 2013

Checker run found 1 new persistent data failures

When entering the mount phase, Oracle found a problem that control file could not be read. Notice the last line of the alert log snippet, Oracle says: the checker I introduced is constantly polling and finds that this problem still exists. At this point, a skilled DBA can continue to work, either restoring it with a backup, or copying a full version. But in the age of DRA, we can also "ask Oracle Advisor what to do?" .

At this point, we use rman to view the information.

RMAN > list failure

List of Database Failures

=

Failure ID Priority Status Time Detected Summary

--

3842 CRITICAL OPEN 06-SEP-13 Control file/ u01/oradata/WILSON/controlfile/o1_mf_7xt44jkr_.ctl is missing

The information is so detailed that Oracle gives the error a number, graded, and has the description information. Make it clear what the problem is.

The List failure command displays all errors and failures, and we can also display information for a failure id.

RMAN > list failure 3842 detail

List of Database Failures

=

Failure ID Priority Status Time Detected Summary

--

3842 CRITICAL OPEN 06-SEP-13 Control file/ u01/oradata/WILSON/controlfile/o1_mf_7xt44jkr_.ctl is missing

Impact: Database cannot be mounted

List failure is the first DRA command. Advise failure is asking Oracle what to do?

RMAN > advise failure

List of Database Failures

=

Failure ID Priority Status Time Detected Summary

--

3842 CRITICAL OPEN 06-SEP-13 Control file/ u01/oradata/WILSON/controlfile/o1_mf_7xt44jkr_.ctl is missing

Impact: Database cannot be mounted

Analyzing automatic repair options; this may take some time

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Analyzing automatic repair options complete

Mandatory Manual Actions

=

No manual actions available

Optional Manual Actions

=

No manual actions available

Automated Repair Options

=

Option Repair Description-available fixes

--

1 Use a multiplexed copy to restore controlfile/ u01/oradata/WILSON/controlfile/o1_mf_7xt44jkr_.ctl

Strategy: The repair includes complete media recovery with no data loss

Repair script. / u01/diag/rdbms/wilson/wilson/hm/reco_148645850.hm

"We can restore using another redundant copy of Control File," Oracle DRA said. And a repair script is given.

[oracle@bspdev controlfile] $cat cat / u01/diag/rdbms/wilson/wilson/hm/reco_148645850.hm

Cat: cat: No such file or directory

# restore control file using multiplexed copy

Restore controlfile from'/ u01According to flashbacks recoveryAccording to WILSONAccording to controlfileAccording to the control lfileAccording to the situation, there is a problem of 7xt44kbv.ctl'.

Sql 'alter database mount'

Both statements are required to run under rman. One is to restore using the current mirror file, and the other is to start the database.

We follow the instructions of DRA and run the script commands manually. At this point, the database is in an intermediate startup state.

-- the instance has been started

[oracle@bspdev controlfile] $ps-ef | grep pmon

Oracle 4360 1 0 07:06? 00:00:00 ora_pmon_wilson

Oracle 4551 3270 0 07:15 pts/0 00:00:00 grep pmon

SQL > select status from v$instance

STATUS

-

STARTED

Execute program scripts in RMAN.

-- execute script commands

RMAN > restore controlfile from'/ u01According to flashworthy recoveryAccording to WILSONAccording to controlfileAccording to o1roommfharm7xt44kbv.ctl'

Starting restore at 06-SEP-13

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=1 device type=DISK

Channel ORA_DISK_1: copied control file copy

Output file name=/u01/oradata/WILSON/controlfile/o1_mf_7xt44jkr_.ctl

Output file name=/u01/flash_recovery_area/WILSON/controlfile/o1_mf_7xt44kbv_.ctl

Finished restore at 06-SEP-13

RMAN > sql 'alter database mount'

Sql statement: alter database mount

Released channel: ORA_DISK_1

At this point, the database can open smoothly, and the original list failure error message disappears.

-- enter open status

SQL > conn / as sysdba

Connected.

SQL > select status from v$instance

STATUS

-

MOUNTED

SQL > alter database open

Database altered.

[oracle@bspdev controlfile] $ls-l

Total 19712

-rw-r- 1 oracle oinstall 10076160 Sep 6 07:21 o1_mf_7xt44jkr_.ctl

-rw-r- 1 oracle oinstall 10076160 Sep 6 06:36 o1_mf_7xt44jkr_.ctl.bak

RMAN > list failure all

No failures found that match specification

This case tells us that DRA in RMAN can constantly diagnose and find problems and provide solutions during startup. More importantly, script statements for state modification can also be provided.

Next, let's do an Open state fault diagnosis and see how to achieve "one-click" system repair in DRA.

On how to understand Oracle Data Recovery Advisor to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.