Ok, so your data is gone. You/the report you ran executed a DELETE statement and performed a COMMIT. What to do now?
It is the productive system. So there is no place to discuss that it may have been a great idea to test the report first here. What’s needed is a solution.
The overall solution: point-in-time (p.i.t.) recovery of the database. But this has MANY disadvantages. Think of downtimes, think of work to be redone, think of all the other connected systems that won’t get set back in time. A whole bunch of inter-system-consistency problems will rise.
If you want to “undo” just the changes to the single table you perhaps may have a chance to get the system back to a consistent state again. The idea here is: there are several thousand tables in the SAP database. Only one of them got messed up, so the problem is rather local. Perhaps the solution can be local as well.
An often heard solution for this “one-table-recovery”-problem is: do a p.i.t.-recovery of the database to a second instance up to the time before the data got deleted, export the table from there and import the data back into the productive database. This procedure works (often) but is rather complicated and complex. This is a task of several hours.
We will try to reduce this time to a few minutes now.
We will try to create a new table with the same structure than our messed up table and copy all the data we can get from old data block versions into this new table. To do so we will utilize the ORACLE FLASHBACK QUERY functionality.
Keep calm. Keep calm. Keep calm! One cannot overstate the importance of keeping his mind focused in a situation like this.
Stop changing data on the table – right away! Best would be: stop changing in the database at all. To get the data back we will need the OLD block versions of the table, that is: the UNDO blocks of this table. If there is further work on the database – even if not on our table – it may overwrite the necessary UNDO blocks.
a) Logon to the database as the SAP SCHEMA owner, e.g. SAPR3, SAPERP, SAPPRD
b) Create a copy of our table structure. I will call the table “AAA” for the sake simplicity here:
create table "AAA_COPY" as SELECT * FROM "AAA" where rownum<1;
c) Copy the data from the old block versions to the copy table. For this example let’s assume the data was deleted today at 13:45.
Get current date:
select to_char(trunc(sysdate), 'dd.mm.yyyy') as time from dual;
Insert old data into copy table:
insert into "AAA_COPY" as select * from "AAA" as of timestamp to_timestamp('08.05.07 13:45:00', 'dd.mm.yy hh24:mi:ss');
If no error like ORA-1555 came up, then: cheer up!
d) VERY important: Don’t rename the copy table now to replace the original one. It currently just “looks” like the original one, but it is not similar to it. We defined no Constraints, no Defaultvalues no indexes. We’ve to copy over the data back into the original table.
Pay attention to the fact that this may lead to duplicate entries, depending on how keys for the specific table are generated.
If the original data was not deleted, but updated in a wrong way, then you will have to remove the wrong rows first or write a UPDATE statement to change the data back.
So as you can see, it is fairly easy to get back the accidentally deleted or changed data back.
Nevertheless this is not a silver bullet for all “user errors” concerning data in tables. Plus it has many constraints like: not useable on tables with LONG or LOB columns, depends on UNDO-information that might have already been deleted, no automatic consistency check from application side.
Nevertheless: it’s a quick and easy solution that seems to be used not too often, since it’s not so well known yet. Best thing to do to get used to this: try it out!
Use your test system, go and create a dummy table and fill it with data. Delete this data and get it back. Once you get used to it, it’s really no big deal when the next emergency call about deleted data comes in.
p.s.: Here is a link for further reading:
Oracle® Database Application Developer’s Guide – Developing Flashback Applications\ \