SCN based incomplete recovery

rman target /

list backup summary;

on another terminal

sqlplus / as sysdba

select count(*) from scott.t1;
(just to check the number of rows)

select count(*) from scott.t2;

here we have 2 tables with rows. now we must note down the SCN where our tables exist.

select dbms_flashback.get_system_change_number scn from dual;

note down the scn.
1057770

now we create a new table

create table scott.t3 as select * from scott.t2;

insert into scott.t3 select * from scott.T2;

commit;


select dbms_flashback.get_system_change_number scn from dual;

note the scn number
1057828

at this number we have a new table T3 with data

now drop the T3 newly added table

drop table scott.t3 purge;

select count(*) from scott.t3;
(table does not exist)

t3 table has been dropped at the scn noted

we dont know the time when T3 was dropped, so we cannot use a time-base
incomplete recovery.

we know a drop operation was performed against that table so we can try
to use a LogMiner session.

select member from v$logfile a, v$log b 
where a.group#=b.group# and b.status='CURRENT';

suppose redo02.log is the current logfile

exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/prod/redo02.log', options=>dbms_logmnr.addfile);

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

col sql_redo format a60
set lines 180
set pages 999
col ts format a30

select scn, to_char(timestamp, 'DD/MM/YYYY HH24:MI:SS') ts, operation, sql_redo
from v$logmnr_contents 
where table_name = 'T3'
order by ts;

create = 1057786
drop   = 1057867

exec dbms_logmnr.end_logmnr();


go to rman window and at RMAN prompt

shutdown immediate;

startup mount;

copy the scn number we need to recover from the last query and come to rman window

run{
set until scn 12345;
restore database;
recover database;
alter database open resetlogs;}

go to sql window
select count(*) from scott.t3;

table is existing and with rows.