Re: lost some data in table

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 24 Oct 2006 04:09:55 -0700
Message-ID: <1161688195.383028.46550@i42g2000cwa.googlegroups.com>

Andrea wrote:
> Hi,
>
> a user tell me that has accidentally deleted some rows in a table, can
> i restore it with flashback area?
> how i restore it without stop the db?
>
> i've 10g DB rel.2 with archivelog and flashback enables.
>
> thanks for your support

If you have LogMiner set up, use that to retrieve the SQL undo to reverse the deletes. A quick walk through (use SQLPlus to execute):

Move the data dictionary to a new archived redo log file: SQL> EXEC SYS.DBMS_LOGMNR_D.BUILD( OPTIONS => SYS.DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); Find the archive log number containing the data dictionary: SQL> SELECT SEQUENCE#, DICTIONARY_BEGIN, DICTIONARY_END FROM V$ARCHIVED_LOG; Include the archived log file containing the data dictionary: SQL> EXEC DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'full path and filename of the archive redo log data dictionary file here', OPTIONS => DBMS_LOGMNR.NEW); Include the archived log files to be examined: SQL> EXEC DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'full path and filename of the archive redo log suspected of containing the undo and redo for this period', OPTIONS => DBMS_LOGMNR.ADDFILE);

Start LogMiner:
SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS); See what columns are available to speed up selection of the data: SQL> DESC V$LOGMNR_CONTENTS; Read the data from LogMiner:
SQL> SELECT SQL_UNDO FROM V$LOGMNR_CONTENTS; When finished with LogMiner:
SQL> EXEC DBMS_LOGMNR.END_LOGMNR(); Notes:

---

The following should have been executed once shortly after the database was created to allow LogMiner to work fully: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; LogMiner's data should be contained in a separate tablespace: SQL> CREATE TABLESPACE LOGMINER DATAFILE 'file name for the LogMiner tablespace goes here' SIZE 200M AUTOEXTEND ON MAXSIZE UNLIMITED; SQL> EXEC DBMS_LOGMNR_D.SET_TABLESPACE('LOGMINER'); If the redo log file containing the change has not yet been archived, you may want to force a log file switch.

---

Good luck

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Oct 24 2006 - 06:09:55 CDT