Overview of Flashback Features in Oracle10g

Errors are unavoidable and may lead to data corruption or cause downtime of IT infrastructure. Typical errors by users may be caused by accidental deletion of valuable data and the process of detection and rectification is a time consuming task that may lead to loss of business productivity. Oracle Flashback technology helps during critical times of support and data recovery by reducing recovery time from hours to minutes.

The flashback features provides the capability to rewind data back and forth in time. It was first introduced in Oracle9i where the flashback query provided a simple and powerful tool to allow users to view the status of data in the past without structural changes to the database. This has been extended in Oracle10g to allow recovery at the database, table, and row and transaction level. The Oracle Database10g Flashback technologies include Flashback database, Flashback Table, Flashback Drop, Flashback versions query and Flashback Transactions Query features.

Flashback Database returns the database to a previous time to correct the problems encountered by logical data corruptions or mistakes of users. It does not require the database backup to be restored. The SYSDBA system privilege should be available to the user to use the flashback database command.

The database should be mounted in exclusive mode, after which the database should have been set in flashback mode by alter database flashback on command. In the flashback command either SCN (System Change Number) or timestamp clause can be used to set the point to which, the entire database should be flashed back.

When the flashback database command is executed, oracle makes sure that all required archived and online redo log files are available and then reverts the online data files to the time or SCN specified. Once the flashback is completed, the database should be opened using the reset logs options in order to have write access. The flashback mode can be turned off when the database is mounted and not open.

The Flashback table command is used to restore the state of one or more tables to an earlier state that is before the occurrence of the problem. The database should be using Automatic Undo Management (AUM) for the flashback table command to work.

The ability to flashback is restricted by the amount of undo retained in the undo tablespace and by the UNDO_RETENTION initialization parameter setting. The relevant privileges to flashback must be available with the user. A table can be flashed back to a specific SCN or timestamp. Flashback table restores the table along with its associated attributes such as indexes, triggers and constraints.

Flashback Drop provides a safety measure when dropping objects in Oracle Database 10g. Usually when a table is dropped, oracle places it in the recycle bin automatically. The RECYCLEBIN is a public synonym for the USER_RECYCLEBIN data dictionary view. This recycle bin is a virtual container containing dropped objects.

The user has the same privileges on dropped objects in the recycle bin as before being dropped and the dropped objects also remain in the same tablespace as earlier. The dropped objects can be viewed by querying the RECYCLEBIN. It shows the recycle bin entries for the current user. A table can be recovered with Flashback table to before drop command to recover the table from recycle bin.

Flashback Query feature allows the user to query the database as it was in past by specifying a time or system change number (SCN). It is most effective when the Automatic Undo Management is used. After viewing the data the user can perform analysis and then undo the changes or capture the changed data for further processing.

The Flashback versions query is an extension of SQL that allows users to view different versions of rows that existed in specified time interval. The committed versions of rows can be retrieved. To execute the flashback versions query, the versions between clauses of the SELECT command is used. Oracle returns one row for each version of each row that existed between the start and end times specified on the versions between clauses.

It offers additional pseudo columns like VERSION_STARTSCN (SCN where row version first occurred), VERSIONS_ENDSCN (SCN where the row version expired), VERSIONS_XID (the transaction id that created this row version) etc to provide transactional details on the row data. It can be used for the DBA to analyze and account the actions of the users. For a given transaction the FLASHBACK_TRANSACTION_QUERY data dictionary view can be used as a means to find the name of the user who executed it, the changes done by the transaction and the undo SQL statements required to reverse the effects etc.

Human errors are the predominant factors for system failure. For application programmers and administrators flashback queries provide an important tool for reconstructing the data by removing the complexity and decreasing the time associated with recovery. It should be used as a fallback option for the support cases in critical periods and not as a part of the application design because of the dependency on system elements outside the applications developers’ scope and control.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |