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,

ts_name,droptime,related,space

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