Finding the reason of the lost data on oracle database

oracle

My application uses Oracle database. At the same time Oracle ERP application and another Windows application (like mine) use same database. Everything looks fine and works synchronized but sometimes the Survey table lose data because of unknown reason!

I thought I delete it mistakenly in a case and added a backup query onto my delete query. Before run delete query, I copy the data to SurveyBackUp table. Yesterday the problem occurred again and I checked my backup table but it was empty! So, it looks like; the lost data is not deleted by my query. Is there any way to detect which query deletes the data?

Best Answer

Use Oracle's "tool" called Log miner.

restore some older backup of your database
identify ROWID (physical location) of record which was accidentally deleted
restore all archived redo logs
use log miner to get the SQL which deleted the row
log miner will also tell you culprits program name and hostname

Related Solutions

Arabic characters not displaying in oracle database

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

 -- or SHUTDOWN NORMAL; 

STARTUP;

Internal query on CDEF$ and CON$ very CPU bound

Browse My Oracle Support for the possible causes. For example quick search revealed a bug about your Oracle internal SQL. Workaround for it is:

Set optimizer_mode=rule for the affected DMLs

The bug is registered against version 11g but of course it is possible that it exists also in 10g.

Best Answer

Related Solutions

Arabic characters not displaying in oracle database

Internal query on CDEF$ and CON$ very CPU bound

Related Question