Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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 …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

610 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