Oracle Database Discussions

My development team would like to use Oracle Flashback database as a tool to help us with development and testing. The idea is to set a restore point when our test cases have been set-up in the database, run through the tests, look at results, and flashback to the restore point so that we can repeat the tests as needed without having to reconfigure our test cases. We want to use Flashback instead of a normal backup for performance reasons.

Disclaimer - I am an application developer, not a DBA. I've recently spent a good bit of time learning the basics of RMAN and Flashback and have a general understanding of the purpose of redo logs, archived redo logs, flashback logs, backup files, control files, and database files, but I am, by no means, an Oracle expert. I have configured and have been using Flashback on my local machine's database without issue, but the other databases we want to use this on are much bigger and will have a much higher volume of transactions.

This is the general configuration that I have performed:

• Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
• set db_recovery_file_dest_size and db_recovery_file_dest
• set log_archive_dest_1 with the 'mandatory' option
• turn on archivelog mode
• setup auto backups of control files via RMAN (in lieu of a recovery catalog)
• configure backup maxpiecesize, parallelism, and compression via RMAN
• set db_flashback_retention_target (although we plan on using guaranteed restore points, so this will be irrelevant)
• turn on flashback
• create backup via RMAN (with database not open, so archived redo logs are not necessary)

A few questions I had along the way:

1. First and foremost, is this completely stupid for any reason? Are there other utilities/technologies that are cheap and easily implemented that would better suit our needs?
2. Are there any obvious configuration steps that I missed?
3. I've been put in charge of keeping track of our restore points and making sure that we don't overflow our flash recovery area. Is there anyway that I could create something that will monitor the flash recovery area and alert me when it reaches a certain level? I was thinking a trigger of some sort, but I'm not sure which objects/events to build the trigger on. I know I can look at v$recovery_file_dest to see usage stats, but I'd like something more automated than manually running a query.
4. Can we delete archived redo logs IF we ONLY do backups when the database isn't open (so the redo logs don't need to be used) AND we are okay with only restoring to the point when the backup was performed? Remember that the important thing for us is that our test cases are backed up and can be restored, and that the backup will only be used when we are unable to flashback for some reason. In my local testing, I only needed the backup when I intentionally ran out of flash_recovery_area (just to see what would happen).
5. What happens to the flashback logs after a flashback is successfully completed? Do the "future" flashback logs (i.e. a higher SCN than what we just flashed back to) get deleted? Do they stay on disk, but become reusable? Do they need to be manually deleted?
6. Any other tips or things to look out for would be greatly appreciated.

Thanks ahead of time for any help you all are willing to provide. Please let me know if I need to provide any more details.