Databases Reference
In-Depth Information
The output shows that you can flash back to at most July 21, 2012, at 5:39:12 p.m. when using the timestamp option.
How It Works
You can flash back the database to any point in the past as long as the required flashback logs are available and as
long as the required archived logs are available. The archived logs can be either online or on backup, but they must be
available.
Information on flashback logs is available on the data dictionary view V$FLASHBACK_DATABASE_LOG.
The dynamic performance view V$FLASHBACK_DATABASE_LOG shows some of the information on flashback
operations. Table
13-1
describes the columns of this view.
Table 13-1.
Columns of V$FLASHBACK_DATABASE_LOG
Column Name
Description
OLDEST_FLASHBACK_SCN
The minimum SCN to which you can flash back the database.
OLDEST_FLASHBACK_TIME
The earliest time to which you can flash back the database.
The initialization parameter
db_flashback_retention_target
determines
how long the flashback logs are retained, in minutes. The same parameter
is shown in this column. See the note after this table for more information.
RETENTION_TARGET
FLASHBACK_SIZE
The size of flashback logs as of now.
ESTIMATED_FLASHBACK_SIZE
This column is a bit more interesting and explained in detail after this table.
CON_ID
The container ID, in case of a pluggable database (only in case of Oracle
Database 12.1 and above). If you don't use a pluggable database, this
column will show the default container ID of 0.
To find the value of the retention target set in the database, you can also issue this SQL:
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
----------------------------- -------- ------
db_flashback_retention_target integer 1440
Note that the database initialization parameter
db_flashback_retention_target
sets the target for the flashback
operation. Since this is set to 1440 in the solution example, the database tries to keep the logs for 1,440 minutes.
The important word here is “tries,” not “guarantees.” The actual number of logs kept depends on the size of the fast
recovery area, which is determined by another database initialization parameter:
db_recovery_file_dest_size
.
When the flashback logs fill up the fast recovery area, the database removes the oldest logs to make room for the new
ones. The age of the oldest logs removed may potentially be less than 1,440 minutes, which is why 1,440 minutes is
merely a target, not a guaranteed value of retention.
So, if the database were to retain the flashback logs for the entire 1,440 minutes, what would the combined size of
those flashback logs be?
The column ESTIMATED_FLASHBACK_SIZE answers the question. In the example shown here, the value of this
column is 20,570,112, or about 20 MB, while the column FLASHBACK_SIZE is 367,001,600, or about 366 MB, much
more than the estimated size. This occurred since the fast recovery area has plenty of space and the older flashback
logs are still retained in the fast recovery area. Normally, on a small fast recovery area and very active database, this
output is reversed—the estimated size is more than the actual size.
