How to get back truncated data using DBMS_LOGMNR

This is also posted in OTN by me.
My test database is not in ARCHIVELOG mode. So

C:\Documents and Settings\Administrator>sqlplus /nolog
 
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 18 13:52:32 2010
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size             109055128 bytes
Database Buffers          171966464 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> alter database archivelog
  2  ;
 
Database altered.
 
SQL> alter database open
  2  ;
 
Database altered.
 
SQL> SELECT log_mode FROM v$database;
 
LOG_MODE
------------
ARCHIVELOG
 

SQL>

By the way in my version once the recovery has failed until I have used:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
Database altered.

Now the main part:

SQL> conn hr@xe
Enter password: **
Connected.
SQL> drop table emp_test_logminer purge;
 
Table dropped.
 
SQL> CREATE TABLE emp_test_logminer AS SELECT * FROM emp;
 
Table created.
 
SQL> SELECT COUNT(*) FROM emp_test_logminer;
 
  COUNT(*)
----------
        14
 
SQL> TRUNCATE TABLE emp_test_logminer;
 
Table truncated.
 
SQL> SELECT COUNT(*) FROM emp_test_logminer;
 
  COUNT(*)
----------
         0
 
SQL> conn sys@xe as sysdba
Enter password: ******
Connected.
SQL>   BEGIN
  2        DBMS_LOGMNR.START_LOGMNR (
  3              starttime => '18-NOV-2010 14:40:00',
  4              endtime   => '18-NOV-2010 14:45:00',
  5              options   => dbms_logmnr.DICT_FROM_ONLINE_CATALOG+
  6                           dbms_logmnr.continuous_mine +
  7                           dbms_logmnr.no_sql_delimiter +
  8                           dbms_logmnr.print_pretty_sql
  9                                     );
 10    END;
 11  / 
  BEGIN
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 2
 
 
SQL> ALTER SESSION SET nls_date_format='DD-MON-RRRR hh24:mi:ss';
 
Session altered.
 
SQL> BEGIN
  2      DBMS_LOGMNR.START_LOGMNR (
  3            starttime => '18-NOV-2010 14:40:00',
  4            endtime   => '18-NOV-2010 14:45:00',
  5            options   => dbms_logmnr.DICT_FROM_ONLINE_CATALOG+
  6                         dbms_logmnr.continuous_mine +
  7                         dbms_logmnr.no_sql_delimiter +
  8                         dbms_logmnr.print_pretty_sql
  9                                   );
 10  END;
 11  / 
 
PL/SQL procedure successfully completed.
 
SQL> SELECT scn , sql_redo , sql_undo from v$logmnr_contents
  2  WHERE username = 'HR'
  3  AND   seg_name = 'EMP_TEST_LOGMINER';
 
       SCN SQL_REDO                            SQL_UNDO
---------- ----------------------------------- -----------------------------------
  23190408 drop table emp_test_logminer purge
  23190684 CREATE TABLE emp_test_logminer AS S
           ELECT * FROM emp
 
  23190689 insert into "HR"."EMP_TEST_LOGMINER delete from "HR"."EMP_TEST_LOGMINER
           "                                   "
            values                              where
               "EMPNO" = 7839,                     "EMPNO" = 7839 and
               "ENAME" = 'KING',                   "ENAME" = 'KING' and
               "JOB" = 'PRESIDENT',                "JOB" = 'PRESIDENT' and
               "MGR" IS NULL,                      "MGR" IS NULL and
               "HIREDATE" = TO_DATE('17-NOV-19     "HIREDATE" = TO_DATE('17-NOV-19
           81 00:00:00', 'DD-MON-RRRR hh24:mi: 81 00:00:00', 'DD-MON-RRRR hh24:mi:
           ss'),                               ss') and
               "SAL" = 5000,                       "SAL" = 5000 and
               "COMM" IS NULL,                     "COMM" IS NULL and
               "DEPTNO" = 10                       "DEPTNO" = 10 and
                                                   ROWID = 'AAAFRsAABAAAMFKAAA'
.................................................................................................

.................................................................................................

So all the 14 rows are there. You can now recover from this.