Re: lost some data in table

Andrea wrote:
> Charles Hooper wrote:
> > 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.
> > ----------------------------------------
> >
>
> thanks very much for your support,.. there is some on-line
> documentation for implementing and using logminer for my case?

Most of the above was pulled from the pages of "Expert Oracle Database 10g Administration" written by Sam Alapati, which in my opinion is a very thorough book for 10g administration tasks.

Oracle documentation for LogMiner (watch for word wrap): http://www.oracle.com/pls/db102/search?remark=advanced_search&word=logminer&book=&preference=

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