Configuring Flashback Database

Connect to your database as sysdba.

1.  Ensure that your database is in ARCHIVELOG mode.

SQL> select log_mode from v$database;

LOG_MODE
————————
NOARCHIVELOG

1.1 My database is not in ARCHIVELOG mode let’s enable it.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> select log_mode from v$database;

LOG_MODE
——————-
ARCHIVELOG

2. Setting up flash recovery area.

SQL> show parameter db_recovery_file_dest;

NAME                                        TYPE             VALUE
————————————   ———–     ————-
db_recovery_file_dest           string
db_recovery_file_dest_size  big integer 0

First of all, db_recovery_file_dest_size parameter must be set.

SQL> alter system set db_recovery_file_dest_size=2G;

Then db_recovery_file_dest parameter.

SQL> alter system set db_recovery_file_dest=’D:\TEST\FLASHRECOVERY’;

3. Setting up retention period.

Because of flash recovery area is used in circular fashion, after some period of time(indicates db_flashback_retention_target,default is 1day) old data is overwritten. This parameter instructs Oracle to save flashback files for a certain minutes before overwriting.

SQL> alter system set db_flashback_retention_target=720;

In our case flashback data will be retained for 12 hours before overwriting.

4. Enable flashback logging.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

At this time RVWR process will be started and flashback buffer will be allocated in SGA.

SQL> alter database open;

5. Check if flashbacking is enabled.

SQL> select flashback_on from v$database;

FLASHBACK_ON
———————–
YES