How to Enable Oracle 12c Flashback Database

By: Michael New, Edward Whalen, Matthew Burke


With Flashback Database, you can rewind the database to a previous point in time without media recovery (that is, without restoring backup copies of the datafiles). When the database is running, Flashback Database buffers and writes before-images of data blocks to flashback logs, which are generated by default for all permanent tablespaces and which reside in the fast recovery area (FRA).
Flashback logs allow Flashback Database to reduce the time to correct an error proportionally to the time it takes to detect the error, rather than to media recovery time (when Flashback Database is not enabled), which depends on database size.
To enable Flashback Database, do the following:
1.   Shut down the database and start up the database in mount mode in SQL*Plus:
p127-02
2.   Confirm that the database is in ARCHIVELOG mode, which is required for Flashback Database, and enable ARCHIVELOG mode if needed.
p127-03
3.   Set the flashback retention target to the desired value (in minutes). The following example value is for a 48-hour flashback target (as recommended earlier):
p127-04
4.   Set or change the relevant FRA parameters for Flashback Database, as noted in the following example (these commands assume the FRA disk group is named +FRA and that you are adding 20GB to the FRA size suggested earlier, which is more than enough to accommodate the flashback logs that will be generated):
p127-05
For a RAC database, you must locate the FRA on ASM or a clustered file system.
NOTE
If DB_RECOVERY_FILE_DEST is previously unset and you want to dynamically set it, you must first set DB_RECOVERY_FILE_DEST_SIZE. (This constraint makes sense if you think about it. The way to remember which parameter you must set first is to remind yourself that Oracle cannot allocate a FRA on disk until it knows how large to make the FRA.)
If these parameters are already set, increase the FRA size to allocate space for flashback logs. The volume and rate of flashback log generation is approximately the same order of magnitude as those of redo log generation.
5.   Set the associated undo retention, required for certain flashback features. Here, we set a 24-hour undo retention (in seconds), equivalent to half the DB_FLASHBACK_RETENTION_TARGET, as in Step 3:
p128-01
6.   Finally, enable Flashback Database and FORCE LOGGING while the database is mounted, then open the database:
p128-02

 

Comments

Leave a Reply