Database Technologies‎ > ‎Oracle‎ > ‎

Flasback Technology

Flashback database allows you to restore a database to a previous point in time
quickly without using traditional recovery methods. Flashback database operations
reverts only the changes and does not restore the complete database

Time required to perform a flashback operations is in proportion to number of changes
that are to be reverted and not the size of the database, it helps in recovering logical
data corruption

Flash recovery area which holds the flash back database logs are used to implement
flashback database

Flashback database logs are not archived and are used in circular fashion, when a
request for flashback operation is received the logs are used to revert the data file
changes to the point in time when flashback logs were last captured

When flashback database is enabled Flashback buffer is created, recovery version
writer (RVWR) background process writes data from buffer to flash recover area

Enable Flashback

set flashback recovery area
SQL>alter database flashback on; at mount
SQL>select flashback_on from v$database

To disable flashback

SQL>alter database flashback off;  //deletes logs
DB_FLASHBACK_RETENTION_TARGET  : Denotes the max limit in minutest to which database may be flashed back, however may not hold true in certain cases, as it depends upon amount ot changes made

SQL>alter system set db_flashback_retention_target=2880  scope=both

v$flashback_database_log :  To monitor retention target oldest scn to which we could flashback

v$flashback_database_stat : monitor overhead for logging
v$recover_file_dest   : Info about the flash recovery area
Mount the database to perform flashback operation
also the database is open is to be opened in reselogs optiom
after the completion of flashback operation
SQL>flashback database to scn <>;

SQL>flashback database to timestamp(sysdate -1/24);

Flashback Time Navigation Query Methods

flashback query
flashback version query
flashback transaction query

SQL>select * from scott.emp as of timestamp to_timestamep('YYY-MM-DD',dddd);

Version query;

To View all version of row between two point in time, one could also see the
transaction that changed the row

select versions_xid XID,version_startscn start_scn,
versions_endscn END_SCN, versions_operation OPERATION,
last_name,salary from scott.employees
versions between scn minvalue and maxvalue
where employee_id=111;

flashback transaction query

select operation,undo_sql,table_name from flashback_transaction_query;

Above would return both active and committed transaction
This view contains information about the flashback data pertaining to last 24 hours

SQL> DROP TABLE EMP cascade constraints;
Other than Flashback there is one more feature called RecycleBin, where the droped objects
are moved.
SQL> show parameter recyclebin;

In 10g when a table is dropped, the table's space and dependent objects space is not
deleted although they appear is dba_free_space, the dropped table objects are stored
in Recycle Bin and they continue to below to the schema

SQL>select owner,original_name,object_name,type,
from dba_recyclebin
where can_undrop='YES';

SQL>show recyclebin
SQL>flashback table scott.emp to before drop;
SQL>flashback table employees to before drop rename to  emp2;

Automatic purge or reclybin
Manual purge or recyclebin

purge table <table_name>
purge index <index_name>
Purge dba_recyclebin command purges all objectcurrent user