joi, 11 iulie 2013

A table seems to be emptied, you don't know how. How to bring back the records?

First of all, don't say "it's imposible to occur in my database". Possible scenario: you have a large number of records in one table and, by mistake, are running against the table its recreation script (after some modifications made by yourself), taken from Toad, let's say. Well, inside of that script, you can easily observe the first command: drop table ..... If the table is dropped and recreated then, needless to say the table will be empty. Your records are gone to the wind. The most optimistic scenario is:

create table test as select * from dropped_table as of timestamp systimestamp-1;
insert into dropped_table select * from test;

where systimestamp-1 is the actual time minus 1 hour. The problem is, if you changed the structure of the table, you will receive the error 
ORA-01466: Unable to read data -- Table definition has changed
So, that is, if the structure is changed since the moment you dropped the table, the data cannot be restored. Next step:

FLASHBACK TABLE dropped_table TO TIMESTAMP SYSTIMESTAMP - INTERVAL '45' MINUTE;

Now, the things seem to come to normal. But, even so, the problems could be still there.

ORA-08189: cannot flashback the table because row movement is not enabled

So, you cannot flashback a table if the row movement is not enforced for it. Now, you've got only one way left:

select object_name, original_name, type, can_undrop , can_purge , droptime  from recyclebin where original_name like '%DROPPED%' ;

BIN$4TtAyuo6IpvgQwylqMDArw==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:06:56:58
BIN$4TtAyuo/IpvgQwylqMDArw==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:06:56:58
BIN$4TtFHS7RIuvgQwylqMBjAA==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:06:58:10
BIN$4TtFHS7WIuvgQwylqMBjAA==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:06:58:10
BIN$4TtHsm3pIyDgQwylqMBOnw==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:06:58:54
BIN$4TtHsm3uIyDgQwylqMBOnw==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:06:58:54
BIN$4TtLw7UlI9LgQwylqMCDgQ==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:07:00:02
BIN$4TtLw7UqI9LgQwylqMCDgQ==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:07:00:02
BIN$4TtMLcnUI+7gQwylqMBMsw==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:07:00:09
BIN$4TtMLcnZI+7gQwylqMBMsw==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:07:00:09


As you can see, in this scenario the Toad script deleting the records was executed more than one time. But you can imagine, the first run is important for us, because after that, anyway, the table is empty. So, what we will try to do is to bring back the version of the table before the first drop, and this will be the second row in the recordset from above. 

create table test as select * from dropped_table;

(meanwhile, maybe some users have had some input of data)

drop table dropped_table; --make some room for the table resurrection
flashback table "BIN$4Ts8ZZrlIkDgQwylqMAp5A==$0" to before drop;
insert into dropped_table select * from test;

(maybe the last command will give you some error, because of the table structure diferencies, but i am sure you can overcome this problem)

That's it! Now the table is back, with the right structure, from the moment in time, before the first drop, and, hope so, with the data submitted in the meantime, on top of the data "lost" after drop. Of course, your modifications made in the table structure will be  no retrievable by flashback, you can make them manually again, but, take the good part, your data is back!