Top 60 Oracle Blogs

Recent comments

How to configure FLASHBACK in #Oracle

flash 600w, 150w" sizes="(max-width: 300px) 100vw, 300px" />

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.


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.


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.


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:


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:



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</p />

    	  	<div class=