Flashback Database

Objects:
Determine which flashback technology to use for each recovery situation.
Configure and use Flashback Database.
Monitor the Flashback Database.
Use the Enterprise Manager to manage the Flashback Database.
Manage or (maintain) the Flash Recovery Area.

--------------------------------------------------------

An Overview of Flashback Technologies

Flashback Database
Flashback Drop
Flashback Versions Query
Flashback Transaction Query
Flashback Table

All the flashback technologies, with the exception of the Flashback Database, are based on undo data.
The Flashback Database is not based on undo data but on flashback logs.
The Flashback Database is best suited to recover from errors such as truncating a large table, an incomplete batch job, or a dropped user.

------------------------------------------------

Flashback Database

The Flashback Database relies on “before” images in the flashback logs, but traditional flashback technology relies on the undo data.

The Flashback Database isa nice substitute for incomplete recoveryfor logical corruption and user errors.

some limitations:
1.Media failurecannot be resolved with the Flashback Database. You will still need to restore datafiles and recover archived redo logs to recover from media failure.
2.Resizing datafilesto a smaller size, also called shrinking datafiles, cannot be undone with the Flashback Database.
3. You cannot use the Flashback Database if thecontrol file has been restored or recreated.
4.Dropping a tablespaceand recovery throughRESETLOGScannot be performed.
5. You cannot flash back the database to an SCN prior to the earliest available SCN in the flashback logs.

Managing the Flash Recovery Area with Recovery Manager

Flash recovery providesa couple of key benefitsto the recovery process:
1. All recovery files are stored in centralized location.
2. It provides a much faster method of backing up and restoring because the information is written to disk as opposed to tape.

Theretention period of the filesin the flash recovery area are determined by the Recovery Manager (RMAN) retention policy, which is defined by the RMAN CONFIGURE RETENTION POLICY command.

Configuring the Flash Recovery Area

1. Add the following database parameter for the size of the flash recovery area to the init.ora and/or SPFILE:
SQL> alter system set db_recovery_file_dest_size=10M scope=both;

2. Add the following database parameter for the location of the flash recovery area:
SQL> alter system set db_recovery_file_dest='C:\oracle\flash_recovery_area\ora101t';

To disable the flash recovery area, use this command:
SQL> alter system set db_recovery_file_dest='';

Using the Flash Recovery Area

Dynamic viewV$RECOVERY_FILE_DESTdisplays all the information about the usage of the flash recovery area.

The flashback recovery area cancontaindatafiles, control files, online redo logs, miscellaneous
RMAN files, and flashback logs.

Initialization parameters for theonline redo log files location:
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
DB_CREATE_FILE_DEST

Initialization parameters for thecontrol files location:
CONTROL_FILES
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
DB_CREATE_FILE_DEST

Initialization parameters for thearchived log files:
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
LOG_ARCHIVE_DUPLEX_DEST

Backing Up the Flash Recovery Area

There are two commands that are used in backing up the flash recovery area(RMAN, only to tape):
·BACKUP RECOVERY AREA backs up the whole recovery area.
·BACKUP RECOVERY FILES just backs up the files in the recovery area.

Configuring the Flashback Database

The database must have ARCHIVE LOG enabled.
The flash recovery area must be configured to store the Flashback Database logs.

1. Start the database in MOUNT mode:
SQL> startup mount

2. Set the DB_FLASHBACK_RETENTION_TARGET parameter to the desired value.
SQL> alter db_flashback_retention_target=4320;-- by minutes, here is 3 days

3. Enable the flashback capability:
SQL> alter database flashback on;

4. Now the database can be opened for normal use:
SQL> alter database open;

Using the Flashback Database with RMAN

1. First, query the V$FLASHBACK_DATABASE_LOG view to retrieve theOLDEST_FLASHBACK_SCN:
SQL> select oldest_flashback_scn, oldest_flashback_time
      2 from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME
-------------------- ---------------------
689316 29-SEP-04

2. Next, shut down and start the database in MOUNT mode:

3. Next, issue the Flashback Database recovery from RMAN:
   RMAN> flashback database to scn=689316;

4. Finally, open the database with the RESETLOGS option:
   SQL> alter database open resetlogs;

Monitoring the Flashback Database

V$DATABASE: view displays if the Flashback Database is on or off.

V$FLASHBACK_DATABASE_LOG: determine the amount of space required in the recovery area to support the flashback activity generated by changes in the database.OLDEST_FLASHBACK_SCNandOLDEST_FLASHBACK_TIMEcolumns give you information regarding how far back you can use the Flashback Database.ESTIMATED_FLASHBACK_SIZEestimated size of flashback data

V$FLASHBACK_DATABASE_STAT: used to monitor the overhead of maintaining the data in the Flashback Database logs.

----------------------------------------

Using the Flashback Database with Enterprise Manager
----------------

The Flashback Database is a new Oracle 10g solution that is best used to recover from logicalcorruptionanduser error. This is a new alternative toincompleterecoveryorthe Log Miner utility.

The flash recovery area will not back up redo log files, control files, or flashback logs with the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES commands.
Permanent files are considered to be current online redo logs and control files.