Recovering database until time

Example of recovery database until time.


For checking after recovery I am going to create simple table with one row

 SQL> create user ulfet identified by ulfet;  
 User created.  
 SQL> grant dba to ulfet;  
 Grant succeeded.  
 SQL> conn ulfet/ulfet  
 Connected.  
 SSQL> create table test1 (id number);  
 Table created.  
 SQL> insert into test1 values(1);  
 1 row created.  
 SQL> commit;  
 Commit complete.

Now taking backup.

RMAN> backup database plus archivelog;

--note date

 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.03.34.646076 AM +04:00  
 --dropping table  
 SQL> drop table ulfet.test1;  
 Table dropped.  
 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.04.40.428868 AM +04:00  
 SQL>  

--start recovery process

--firstly start database in nomount mode for restoring controlfile

RMAN> startup nomount  
 connected to target database (not started)  
 Oracle instance started  
 Total System Global Area   523108352 bytes  
 Fixed Size           1337632 bytes  
 Variable Size        314574560 bytes  
 Database Buffers       201326592 bytes  
 Redo Buffers          5869568 bytes  
 RMAN>   

--drop control files

[oracle@dbserver oradata]$ cd mydb/  
 [oracle@dbserver mydb]$ ls  
 control01.ctl redo01.log redo03.log  system01.dbf undotbs01.dbf  
 example01.dbf redo02.log sysaux01.dbf temp01.dbf  users01.dbf  
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ rm control01.ctl   
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/  
 mydb/ MYDB/   
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/mydb/  
 [oracle@dbserver mydb]$ rm control02.ctl   
 [oracle@dbserver mydb]$   

--Restoring controlfile from autoback;

RMAN> restore controlfile from autobackup;  
 Starting restore at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 recovery area destination: /u01/app/oracle/flash_recovery_area  
 database name (or database unique name) used for search: MYDB  
 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp found in the recovery area  
 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160113  
 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 channel ORA_DISK_1: control file restore from AUTOBACKUP complete  
 output file name=/u01/app/oracle/oradata/mydb/control01.ctl  
 output file name=/u01/app/oracle/flash_recovery_area/mydb/control02.ctl  
 Finished restore at 13-JAN-16  

check if you want :)

[oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ ls control*  
 control01.ctl   
 [oracle@dbserver mydb]$ ls ../../flash_recovery_area/mydb/  
 control02.ctl  

alter database in mount mode for restore and recovery process

 RMAN> alter database mount;  
 database mounted  
 released channel: ORA_DISK_1  
 RMAN>   

--write commands for recovery process

 RMAN> run  
 {  
 set newname for database to '/u01/app/oracle/oradata/mydb/%b';  
 set until time "to_date('2016-01-13 11:03:34', 'yyyy-mm-dd hh24:mi:ss')";  
 restore database;  
 switch datafile all;  
 switch tempfile all;  
 recover database;  
 }2> 3> 4> 5> 6> 7> 8> 9>   
 executing command: SET NEWNAME  
 executing command: SET until clause  
 Starting restore at 13-JAN-16  
 Starting implicit crosscheck backup at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 Crosschecked 9 objects  
 Finished implicit crosscheck backup at 13-JAN-16  
 Starting implicit crosscheck copy at 13-JAN-16  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 13-JAN-16  
 searching for all files in the recovery area  
 cataloging files...  
 cataloging done  
 List of Cataloged Files  
 =======================  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901018889_c9cxlcbt_.bkp  
 using channel ORA_DISK_1  
 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 /u01/app/oracle/oradata/mydb/system01.dbf  
 channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mydb/sysaux01.dbf  
 channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mydb/undotbs01.dbf  
 channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mydb/users01.dbf  
 channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/mydb/example01.dbf  
 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp  
 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp tag=TAG20160113T110002  
 channel ORA_DISK_1: restored backup piece 1  
 channel ORA_DISK_1: restore complete, elapsed time: 00:01:56  
 Finished restore at 13-JAN-16  
 Starting recover at 13-JAN-16  
 using channel ORA_DISK_1  
 starting media recovery  
 archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc  
 archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/mydb/redo03.log  
 archived log file name=/u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc thread=1 sequence=2  
 archived log file name=/u01/app/oracle/oradata/mydb/redo03.log thread=1 sequence=3  
 media recovery complete, elapsed time: 00:00:02  
 Finished recover at 13-JAN-16  

I have forgotten writing open table in resetlogs in end of command.
Now you can see, out database`s state is mounted, open it with resetlogs

SQL> conn /as sysdba  
 Connected.  
 SQL> select open_mode from v$database;  
 OPEN_MODE  
 --------------------  
 MOUNTED  
 SQL> alter database open resetlogs;  
 Database altered.  

--Now try to check

SQL> conn ulfet/ulfet  
 Connected.  
 SQL> select * from tab;  
 TNAME             TABTYPE CLUSTERID  
 ------------------------------ ------- ----------  
 TEST1             TABLE  
 SQL> select * from test1;  
     ID  
 ----------  
      1  
 SQL>   

That`s all