Oracle has provided powerful mechanism to protect database in situation of data loss. This mechanism includes a range of methods to Backup and Recover your data. First step is to backup you data and second step is to recover required data. Broadly speaking, oracle supports two types of backups, Logical Backup and Physical database backups.
Logical Database Backup
Logical backup means to backup you logical structure, for example, tables and packages. Logical backup can be taken using oracle utilities like Data Pump or oracle export utility.
Physical Database Backup
Physical backup is taken by maintaining backup of those files which constitute physical structure of oracle database. These files include datafiles, control files, redo log files, archived redo logs, parameter file and password file. Physical backup is considered more reliable and all of oracle backup and recovery techniques revolve around physical backups. Oracle uses term “Backup” for physical backups. Physical backups can be taken by using operating system commands or by using oracle utility RMAN. RMAN is powerful utility to mange backups. Most of oracle backup and recovery techniques are designed around RMAN.
Usually DBA requires data recovery in two possible scenarios, loss of data due to user error or loss of data due to media failure.
Some user can accidentally change or deleted data or an error in application might cause logical data corruption. As a result of this, DBA is required to recover database to a stage prior to that mistake. In this case, an old backup is restored and archive logs are applied to recover lost data.
Some time, due to corruption of physical media, few or all of storage files are lost. In this case, DBA is required to reconstruct the database.
Oracle Flashback Features
Before the release of oracle database 10g, all data recovery techniques were applied when database is not in open stage. Oracle database 10g has provided a new feature called Flashback feature which can be used to recover data lost due to user errors and data can be recovered when database is in open stage. Flashback recovery is considered an alternate to Point-in-time recovery. Oracle Flashback feature can be divided into two categories, Logical Level Features and Physical Level Features.
Except Flashback Drop, all Logical Level Flashback features rely on UNDO Data which is kept in undo tablespace. Recovery using these features depends on retention period. At logical level Flashback features consists of following
Oracle Flashback Query
This feature allows you to execute a query on past stage of data. For example, you can execute a query by provide time interval of two hours back, the result will be same as query would have retrieved two hours back.
Oracle Flashback version and Flashback transaction query
In oracle database, a transition begins when first SQL statement is executed and transacting ends when commit or rollback is issued. When transaction is completed, all effected rows attain a Version. If within a transaction, a row has been updated ten times, it will have only one final Version. When a new transaction is performed a new Version of the rows is recorded.
Oracle flashback version query feature allows us to get all version of particular rows within a given time interval. As stated earlier, a final version may have been attained after execution of several SQL statements. Oracle Flashback transaction query feature allows to view changes made within a transition.
is used to get Flashback transitions. This view also contains undo statements to reverse the effect of transaction.
Oracle Flashback Table
This feature is used to bring a table to a past stage.
Oracle Flashback Drop
This feature is used to recover a dropped table. Flashback drop is managed around Recycle Bin concept.
At physical level, oracle has provided Flashback Database feature. Flashback Database use flashback logs and archives redo logs to obtain past version of data. Flashback database can be used to restore entire database to an earlier point.
Oracle Database 10g also supports restore points. You can define a restore point with an SCN number. In future, database can be restored to an available restore point. Restore points can also be used in conjunction with Flashback Database.