Flashback Database

Introduction to Flashback Technology
Flashback provides a simple, powerful and completely non-disruptive mechanism for recovering from human errors.It is used when logical corruption occurs in Oracle Database and data needs to be recovered quickly and easily.
This is applicable at Database, Table and Transaction Level.
With this technology you can diagnose, how errors are introduced in database and then repair the damage. There are different flashback technologies like Flashback Database,Flashback Drop, Flashback Table, Flashback Query, Flashback Version Query,Flashback Data Archive, Flashback Transaction Query. Almost all above mentioned technology uses the undo data for historical data other than Flashback Database and Flashback Drop, which uses Flashback logs and Recycle bin respectively.

What is Undo Data and UNDO_RETENTION?
Every Oracle database must have a method of maintaining the information such as records of the actions of transactions before they are committed. These records are collectively referred to as Undo Data. Undo Information is divided into 3 categories which are:
• Uncommitted Undo Information (Active)
• Committed Undo Information (Unexpired)
• Expired Undo Information.(Expired)

The term UNDO_RETENTION , specifies (in seconds) how long already committed undo information is to be retained. UNDO_RETENTION also depends on the space available, so in case of insufficient space unexpired undo data will be overwritten by recently generated data. To Stop so, need to include RETENTION GUARANTEE clause for the undo tablespace. In that case transaction will fail if it generates more undo than there is space.

To use Flashback technology following are Prerequisite:
– Create Undo tablespace with space analysed by $UNDO_STAT view’s data.
– Enable Automatic Undo Management,if not enabled.
– Set the Undo_Retention parameter.
– Grant flashback privileges to users, roles that need to use flashback features.

Undo Retention

Enable Flashback:
Check for the status of Flashback_on and ARCHIVELOG mode

>Step to set FLASHBACK_ON=’YES’

One another parameter which is to keep in mind is:

DB_FLASHBACK_RETENTION_TARGET

For error Analysis of the data using Flashback following technologies are used:
Flashback Query:
• With the Flashback Query feature, you can perform queries as of a certain time.
• By using the AS OF clause of the SELECT statement, you can specify the time stamp for which to view the data.
• TIMESTAMP and SCN are valid options.

Flashback Version Query:
See all version of row between two times.
• Flashback Version Query retrieves only committed occurrences of the rows. Uncommitted row versions within a transaction are not shown.
• You can use this technology to retrieve row history. It provides with a way to audit the rows of a table and retrieve info about the transaction that affected the rows. Then can use the returned transaction identifier (VERSION_XID) to perform transaction mining by using LogMiner
• TIMESTAMP and SCN are valid options.

Flashback Transaction Query:
See all changes made by a transaction.
• Can reverse a transaction and dependent transactions.
• To use this functionality, supplemental logging must be enabled and the correct privileges established.
o SUPPLEMENTAL_LOG_DATA
o SUPPLEMENTAL_LOG_DATA_PK
o EXECUTE ON DBMS_FLASHBACK
o SELECT ANY TRANSACTION

Scenario:

 

 

 

Query:

Version Query:

For error recovery of the data using Flashback following technologies are used:
Flashback Table:
• Using Flashback Table, you can recover a table or tables to a specific point in time without restoring backup.
• Flashback Table Operation is done in-place ,while database is online. It acquires exclusive DML locks on all the tables that are specified in the statement.
• Must have to enable row movement on a table to be able to flash back the table. By enabling that, Oracle Server can move a row in the table.
• Can not Perform FLASHBACK Table to a particular time that is older than the time of the execution of DDL operation that altered the structure of .
• ALTER TABLE table_name ENABLE ROW MOVEMENT;
• FLASHBACK TABLE table_name TO TIMESTAMP TO_TIMESTAMP(time,format);

Flashback Drop:
• This reverse the effects of DROP Table.
• RECYCLEBIN initialization parameter is used to control this technology.
• When above mentioned parameter is set to ON, the deleted object will moved (actually renames the object by BIN$unique_id$version structured identifier) to recyclebin, otherwise if deleted normally, the space will be reclaimed.
• FLASHBACK TABLE table_name TO BEFORE DROP [Rename To NewName].
Flashback Database:
With this technology you can quickly bring your database to an earlier point in time by undoing all changes that have taken place since that time.
• Pre-requisite: Database should be in ARCHIVELOG mode, Enable logging and specify the FRA(Fast recovery Area).
• How it works: When this is enabled ,the Flashback Writer background process is started and sequentially writes data from Flashback buffer to Flashback database logs, which are circularly reused. When Flashback command is issued flashback logs are used to restore the blocks before image and then redo data is used to roll forward to the desired flashback time.
• Configuration:
o FRA configuration
o Set retention target(DB_FLASHBACK_RETENTION_TARGET)
o Enable Flashback database
• There are some limitations also when we can’t use this , for example: A tablespace has been dropped.
Flashback Data Archive:
• This technology is enabled at table level with your specified retention period.
• Can automatically track and store data in tables enabled for FDA.
• Overview:
o Row captured by bgprocess at self-tuned intervals.
o Stored compressed and portioned.