Flashback Feature

Flashback Feature

 

 

Flashback Query

SQL> select * from emp as of timestamp to_timestamp ('2007-06-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

 

Flashback Table to Past time.

Sql> alter table emp enable row movement;

Sql> flashback table emp to timestamp to_timestamp(‘07-06-19 09:30:00', `YYYY-MM-DD HH24:MI:SS');

 

Recovering Drop Tables (Undo Drop Table)

SQL>drop table emp;

SQL> Flashback table emp to before drop;

 

You can also restore the dropped table by giving it a different name like this

SQL> flashback table emp to before drop rename to emp2;

 

Purging Objects from Recycle Bin

SQL> purge table emp;

SQL> purge recycle bin;

 

Enabling Flash Back Database

Step 1. Shutdown the database if it is already running and set the following parameters

           DB_RECOVERY_FILE_DEST_SIZE=3g

           DB_RECOVERY_FILE_DEST=’/u02/user18/fra’
           DB_FLASHBACK_RETENTION_TARGET=4320
Step 2. Start the instance and mount the Database.

            SQL>startup mount;

Step 3. Now enable the flashback database by giving the following command

            SQL>alter database flashback on;

 

How to flashback database

Sql> startup mount;

Sql> flashback database to timestamp(SYSDATE – 5/1440);

 

How to create restore point

Sql> create restore point  tz_restore_point;

 

How to restore database using  restore point

Sql> startup mount;

Sql> flashback database to restore point tz_restore_point;

 

How to restore a tabl`e using restore point

Sql> flashback table emp to restore point tz_restore_point;

 

How much size we should set the flash recovery area.

After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

SQL> select estimated_flashback_size from v$flashback_database_log;

 

 

 

 

 

 

 

How far back you can flashback the database.

Sql> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

 

 

Flashback archive 

Creatng Flashback Archive 

SQL>create flashback archive flash_arc tablespace <tablespace_name> quota 1g Retention 1 year ;

SQL>alter table u1.emp flashback archive flash_arc;

Sql> flashback table emp to timestamp to_timestamp(’16:12:10 02-NOV-2010’ ,’hh24:mi:ss dd-mon-yyyy);

 

SQL>select * from dba_flashback_archive_tables;

SQL>select flashback_archive_name,flashback_archive#,tablespace_name,quota_in_mb from dba_flashback_archive_ts;

SQL>select owner,name,flashback_archive_name from dba_flashback_archive;

Views:-

V$flashback_database_log

V$recovery_file_dest

V$flash_recovery_area_usage

Desc dba_flashback_archive_tables;

Desc dba_flashback_archive_ts;

Desc dba_flashback_archive;