Log-miner and Incomplete recovery

Problem Statement : Someone truncated a table accidentally and you want the data back . You are using oracle 11g .

Things you need to do get data back truncated data :
------------------------------------------------------------------
1. You should know the exact time or scn at which table got truncated . Logminer can help you here .
2. You need to perform incomplete recovery until scn at some test instance other than production
3. once your table is back on test instance just export it and import on production .


SQL> archive log list ;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     73
Next log sequence to archive   75
Current log sequence       75
SQL>
SQL> conn sumit/sumit
Connected.
SQL> create table tab(i int) ;

Table created.

SQL> insert into tab select 1 from dual ;

1 row created.

SQL> commit ;

Commit complete.

SQL> update tab set i=5 where i=1 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> insert into tab select 1 from dual ;

1 row created.

SQL> commit ;

Commit complete.

SQL> alter system switch logfile ;

System altered.

SQL> commit ;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> update sumit.tab set i=7 ;

2 rows updated.

SQL> commit ;

Commit complete.

SQL> truncate table sumit.tab ;

Table truncated.

SQL> select count(*) from sumit.tab ;

  COUNT(*)
----------
0

SQL> commit ;

Commit complete.

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select name from v$archived_log ;

NAME
--------------------------------------------------------------------------------

+DATA/orcl/archivelog/2015_06_06/thread_1_seq_73.288.881698907
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_77.269.881699637
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_78.268.881699639

Determine the SCN for the truncate statement using logminer 

exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_73.288.881698907',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_77.269.881699637',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_78.268.881699639',options=>dbms_logmnr.addfile);

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);


COLUMN username   FORMAT A8
COLUMN operation  FORMAT A9
COLUMN sql_redo   FORMAT A50 WORD_WRAPPED
COLUMN sql_undo   FORMAT A50 WORD_WRAPPED
COLUMN timestamp  FORMAT A20

SELECT  username , operation, sql_redo, sql_undo, TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') timestamp, scn FROM v$logmnr_contents WHERE table_name='TAB'

SQL> select count(*) from sumit.tab ;

  COUNT(*)
----------
0

Perform incomplete recovery until scn 1204306 on a TEST instance :

RMAN> run {
 restore database until scn 1204306 ;
 recover database until scn 1204306 ;
 }

Starting restore at 06-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.276.875242649
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.277.875242649
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.278.875242649
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.279.875242649
channel ORA_DISK_1: restoring datafile 00005 to +DATA2/orcl/datafile/users.294.875976397
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/datafile/user02.dbf
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2015_06_06/nnndf0_tag20150606t202335_0.270.881699015
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2015_06_06/nnndf0_tag20150606t202335_0.270.881699015 tag=TAG20150606T202335
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 06-JUN-15

Starting recover at 06-JUN-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 74 is already on disk as file +DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115
archived log for thread 1 with sequence 75 is already on disk as file +DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583
archived log for thread 1 with sequence 76 is already on disk as file +DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635
archived log file name=+DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115 thread=1 sequence=74
archived log file name=+DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583 thread=1 sequence=75
archived log file name=+DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635 thread=1 sequence=76
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-JUN-15

SQL> alter database open resetlogs;

Database altered.

Check the data , now you just need to export the tab table and import in prod server :

SQL> select count(*) from sumit.tab ;

  COUNT(*)
----------
2