Recovering Dropped tablespace using Flashback Database
Posted by Kamran Agayev A. on July 27th, 2009
Today, in OTN forum, there was a question relating the recovery of dropped tablespace using Flashback Database. I did a little scenario where you can see the recovery process
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 1 14:20:34 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248624 bytes
Variable Size 96469648 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> create tablespace tb datafile 'c:\tb.df' size 1m;
Tablespace created.
SQL> create user tb identified by tb;
User created.
SQL> grant dba to tb;
Grant succeeded.
SQL> alter user tb default tablespace tb;
User altered.
SQL> create table tb (id number);
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
547292
SQL> drop tablespace tb including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248624 bytes
Variable Size 96469648 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> flashback database to scn 547292;
flashback database to scn 547292
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005
SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'c:\tb.dbf';
Database altered.
SQL> flashback database to scn 547292;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> select * from tb;
no rows selected
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
C:\TB.DBF
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TB
6 rows selected.
SQL>
July 28th, 2009 at 6:59 am
Great! to test with flashback database.
I wish I’ll see this test with tb table (have data).
My curious is “create table tb as select rownum id from dual connect by level<100".
and "select count (*) from tb" after resolved.
I need to make sure data, after flashback database. not just metadata recovered.
Thank you buddy, if you could test+show me.
July 28th, 2009 at 9:22 am
Hi Surachart. I’ve tested it and Oracle was able to return the data as well
C:\Documents and Settings\Administrator>sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jul 28 09:14:50 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248624 bytes
Variable Size 96469648 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> create tablespace tb datafile ‘c:\tb.dbf’ size 1m;
Tablespace created.
SQL> create user tb identified by tb;
User created.
SQL> grant dba to tb;
Grant succeeded.
SQL> alter user tb default tablespace tb;
User altered.
SQL> conn tb/tb
Connected.
SQL> create table tb as select rownum id from dual connect by level select count(1) from tb;
COUNT(1)
———-
99
SQL> select current_scn from v$database;
CURRENT_SCN
———–
547131
SQL> drop tablespace tb including contents and datafiles;
Tablespace dropped.
SQL> select count(1) from tb;
select count(1) from tb
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248624 bytes
Variable Size 96469648 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> flashback database to scn 547131;
flashback database to scn 547131
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown – rename to correct file
ORA-01110: data file 5: ‘C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005’
SQL> alter database create datafile ‘c:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNA
MED00005’ as ‘c:\tb.dbf’;
Database altered.
SQL> flashback database to scn 547131;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> conn tb/tb
Connected.
SQL> select count(1) from tb;
COUNT(1)
———-
99
SQL>
July 28th, 2009 at 9:35 am
thank you…
i cleared about flashback database + lost tablespaces 😉
July 28th, 2009 at 9:48 am
You’re welcome!
July 28th, 2009 at 12:09 pm
Thanx, teacher, as previous posts this post is also very useful in daily work.
July 28th, 2009 at 12:14 pm
You’re welcome Shappy!
August 5th, 2010 at 1:07 am
I tested and works fine!
but only if that command: drop tablespace XX including contents and datafiles;
it doesn’t work if: drop tablespace XX including contents; (without datafiles).
do you have any ideia how to recover a dropped tablespace with that way?
August 5th, 2010 at 3:25 pm
Dear Sandro
I haven’t tested it, so if you test it, let us know
August 12th, 2010 at 12:20 am
I tested, and the result are:
SQL> flashback database to scn 167742;
flashback database to scn 167742
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 4 will be lost if RESETLOGS is done
ORA-01110: data file 4: ‘C:\TB.DBF’
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 4 will be lost if RESETLOGS is done
ORA-01110: data file 4: ‘C:\TB.DBF’
October 13th, 2010 at 6:44 pm
Hi Kamran Agayev,
Really the blog is useful to me thanks for that. regarding the Recovering Dropped tablespace using Flashback Database , i have one doubt.. whether flashback the database to some particular scn will affect the other transactions happend after the scn or not. correct me if i am wrong…
October 13th, 2010 at 7:47 pm
Dear Sakthivel, thank you for the feedback
You can go back to any SCN, open the database in read only mode, get any data you want, go to the latest SCN value and open the database in read write mode “without loosing any transaction”
October 13th, 2010 at 8:11 pm
Thanks for the response!!! Kamran Agayev. In Recovering Dropped tablespace using Flashback Database. after flashback to the particular scn. whether it will affect other non-system tablesapce transaction or not.
EX:
SCN: 547292 – before dropping tablespace..
— transaction happenning on two tablespaces… USERS and tools—-
users tablesapce dropped…………
if i recovered users tablespace to scn 547292.
What will happen to the transactions on Tools tablespace.
Correct me if i am wrong.
Thanks.
Sakthivel
October 14th, 2010 at 7:34 pm
Dear Sakthivel, actually you can try it by yourself, it’s very easy
But as you’re performing flashback “database”, you take the “whole” database to specific point in time, so it will affect all tablespaces you have
April 5th, 2013 at 9:32 pm
[…] Recovering Dropped tablespace using Flashback Database […]
July 19th, 2013 at 2:34 pm
HI team,
I accidentally updates the tables which have data with empty tables. now the tables have got overwritten.
please let me know how can i retrive the data.
I have a schema( which has seperate table space) under which i have all the tables whic are iverwrittrn and empty now.
please provide any hlep oon this/
July 22nd, 2013 at 4:00 am
Hi Dinesh
To get the previous image of your data you should use Flashback Query. However, retrieved data shouldn’t be overwritten from UNDO tablespace