7 Replies Latest reply: Sep 12, 2013 6:23 AM by DK2010 RSS

    Can not recover table from rman backup


      For testing I want to recover deleted table from rman backup.

      I have make vm of oracle 11g.  the following step i have applied but could not recover the table.


      rman> backup database ;


      On sqlplus create login as scott/tiger


      create table scott.emp1(col1 varchar2(10));


      insert into emp1(col1) value('Yasir ') ;

      commit ;


      select * from emp1 ;


      it shows me one row successfully.


      Now i have login the  sys as sysdba


      sqlplus> alter system switch logfile ;


      after this i have drop the table .

      Now to recover the table i have restored database on same vm.


      rman>restore database ;


      rman> recover database ;


      rman> alter database open ;



      on sqlplus i have issued

      sqlplus> select * from scott.emp1 ;


      table or view does not exist.



      any help how to recover the deleted table from rman backup

        • 1. Re: Can not recover table from rman backup



          You created the table after the backup. you can do as follows

          1. create table

          2. backup database

          3. drop table

          4.restore and recover database.

          • 2. Re: Can not recover table from rman backup

            still you will not be able to get the deleted table back.


            As you recover the database the table will get drop.


            So what you need to do after the restore. Open the database in resetlog. 

            • 3. Re: Can not recover table from rman backup

              but what will happen if i have taken backup at night and table dropped at day time and i have also archive logs.

              • 4. Re: Can not recover table from rman backup

                i have create table

                take back up

                drop table

                restore database

                recover database

                alter database open ;


                sqlplus>connect scott/tiger ;

                sqlplus> select * from scott.emp1 ;


                Ora:00942 table or view does not exist



                i also try to


                alter database open resetlogs   but it give following error


                ORA-01139: RESETLOGS option only valid after an incomplete database recovery

                • 5. Re: Can not recover table from rman backup



                  rman>restore database ;

                  rman> recover database ;

                  It will recover database till date/time if your db is in Archive log mode...

                  in your case you created that table and the then drop.. so at the end you have no table..

                  If you want to check some scenario then you can  use point in time recovery . Recover database until time.. and you will get the desired result.

                  or use the same what i said in my previous reply.


                  Hope this help

                  • 6. Re: Can not recover table from rman backup

                    what you have done is complete recovery. So the database opened without resetlog.


                    You before query.


                    If your backup is of night and your table droped in the morning then you can do as below,


                    1. restore database.

                    2. recover database until (cancel/scn/time). if you know the scn number before the table was dropped or time when it was dropped.

                    3. alter database open resetlogs.  As it is an incomplete recovery.

                    • 7. Re: Can not recover table from rman backup



                      Check this test case




                      SQL> create table TEST2 as select * from emp;



                      Table created.



                      SQL> select count(*) from TEST2;








                      SQL> select TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') from dual;





                      10-SEP-2013 10:57:05



                      SQL> alter system switch logfile;



                      System altered.



                      SQL> drop table TEST2 purge;



                      Table dropped.



                      SQL> shu immediate



                      [oracle@vnode MYSHELL]$ rman target /



                      Recovery Manager: Release - Production on Tue Sep 10 10:58:39 2013



                      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.



                      connected to target database: DDTEST (DBID=1667776968, not open)



                      RMAN> run{

                      set until time "to_date('2013 Sep 10 10:57','yyyy mon dd hh24:mi')";

                      restore database;

                      recover database;

                      alter database open resetlogs;

                      }2> 3> 4> 5> 6>



                      media recovery complete, elapsed time: 00:00:02

                      Finished recover at 10-SEP-13

                      database opened



                      RMAN> exit



                      SQL> set lin 200 pages 100

                      SQL> select count(*) from test2;