Re: lost some data in table

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? Received on Tue Oct 24 2006 - 07:47:40 CDT