Oracle 11.2 restore DB to a specific point

Question

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

Answer 1

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.