As a general rule, configure the FRA and define your local archiving parameters to be:

LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST’ 

As part of best practices, you should enable Flashback Database on primary and standby databases for easy re-instantiation after a failover. With Flashback Database enabled, you do not have to rebuild the primary database after a database failover. You can re-instate the failed primary database. Also Flashback Database provides the mechanism to expeditiously rewind the database after an erroneous batch update, a bad data load, user error(s), or a malicious set of activities on the database.

Oracle MAA recommends that DB_FLASHBACK_RETENTION_TARGET should be set to a minimum of 60 minutes if all you are trying to achieve re-instantiation of the primary database after a failover; however, if you require the additional protection from user errors and corruptions, then you will need to extend that time. Oracle MAA best practices recommend a minimum of 6 hours retention period. You need to determine what that retention period is based on your business requirements. The longer you set the flashback retention time, more disk space you will need.

You should also enable Flashback Database on the standby database to minimize downtime resulting from logical corruptions.

It looks like as of Oracle Database 11g Release 2, we can enable flashback database while the database is online.  For some DBAs who do not want to turn on flashback database, this can provide them a little more flexibility for enabling or disabling flashback for critical database operations or in a controlled scenario.  You can now turn on Flashback Logging (alter database flashback on;) prior to a critical operation and turn it off (alter database flashback off;) afterwards without a database outage.  For DBAs who have to deal with limited space for the FRA and have to disable flashback logging from time to time due to limited space and uncontrolled batch jobs, we can now re-enable it when crisis situations clear up.

Posted by Charles Kim, Oracle ACE Director


Leave a Reply

Your email address will not be published. Required fields are marked *

*