One of the
more interesting Oracle 10g new features in the area
of backup and recovery (more specifically human error correction) is
the flashback database feature.
This is not
to be confused with the flashback query technology introduced with
Oracle 9i although the concept is similar.
Rather than just enabling
you to query a table as it was in the past, this new feature allows you
for which flashback database is ideal are those which would otherwise
call for a
tablespace point-in-time recovery such as accidentally
dropping a whole
schema or tablespace or logical database corruption due to loading the
wrong data. However it can't be used to recover from physical data
corruption or media failure.
One of the main benefits of this new feature is that the
whole process is automated so you no longer need to worry about taking
ad-hoc backups before a major update or data load or where you stored
the files or which versions to use. All this is taken care of by Oracle.
You might be thinking "This sounds great ... but where's the catch?"
You're right, there is a catch which could be big or small depending on
The catch is that there is a new background process Recovery Writer
(RVWR) which periodically writes changed blocks to flashback logs on
disk. This will obviously take cpu, memory, i/o and disk
resources as these new logs are additional to the normal redo logs.
Fortunately, not every change to every
block results in the block being saved to a flashback log, so
the performance hit is minimised.
Flashback Database Architecture
Let's have a look at how this works.
First the prerequisites:-
- the flashback area must have been defined using
db_recovery_file_dest and db_recovery_file_dest_size
- the flashback area must have been created on
- the database must be running in archivelog mode
- flashback database must be enabled
When flashback database is enabled, the before images of changed blocks
are written to the flashback buffers in the SGA before being written to
the flashback logs on disk by RVWR.
Recovery Using Flashback Database
To recover from the loss of data, you can either use RMAN or Database
Control or issue SQL commands. In SQL it's as easy as issuing the
database to timestamp (sysdate-1/12);
which will rewind the whole database to its state 2 hours previously.
N.B. the database must be mounted but not open when this command is
You can then open the database read only and check that your data is
there. If not you can close the database and flashback to an earlier
time or if it is there you can flashback to a later time to try to
recover more data. Once you're happy with the state of the database you
can open it for normal use with the RESETLOGS
More information on this aspect of Oracle 10g new features can be found
in the Oracle 10g backup and recovery guide