Oracle Jobs   

   Ask A Question   

Restore and Recovery Strategies


Oracle provides a variety of procedures and tools to assist you with recovery. To develop an effective recovery strategy, do the following:

This section contains these topics:

Learn Oracle - Highlights

Learn Oracle - Testing Backup and Recovery Strategies

Practice backup and recovery techniques in a test environment before and after Oracle DBA move to a production system. In this way, Oracle DBA can measure the thoroughness of your strategies and minimize problems before they occur in a real situation. Performing test recoveries regularly ensures that your archiving, backup, and recovery procedures work. It also helps you stay familiar with recovery procedures, so that you are less likely to make a mistake in a crisis.

If Oracle DBA use RMAN, then run the DUPLICATE command to create a test database using backups of your production database. If you perform user-managed backup and recovery, then you can either create a new database, a standby database, or a copy of an existing database by using a combination of operating system and SQL*Plus commands.

When testing your backup and recovery strategy, ask yourself these questions:

  • If a disk failed and destroyed some of the database files, could I perform a full recovery of the files on this disk? Test separately for loss of datafiles, control files, and online redo logs.
  • If a user accidentally dropped a table, how could I recover from it? Test scenarios involving incomplete recovery of the whole database, tablespace point-in-time recovery, and using the Import utility.
  • What if the alert_SID.log revealed that one or more tables contained corrupt blocks? Test block recovery using the RMAN BLOCKRECOVER command. Also, troubleshoot recovery with the SQL*Plus RECOVER ... TEST command.
  • If the entire data center was destroyed by a fire, could you perform disaster recovery? Assume that all Oracle DBA have is an archived tape containing backups. How would you recover the database?

Back to Top

Learn Oracle - Validating Backups and Restores Using RMAN

If you use RMAN, then you can use the VALIDATE keyword on the BACKUP and RESTORE commands. BACKUP VALIDATE tests whether Oracle DBA are able to make a valid backup of database files. RESTORE VALIDATE tests whether you are able to restore an RMAN backup. Note that neither of these commands produces any actual output files.

Back to Top

Learn Oracle - Planning a Response to Media Failures

Media failure is the biggest threat to your data. A media failure is a physical problem that occurs when a computer unsuccessfully attempts to read from or write to a file necessary to operate the database. Common types of media problems include:

  • A disk drive that holds one of the database files experiences a head crash.
  • A datafile, online or archived redo log, or control file is accidentally deleted, overwritten, or corrupted.
The technique you use to recover from media failure of a database file depends heavily on the type of media failure that occurred. For example, the strategy you use to recover from a corrupted datafile is different from the strategy for recovering from the loss of the control file.

The basic steps for media recovery are:

  • Determine which files to recover.
  • Determine the type of media recovery required: complete or incomplete, open database or closed database.
  • Restore backups or copies of necessary files: datafiles, control files, and the archived redo logs necessary to recover the datafiles.
  • Apply redo records (and/or incremental backups when using Recovery Manager) to recover the datafiles.
  • Reopen the database. If Oracle DBA perform incomplete recovery or restore a backup control file, then you must open the database with the RESETLOGS option.

Back to Top

Learn Oracle - Planning a Response to Datafile Block Corruption

If selected blocks within a datafile are corrupt, then you may not have to restore and recover the whole datafile. Instead, you can perform block media recovery. The Recovery Manager BLOCKRECOVER command can restore and recover specified data blocks while the database is open and the corrupted datafile is online.

Back to Top

Learn Oracle - Planning the Response to Non-Media Failures

Although media recovery is your primary concern when developing your recovery strategy, Oracle DBA should understand the basic types of non-media failures as well as the causes and solutions for each.

Statement Failure A statement failure is a logical failure in the handling of a statement in an Oracle program. The Oracle database server or the operating system usually returns an error code and a message when a statement failure occurs.

User Error Users errors are any mistakes that users make in adding data to or deleting data from the database. If you have a logical backup of a table from which data has been lost, sometimes you can simply import it back into the table.

Depending on the scenario, you may have to perform some type of incomplete media recovery to correct user errors. You can perform either database point-in-time recovery (DBPITR) or tablespace point-in-time recovery (TSPITR). The following table explains the difference between these types of incomplete recovery

Type Description
  1. Restore whole database backup.
  2. Recover the database to the time just before the error.
  1. Create auxiliary instance with RMAN or user-managed methods.
  2. Recover the tablespace on the auxiliary to the time just before the error.
  3. Import data back into the primary database.
Instance Failure Instance failure occurs when an instance abnormally terminates. An instance failure can occur because:

  • A power outage causes the server to crash.
  • The server becomes unavailable because of hardware problems.
  • The operating system crashes.
  • One of the Oracle background processes fails.
  • Oracle DBA issue a SHUTDOWN ABORT statement.
Fortunately, Oracle performs instance recovery automatically: all you need to do is restart the database. Oracle automatically detects that the database was not shut down cleanly, then applies committed and uncommitted redo records in the redo log to the datafiles and rolls back uncommitted data. Finally, Oracle synchronizes the datafiles and control file and opens the database.

Back to Top


You are just 10 steps away from becoming a Oracle DBA. Materialize your dream by following the The 10 Simple Steps .

More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info