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 |
Database Technologies > Oracle >