Recover the deleted records in Oracle database

Hi all gurus out there
  This might sounds silly, but mistakenly I've deleted and commited data in my database. Following
a good practice I know I've should have made a backup, but somtimes things happen.
  Is there anyway that I can recover the deleted data, by all means I really do appreciate any helps.
As the data is really important for my company.
   Machine : RISC 6000
   OS      : AIX 4.3.3
   Database : Oracle 8.1.5

Who is Participating?
Now if ur database is in Archive Log mode
You can recover the data with Point in time recovery using recover database command, For example the following comand is used to recover database to a point back in time. U can set this time to a time when ur records exsisted. All information available after that will not be available.

"Recover Database Until Time '1999-12-31-:12:47:30'"
(use server manager for this)

However if Your database is not in archive log mode
mayb You can do that with the help of LOgminer.
Logminer utility helps u veiw the contents of your logfiles.
if your logfile is still not overwritten it is possible to have your record back.

DBMS_logmnr package is available for that

Follow these steps (For any furthur detail , view Oracle Supplied Package Reference)

1) Set "Utl_file_dir" initialization parameter to a directory name where ur dictionary file will be placed.

2) Create Directory in ur Operating system filesystem if it doesn't already exsists.

3) issue this command from the sql prompt( u must have execute priviledge on Dbms_logmnr,Dbms_logmnr_d) package.

"Execute Dbms_logmnr_d('Dict.ora','c:\Dict')"

where first parameter is the dictionary filename and the other parameter the directory name (same as utl_file_dir)

4) Add logfiles to be analyzed in the current session of logmnr.
issue following command.

"Execute DBMS_LOGMNR.add_logfile('C:\Oracle\oradata\ora81\redo01.log')"

now using above command add all the logfiles to be analyzed..i suggest add all log files that u have.

5) start the logmnr session issue following command.
"Execute dbms_logmnr.start_logmnr(DICTFILENAME => 'c:\dict\dict.ora',STARTTIME => to_date('30-jan-99:08:30:00','DD-MON-RR:HH:MM:SS'),
ENDTIME =>to_date('30-jan-99:09:30:00','DD-MON-RR:HH:MM:SS'))

Note that inthis case we are trying to find out the changes made between 8:30 - 9:30 on 30th Jan 1999.

6) Now view changes made to the SALARY table in the specified time with following query

"select sql_redo, sql_undo from v$logmnr_contents
where username='JONES' and tablename = 'SALARY'"

SQL_REDO shows the query that was executed, and sql_undo shows the query to undo the action( you can use this query to get back ur records).

I Hope that helps u


jtriftsMI and AutomationCommented:
I am not aware of any means to retrieve deleted and committed data however, I would put in a call to ORACLE ASAP...and I would take backups now (both export and cold backup), so that if any new data overwrites the data marked as deleted, you will still be able to get back to the *moment after deletion*.

  If you have not any backup and logfile include deletd data,your data will lose for ever!
  If your database on noarchivelog mode and have backup include deleted data,you can restore backup to another place,then open it,fetch deleted data and insert into product database table.if your database is very large,you can mount database and recreate controlfile,keep include data tablespace datafile,offline any other datafile.thus you can only restore system datafile and tablespace(include data) datafile.
  If you have export dump file include deleted can rename the product database table,import the backup table,fetch deleted data into rename table,then drop the import table,rename table return.
  If your database archivelog can recover it from backup and long as you have all archivelog.
  If online redo log or archivelog include delete data record,you can use logmnr to retrieve data.pls reseach oracle doc:admin ref
Mark GeerlingsDatabase AdministratorCommented:
If your database is not in archivelog mode, then make a copy of your on-line redo logs immediately!   Also, see if your operating system can recover on-line redo logs that may have been overwritten recently.  Then use those log files along with LogMiner and you *may* be able to recover your data.

If you are running in archivelog mode and you have the archived redo logs, just use LogMiner on those files.  If you haven't used LogMiner before, you will have to do some setup.  Also, it is not completely trouble-free and bug-free, but it is the best chance you have.

I think you have learned that you should never run an Oracle database without a good backup strategy for it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.