How to Recover Dropped Tablespace

Oracle Ver: 10gr1/Win 2003
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 location

SQL> 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 tablespace

SQL> conn scott/tiger
Connected.

SQL> select count(*) from a;
select count(*) from a
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn sys/oracle as sysdba
Connected.

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 bytes
Fixed Size 789100 bytes
Variable Size 94631316 bytes
Database Buffers 197132288 bytes
Redo Buffers 1048576 bytes

SQL> --restore controlfile from bkp

SQL> alter database mount;
Database altered.

SQL> --restore all datafiles from bkp

SQL> --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 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001
ORA-00280: change 461791 for thread 1 is in sequence #2
Specify log: {=suggested filename AUTO CANCEL}
auto

ORA-00279: change 461827 generated at 03/25/2008 08:44:45 needed for thread 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001
ORA-00280: change 461827 for thread 1 is in sequence #3
ORA-00278: log file'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001' no longer needed for this recovery
ORA-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 file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> --Open database with RESETLOGS

SQL> alter database open resetlogs;
Database altered.

SQL> --now check dropped tablespace is exist

SQL> select name from v$datafile where name like '%TEST%';
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF

SQL> conn scott/tiger
Connected.

SQL> select count(*) from a;
COUNT(*)
----------
10000

=======================================================

Oracle 10gr1/Win2003
Backup Method: RMAN

C:\>rman target sys
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1178009698)
RMAN> run
2> {
3> backup database plus archivelog;
4> backup current controlfile;
5> }
Starting backup at 25-MAR-08
current log archived using target database controlfile instead of recovery catalog

ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 25-MAR-08
channel ORA_DISK_1: finished piece 1 at 25-MAR-08
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupsetincluding current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25-MAR-08
channel ORA_DISK_1: finished piece 1 at 25-MAR-08
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0CJC4UJK_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished 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 datafiles
C:\>rman target sys/oracle
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed 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=DISK
channel 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_1
RMAN> 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 clause
Starting restore at 25-MAR-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=160 devtype=DISK
channel 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-08
Starting recover at 25-MAR-08using channel ORA_DISK_1
starting media recovery
archive 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.