Oracle 11.2 restore DB to a specific point

datapumpflashbackoracleoracle-11g-r2rman

I should execute following tasks:
1. test system and during test change Oracle data and Create New/Remove Old datafiles and tablespace.
2. at the end of test rollback to behviour before of point 1.

I'm working on Oracle 11.2 on two different sites: on single instance configuration and another in DataGuard environment.

To reach the scope I think following possibilities:
1) a- execute dump before starting.
b- recreate manually datafiles/tablespaces and import data from dump at the end.

2) a- backup via RMAN all the DB.
b- restore via RMAN the backup at the end.

3) a- create recovery-point.
b- flashback at the end ….???? (I'm not sure about this chance with change on Datafiles and tablespace.

What your opinion on it?
Thanks,
Ste

Best Answer

From the very first lines in the Oracle Documentation about Flashback Database feature ( https://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta023.htm#RCMRF194 ):

This command works by undoing changes made by Oracle Database to the data files that exist when you run the command.

Flashback its mean to undo logical changes in the database. A Backup its a cloned copy of your database in a point in time. So, if you want to reproduce the exactly same environment for any subsequent tests, involving DB changes, you should go for a RMAN backup/restore technique.

Also, for the single instance DB, If it's going to be just a Test environment, you could avoid the creation of Archives and Flashback logs for saving lots of space, and create/restore from cold backups.

Scripting to recreate the DB + dp exp/imp can be another option, but its a waste of time having RMAN.

Related Solutions

Use RMAN to backup and restore data in a certain point in time

There is a feature specifically for that:

 CREATE RESTORE POINT before_test1 GUARANTEE FLASHBACK DATABASE;
 select NAME,SCN,TIME from v$restore_point; 
 exec perform_my_test1;
 SHUTDOWN IMMEDIATE;
 STARTUP MOUNT;
 FLASHBACK DATABASE TO RESTORE POINT before_test1;
 DROP RESTORE POINT before_test1 ;

This is a database's feature, not RMAN's (commands are for sqlplus), see doc for the full setup and disclaimers.

To more precisely answer your question, RMAN can also be used for the task. The general script is:

run { 
  set until scn 123123123 ;
  restore database /* from tag ... */;
  recover database;
}

Don't use bare RESTORE DATABASE FROM TAG because without SET UNTIL it doesn't work as you'd expect in some scenarios.

I wouldn't say any of these two solutions are inherently safe, both have as many pitfalls as any other Oracle's advanced features, so you need to do some research upfront.

RMAN rename datafile created in an archivelog

SET NEWNAME works for RESTORE and SWITCH, but not for RECOVER. Datafiles had been renamed and switched before the recovery started.

There is a flaw in your process though, and the above issue is its side effect:

Restore the control file from the 3/9 backup.

Why? You do a nearly complete recovery to get as close as possible to the current state, not the state on 3/9. Restore the controlfile from the latest backup taken on 3/14.

Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each

This step is unnecessary if you use the latest controlfile.

Best Answer

Related Solutions

Use RMAN to backup and restore data in a certain point in time

RMAN rename datafile created in an archivelog

Related Question