Solved

Recover the deleted records in Oracle database

Posted on 2001-07-09
4
1,871 Views
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

0
Comment
Question by:ezree
4 Comments
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
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*.
0
 
LVL 3

Accepted Solution

by:
UsamaMunir earned 5 total points
Comment Utility
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

Regards
UsamaMunir






0
 

Expert Comment

by:sunsapollos
Comment Utility

  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
 
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now