Flashback Architechture

Flashback Technology, first introduced in Oracle 9i,is a set of Oracle Database features that allow you to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. This new technology reduces recovery time from hours to minutes.

The flashback features are:
1.   Flashback Query                       (Introduced in 9i)
2.   Flashback Version Query          (Introduced in 9i)
3.   Flashback Transaction Query   (Introduced in 9i)
4.   Flashback Table                       (Introduced in 9i)
5.   Flashback Drop                        (Introduced in 9i)
6.   Flashback Database                (Introduced in 10g)
7.   Flashback Data Archive           (Introduced in 11g)

There are a number of flashback levels

row level	flashback query, flashback versions query, flashback transaction query
table level	flashback table, flashback drop
database level	flashback database

Oracle 10g has several error-correction techniques that use undo data, however they are only available if you use automatic undo management (AUM),

• Flashback query - retrieves data from a past point in time
• Flashback versions query - shows you different versions of data rows, plus start and end times of a particular transaction that created that row
• Flashback transaction query - lets you retrieve historical data for a given transaction and the SQL code to undo the transaction.
• Flashback table - recovers a table to its state at a past point in time, without having to perform a point in time recovery.

There are two other flashback technologies that do not use the undo data, they use flashback logs and recyclebin instead.

• flashback database - restore the whole database back to a point in time.
• flashback drop - allows you to reverse the effects of a drop table statement, without resorting to a point-in-time recovery

DBMS_FLASHBACK, flashback table query, flashback transaction query, flashback version query and select .. as of .. statements all use the undo segments. Flashback database uses the flashback logs and flashback drop uses the recycled bin.

When using flashback, if any operations violate a constraint the flashback operation will be rolled back, you can disable constraints but it’s probably not a good idea. If you have a table using a foreign key it is a good idea to flashback both tables. Flashback technology requires you to lock the whole table if it cannot it will fail immediately.

RMAN can only do flashback database and no other flashback technology.

Flashback Query:

Using flashback query involves using a select statement with an AS OF clause. you can select data from a past point in time. If you get a ORA-08180 it means that the data is no longer available in the undo segments.

Flashback Version Query:

Flashback version query provides you with all the versions of a row between two points in time or SCN, this is useful if you want to audit a table finding out what happened to a row. However there are some points to remember:

• You can only retrieve committed rows
• They query will retrieve all deleted rows as well as current rows
• The query will retrieve any rows that were deleted and reinserted later on
• Query result is table format and contains a row for each version of a row during the time or SCN interval you specify

The limitations of flashback version query are:

• You can only query actual tables not views
• you cannot apply the versions clause across DDL operations
• The query will ignore physical row changes for example during a segment shrink operation
• You cannot use against external or temporary tables.

The most useful columns to obtain are below:

• VERSIONS_STARTTIME - start timestamp of version
• VERSIONS_STARTSCN - start SCN of version
• VERSIONS_ENDTIME - end timestamp of version
• VERSIONS_ENDSCN - end SCN of version
• VERSIONS_XID - transaction ID of version
• VERSIONS_OPERATION - DML operation of version

Flashback Transaction Query:

Identifies which transaction or transactions were responsible for a certain change in a table's data during a specified time period. Basically it queries the flashback_transaction_query view. It provides the SQL code that will undo the change, flashback transaction query can use an index path to retrieve data instead of reading the entire redo log file.

Flashback transaction considerations:

• Turn on minimal supplemental logging if your operations involve chained rows and special storage structures, such as clustered tables
• When querying IOT, an update is shown as a delete/insert operation.
• If the query involves a dropped table or a dropped user, it returns object numbers and user ID's instead of the object names and usernames.

Consider setting the retention guarantee option for the undo tablespace, this will ensure that the unexpired data in the undo segments is preserved.

Flashback transaction query will contain the following columns

• start_scn and start_timestamp - identify when a certain was created
• commit_scn and commit_timestamp - tell you when a certain row was committed
• xid_row_id and undo_change# - identify the row, transaction and change numbers
• operation - tells you what sort of operation occurred insert, delete or update.
• logon_user, table_name and table_owner - username, table name and schema name
• undo_sql - the exact SQL code to undo the change

