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 DATABASEare 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_BUFFERto be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.
43 Comments. Leave new
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é
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 ;)
Surachart: You can also enable FORCE LOGGING for your database, if that’s appropriate.
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
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
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.
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.
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
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
Hi, Gilles,
Your observation about the documentation is correct. In fact, Oracle doesn’t mention that FLASHBACK DATABASE can be used to roll the database forward.
My full answer to this was getting a bit too long so I posted it as a new post instead. Please have a look at https://www.pythian.com/news/35399/flashback-and-forth/
Thanks for you feedback!
Regards,
Andre
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
This info is very useful
Thanks for your feedback, JP.
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.
You might want to have a look at Oracle’s Edition-based Redefinition (https://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_editions.htm).
This might be what you’re looking for.
Regards,
Andre
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.
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?
Are there any specific recommendations for flashback area sizing?
You can set the flash recovery area using db_trecovery_file_dest_size
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
Can you tell in which of the listed modes , database flash back can be performed
rman ,
sqlplus ,
Database Control ?
can anyone answer why do RMAN does not back up redo log files??please do answer
thank you
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!
Hello,
Information here is very worthy and useful.
I have a question here. What happen when defined Flashback area goes 100%. My Oracle version is 11.1.0.7.0.
Thanks
Also I am not using Guaranteed Restore points.
Is FULL RMAN Backup is possible if database is open (Read-Write) in Flashback
I have 3 GRP, A, B, C. I want to Flash Back to Restore point B. Will i be able to Flash back to GRP A from B. I see the comment that Flash forward is possible. Kindly suggest.
Hello sir, Thanks for such a great post,
I have one doubt..Why flash back need archive logs
Hi
My question is:
We are using flashback database which creates flashback logs.
These logs go into the ASM FRA . That means the the archive logs and Flashback logs both fill up the disk group. The question I have is there a way of having the Flashback logs go to a different disk group automatically then the archive logs. We do monitoring but you don’t know if the FRA is filling up with Flashback logs or Archive logs.
what is major difference between “recovery using archivelog” and “flashback query like database or any table”.
In V$FLASHBACK_DATABASE_LOGFILE view i Could see below Log type:
I am confused with this.
NORMAL==>In my Output(not pasted here) i could see most of them are Normal but few showing as Reserved
RESERVED
FREE
TO DELETE
can i recover flashback logs in case of dirty shutdown i.e:
i have a database with flashback enabled and guaranteed restore point, but unfortunately i had to shut down db in abort mode so after starting DB it couldn’t be opened with error: failed to turn on flashback database
so i found the last resort to open db is to drop guaranteed restore point and it works, but sometimes those restore points are important in that case can i do a work around or recover flashbak logs to open db without the need to drop restore points ??
thanks in advance
How do I check historical flashback generation and what flashback logs were deleted?
In our environment, Flashback is enabled in standby. I just want to make sure that flashback is not doing anything unusual.