Author: Tammy Bednar

We face many different problems that can halt the normal operation of an Oracle database or affect database IO operations. Performing regular test recoveries ensures that your backup is working and it also helps you stay familiar with recovery procedures. However if recovery is required then additional resources may be needed and recovery time extended.

For some database problems, crash and instance recovery occur automatically and require no action on the part of the database administrator. However some other problems require the administrator initiated media recovery.

Documentation and Regular Testing:

Documenting the processes required to complete a successful restoration is an important part of the backup and recovery plan. Regular testing must be scheduled to perform recovery based on known failures and disasters.

Media Failure:

If you try to perform read or write operation on a file on disk that is required to operate an Oracle database then you will get an error. This is called a media failure as a physical problem occurs in reading or writing to files on the storage medium.

Disk head crash:

Disk head crash is a type of media failure that causes the loss of all database files on a disk drive. All files associated with a database including datafiles, control files, online redo logs, and archived logs are vulnerable to a disk crash.

Recovering from a media failure:

The appropriate recovery from a media failure depends on the files affected. Media failure is the primary concern of a backup and recovery strategy, because it typically requires restoring some or all database files and the application of redo during recovery.

Block Corruption:

Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks (also called logical blocks, Oracle blocks, or pages), extents, and segments. Faulty hardware or an operating system bug can cause Oracle block that is not in a recognized Oracle format, or whose contents are not internally consistent.

Corrupt data blocks:

There are two types of corrupt data blocks in Oracle.

1. Logically corrupt blocks. For example a block corrupted by an incorrect block type but does not appear to be media corrupt.
2. Media corrupt blocks for which the block format is not correct.

Block media recovery:

Block media recovery minimizes redo application time and avoids IO overhead during recovery. It allows affected datafiles to remain online during recovery of the blocks. Without block-level recovery, if even a single block is corrupt you must restore a backup of the entire datafile and apply all redo generated for that file after the backup was created.

User Error:

A database administrator has a responsibility to prevent user errors such as accidentally dropping a table. You can avoid user errors by administering privileges correctly so that users are able to do less potential damage. A user should be given as much privileges as he require for performing a task. An effective recovery scheme must be planned ahead of time to help you recover from user errors.

Manual Recovery:

User error such as a dropped table requires re-entering the lost changes manually, if a record of them exists, importing the dropped object, if an export file exists, or performing incomplete recovery either of an individual tablespaces or of the entire database.

Standby Database:

If a standby database is in place then table can be exported from the standby database or failover to the standby before the change has been applied.

Offsite backups:

The recovery plan must include provisions to recover at an off-site host in case of hardware host failure or the entire facility lost. Once the backups are located offsite, the next challenge is to restore the system and database to the host. Unscheduled testing can help you ensure if your recovery plan is sound.

Recovery from Disks:

Database recovery from disk is always faster than recovering from tape. If the Oracle database is in archivelog mode, you can keep a certain number of archive logs to enable recovery. When more disk space is devoted for recovery, more than archive logs can be put into the backup disk cache area.

Backup tools/utilities:

You can directly backup your database by using RMAN, OS utilities, and media-manager tools. You may want to consider making image type backups of datafiles since it is fast operation to use the backup image copy when a file may require recovery.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for Oracle Temp Tablespace Growth Issue!!
   Data Warehouse Testing: The key to Data Warehouse success
   Inside Oracle Temporary Tables!!
   Great Tips on Table Recovery with RMAN Backup!!