It’s easy to get confused by the various different forms of FLASHBACK possible with Oracle Databases. Let’s try to sort that out. I start with forms of FLASHBACK that work with little effort to the system and not much – if anything – to do for the DBA to enable it.
FLASHBACK TABLE TO BEFORE DROP
This is on per default since version 10g and configured with the initialization parameter RECYCLEBIN=ON. Set this parameter to OFF if you want the old behavior. The effect of RECYCLEBIN=ON is that tables are not removed from the Data Dictionary upon DROP TABLE commands. Instead, they just get another name and are marked as deleted. As long as other segments do not need the space that is consumed by the marked table, it can be re-renamed after the original name. See details here.
FLASHBACK QUERY
Possible since 9i, this feature makes use of the undo information that gets created upon every DML statement in case it needs to be rolled back. The initialization parameter UNDO_RETENTION can be used to make it reach further into the past. Otherwise, there is no effort for the system to enable it at all. See details here and an example here.
FLASHBACK TABLE TO TIMESTAMP
Introduced in 10g, it has the same foundation as FLASHBACK QUERY: works using undo information. Additionally, you need to enable it on the table layer with ALTER TABLE ENABLE ROW MOVEMENT. See details here.
The FLASHBACK features above are all enabled by default and require little effort by the system. That’s different for the next two:
FLASHBACK ARCHIVE
Since 11g, that feature enables FLASHBACK QUERY for certain tables for as long as you like. The undo information of those tables is preserved in internal tables (the flashback archives). It is some effort involved to store the data in the flashback archive and it consumes space. The more space the longer you want to keep that undo data. See details here and an example here. Notice that this feature is since 12c included in all editions:
FLASHBACK DATABASE
Introduced in 10g, that is the only form of Flashback that is affected by what you see in the column FLASHBACK_ON in V$DATABASE. It enables you to do a fast Database Point In Time Recovery by storing old versions of Oracle Blocks periodically into the Flashback Logs. You need to configure that on the Database Layer with ALTER DATABASE FLASHBACK ON – it is OFF by default. DB_RECOVERY_FILE_DEST has to be set to point to where the Flashback Logs are to be stored, while DB_RECOVERY_FILE_DEST_SIZE sets an upper limit for the space this database is allowed to consume in the Recovery Area. The Recovery Area must be used for Flashback Logs and is recommended for Backups and Archived Logs as well. See an example here.
The initialization parameter DB_FLASHBACK_RETENTION_TARGET specifies how long you wish to be able to do FLASHBACK DATABASE into the past. Default is 1440 Minutes which is one day. Again, this causes some effort for the system to write the Flashback Logs and requires space to store them. See details here.
So the various Flashback forms use quite different techniques underneath – they have just the term in common and that logical errors are being addressed to some degree. Hope this article helps to clarify some misconceptions that I observe in the Oracle community from time to time 🙂
Pickleball spielen 
#1 von Noons am Dezember 14, 2016 - 12:42
Thanks for this, always useful.
One thing that needs to be stressed on this feature is that it is not free!
It requires disk space (not easy to calculate how much, in advance..) and causes a heavy performance hit on a query that returns a lot of data or when the tables involved are very large.
Again, not easy to quantify in advance how much.
Unfortunately, an area that has never received the full attention it deserves.
I lost count of the folks that come to me asking why is the flashback query of some of our DW tables used in the ET portion of ETL not as fast as querying the non-flashback data.
Really?
So what happens when a multi-partition T table gets „preserved“ in the flashback log area?
Same query speed? I don’t think so…
Would be nice if there was some mechanism in OEM to easily and clearly show this and the impact it may have.
#2 von Uwe Hesse am Dezember 14, 2016 - 12:56
Noons, talking about Flashback Query, the SELECT performance is determined by the amount of data we need to reconstruct from undo in the very same way like with an ordinary consistent read that hits data that has been modified after the SELECT started. Only that this time is specified explicitly into the past with the AS OF TIMESTAMP clause in case of a Flashback Query. The more has changed since that timestamp the more effort the reconstruction takes.
This works without Flashback Logs, to be clear.
It may work together with Flashback Archives, though. Without Flashback Archive being configured, we just try to find that old data in the undo tablespace and return an error if it can’t be found.