Oracle rman simple backup on 11g

Question

This question is almost exaclty like oracle rman simple backup

but there isn't an acceptable answer there, and this question is about 11g. So I'll ask:

I'd like to do some table initialization DDL tests on an oracle shema, and I'd like to revert the database to the prior-test state between runs. I'm executing the following in RMAN:

shutdown immediate;
startup mount
backup database;
sql 'alter database open';

As I see it works fine, list backup shows backups.

Than I made some modifications (Added some users, added some tables, adding data) and I tried to restore backup:

shutdown immediate;
startup mount
restore database;
recover database;
sql 'alter database open resetlogs';

Expected result: the database should be restored to the exact state as to when the initial backup was taken.

Actual result: all the new tables and users I created in my test DDL continue to exist. I verified this by closing connections, restarting sessions, and then even selecting from the tables! The tables still exist even after the restore!

What is the deal with this? In MSSQL and Postgres, a backup means you save the state of the db, and restoring it means you go back to when the backup was. But in RMAN for oracle 11g, it 'claims' the restore was successful, but the evidence clearly shows otherwise.

How can I get oracle to save the state of the database exactly as it is, and then make changes, and when I restore, i want the database to be exactly as it was when I backed it up?

Is this possible in Oracle?

All right, so this is interisting: after saying 'screw it, i'll roll back manually', I went through a lot of db changes (35 tables added) and thought 'heck, i'll just try to restore the db', and LO: the user still existed, but all the tables that the deployment DDL created were gone. So, the only real question is why are users kept around but the data is not? — Chris Knoll
GOD! I don't know what's happening now, but now it does turn out that the user was blown away. Really strange that i was able to navigate to the user and not see tables, but then trying to connect as the user gave a login failed message, and then the user disappeard (thus the database is now back in it's origional form). This is very very strange... — Chris Knoll

Sergiu Velescu Sergiu Velescu · Accepted Answer · 2015-12-09T09:04:52

Yes it is possible - you have several options:

create a cold backup of the database (datafiles, controlfiles, online redo logs) and then to restore them when it is necessary
Perform so called "point in time recovery" (assuming your DB is in archivelog mode). Take DB backup with RMAN note the "time" or "SCN" or "archivelog sequence" after a while you can restore DB and recover until previous noted time/SCN/LOG SEQUENCE
Special designed by Oracle for this purposes and I recommend it in your case "Flashback Database" (brows Oracle docs to see what this is).

Oracle always "try" to restore/recover your database up to last committed transaction if it is possible, that is why you get the result you described above, but if you want to restore up to specific time/SCN/SEQUENCE just tell Oracle about this :)

Oracle rman simple backup on 11g

1 Answers