THE FLASHBACK OPERATIONS

THE FLASHBACK OPERATIONS

Rating:

With the advancement in technology, there has been a drastic increase in the information that a database can hold. Big enterprises and organizations have to store billions of records to be always up-to-date with all the information required. In such a scenario, the risk of data loss also goes up. There are many tools and techniques available in Oracle 10g that can provide backup and recovery in case of data loss. Disks have an advantage over magnetic tapes, as even a slow disk can access data randomly, faster than a magnetic tape.


The Flashback operations have provided a vast improvement in performing database recovery. It serves Database Administrators (DBA's) as advancement to recover a database at a particular point in time.
Various Flashback operations that are available to perform recovery are given below:

• Flashback Database
• Flashback Table
• Flashback Query
• Flashback Transaction Query
• Flashback Version Query
• Flashback Drop

Now, describing each Flashback operation one by one will help to understand the concepts.

Flashback Database

Flashback Database is a Flashback technology that is used to nullify the changes made to a database. Flashback Database uses a log file called the Flashback Database Log. In other words, it is a Flashback technology that returns a database to a particular point of time or SCN. It is a fast and preferred method to perform an incomplete recovery.


How Flashback Database works


When a Flashback Database statement is issued, Oracle searches for all the required online redo logs and archive logs. When they are found, it (Oracle) reverts all the current online data files in the database to the specified time or SCN provided through the statement.

Flashback Table

Flashback Table is a command used at the SQL prompt to restore the state of a table/tables to a specific earlier point in time without using tablespace point in time recovery (a traditional method to restore tables), which is very much time consuming. It uses undo tablespace to restore the state of a table/tables to a specific earlier point in time in case of human and application error. It undoes recent transactions on an existing table. The row movements for the table have to be enabled for the Flashback Table command to take effect. When a Flashback Table operation takes effect for a table then the rest of the database remains unaffected. The Flashback Table acquires exclusive DML locks on the tables involved in the flashback.



Note:

• The current SCN should be recorded before using the Flashback Table command.
• The time to which a table can be flashed back, directly depends on the amount of undo data.

An example of the Flashback Table command is given below:

• SQL> alter table o.abc enable row movement; (This will enable row movement for the table)
• 
  SQL> flashback table o.abc to timestamp systimestamp - interval '15' minute; (This will flashback the table to 15 minutes before the flashback)

Flashback Query

Flashback Query is a combination of SQL statements that uses Oracle's multi-version read consistency capabilities to restore data by applying undo as needed. Flashback Query is used to view and repair historical data, and to view the contents of a table at any specific point in time.

Flashback Transaction Query

Flashback Transaction Query is one of the Flashback technologies, used as a diagnostic tool in Oracle. It is used to identify the changes done to a database at the transaction level. Using the Flashback Transaction Query can also perform transactional recovery of a table. It helps in the repair process, once the Flashback Versions Query is run to diagnose the erroneous transaction. It uses the FLAHBACK_TRANSACTION_QUERY view. It is based on undo data. It utilizes the UNDO_RETENTION initialization parameter to determine the period of time in order to retain committed undo data in a database. It is also used to obtain transaction information inclusive of the SQL code that can be used to undo the changes performed by transactions.

Flashback Versions Query

Flashback Version Query is one of the Flashback Technologies. It is used to retrieve all the versions of the specific rows that exist or existed between the time the query was executed and a particular point in time. Whenever a statement is committed, an entirely new version of the row is created. Flashback Version Query returns a table, consisting of new versions of every row that existed for the specified SCN and time.

Flashback Drop

Flashback Drop is used to undrop a table. This technology saves a copy of the dropped database object and all its dependent objects in the recycle bin, so that these objects can be recovered if needed.

This article describes the various Flashback operations. After reviewing this article, a database user is now well equipped to understand several available Flashback technologies.

Rating: