Configuring Flashback Database

C:\Documents and Settings\mazar>set oracle_sid=riyadh

C:\Documents and Settings\mazar>sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 18 11:17:37 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys/sys as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

Step 1

Ensure that the database in archivelog mode.

Archivelog mode is a prerewuistite for enabling flashback database.

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

Step 2

Set up a flash recovery area.

The flash recovery area is the location for the flashback logs. YOu have no control over them other than setting the flah recovery area directory and limiting its size. It is controlled with two instance paramenters: DB_RECOVERY_FILE_DEST specifies the destination directory;
DB_RECOVERY_FILE_DEST_SIZE restricts that the flash recovery area is used for purposes other than flashback logs and it will need to be sized appropriately.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      D:\flashrecovery
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

Step 3

Set the lifespan for the flashback retention target.

This setting is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which specifies a time in minutes (the default is one day).

The flashback log space is reused in a circular fashion, older data being overwritten by newer data.This parameter instructs Oracle to keep flashback data for a certan cumber of minutes before overwriting it

SQL> alter system set db_flashback_retention_target=240;

System altered.

It is only a target(four hour in this example), and if the flash recovery area is undersized, Oracle may not be able to keep it.but is principle, you should be able to flash back to any time within this target.

Step 4

Cleanly Shutdown the database and mount

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  247463936 bytes
Fixed Size                  1248332 bytes
Variable Size             104858548 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.

Step 5

Enable flashback logging, while in mount mode.
SQL> alter database flashback on;

Database altered.

This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.

Step 6

Open the database.

SQL> alter database open;

Database altered.

Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.

Step 7

Check Whether Flashback On or Off.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES