Oracle 10g Flashback Database

The Oracle 10g flashback database feature enables a fast "rewind" of the whole database to an earlier point in time.

Oracle 9i introduced the concept of flashback queries which enable users to query a table as it was at an earlier point in time (either by specifying the time or the scn) and this makes use of the undo tablespace.

In Oracle 10g this feature has been extended to the database level but with one important difference - you're not just querying the database at an ealier point in time, by using flashback database you effectively rewind the whole database to the earlier point in time. This makes it useful for resolving wide spread, logical data corruptions or user errors.

It is important to note, though, that Oracle 10g flashback database can't be used to recover from physical corruption of the database nor from the loss of objects in the database (for example a dropped table - you need to use flashback drop for that).

Support for this new feature is provided by three new features in Oracle 10g: flashback buffers in the SGA; the new Oracle background process Recovery Writer (RVWR); and flashback database logs but it also relies on the database running in archive log mode.

This is required because performing a flasback database is equivalent to performing an incomplete recovery - therefore to get a consistent set of data, the database is rolled forward by use of the redo logs from the time of the latest flashback log before the required time to the exact time or scn that you want.

When the flashback database feature is enabled, blocks in the SGA that are about to be "dirtied" (changed) are periodically copied from the database buffer cache to the flashaback buffers.

Oracle automatically determines which blocks are copied and how often as only a subset of changes are copied to the flashback buffers to ensure that the memory and i/o overhead is kept to a minimum. This means that even frequently changed blocks will be logged only infrequently.

RVWR is analagous to the Log Writer (LGWR) background process but instead of writing to the redo logs, it writes out the flashback buffers from the SGA to the flashback database logs.

Flashback database logs contain the before image of changed blocks (redo logs contain the after image). They must reside in the flashback recovery area (which must be configured before enabling flashback logging) and unlike redo logs they are not archived.

As mentioned earlier there are three parts to the flashback database feature but the only aspects under the control of the dba are the size and location of the flashback recovery area which holds the flasback logs and the target retention period for flashback data. Once these are specified, flashback database can be enabled.

This is specified by use of two parameters:
DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST. The size of the area must be specified first.

For example:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH;

ALTER SYSTEM SET  DB_RECOVERY_FILE_DEST='/flashback_recovery_area/' SCOPE=BOTH;

The flash recovery area is also used by default by RMAN for other recovery-related files including archived redo logs so it is important to ensure there is sufficient space for the flashback logs to be retained for the desired length of time.

This determines how many minutes flashback data can be kept before being over written and is specified by the parameter DB_FLASHBACK_RETENTION_TARGET.

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=480 SCOPE=BOTH;

This target (of 8 hours in this case) may not be achievable if the flashback recovery area is under sized.

With the size and location of the recovery area sepecified, flashback recovery can be enabled by use of the following command:
ALTER DATABASE FLASHBACK ON;

The database must be mounted but not open to issue this command and archive logging must be enabled.

You can flashback a database using SQL*Plus, RMAN and Database Control and it involves a 4 step process:

1. Shut down the database
2. Re-mount the database
3. Flashback to the required time, SCN, or log switch sequence number (if using RMAN)
4. Open the datbase with the RESETLOGS option.

There are slight differences in the syntax between RMAN and SQL*Plus but the overall process is the same.

SQL> SHUTDOWN IMMEDIATE
Database closed
Database dismounted
ORACLE instance shut down.
SQL> STARTUP MOUNT
Database mounted.
SQL> select to_char(oldest_flashback_time,
  2  'dd-mon-yyyy hh24:mi')
  3  from v$flashback_database_log;

TO_CHAR(OLDEST_FL
-----------------
17-jun-2009 16:32

SQL> flashback database to timestamp('17-jun-2009 16:42',
  2  'dd-mon-yyyy hh24:mi');

SQL> alter database open read only;

At this point you can query the database to see if you have gone back to the right point in time.

If you haven't gone back far enough you need to shutdown the databse again and flashback to an earlier time.

If you have gone too far back you can roll the database forward by issuing the command
SQL> recover database until time '2009-jun-17 17:25'

Once you are happy with the state of the database issue the following command to open the database for general use.
SQL> alter database open resetlogs;

Refer to the Oracle Backup User Guide for more details of using Oracle 10g Flashback Database.

Take advantage of our consultants' skills honed to a fine edge over many years with myriad clients in a wide variety of industries and use their in-depth knowledge and experience to maximise the benefits and minimise the costs of your Oracle systems.

Contact us to learn how we can help you reduce costs, boost productivity and eliminate downtime

Return from Oracle 10g Flashback Database to Oracle 10g