Way to track data deleted from database

Question

I'm using Oracle 11g database in archivelog mode and auditing is not enabled. Two days before, a few lines from a child table in my database got deleted. As the schema password was shared, I'm unable to track how it happened.I checked the trace files as well as queried tables v$log_history, V$SQL, DBA_HIST_SQLTEXT to get the history, but couldn't find any relevant information. Also, I reviewed the data by restoring the backup on this same date. But, since this data was created and got deleted on the same date, I'm unable to track this data even from the backup. As I'm a newbie, I don't know how to tackle this issue. Are there any other methods/queries by which I can check if any delete operation had been performed in my database?

Answer 1

Provided Minimal Supplemental Logging was turned on before the incident occurred that you want to research (it is off by default since there is a minor performance hit by having it on) which you can check via: select supplemental_log_data_min from v$database;, then Logminer is the tool for this.

Start logminer:

execute dbms_logmnr_d.build(options=> dbms_logmnr_d.store_in_redo_logs);

Load the archive logs that you want to view into Logminer:

begin
   dbms_logmnr.start_logmnr(
     starttime => TO_TIMESTAMP('2017-03-02 07:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
     endtime => TO_TIMESTAMP('2017-03-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
     options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine);
end;

The above assumes that the ArchiveLogs are still on disk. If you've backed them up, you'll have to unpack them from the backup and register them with the database first.

Query the Redo

select timestamp, operation, username, session#, scn, sql_redo
   from v$logmnr_contents where sql_redo like '<SOME SEARCH CRITERIA>';

End your logminer session

exec dbms_logmnr.end_logmnr;

You can read about Logminer from the docs.

If supplemental Logging is turned off, you may still be able to get the information you need by determining when the incident occurred using flashback queries to narrow down the exact time of the incident (provided you have a large enough Undo and retention policies are adequate):

select * from <SOMETABLE> 
  AS OF TIMESTAMP TO_TIMESTAMP('2017-03-02 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Then query the audit trail to find out when the shared schema user logged on around the above timeframe:

select * from dba_audit_trail where username = '<SOME USER>' order by timestamp;

The above assumes that you are logging audit information to the DB (show parameter audit_trail;). You can read about auditing from the docs.