Skip to Content

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.

STEP ONE

Keep calm. Keep calm. Keep calm! One cannot overstate the importance of keeping his mind focused in a situation like this.

STEP TWO

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.

STEP THREE

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;



 TIME

 ----------

 08.05.2007

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.

Best regards,

Lars

p.s.: Here is a link for further reading:

Oracle® Database Application Developer’s Guide – Developing Flashback Applications\  \

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

    1. Lars Breddemann Post author

      Hi Tom.

      Glad my old blog post is still enjoyable.

      But actually I find it a bit surprising that now – 6 years after I published it – a kind of revival sets in…

      funny thing.

      BTW: if I didn’t mention it yet… folks: it’s not Oracle anymore.. it’s SAP HANA! 😀

      – Lars

      (0) 
      1. Stefan Koehler

        😈 😀 SAP HANA? What are you talking about .. never heard of it  😉

        *side blow start* Say cheese… on taking snapshots with SAP HANA and SPS 5 quick note – on NULL values *side blow end* 😛

        > But actually I find it a bit surprising that now – 6 years after I published it – a kind of revival sets in…

        It seems like there is an issue on SCN (like so many others nowadays), that old blogs are republished and pushed on the RSS feeds again. I have seen this with some of my blogs several times as well 😡   … but hey so you are talking about Oracle again .. it seems like a good thing 😉

        *Going back in my lab to re-engineer the real cool stuff (Oracle 12c)*

        (0) 
  1. Akshay Gupta

    Hi Lars,

    This is just great!

    The recovery procedure is sophisticated yet simple and fast 😎

    I am sure this is going to come handy someday when something critical of this nature happens and admin guy (sap/db-os) will be summoned “How fast can you get my old SAP System without inconsistencies?”. I bet, your approach will be * A Knight in the shining armor*

    I will be trying this out very soon, to see how it goes.

    Would just like to know, that are there any exclusive requirements on the Oracle DB?

    Much thanks!

    -Akshay.

    (0) 
    1. Lars Breddemann Post author

      Hey Akshay,

      afaik the flashback features require the Oracle Enterprise license which is a given for SAP systems anyhow.

      Maybe a bit of heads-up: don’t get over excited on this feature.

      Although it helps to get back already deleted data, it doesn’t make your application landscape consistent automatically.

      Typically *that’s* the real drag.

      Anyhow  – there are tons of SAP notes and Oracle documentation on the flashback features. So, if you got a couple of weeks and nothing to read yet … 🙂

      – Lars

      (0) 
      1. Akshay Gupta

        Hey Lars,

        I appreciate the heads-up and  totally agree regarding the application’s consistency communicating to the database.

        I will plan it up in some-time and will try it out on a sandbox.

        Thanks a lot.

        -Akshay.

        (0) 

Leave a Reply