1 2 Previous Next 15 Replies Latest reply: Apr 1, 2013 7:56 PM by sunner RSS

    How to recover the Dropped Tablespace

    616200
      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
          JustinCave
          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
            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
              orawarebyte
              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
                Pavan Kumar
                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
                  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
                    Hi,

                    My DB version is ORACLE 10G

                    i didnt enable flashback
                    • 7. Re: How to recover the Dropped Tablespace
                      OrionNet
                      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
                        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
                          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
                            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
                              $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
                                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
                                  $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.
                                  1 2 Previous Next