If you have chained rows or use clustered tables then oracle recommends that you should turn on supplemental logging on at the database level.

Flashback Table:

There are two distinct table related flashback table features in oracle, flashback table which relies on undo segments and flashback drop which lies on the recyclebin not the undo segments.

Flashback table lets you recover a table to a previous point in time, you don't have to take the tablespace offline during a recovery, however oracle acquires exclusive DML locks on the table or tables that you are recovering, but the table continues to be online.

When using flashback table oracle does not preserve the ROWIDS when it restores the rows in the changed data blocks of the tables, since it uses DML operations to perform its work, you must have enabled row movement in the tables that you are going to flashback, only flashback table requires you to enable row movement.

If the data is not in the undo segments then you cannot recover the table by using flashback table, however you can use other means to recover the table.

Restriction on flashback table recovery

• You cannot use flashback table on SYS objects
• You cannot flashback a table that has had preceding DDL operations on the table like table structure changes, dropping columns, etc
• The flashback must entirely exceed or it will fail, if flashing back multiple tables all tables must be flashed back or none.
• Any constraint violations will abort the flashback operation
• You cannot flashback a table that has had any shrink or storage changes to the table (pctfree, initrans and maxtrans)

Flashback Drop:

Flashback drop lets you reinstate previously dropped tables exactly as it was before the drop, below is a table of what is kept where when a table is dropped:

• Recyclebin: tables and indexes
• Data dictionary: unique keys, primary key, not-null constraints, triggers and grants
• Not recovered: foreign key constraints

If two tables exist in the recyclebin with the same name the newest one will be restored unless you state which one you want to restore. If you restore a table it is removed from the recyclebin.

Limitations on flashback drop:

• Recyclebin is only available to non-system, locally managed tablespaces.
• There is no guaranteed timeframe for how long an object will be stored in the recyclebin
• DML and DDL cannot be used on objects in the recyclebin
• Must use the recyclebin name to query the table
• All dependent objects are retrieved when you perform a flashback drop.
• Virtual private database (VPD) and FGA policies defined on tables are not protected for security reasons
• Partitioned index-organised tables are not protected by the recycle bin.
• Referential constraints are not protected by the recycle bin. They must be re-created after table has been rebuilt.

Flashback Database:

The database can be taken back in time by reversing all work done sequentially. The database must be opened with resetlogs as if an incomplete recovery has happened. This is ideal if you have a database corruption (wrong transaction, etc) and require the database to be rewound before the corruption occurred. If you have media or a physical problem a normal recovery is required.

Flashback database is not enabled by default, when enabled flashback database a process (RVWR – recovery Writer) copies modified blocks to the flashback buffer. This buffer is then flushed to disk (flashback logs). Remember the flashback logging is not a log of changes but a log of the complete block images. Not every changed block is logged as this would be too much for the database to cope with, so only as many blocks are copied such that performance is not impacted. Flashback database will construct a version of the data files that is just before the time you want. The data files probably will be in a inconsistent state as different blocks will be at different SCN’s, to complete the flashback process, Oracle then uses the redo logs to recover all the blocks to the exact time requested thus synchronizing all the data files to the same SCN. Archiving mode must be enabled to use flashback database. An important note to remember is that Flashback can never reserve a change only to redo them.

The advantage in using flashback database is speed and convenience with which you can take the database back in time.

You can use rman, sql and Enterprise manager to flashback a database. If the flash recovery area does not have enough room the database will continue to function but flashback operations may fail. It is not possible to flashback one tablespace, you must flashback the whole database. If performance is being affected by flashback data collection turn some tablespace flashbacking off.

You cannot undo a resized data file to a smaller size. When using ‘backup recovery area’ and ‘backup recovery files’ controlfiles , redo logs, permanent files and flashback logs will not be backed up.

Note: if one or more tablespaces are not generating flashback data, then before carrying out a flashback operation the files making up the tablespace must be taken offline. Offline files are ignored by recover and flashback. Remember that you must make these files to the same point as the flashback otherwise the database will not open.

Flashback Recovery Area:
The alert log and DBA_OUTSTANDING_ALERTS will hold status information regarding the flash recovery area. You can use the commands backup copy or backup for flash recovery area. Controlfiles and redo logs are permanently stored in the flash recovery area.