Oracle isn’t restoring correctly a backup

backuporacleoracle-11goracle-sql-developerrestore

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

Best Answer

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.

Related Question