Questions you always wanted to ask about Flashback Database…

Nov 6, 2009 / By André Araújo

Tags: ,

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.

32 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”

  • Hi, Michael,

    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.

    I’ll rectify the post soon. Thanks for your feedback.

    Cheers,
    André

    • Ashu says:

      hello..

      my question,why In real time company not preferred flashback recovery area concept ,Is it true Or False.

      Regards
      Ashu

  • Hi André Araujo,

    Thank you for good stuff.
    What happen, On flashback database with tablespace (nologging) state?

  • Thank you for this helpful article.

  • @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.

    @Emre: Thanks for your feeback, Emre.

  • [...] Araujo-Questions you always wanted to ask about Flashback Database… Leave a [...]

  • Hi André Araujo,

    Thank You for your reply… Good way, I think I should avoid NOLOGGING ;)

  • Fung-Chiao says:

    Hi André Araujo,

    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?

    Thanks,

    Fung-Chiao

    • Hi, Fung-Chiao,

      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.

      Regards,
      Andre

  • Peter Moore says:

    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.

    See below for paste from two concurrent sessions:

    1. rman target /

    Recovery Manager: Release 10.2.0.4.0 – Production on Mon May 2 11:32:22 2011

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    connected to target database: PRJ001D (DBID=1045557503, not open)

    RMAN> flashback database to restore point PR2011_PRE_TIMERAVEL;

    Starting flashback at 02-MAY-11
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=1980 devtype=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: sid=1979 devtype=DISK
    allocated channel: ORA_DISK_3
    channel ORA_DISK_3: sid=1978 devtype=DISK
    allocated channel: ORA_DISK_4
    channel ORA_DISK_4: sid=1977 devtype=DISK

    starting media recovery

    This has been running for several hours.

    On another session:

    sqlplus “/ as sysdba”

    SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 2 16:46:36 2011

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    16:46:36 SYS @ PRJ001D1 AS SYSDBA> select * from v$session_longops;

    no rows selected

    Elapsed: 00:00:00.01

  • Peter Moore says:

    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…

    • Thanks for the comments, Peter.
      I had verified V$SESSION_LONGOPS on 11gR2 and assumed that it was the case for previous versions as well.

      I’ve rectified the post to note this is true in 11gR2.

  • Ed says:

    Hi,

    Is there a way to check what session/process has generated the flashback log?

    • Hi, Ed,

      the flashback logs are not session specific. Oracle can write flashback information for several sessions to each of the logs. They are generated in a sequential fashion, same as the archived logs.

      I’m not aware of any tools to do mining on flashback logs, like we can do for archived logs with the Log Miner.

      What do you need this information for? What are you trying to achieve?

      Thanks
      Andre
      PS: I’ve re-read this post after a long time and found a few mistakes, and also noticed some old comments and I missed to answer. I’ll fix this soon.

  • gilles says:

    André,
    are you really sure you can “flash forward”
    either with or without resetlogs in between ?

    • Hi, Gilles,

      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.

      Cheers,
      Andre

      • gilles says:

        André,

        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

  • Susanne says:

    Do you know what happens on a physical standby if I enable flashback database on the primary? Meaning: Do I need a fra on the standby site, too?

    Best regards,
    Susanne

  • jp says:

    This info is very useful

  • Sandeep says:

    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?

  • Ed says:

    Another question please if you don’t mind.

    My retention is set to 240 = 4 hours, yet I have FLASHBACK LOGS on my physical standby as far baack as 2 years ago? Why? I don’t have any restore points, guaranteed or otherwise.

    I’ve seen this problem before but can’t replicate it. To “force’ a deletion I change/re-instate the retention to trigger some sort of deletion.

    Anyway, maybe you can shed some light on it.

  • Andrey Polyakov says:

    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?

  • Sonia Singh says:

    Are there any specific recommendations for flashback area sizing?

  • Vivek says:

    Hi Andrea,

    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.

    Thanks
    Vivek

  • Guhan says:

    Can you tell in which of the listed modes , database flash back can be performed
    rman ,
    sqlplus ,
    Database Control ?

  • JYOTI MANCHANDA says:

    can anyone answer why do RMAN does not back up redo log files??please do answer
    thank you

  • Siva says:

    Hi Andre,

    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.

    Thanks!

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>