Introduction
Last week we got a call from a customer who deleted some materials on our production system. It turned out, he was using transaction SE16 and acctually did not know the exact material numbers anymore. But at least he could tell us, that he deleted only rows from the table MARA.
We were able to identify the deleted rows, retrieve them from UNDO space and re-insert them back again. In this blog i will show you how this can be done. This blog addresses basis consultants and dba folks. Please be aware, that doing DML directly with SQLplus is not supported in SAP environnements. The tests have been done on Oracle 10g, but most of the functionality is already available with 9i.
So lets get started, usually we get a call on our hotline, something like:
"Hello, Basis team, we seem to have deleted some materials. Can you please fix this?"
I skip the painful procedure of getting the callers identity, where as well as the exact time and the details of their actions. In short: some poor guy has deleted some rows out of table MARA, which everybody knows is the table containing general material data. In this particular case the user has done this through transaction SE16. This is a point of interest, because if the user had deleted a material through the correct application ways, there would have been lots of other tables affected.
In the following example i deleted two rows from MARA, done 21st April between 01:00-01:30 pm:
SQL> DELETE FROM SAPR3.MARA WHERE MANDT = '570' AND MATNR IN ('286600400000','286600500000');
|
What are you talking about
A brief explanation on how Oracle Flashback Query works. Every change (DML) of data in the database creates undo. This is needed to rollback any change to its original state and to guarantee consistent reading of other users accessing the same data. Now even after a commit this undo data will not be overwritten instantly. How long your undo will be kept depends on your undo configuration, space and on how much undo is generated in your system. Normally this is within hours. Off course this implies you have to act fast enough to get the rows back from undo, it will probably be gone the next day.
A Flashback Query behaves like a SELECT issued a given time in the past. If you specify SELECT * FROM TAB AS OF TIMESTAMP X, then your result will be the same as the simple query at that time X.
How to find the missing rows
So if we know the object name and the time range. Now we can use Oracle Flashback Query to find, what was going on.
SQL> SELECT MANDT, MATNR FROM
(SELECT MANDT, MATNR FROM SAPR3.MARA AS OF TIMESTAMP
TO_TIMESTAMP('2008-04-21 13:00:00','YYYY-MM-DD HH24:MI:SS')) MINUS
(SELECT MANDT, MATNR FROM SAPR3.MARA AS OF TIMESTAMP
TO_TIMESTAMP('2008-04-21 13:30:00','YYYY-MM-DD HH24:MI:SS'));
MAN MATNR
--- ------------------
570 286600400000
570 286600500000
|
As MARA has MANDT, MATNR as key, this two columns uniquely identify a row. As we know there has been rows deleted, so we simply select the keys we had before deletion and subtract (MINUS) the ones we still have.
We can use the VERSIONS BETWEEN clause to get the information as well. As we know the keys already from the above statement, i use them now:
SQL> SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, MANDT, MATNR FROM SAPR3.MARA
2 VERSIONS BETWEEN TIMESTAMP
3 TO_TIMESTAMP('2008-04-21 10:00:00','YYYY-MM-DD HH24:MI:SS') AND
4 TO_TIMESTAMP('2008-04-21 13:30:00','YYYY-MM-DD HH24:MI:SS')
5* WHERE MANDT = '570' AND MATNR IN ('286600400000','286600500000')VERSIONS_STARTTIME VERSIONS_ENDTIME V MAN MATNR
------------------------------ ------------------------------ - --- ------------------
21-APR-08 01.22.05 PM D 570 286600400000
21-APR-08 01.22.05 PM 570 286600400000
21-APR-08 01.22.05 PM D 570 286600500000
21-APR-08 01.22.05 PM 570 286600500000
|
Notice the use of the pseudo columns VERSIONS_STARTTIME, VERSIONS_ENDTIME and VERSIONS_OPERATION. Here we see the exact time of the operation, the 'D' stands for DELETE. STARTTIME is empty for the old version, as it was earlier than the observed range.
If we didn't know the keys already (or the exact time), another approach would have been to query for deleted rows on VERSION_OPERATION.
SQL> SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION,MANDT, MATNR FROM SAPR3.MARA
2 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-04-21 00:00:00','YYYY-MM-DD HH24:MI:SS')
3* AND TO_TIMESTAMP('2008-04-21 13:30:00','YYYY-MM-DD HH24:MI:SS') WHERE VERSIONS_OPERATION = 'D'
VERSIONS_STARTTIME VERSIONS_ENDTIME V MAN MATNR
------------------------------ ------------------------------ - --- ------------------
21-APR-08 01.22.05 PM D 570 286600400000
21-APR-08 01.22.05 PM D 570 286600500000
|
Rescue the deleted data
I suggest the dba does save the deleted rows in a human readable format and provide it to the dev / application folks. They can check them, and decide on the further steps then.
Aspecial feature of MARA is the count of columns: 237 !!!
I initially started to manually write a SELECT statement, which produces csv style output. I stopped, when my keyboard broke down. This means we need some more automation to generate our SELECT statement.
Generate the SELECT:
SELECT 'CHR(39)||"' || COLUMN_NAME || '"||CHR(39)||CHR(44) ||' FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = 'MARA' ORDER BY COLUMN_ID ASC;
|
I know, this sucks, but it works, chr 39 is a apostrophe (') and 44 is a comma. Spool the output to a file. You will end up having a list looking like this:
CHR(39)||"MANDT"||CHR(39)||CHR(44) ||
CHR(39)||"MATNR"||CHR(39)||CHR(44) ||
CHR(39)||"ERSDA"||CHR(39)||CHR(44) ||
...
|
Remove the this part from the last line ||CHR(44) ||, it is not needed. Now we have the body of our select, just put a SELECT at the front and the FROM SAPR3.MARA at the bottom:
SELECT
CHR(39)||"MANDT"||CHR(39)||CHR(44) ||
CHR(39)||"MATNR"||CHR(39)||CHR(44) ||
...
CHR(39)||"FASHGRD"||CHR(39)
FROM SAPR3.MARA AS OF TIMESTAMP TO_TIMESTAMP('2008-04-21 13:00:00','YYYY-MM-DD H
H24:MI:SS') WHERE MANDT = '570' AND MATNR IN ('286600400000','286600500000');
|
This produces our csv output:
'570','286600400000','20041109','STARTER','20050829','TEST1',...,''
'570','286600500000','20041109','STARTER','20050508','TEST1',...,''
|
Now we saved the lost data, i suggested already to deliver it to development or apps people. It is not a Basis / DBA task to change application data, you will not get any support if you do. Nevertheless i will show the unofficial direct undo method below.
Can you please fix it?
As in most cases everybody expects the Basis / DBA to immediately fix other people's mistakes. As already mentioned I do not recommend this without careful checking, testing, and best only on dev or test systems. But the direct way is pretty easy, we simply re-insert the rows we had before:
INSERT INTO SAPR3.MARA ( SELECT * FROM SAPR3.MARA
AS OF TIMESTAMP TO_TIMESTAMP('2008-04-21 13:00:00','YYYY-MM-DD HH24:MI:SS')
WHERE MANDT = '570' AND MATNR IN ('286600400000', '286600500000') );
|
Conclusions
- With Oracle Flashback Query you can easily get back deleted / changed data of single tables.
- You might not be able to recover large distributed transactions, because it is just to complicated.
- Be sure to test your procedure first
- Do not re-apply the data on a prod system yourself, just producing a csv style text file should be the way to go
- Be careful when exporting data containing special characters like apostrophe, ampersand, they have a special meaning in SQLplus too. Your terminal might not support every character you use in your SAP system.
Further information:
SAP Note 937492 - FAQ: Oracle Flashbackhttps://service.sap.com/sap/support/notes/937492
Flashback Query Discussion on Ask Tom
Flashback Query Examples in official Oracle Documentation
