Questions you always wanted to ask about Flashback Database…

Last Friday in the Sydney Oracle Meetup I talked about Oracle Flashback technology and how it helps to reduce downtime. The session generated great interest among the attendees, which led to interesting discussions and many questions about the subject.

Some of the questions couldn’t be answered during the meeting so I’ve followed up on them and I’m posting the answers here since they may be of interest for many others.

Q: Is there a separate background process for writing flashback logs?

A: Yes. RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

Q: Do I need to shutdown and mount the database to turn flashback on?

A: ALTER DATABASE FLASHBACK ON is an online operation in 11g Release 2. In 10g Release 2 the database must be mounted, but not open.

Once flashback is turned on Oracle starts to save the before-images of the database blocks before they are changed. This guarantees that the database can be flashbacked to the exact point-in-time when the flashback mode was turned on.

Q: What happens if RVWR cannot write to disk?

A: It depends on the context where the write error occurs:

• If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.
• If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will continued to operate normally.
• If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.

Q: Is it possible to specify the size of the Flashback Buffer in the SGA?

A: Yes, but indirectly. The size of the Flashback Buffer is set to 2 * LOG_BUFFER.
For performance reasons, it’s recommended to set LOG_BUFFER to at least 8MB for
databases running in Flashback Mode.

UPDATE: For large 11.1.0.7+ databases with more than a 4GB SGA, you may consider setting LOG_BUFFER to values in the range of 32-64 MB.

Q: Can RMAN be used to backup flashback logs?

A: No. Flashback Logs are not backed up. Even if the command BACKUP RECOVERY AREA is used to backup the contents of the FRA to tape only the following file types are backed up: full and incremental backup sets, control file autobackups, datafile copies, and archived redo logs.

Flashback Logs are considered to be transient files and cannot be backed up by RMAN. They are not needed for media recovery.

Q: When are the flashback logs deleted?

A: Flashback logs are managed by Oracle only. Oracle will try to keep as much Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs, for example.

• If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.
• If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.
• If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
• If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
• No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.

Other than that flashback logs are deleted according to the below:

• When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.
• When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.

Q: How to list restore points in RMAN?

A: In RMAN you can use the LIST RESTORE POINT [ALL|restore_point_name] command. If you use a recovery catalog you can use the view RC_RESTORE_POINT in the recovery catalog repository, or the command the V$RESTORE_POINT in the target database..

Q: After flashback’ing to a point-in-time before a RESETLOGS operation is it possible to flash forward to the incarnation after the RESETLOGS?

A: Yes, it’s perfectly possible.

Q: Can you see the progress of a FLASHBACK DATABASE operation?

A: Yes, you can. During a FLASHBACK DATABASE operation you can query V$SESSION_LONGOPS from another session to see the progress of the flashback.

The FLASHBACK DATABASE operation has two distinct phases: the actual flashback and the media recovery that happens afterwards to bring the database to a consistent state.

While the actual flashback is running you’ll see the following message in V$SESSION_LONGOPS, on Oracle 11gR2:

Flashback Database: Flashback Data Applied : 238 out of 282 Megabytes done

During the media recovery, the following messages will be seen:

Media Recovery: Redo Applied : 263 out of 0 Megabytes done
Media Recovery: Average Apply Rate : 1164 out of 0 KB/sec done
Media Recovery: Last Applied Redo : 626540 out of 0 SCN+Time done
Media Recovery: Elapsed Time : 232 out of 0 Seconds done
Media Recovery: Active Time : 116 out of 0 Seconds done
Media Recovery: Active Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Maximum Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Log Files : 15 out of 0 Files done
Media Recovery: Apply Time per Log : 7 out of 0 Seconds done

Q: How should I set the database to improve Flashback performance?

A: Oracle’s recommendations are:

• Use a fast file system for your flash recovery area, preferably without operating system file caching. It is recommended to use a file system that avoids operating system file caching, such as ASM.
• Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
• If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
• For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.