Wednesday, November 25, 2009

Oracle 10g flashback table feature

FLASHBACK TABLE enables to recover a table to a point in time in the past without restoring a backup.The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.You cannot restore a table to an earlier state across any DDL operations that change the structure of the table.You cannot roll back a FLASHBACK TABLE statement but you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Its crucial to record the current SCN before issuing a FLASHBACK TABLE clause.

In order a user to flash back a table to an earlier SCN or timestamp, or before drop you must have either :
1.FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3.Row movement must for tables in the Flashback list.

e.g
create table test (a int);
alter table test enable row movement;
insert into test values(1);
insert into test values(2);
insert into test values(3);
commit;


select dbms_flashback.get_system_change_number from dual;

8657840224 -- SCN before delete

delete from test where a=3;

commit;

select dbms_flashback.get_system_change_number from dual;

8657840556 -- SCN after delete

select * from test;
1
2

flashback table test to scn 8657840224; -- Restore table to before delete !!!

select * from test;

1
2
3

flashback table test to scn 8657840556; -- Restore table to after delete !!!

select * from test;

1
2

flashback table test to timestamp(systimestamp - interval '3' minute);

select * from test;

1
2
3

Now we will drop the table and flashback from recycle bin.

drop table test;

flashback table test to before drop;

select * from test;

1
2

And as you can see we can go back again !!!

flashback table test to scn 8657974029;

select * from test;

1
2
3

If you specify the user-specified name, and if the recycle bin contains more
than one object of that name, then the database retrieves the object that
was moved to the recycle bin most recently.

drop table test;


create table test (a int);
alter table test enable row movement;
insert into test values(1);
commit;

select * from test;
1

drop table test;

flashback table test to before drop;

select * from test;
1

flashback table test to before drop rename to test1;

select * from test1;
1
2
3


Now lets take a look information recycle bin has.

drop table test1;

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

owner object_name original_name operation can_undrop can_purge
--------------------------------------------------------------------
AGIS BIN$eTA7dNdz5FfgQAkKeIc6fw==$0 TEST1 DROP YES YES


purge recyclebin; -- Clear recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

flashback table test1 to before drop;

ORA-38305 Object not in RECYCLE BIN.

drop table test; -- Put table to recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';


OWNER OBJECT_NAME ORIGINAL_NAME OPERATION CAN CAN
------------------------------ ------------------------------ -------------------------------- --------- --- ---
AGIS BIN$eTA7dNd05FfgQAkKeIc6fw==$0 TEST DROP YES YES

flashback table test to before drop;


select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

drop table test purge; -- Permanently drop table

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';


no rows selected


Amazing 10g recovering features with no backup recover !!!!!

No comments:

Post a Comment