How to Recover Dropped Tablespace

Backup Method: User Managed (hot Bkp)SQL> conn sys/oracle as sysdbaConnected.SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to bkp locationSQL> alter database end backup;Database altered.SQL> alter system switch logfile;System altered.SQL> alter database backup controlfile to 'd:\bkp\control01.ctl';Database altered.SQL> drop tablespace test including contents and datafiles;Tablespace dropped.SQL> ---oops by mistake drop production tablespaceSQL> conn scott/tigerConnected.SQL> select count(*) from a;select count(*) from a*ERROR at line 1:ORA-00942: table or view does not existSQL> conn sys/oracle as sysdbaConnected.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> ---delete all datafiles & controlfile.SQL> startup nomount;ORACLE instance started.Total System Global Area 293601280 bytesFixed Size 789100 bytesVariable Size 94631316 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytesSQL> --restore controlfile from bkpSQL> alter database mount;Database altered.SQL> --restore all datafiles from bkpSQL> --now perform time based recovery.SQL> --check ALERT.LOG file for exact time when tablespace was dropped.SQL> --perform TIME BASED recovery just before dropped tablespace.SQL> recover database using backup controlfile UNTIL TIME '2008-03-25 08:47:00';ORA-00279: change 461791 generated at 03/25/2008 08:43:31 needed for thread 1ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001ORA-00280: change 461791 for thread 1 is in sequence #2Specify log: {=suggested filename AUTO CANCEL}autoORA-00279: change 461827 generated at 03/25/2008 08:44:45 needed for thread 1ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001ORA-00280: change 461827 for thread 1 is in sequence #3ORA-00278: log file'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001' no longer needed for this recoveryORA-00308: cannot open archived log'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001'ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 2) The system cannot find the file specified.SQL> --Open database with RESETLOGSSQL> alter database open resetlogs;Database altered.SQL> --now check dropped tablespace is existSQL> select name from v$datafile where name like '%TEST%';NAME--------------------------------------------------------------------------------D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBFSQL> conn scott/tigerConnected.SQL> select count(*) from a;COUNT(*)----------10000=======================================================Oracle 10gr1/Win2003Backup Method: RMANC:\>rman target sysRecovery Manager: Release 10.1.0.5.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database: ORCL (DBID=1178009698)RMAN> run2> {3> backup database plus archivelog;4> backup current controlfile;5> }Starting backup at 25-MAR-08current log archived using target database controlfile instead of recovery catalogORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: starting piece 1 at 25-MAR-08channel ORA_DISK_1: finished piece 1 at 25-MAR-08piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current controlfile in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 25-MAR-08channel ORA_DISK_1: finished piece 1 at 25-MAR-08piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0CJC4UJK_1_1 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 25-MAR-08
SQL> conn sys/oracle as sysdba
Connected.
SQL> drop tablespace test including contents and datafiles;Tablespace dropped.
SQL> ---oops dropped production tablespace
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
C:\>REM delete all controlfile and datafilesC:\>rman target sys/oracleRecovery Manager: Release 10.1.0.5.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.connected to target database (not started)RMAN> startup nomount;Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 789100 bytes
Variable Size 94631316 bytes
Database Buffers 197132288 bytes
Redo Buffers 1048576 bytes
NOTE: restore controlfile from backupset.
RMAN> restore controlfile from 'D:\oracle\product\10.1.0\Db_1\database\0CJC4UJK_1_1';Starting restore at 25-MAR-08
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 25-MAR-08
RMAN> alter database mount;database mounted
released channel: ORA_DISK_1RMAN> run
2> {
3> set UNTIL TIME "to_date('2008-03-25 09:19:44','YYYY-MM-DD HH24:MI:SS')";
4> restore database;
5> recover database;
6> }executing command: SET until clauseStarting restore at 25-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 tag=TAG20080325T091205
channel ORA_DISK_1: restore complete
Finished restore at 25-MAR-08Starting recover at 25-MAR-08using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 2 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG
archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG
archive log filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG thread=1 sequence=2
archive log filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG thread=1 sequence=3
media recovery complete
Finished recover at 25-MAR-08
RMAN> alter database open resetlogs;
database opened
NOTE:
1. I am using Controlfile Instead of Recovery catalog for RMAN repository
2. Don't use AUTOBACKUP controlfile option becuase we need backup controlfile for incomplete recovery not current controlfile.