How to restore the records from the backup

[D.Ramesh]

Hi,
We are taking hot backup of the oracle 9i database daily.Which is running in archive log mode. Accidentaly one person deleted some records in one of the table. But this deletion information is reported to us after a days time. How to restore the records from the backup.

Once in a week we take cold backup.

1.Is it possible to restore that particuar table with the deleted records. If possible? How to do it.

In order to fix this type of scenario what precations should be taken at the database level.

Thanks,

[SANJAY_G]

If on 9i you can use Oracle Flashback Query. But ofcourse your UNDO_RETENTION and UNDO_TABLESPACE should be big enough to still have previous day data.
Exports are pretty useful in such scenarios. Otherwise TSPITR and re-enter the data thereafter.

[jmodic]

I gues using LogMiner would be the fastest choice here if you know the approximate time when the deletes were performed.

[D.Ramesh]

Hi,

I am not using logminer in my database. If so how do i take the data back from the database. The logminer just records all the activity(insert,delete,update etc...)on the database. How do i get the data?

Query
execute DBMS_LOGMNR.START_LOGMNR(dictfilename => ‘c:\logminer\dictionary.ora’, starttime=>to_date(‘04062003’,’ddmmyy’) ,endtime=> to_date(‘0406003’,’ddmmyy’));

Thanks,

[stmontgo]

laydown the backup and roll forward to the point in time before the failure (user). Then export the desired records or do something like insert as select over a db link.

Investigate tablespace point in time recovery and transportable tablespaces for ways to accomplish this.

steve

[vinit]

You can clone..basically restore your old backups and recover probably on a different host, using arch logs until time you deleted the table records. Once you complete this, take an export and import into prod.

[jmodic]

Originally posted by D.Ramesh
The logminer just records all the activity(insert,delete,update etc...)on the database. How do i get the data?

No, the logminer does not record all the changes on the database, your archived logs allready contain all that activities. Logminer only analyze the archived logs and construct the DML statements that have been executed. And as it can reconstruct the DML statements, it is also able to construct the opposite DML statements that will "undo" the changes recorded in the log files. So if you know your deletes were executed between time1 and time2, analyze the archlogs that were generated during that period - and you will be able to get INSERT statement that will be exactly the opposite of the DELETES.