4 Replies Latest reply: Apr 11, 2013 9:16 AM by KR10822864 RSS

    Recover a dropped table

    $phinx19
      Hi All,

      OS: AIX
      DB: 11G

      I need to recover a dropped table, I have a RMAN backup of midnight and the table was dropped at 7:00 PM today. I do not have flash back enabled on the database. I am thinking of restoring mifnight backup adn applying the archives just before the table was dropped and opening the database in resetlogs method.

      Is there any other way?

      Regards,
      Sphinx
        • 1. Re: Recover a dropped table
          JustinCave
          What, exactly, do you mean when you say " I do not have flash back enabled on the database"? Are you talking about flashback logging? Because that is not relevant here. Are you saying that you disabled the recycle bin?

          Unless you proactively disabled the recycle bin, you should be able to
          FLASHBACK TABLE table_name TO BEFORE DROP
          which will recover the table from the recycle bin.

          Justin
          • 2. Re: Recover a dropped table
            $phinx19
            Hi Justin,

            Thanks.

            I meant the recycle bin is not of any use as the table was dropped with a purge option. Hence, FB is of no use here.
            That is why, I am going for Rman.

            Regards,
            Sphinx.
            • 3. Re: Recover a dropped table
              Girish Sharma
              Recover a Dropped or Deleted Table Using RMAN Backup

              http://oracleinstance.blogspot.in/2010/07/recover-dropped-or-deleted-table-using.html

              Regards
              Girish Sharma
              • 4. Re: Recover a dropped table
                KR10822864
                To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.
                Once the table export is complete, the table can be imported into the primary database. This dummy database can be a subset of the primary database.

                please refer below MOS note for more info.

                How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN [ID 223543.1]