Flash Back Technologies

Flash Back Technologies

Sql>show parameter db_recovery_file_dest;

Rman>backup db_recovery_file_dest;

Sql>show parameter db_recovery_file_dest_size;

Sql>alter system set_db_recovery_file_dest_size=10G;

Flashback Enable

/*

Sql>startup mount

Sql>alter database archievelog;

Sql>alter database flashback on;

Sql>alter database open;

Sql>select flashback_on from v$database;

o/p : yes à enable ; no à disable

*/

Flashback database

/*

Example

Sql>create table product (product number(10));

 insert à 100 records

sql> commit;

sql>select current_timestamp from dual;

o/p: display the current time stamp

sql> select current_scn from v$database;

o/p: display the current scn

sql> drop table product ;

sql> create table dept (dno number (10));

sql> insert 100 record;

sql> commit;

sql> shutdown immediate;

sql>startup mount;

sql> flashback database to timestamp to_data (09-JUN-10  12:15:40);

sql> flashback database to scn ‘11123’;

sql> flashback database to logseq 1002 thread 1;

sql> alter database open resetlogs;

*/

sql> alter tablespace users flashback off;

sql> alter tablespace users flashback on;

sql > select  name , flashback  from v$tablsepace;

o/p : yes à enable ; no à disable

Flashback Drop:

Sql> show recycle bin;

                or

Sql > select * from dba_recyclebin

                Or

Sql > select * from user_recyclebin;

Example

Sql > drop table product;

Sql > flashback table product to before drop;

Sql > flashback table product to before drop rename to product1;

Permanent Drop

Sql > drop table product purge ;

                Or

Sql > purge table product;

Sql> purge tablespace users;

Sql> purge tablespace data user mathan;

Sql > drop tablespace data;

Sql > drop user mathan;

Tablespace data & user mathan wont store in recyclebin

Flashback Query :

Sql>create table  product (product number (10));

Sql > insert “200” record;

Sql>commit;

Sql>select current_timestamp from dual;

Sql> select current_scn from v$database;

Sql> delete from product;

Sql> commit;

Sql> select * product as of timestamp to_timestamp ( ’90-11-2009. 12:15:45………’);

Sql> select * from product as of scn ‘556790’;

Sql> select * from product;

Move to another table:

Sql>create table product1 as select * from product as of scn ‘556790’;

Flashback Version Query:

It is used to find the transactions happened in the table.

Ex :

Sql> create table product (pno  number(10) );

Sql> insert record  values 1 to  100;

Sql>commit;

Sql> select current_scn from v$database;

o/p: 1000

sql>insert into product values (101);

sql>update product set pno = 1000 where pno = 100;

sql>delete from product where pno = 101;

sql> commit;
sql>select current_scn from v$database;

o/p: 1100

sql> select version_xid, versions_operation ,pno from product version between scn 1000 and 1100;

o/p:

VXID         VO          pno

X111      I               200

X112      U             1000

X113      D             202

 I à insert ; Uà update ; D à delete

Flashback Transaction Query

It is used to find out which query it has been executed.

Sql>select undo_sql from flashback_transaction_query where xid = ‘x112’;

o/p :  update product set pno = 1000 where pno = 100;

Flashback table :

We can use flashback table for scn, time .

Sql> create table product (pno number (10));

Sql > insert 100;

Sql > commit;

Sql> select current_scn from v$database;

o/p: 1000

sql> delete from product;

sql > commit;

sql> insert 101;

sql> commit;

sql> alter table product  enable row movement;

sql> flashback table product to scn 1000;

sql> select * from product;

o/p: 100