Looking for something? Try here..

Monday, July 18, 2011

Retrieve deleted rows from table using audit.

Firstly connect as scott from any other user.
Conn scott/tiger
select * from emp;
..
..
DELETE FROM EMP WHERE EMPNO = 7499;
Now lets retrieve this row by using audits and flashback.
I have mentioned using audits as the row may be accidentally deleted or even deleted by any malicious activity (some unauthorised person performing the delete operation)
If it's due to malicious activity, we can also find the culprit who performed the act.

Check what audit is recorded in the database.
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
audit_sys_operations
FALSE
audit_file_dest
D:\ORACLE\PRODUCT\10.2.0\ADMIN\10gDB\ADUMP
audit_trail
DB
The audit is being done to the DB (Mostly DB audit would be done)
Now lets get some details from sys.aud$ table which is the table used for storing audit related things.
SELECT userid, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss') 
FROM sys.aud$ ORDER BY timestamp# asc;
Output would look like this

Timeline from the database audit:

USERID
ACTION#
STATEMENT
OBJ$NAME
TIMESTAMP
SCOTT
101
1

04/30/2006 09:11:36
SCOTT
3
2
X$NLS_PARAMETERS
04/30/2006 09:29:07
SCOTT
3
2
GV$NLS_PARAMETERS
04/30/2006 09:29:07
SCOTT
3
2
V$NLS_PARAMETERS
04/30/2006 09:29:07
SCOTT
3
2
NLS_SESSION_PARAMETERS
04/30/2006 09:29:07
SCOTT
3
5
DUAL
04/30/2006 09:29:07
SCOTT
100
1

04/30/2006 09:29:41
SCOTT
3
22
OBJ$
04/30/2006 09:31:07
SCOTT
3
22
USER_OBJECTS
04/30/2006 09:31:07
SCOTT
3
28
EMP
04/30/2006 09:32:01
SCOTT
3
31
EMP
04/30/2006 09:32:20
SCOTT
7
37
EMP
04/30/2006 09:33:28
SCOTT
3
46
EMP
04/30/2006 09:35:24
SCOTT
7
52
EMP
04/30/2006 09:37:04
SCOTT
7
55
EMP
04/30/2006 09:37:13
SCOTT
3
61
EMP
04/30/2006 09:37:28


Now we have to find which action# refers to what operation. To find this issue the command below.
SELECT * FROM AUDIT_ACTIONS;
Now you can see action# 7 denotes delete operation.

Delete operation was performed around 9 37. So we want to flashback to before then so have to get the recorded timestamp. Oracle does not actually record a full timeline. Only takes the time every 5 minutes with the relevant SCN.
So now we will find the scn according to the timestamp from the below command which would be stored for maximum 5 days rolling figure.
SELECT To_Char(TIME_DP, 'dd/mm/yyyy hh24:mi:ss'), SCN_BAS 
FROM SYS.SMON_SCN_TIME order by scn_bas;
Now you can retrieve the deleted row by flashback the table to before the timestamp where the data is deleted.
CREATE TABLE EMPCOPY AS 
SELECT * FROM SCOTT.EMP 
AS OF TIMESTAMP (TO_TIMESTAMP('30/04/2006 09:31:10','DD-MM-YYYY:HH24:MI:SS'));
where 09:31:10 is the predecessor scn timestamp before the data deletion time of 09:37:13.

Now you can find the deleted row back in the EMPCOPY table.

Let's now find out did SCOTT do this deletion activity or any other malicious user did this on purpose to get SCOTT into trouble.

We can use the same sys.aud$ table to find this too..
SELECT userid, USERHOST, TERMINAL, SPARE1, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss') 
FROM sys.aud$ ORDER BY timestamp# asc;
This additional data shows that SCOTT was coming from a different workstation from normal additionally the SPARE1 column shows that the Windows username was in fact SELVA and not SCOTT.

So take actions accordingly.

12 comments:

  1. hi sir
    thank you so so much for giving such a valuable and detailed information regarding this.

    sir one problem is regarding truncate operation .
    as we know truncate operation in oracle sever delete the rows permanently of specified table.
    if i do this truncate operation in same scenario as we earlier discussed. is there any chance to retrieve it back in reality.

    if you will suggest regarding this the i will greateful to you

    ReplyDelete
  2. Once when you truncate a table, the table definition would be changed i.e. the system time has changed since the last snapshot was taken. So you cannot retrieve the data of a truncated table unless FLASHBACK feature of the database is enabled.
    You can do a DB point in time recovery to bring back the datas of the table.

    ReplyDelete
  3. thank you sir

    sir can you elaborate this in steps it will be benifical for me.

    ReplyDelete
  4. I have explained step by step by using scott user. You can try the same as explained!!

    ReplyDelete
  5. hello sir good morning and happy new year to you.sir regarding retrieve deleted rows from table using audit when i fire third command i.e

    SQL> SELECT userid, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss') FROM sys.aud$ ORDER BY timestamp# asc;

    i get output
    SQL> no row selected

    but i have already set audit_trail = "db" in v$parameter.
    please suggest regarding this

    thank you!

    shivesh mishra

    ReplyDelete
  6. Check the pfile entry for audit_trail. It should be set to db. Also if you want to audit sys operations you should set AUDIT_SYS_OPERATIONS = TRUE.

    ReplyDelete
  7. Hi Mamu Kutti,
    Data is deleting from my table.I want to know which procedure is deleting this data.
    Please send your comments to rajeev.lds@gmail.com

    ReplyDelete
  8. Rajeev, You have to turn on auditing or if you want to check the delete on a particular table you can set up a simple trigger to be initiated whenever data being deleted.

    ReplyDelete