Recover the deleted records in Oracle database

Posted on 2001-07-09
Medium Priority
Last Modified: 2012-06-21
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

Question by:ezree

Expert Comment

ID: 6268150
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*.

Accepted Solution

UsamaMunir earned 20 total points
ID: 6268158
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



Expert Comment

ID: 6268435

  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 data.you 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 mode.you can recover it from backup and archivelog.as 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
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6270687
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.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month17 days, 1 hour left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question