recovery using set until time

Scenario
a big and important table user_logs is in database which stores all user transactions logs. We periodically create a new table by selecting whole data from user_logs table (e.g. user_logs_15Dec2012) and then drop and recreate user_logs table using its script.
Then we export newly created table into a dump file and them import it into another database for keeping history of all transactions.

as this is a very big table so new table which was created using

create table user_logs_15dec2012 as select * from user_logs

took 10+ minutes.

while this table was being created there were 8 more transactions recorded in user_logs table. We didn't check it and dropped the table soon after new table creation.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                   TYPE         DROP TIME
---------------- --------------------------------- ------------ -------------------
user_logs          BIN$/5zg2tXYSNSkivsGnYnPZg==$0  TABLE        2012-12-15:14:17:00

upon comparison of both tables user_logs_15dec2012 and dropped table "BIN$/5zg2tXYSNSkivsGnYnPZg==$0" (Name assigned by after drop)


SQL> select count(*) from user_log_15dec2012;

  COUNT(*)
----------
  14885076

------------------------------------------------------------------------

SQL> select count(*) from "BIN$/5zg2tXYSNSkivsGnYnPZg==$0";

  COUNT(*)
----------
  14885084




There was difference of 8 records.

when i tried to create new table using same statement

SQL>create table user_logs_restored as select * from "BIN$/5zg2tXYSNSkivsGnYnPZg==$0"


ORA-08103: object no longer exists

Oops....
The reason was because dropped objects in recylebin are still in same tablespace and upon creation of new permanent objects space was reclaimed and objects in recyclebin were automatically deleted by database.

so did we lost those 8 transactions ????
----------------------------------------------------------------------

Here comes rman into play

--------------------------------------------------------------------

I checked creation timestamp of user_log table, it was 2:17pm, so at that point of time deleted table was present in recyclebin, on test server, i restored database using until time clause




C:\Users\farif>set oracle_sid=homedb

C:\Users\farif>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 6 14:20:17 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: homeDB (DBID=1547250382)

RMAN>RUN{
      ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
        SEND 'NB_ORA_CLIENT=OR-01, NB_ORA_SERV=mg-01';
       restore controlfile to 'D:\app\Oracle\oradata\homedb\CONTROL01.CTL' from 'cf_c-1547250382-20121217-01';
        RELEASE CHANNEL ch00;
  }



RMAN>alter database mount;

database mounted

Started restore of database upto time 2012-12-15:14:20:00 (Time when dropped table was present in recyclebin)

RMAN>

run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=OR-021, NB_ORA_SERV=p-mg-01';
set until time "to_date('2012-12-15:14:20:00','YYYY-MM-DD:HH24:MI:SS')";
set newname for datafile 1 to 'D:\app\Oracle\oradata\homedb\system.256.770482167';
set newname for datafile 2 to 'D:\app\Oracle\oradata\homedb\sysaux.257.770482167';
set newname for datafile 3 to 'D:\app\Oracle\oradata\homedb\undotbs1.258.770482167';
set newname for datafile 4 to 'D:\app\Oracle\oradata\homedb\users.259.770482167';
set newname for datafile 5 to 'D:\app\Oracle\oradata\homedb\example.264.770482273';
set newname for datafile 6 to 'D:\app\Oracle\oradata\homedb\undotbs2.265.770482381';
set newname for datafile 7 to 'D:\app\Oracle\oradata\homedb\homelog01.dbf';
set newname for datafile 8 to 'D:\app\Oracle\oradata\homedb\test_netbkup.dbf';
set newname for datafile 9 to 'D:\app\Oracle\oradata\homedb\recop1.dbf';
set newname for datafile 10 to 'D:\app\Oracle\oradata\homedb\homelog02.dbf';
set newname for datafile 11 to 'D:\app\Oracle\oradata\homedb\home_ts01.dbf';
set newname for datafile 12 to 'D:\app\Oracle\oradata\homedb\home_ts02.dbf';
set newname for datafile 13 to 'D:\app\Oracle\oradata\homedb\home_ts03.dbf';
set newname for datafile 14 to 'D:\app\Oracle\oradata\homedb\home_ts04.dbf';
set newname for datafile 15 to 'D:\app\Oracle\oradata\homedb\home_ts05.dbf';
set newname for datafile 16 to 'D:\app\Oracle\oradata\homedb\undotbs1.278.770746419';
set newname for datafile 17 to 'D:\app\Oracle\oradata\homedb\undotbs2.279.770746495';
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
RELEASE CHANNEL ch00;
}

now the recycled table was present in database 

SQL> select count(*) from "BIN$/5zg2tXYSNSkivsGnYnPZg==$0";

  COUNT(*)
----------
  14885084

i restored it and renamed it to a new name

sqlplus> FLASHBACK TABLE "BIN$/5zg2tXYSNSkivsGnYnPZg==$0" TO BEFORE DROP 

     RENAME TO home.user_log_retored;

SQL> select count(*) from user_log_restored

  COUNT(*)
----------
  14885084