This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Apr 1, 2013 5:56 PM by sunner RSS

How to recover the Dropped Tablespace

616200 Newbie
Currently Being Moderated
Hi,

By mistake I ' ve dropped my 'test' tablespace
its having 250 tables,50 stored procedures etc
but i did a hot backup yesterday night.....
how to recover my tablespace
  • 1. Re: How to recover the Dropped Tablespace
    Justin Cave Oracle ACE
    Currently Being Moderated
    Unless you dropped the SYSTEM tablespace, your tablespace didn't contain stored procedures.

    You'll probably want to do a [tablespace point-in-time recovery|http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/backrec.htm#i1012545] to the point before you dropped the tablespace.

    Justin
  • 2. Re: How to recover the Dropped Tablespace
    NavneetU Expert
    Currently Being Moderated
    Hi,

    1. Restore the backup and recover until time before the time at which u dropped the tablespace. (Incomplete recovery).
    2. If you are having export dump of the tablespace then you can use that dump file and re create the tablespace.
    3. You can make use of TSPITR.

    Regards,
    Navneet
  • 3. Re: How to recover the Dropped Tablespace
    316993 Pro
    Currently Being Moderated
    RMAN> alter tablespace <tablespacename> offline;
    RMAN> restore tablespace <tablespacename>;
    RMAN> recover tablespace  <tablespacename>;
    RMAN> alter tablespace online;
    Khurram
  • 4. Re: How to recover the Dropped Tablespace
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Refer to : http://students.kiv.zcu.cz/doc/oracle/backup.102/b14191/ostspitr002.htm

    Please specify the version of DB. ??
    Does FlashBack is Enable ?? If DB is 10g Version.

    - Pavan Kumar N
  • 5. Re: How to recover the Dropped Tablespace
    OrionNet Guru
    Currently Being Moderated
    Hello,

    If you have backup then you can definitely restore and recover the tablespace using RMAN. Like mentioned in previous post using TSPITR is an another option but somewhat complex but surely work (remember TSPITR has some restrictions - refer to ORacle docs for restrictions but in your case they might not apply but just confirm if you decide to use it)
     sql "alter tablespace test offline";
    restore tablespace test;
    recover tablespace test;
    sql "alter tablespace test online";
    Regards
  • 6. Re: How to recover the Dropped Tablespace
    616200 Newbie
    Currently Being Moderated
    Hi,

    My DB version is ORACLE 10G

    i didnt enable flashback
  • 7. Re: How to recover the Dropped Tablespace
    OrionNet Guru
    Currently Being Moderated
    Hello,

    As you mentioned you got a backup then you should be ok using following
    sql "alter tablespace test offline";
    restore tablespace test;
    recover tablespace test;
    sql "alter tablespace test online";
    Regards
  • 8. Re: How to recover the Dropped Tablespace
    616200 Newbie
    Currently Being Moderated
    Hi,


    When i issue this command "alter tablespace test offline"
    i got this error

    ORA-00959: tablespace 'test ' does not exist
  • 9. Re: How to recover the Dropped Tablespace
    26741 Oracle ACE
    Currently Being Moderated
    As you have actually dropped the tablespace you cannot issue an ALTER TABLESPACE test OFFLINE

    Nor can you issue a RESTORE TABLESPACE or RECOVER TABLESPACE with the current database (ie current controlfile and SYSTEM tablespace).

    If you can afford to revert the whole database :
    a. Restore the controlfile and database backup as of the last backup before the DROP TABLESPACE
    b. If using RMAN, use SET UNTIL for the SCN or Log SEQUENCE before the DROP TABLESPACE
    c. RECOVER DATABASE . If not using RMAN, use RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
    d. OPEN RESETLOGS.
    Your whole database (including other tablespaces !) to the point in time before the DROP.

    If you CANNOT afford to revert the whole database, you would do a TSPITR (Tablespace Point In Time Recovery). Essentially, you Restore and Recover the key portions of the database (SYSTEM, UNDO, SYSAUX and TEST) to an auxiliary instance -- preferably on a seperate server. Then you export the TEST Tablespace (using Transportable Tablespaces) and import it back into your current database.
    See http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit.htm#i1014116
    (Chapter 8 "RMAN Tablespace Point-in-Time Recovery" of the ORacle Database Backup and Recovery Advanced User's Guide).


    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 10. Re: How to recover the Dropped Tablespace
    $phinx19 Newbie
    Currently Being Moderated
    Hi ALL,

    I know this is an old thread but I do not understand why every one has recommended TSPITR even though the TEST tablespace was dropped using
    drop tablespace test including contents and datafiles.
    The above command as per my understanding changes the structure of the database i.e removes the entry of the TEST tablespace from the controlfile, so how one can do a TSPITR on a tablespace which do not have an entry at the database level.

    I have also referred the belowlink:

    http://docs.oracle.com/cd/B13789_01/server.101/b10734/rcmtspit.htm

    Regards,
    Sphinx.
  • 11. Re: How to recover the Dropped Tablespace
    EdStevens Guru
    Currently Being Moderated
    $phinx19 wrote:
    Hi ALL,

    I know this is an old thread but I do not understand why every one has recommended TSPITR even though the TEST tablespace was dropped using
    drop tablespace test including contents and datafiles.
    The above command as per my understanding changes the structure of the database i.e removes the entry of the TEST tablespace from the controlfile, so how one can do a TSPITR on a tablespace which do not have an entry at the database level.

    I have also referred the belowlink:

    http://docs.oracle.com/cd/B13789_01/server.101/b10734/rcmtspit.htm

    Regards,
    Sphinx.
    That was addressed by the reply from user 23738, the last posting to this thread prior to yours.
  • 12. Re: How to recover the Dropped Tablespace
    $phinx19 Newbie
    Currently Being Moderated
    Hi Ed,

    I have read the said user post,wherein he is also talking about doing a TSPITR as an alternative to doing PITR where the entire database will outage.

    The two method that I am aware are:
    1.PITR
    2.Create a clone and do a export and import of the concern tables.

    Where am I misinterpreting?
  • 13. Re: How to recover the Dropped Tablespace
    EdStevens Guru
    Currently Being Moderated
    $phinx19 wrote:
    Hi Ed,

    I have read the said user post,wherein he is also talking about doing a TSPITR as an alternative to doing PITR where the entire database will outage.

    The two method that I am aware are:
    1.PITR
    2.Create a clone and do a export and import of the concern tables.

    Where am I misinterpreting?
    I don't know, because now you are comparing apples and oranges. When you revived this archeological find, you (correctly) pointed out the problem with doing a TSPITR on a database where the TS had actually been dropped. The link I referred you to addressed the issue of dealing wiith a dropped TS. I referred to it because it addressed what appeared to be your concern over the problem of doing a TSPITR.
  • 14. Re: How to recover the Dropped Tablespace
    $phinx19 Newbie
    Currently Being Moderated
    :)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points