In order to work with logminer, the Oracle database should be in archive log mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 100664912 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.


# The archive log files just contain enough data to do database
# recovery in case of an instance crash. In order for logminer
# to work you have to enable supplemental log data prior to or
# before the generation of archived log files.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> alter database open;

Database altered.

SQL> archive log list;
Code:
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> alter user scott identified by tiger account unlock;

User altered.


# Unlocking scott user.

SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";

Session altered.


# Set the session date format with nls_date_format because
# then you don't have to use character conversion or date
# conversion later.

SQL> select sysdate from dual;

Code:
SYSDATE                                                                      
--------------------                                                         
16-OCT-2010 11:28:16
SQL> show user;
USER is "SYS"
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"

SQL> select dname from dept;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL> update dept set dname = 'XYZ';

4 rows updated.


# Records update with wrong entries.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";

Session altered.

SQL> select sysdate from dual;
Code:
SYSDATE                                                                      
--------------------                                                         
16-OCT-2010 11:30:52
SQL> show user
USER is "SYS"


SQL> alter system switch logfile;

System altered.


# Generate archived log files through the above command.

SQL>begin
dbms_logmnr.start_logmnr (
starttime => '16-OCT-2010 11:28:16',
endtime => '16-OCT-2010 11:30:52',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/

PL/SQL procedure successfully completed.


# 1)With the dbms_logmnr.start_logmnr we will start the logminer session.
#
# 2)The starttime and endtime gives a time window. The logminer will collect
# all the data within this time window.
#
# 3)The dbms_logmnr.dict_from_online_catalog means that the logminer will
# collect all the information directly from the Data Dictionary. This is because
# logminer needs the Data Dictionary information so that it can present the
#Archived redo log information in a human readable format.
#
# 4)The Continuous_mine option means that it will automatically hunt for the required
# archived redo log files, you don't have to specify them manully.
#
# 5)The no_sql_delimiter and print_pretty_sql options make the output a little less
# cluttered and more readable.




SQL> column sql_undo format a35
SQL> column sql_redo format a35
SQL> set lines 10000
SQL> set pages 200

SQL> select timestamp, sql_redo, sql_undo
2 from v$logmnr_contents
3 where username = 'SCOTT'
4 and seg_name = 'DEPT';
Code:
TIMESTAMP            SQL_REDO                            SQL_UNDO
-------------------- ----------------------------------- ----------------------------
16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'ACCOUNTING'
                    where                               where
                      "DNAME" = 'ACCOUNTING' and          "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAA'        ROWID = 'AAAMfKAAEAAAAAQAAA'

16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'RESEARCH'
                    where                               where
                      "DNAME" = 'RESEARCH' and            "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAB'        ROWID = 'AAAMfKAAEAAAAAQAAB'

16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'SALES'
                    where                               where
                      "DNAME" = 'SALES' and               "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAC'        ROWID = 'AAAMfKAAEAAAAAQAAC'

16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'OPERATIONS'
                    where                               where
                      "DNAME" = 'OPERATIONS' and          "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAD'        ROWID = 'AAAMfKAAEAAAAAQAAD'
# What we have done above is queried the V$logmnr_contents and retrieved all the changes
# that have been made by the user scott in the dept table.
# The sql_redo shows the changes that has been made and the sql_undo shows the opposite
# entries of the changes made. The statements from the sql_undo can be used to undo
# the changes that had been made accidentally.

SQL> select dname from scott.dept;
Code:
DNAME
--------------
XYZ
XYZ
XYZ
XYZ
SQL> set serveroutput on
SQL> declare
2 cursor c1 is
3 select sql_undo from v$logmnr_contents
4 where username = 'SCOTT'
5 and seg_name = 'DEPT';
6 begin
7 for rec in c1 loop
8 execute immediate rec.sql_undo;
9 dbms_output.put_line(sql%rowcount||' row(s) updated.');
10 end loop;
11 end;
12 /
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
PL/SQL procedure successfully completed.


# With the above PL/SQL procedure i have recovered all the accidental changes.

SQL> commit;

Commit complete.

SQL> select dname from scott.dept;

Code:
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.


# After doing everything we have to close the logminer session
# by executing the above statement.