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.