First of all I'll say I'm using:

  • Oracle Database 11g Express Edition
  • Oracle SQL developer

What I'm trying to achieve

Create a backup and restore it, so for example I can delete a table after a backup and then restore the backup to recover that table.

What I tried step by step

Step 1

First I entered to RMAN and used the following statements

connect target;
shutdown immediate;
startup mount;
configure controlfile autobackup on;
backup database;

enter image description here

So for now I'm connected to the database and it's only mounted, and the backup has finished correctly. You have above what I got after executing the backup.

Step 2

Now I execute startup dba; because I want to initiate the database, so I can connect with Oracle SQL Developer and for example delete a table. enter image description here

As you can see I don't have now tables because I deleted the only I had (before the backup I had the table "regions" of the HR schema so that's inside the backup I guess).
After all of this I close the connections in sql developer, because I don't know if it's going to create errors, but I want to avoid them.

Step 3

Now I want to start trying recovering the backup and I execute this because I read that both for backing up and restoring, you must leave the database in the "mount state", so I go to RMAN and execute only this now:

shutdown immediate;
startup mount;

Step 4

And now in this step the real recovery, I execute this:

restore database;

enter image description here

recover database;

enter image description here

Now I should execute the following but it's returning this error:

alter database open;

enter image description here

So, I execute this instead:

alter database open resetlogs;

enter image description here

Step 5

Now that all is done and opened, I'll check if the table is recovered in sql developer. enter image description here

And no, it's not there.

I hope all the info was clear

  • From what I can see the table is not there since before the backup, Otherwise the table would be there when you finished the restore. Oracle does not exclude include tables just because it wants, it will backup everything currently in the database. Try to create the table again, insert some data, commit, execute the backup then drop the table and restore the database. Also you don't need to stop the DB to start the backup unless you're not in archivelog mode.
    – dbamex
    May 27, 2019 at 21:14
  • You probably forgot to remove the log/archive log file(s) that included the drop table command. May 27, 2019 at 21:19
  • Now I tried a commit in the moment I created the table with the data before the backup, and another commit after I created the backup and deleted the table, so I could be sure there was no data left. But it's happening the same, I see that is not a problem of commits maybe @dbamex May 27, 2019 at 22:26
  • @Balazs Papp provided the correct answer, but let me point out that the commits did not enter into it at all. In fact all DDL (which includes CREATE and DROP) carries an implied COMMIT. Explicit COMMIT or ROLLBACK only applies to DML (INSERT, UPDATE, DELETE).
    – EdStevens
    May 27, 2019 at 23:08

1 Answer 1


Based on the above output, you never had the table.

But let's assume you had the table, then created the backup, then dropped the table.

You restored the database with restore database.

Then you recovered the database to the most recent point with recover database.

So you restored the database, and it had the table. Then you recovered the database using the redo log, which contained the change that dropped the table, so the table was dropped again. It is no wonder you can not see the table after opening the database.

If you took the backup at 2019-05-27 22:00:00, then dropped the table at 2019-05-27 22:10:00, and you want to restore the database to a point of time when it still had the table, you need to perform an incomplete recovery.

So instead of recover database;, specify a point of time where you still had the table, for example: recover database until time "to_date('2019-05-27 22:05:00', 'YYYY-MM-DD HH24:MI:SS')";.

Oracle did exactly what you asked from it.

  • What I don't understand is, when I execute the backup database and I know i have the table, why I have problems with different versions in different times? If I execute a backup, shouldn't I have only 1 backup? So If I restore that, why the table is not there? Yes, I have read your answer but I don't really understand what's the difference between a restore and a recover, so I don't know what's happening with those different "versions" of the backup May 28, 2019 at 10:31
  • There are not 'versions' of a backup. Backing up a multi-user relational database is far, far more complex than backing up, say, a simple text file. An rman backup is a block-by-block deconstruction of the many data files that comprise the database. When you 'restore' a database, rman is simply reconstructing the data files from the blocks that were copied a the time of the backup. But when this is complete, the files are still not in a consistent state, so must be 'recovered' to apply all db changes to a specified point in time.
    – EdStevens
    May 28, 2019 at 15:12
  • When you execute "recover database" without any time, it will try to recover database to the latest point in time possible. As a test, before drop the table , get the time , and then recover database with the Command @balazs Papp mentioned, it should have the DB.
    – dbamex
    May 28, 2019 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.