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 36

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

600 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