Flashback Technology in Oracle

Contents:-Flashback Table, Flashback Drop, Flashback Query, Flashback Version, Flashback Database, Flashback Transaction, Flashback Transaction Backout

 1.   Flashback Table – Is used to Restore a Table to a Particular Point in time by using undo Tablespace.

STEPS

sql>create table demo(id number);sql>insert into demo values(1);

sql>insert into demo values(2);

sql>commit;

sql>select to_char(sysdate,'dd-mon-rr hh24:mi:ss') from dual;

sql>delete from demo;

sql>commit;

sql>alter table demo enable row movement;

sql> flashback table demo to timestamp to_timestamp('02-may-13 09:45:60','dd-mon-rr hh24:mi:ss');

sql>select * from demo;

 2.   Flashback Drop – Is used to recover a dropped table using recyclebin

steps

sql>drop table demo;

sql>flashback table demo to before drop;

 3.   Flashback Query:- is used to show a value of a row at a specified time using AS OF Clause by using undo Tablespace.

    Steps

    sql>insert into demo values(4,50000);

    sql>commit;

    sql>select to_char(sysdate,'dd-mon-rr hh24:mi:ss') from dual;      -- note time

    sql>update demo set salary=40000 where id=4;

    sql>commit;

    sql>select salary from demo as of timestamp to_timestamp('02-may-13 09:42:13','dd-mon-rr hh24:mi:ss') where id=4;

 4.   Flashback Version:- is used to retriever a different version (values) of a row value between a specified point of time by using undo Tablespace.

Steps

sql>insert into demo values(4,50000);

sql>commit;

sql>update demo set salary=40000 where id=4;

sql>commit;

sql>select salary from demo versions between scn minvalue and maxvalue where id=4;

 5.   Flashback Database:- is used to recover a database at a specified point in time or system change number(SCN) by using flashback logs.

Notes:

 è Flashback database is used to recover logical corruption not used for physical corruption.

 è Db_flashback_Retention_target parameter is used for flashback database retention.

Steps

sql>select flashback_on from v$database;         -- check database is in flashback mode or not.

sql>shut immediate                          -- if not in flashback mode then

sql>startup mount exclusive

To make database in flashback mode database must be archive log mode

sql>select log_mode from v$Database;               -- check database is in archive mode or not if not make it

sql>alter database flashback on;

sql>select flashback_on from v$Database;

sql>alter database open;

sql>create table demo(id number);

sql>insert into demo values(1);

sql>commit;

sql>select to_char(sysdate,'dd-mon-rr hh24:mi:ss') from dual;      -- note time

sql>drop table demo purge;

sql>drop user scott cascade;

sql>shut immediate

sql>startup mount

sql>flashback database to timestamp to_timestamp('02-may-13 10:23:15','dd-mon-rr hh24:mi:ss');

or

sql>flashback database to scn <scn number>;

or

sql>flashback database to restore point <restore point_name>;

sql>alter database open read only                      -- make read only to check data recovered or  not.

sql>select * from demo /and/ select username from dba_users where username='SCOTT';

sql>shut immediate;

sql>startup mount;

sql>alter database open resetlogs;

 6.   Flashback Transaction Query

Flashback Transaction Query is to view all database changes made at the transaction level. possible solutions to recover from user error.

 7.   Flashback Transaction Backout

Flashback transaction backout is used to rolls back a specific transaction and dependent transaction.