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
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Ensure that the database in archivelog mode.
Archivelog mode is a prerewuistite for enabling flashback database.
SQL> select log_mode from v$database;
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;
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;
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.
Cleanly Shutdown the database and mount
SQL> shutdown immediate;
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
Enable flashback logging, while in mount mode.
SQL> alter database flashback on;
This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.
Open the database.
SQL> alter database open;
Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.
Check Whether Flashback On or Off.
SQL> select flashback_on from v$database;