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:

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

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.

Share this article

34 Responses to “Questions you always wanted to ask about Flashback Database…”

André Araujo, can you please clarify about question #2 about turning on Flashback while the database is open? I have just tried this on a 10.2.0.4.1 database, and received the ORA message “ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance”

Indeed that information is not totally accurate. ALTER DATABASE FLASHBACK ON is an online operation on 11g Release 2 (I haven’t tested in 11g Release 1 yet). In 10g it requires the database to be mounted.

@Surachart: Thanks, Surachart. When you have NOLOGGING operations going on on your database you can still use Flashback. However, if you flashback to a point-in-tmie *during* a NOLOGGING operation the blocks affected by the operation will be marked as logically corrupt. If the flashback time is *after* the NOLOGGING operation you’ll see the data *without* the rows affected by the operation. To ensure recoverability you should always backup the database after NOLOGGING operations.

You have done a great job for clarify some of the questions that I have regarding flashback database. I do have another question: Is flashback log a physical file that will be created when we turn on flashback (How do we know which file name for flashback log in OS level)? What is the file format or it is a table that created in Oracle database?

Sorry for this belated response.
Yes, the flashback logs are physical files created by the database. These files, though, are managed by Oracle and have a proprietary format. Even though we can see and list the files in the FRA, we shouldn’t try to manipulate or remove them directly.

I have to disagree about FLASHBACK DATABASE being visible in V$SESSION_LONGOPS. I do this on a regular basis on our 10.2.0.4 databases, and neither the Flashback phase, nor the media recovery phase show up in session_longops.

Following up my previous comment, see MOS/Metalink note [ID 1071391.1] which confirms that in 10g and 11g FLASHBACK DATABASE does not appear in V$SESSION_LONGOPS. The note recommends querying V$DATAFILE_HEADER to trace progress…

yes, I’m sure indeed. You can flash the database back and forward independently of whether there is a resetlogs in between. This is certainly true on 11g; I believe it’s also valid for 10gR2, but I’m not able to test that at the moment.

The “flash forward” is just a roll-forward of the database. You can use either the FLASHBACK DATABASE command or RECOVER DATABASE to advance the database in time.

Do you have a recent reference in the Oracle documentation set ?
Because in Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03, in § 7.5.1.2 “Options After Flashback Database to the Wrong Time”, nothing is mentioned about “flashing forward”.
Regards

Can we have two or more flashback points?
In our production system, we use the flashback. Its java application run on Jboss, we bring down the Jboss and turn on the flashback than we make are DB changes and do the QA testing if everything is fine we just bring up jboss and start the app. Or if something goes wrong restore the last db point.
I want to achieve the same without bring down the Jboss. If we have two flashpoint, in one point I will make new db change and start QA testing meanwhile second point will server the customer. Do we have something to achieve this?

Sandeep, you can have more than one restore point. However, you can’t have the database in two states at the same time. If I understood your question correctly, you want to have two “editions” of the database available at the same time (one for JBoss and one for QA) using restore points, which is not possible.

Hi there!
I am wondering whether it is possible to determine that point is valid. It is known that points are not always may be flashed back, so I want get rid of old useless points.
Do you know how cant it be done?

If we want to flashback our database then we need both flashback and archs ( i have tested ) as flashback not contains all the information that needed to flashback so my question is it possible to change the flashback level so it will keep whole info and without archs we can rollforward and rollbackward the database.

Its very useful and easy to learn the concept. We are going to implement the Flashback Database technology in our Prod environment. Could you please provide the storage metrics, such as how much Disk space required for Flashback logs for 500M size of the DB.