There are various conditions in which one might perform a complete recovery, incomplete recovery, block level recovery, flashback query, flashback table, and flashback database recovery.

Complete recovery is a when a database is totally recovered to the most recent or latest possible point in time, without the loss of any committed transactions. In certain circumstances, there might be a need to restore the database to a state past the recent point in time. For instance, recovering the database to a state before its contents such as tables, views were deleted or dropped. RMAN Complete recovery enables you to automate restore and recovery operations, detects block corruption during, restoration and recovery operations, and can be used to update tablespace or datafile completely by applying all changes from the archived redo logs to the backup files, etc. Complete recovery can be taken when performing hardware striping reconfiguration, after opening a database with the RESETLOGS option, and when media failure has damaged datafiles or the control file. During recovery, changes made to ‘undo data blocks’ create a redo, which is redo for undo. In the roll forward phase, all the redo is used in complete recovery (Velpuri, 2002).

Incomplete recovery (also called point-in-time recovery) restores a database to its state at some previous target SCN or time (specified time in the past). You perform incomplete recovery when it’s the only option left to recovery lost data, a user (DBA) executes the wrong operation (batch job) logically corrupting the data in the application, the archived redo log file needed to perform complete recovery is not available or corrupted, and when online redo log file is lost due to hardware failure, and so forth. There are three ways to perform incomplete recovery: change-based recovery, to know the highest system change number (SCN) written to the archived redo log just before the missing log; cancel-based recovery, which proceeds until you enter the word cancel, and time-based recovery, to know the time recorded in v$archived_log for archived redo log sequence number (the missing redo log). Unlike complete recovery, when performing incomplete recovery of a database, only a portion of the redo is used. As a result, incomplete recovery constantly incurs data loss (Kuhn et al 2007, Velpuri, 2002).

RMAN’s block level recovery utility is used to catalog corrupt database blocks during an RMAN backup and recovery process. This capability allows you to selectively recover just the affected blocks within the damaged data file rather than whole datafiles. Thus making sure the rest of the data file is available to users. The disadvantage to it is that it’s only used through RMAN and not SQL Plus, can only recover blocks marked corrupt and performs complete recovery of individual blocks, which can be time consuming.

Oracle Flashback Database is a physical-level recovery; similar to point in time recovery, enables you to take the entire database to a past point in time (using flashback logs). Hence, it’s generally faster and does not need to restore of data (datafiles) from backup. Flashback Database recovery requires applying fewer changes from the archived redo logs. However, it cannot by itself recover from media failure or use flashback database if the database's control file has been restored from backup or re-created after flashback database was turned on (Oracle Users Guide).

Oracle Flashback Table work at the logical level, to restore tables and their associated attributes such as current indexes, triggers and constraints to their state at some past SCN or time. This frees the DBA from trying to find, recover, and restore application-specific properties. In certain scenarios, flashback table eliminates the need to perform more complicated point-in-time recovery operations. It allows you retrieve accidentally dropped tables and indexes etc. Flashback Table also relies on the undo data, which must be available for the recovery of the tables to be successful (Oracle Users Guide, Nanda).

Oracle Flashback Query lets you query and restore data rows to a point in time using undo data. It’s useful in viewing past contents of tables and investigating the depth of logical data corruption affecting a database. Flashback Query is supported by the export utility to export data, which is consistent as of a specified point in time. Flashback Query depends on the availability of the undo information in the database, so if the undo information has been aged out, this approach will fail. However, a DBA can specify how long they want to retain the undo data using the UNDO_RETENTION parameter in the init.ora file and hence, can control how far back a flashback query can go (Nanda).



References:

Kuhn, D., Alapati, S., & Nanda, A. (2007). RMAN Recipes for Oracle Database 11g A Problem Solution Approach. Apress.

Powell, G. & McCollough-Dieter, C. (2007). Database Administrator: Implementation & Administration. Thompson.

Rajan, C. (2007). Database Administrator II: Backup/Recovery & Network Administration. Thompson.

Velpuri, Rama. (2002). OCP Oracle9i Database: Fundamentals II Exam Guide McGraw Hill- Osborne

Nanda, Arup. Oracle Database 10g: The Top 20 Features for DBAs
http://www.oracle.com/technology/pub/articles/10gdba/pdf/10gdba_complete.pdf

Surapaneni S. October 12, 2006, Oracle 10g: Exploring Data Pump
http://www.databasejournal.com/features/oracle/article.php/3636536


Oracle Database Backup and Recovery Advanced User's Guide10g
http://www.itk.ilstu.edu/docs/Oracle/server.101/b10734/part1.htm