Kamran Agayev's Oracle Blog

Oracle Certified Master

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>
 

16 Responses to “Recovering Dropped tablespace using Flashback Database”

  1. Surachart Opun Says:

    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.

  2. Kamran Agayev A. Says:

    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>

  3. Surachart Opun Says:

    thank you…
    i cleared about flashback database + lost tablespaces 😉

  4. Kamran Agayev A. Says:

    You’re welcome! :)

  5. Shappy Says:

    Thanx, teacher, as previous posts this post is also very useful in daily work.

  6. Kamran Agayev A. Says:

    You’re welcome Shappy! :)

  7. Sandro Says:

    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?

  8. Kamran Agayev A. Says:

    Dear Sandro

    I haven’t tested it, so if you test it, let us know

  9. Sandro Says:

    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’

  10. SAKTHIVEL Says:

    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…

  11. Kamran Agayev A. Says:

    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”

  12. SAKTHIVEL Says:

    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

  13. Kamran Agayev A. Says:

    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

  14. Recovering Dropped tablespace using Flashback Database « The Oracle,Unix & Security You May Not Know … Says:

    […] Recovering Dropped tablespace using Flashback Database […]

  15. Dinesh Says:

    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/

  16. Kamran Agayev A. Says:

    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

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>