How to Enable Flashback Database

To enable flashback database the following operations is needed.

1)Configure the Database in archivelog mode.
To change archiving read, Change Archival Mode

2)Configure Flash Recovery Area.
To configure flash recovery area,
Set up Flash Recovery Area


3)Clean Shutdown and mount the database.

Alter Database Flashback ON;

Before running command you can check whether flashback was actually On or not.

select flashback_on from v$database;

Steps:
------
SQL> alter database flashback ON;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

4)Open the database and optionally you can set DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes. By default it is 1 day(1440 minutes).

To make it 3 days
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 4320

However you can disable Flashback Database for a tablespace.Then you must take its datafiles offline before running FLASHBACK DATABASE.

Like,

SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST';
FILE_NAME FILE_ID
------------------------------ ----------
/oradata2/1.dbf 5

SQL> alter database datafile 5 offline;
Database altered.

SQL> ALTER TABLESPACE test flashback off;
Tablespace altered.

SQL> recover datafile 5;
Media recovery complete.

SQL> alter database datafile 5 online;
Database altered.

To disable flashback feature simply issue,
SQL>ALTER DATABASE FLASHBACK OFF;
Database altered.

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

How Flashback Database Works

• Once you enable the flashback database feature, at regular intervals, a new process   RVWR (RecoveryWriter) copies images of each altered block in the datafiles from   memory (flashback buffer) to the new flashback logs.

• Oracle stores these flashback logs in the flashback recovery area.

• If you want to flashback to 8:00 A.M., it may turn out that the flashback logs nearest to   the target time were written at 7:56 A.M. To cover this gap, you must apply the changes   from archived or online redo log files pertaining to that period.

• Always remember that Oracle doesn’t guarantee that you can flashback your database   to the flashback retention target. If Oracle is running low on free space in the flash   recovery area for newly arriving archived redo log files, it will remove some flashback   logs to make room.

Flashback Database Considerations

• If a datafile was resized during the time span covered by the Flashback Database operation, you can’t flashback that datafile. Instead, you must offline that particular datafile before you start the flashback database operation.

• If a control file has been restored or re-created during the time span you want to   flashback over, you can’t use the Flashback Database feature.

• You can’t flashback a database to before a RESETLOGS operation.

• You can’t flashback a datafile that was dropped or shrunk during the time span covered   by the flashback table operation.

Configuring Flashback Database

1. Ensure that your database is in the archivelog mode.

V$DATABASE (cols: logmode)
ARCHIVE LOG LIST (in SQL*Plus)

2. Your database must be using the flash recovery area.

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest'

3. You must set the initialization parameter DB_FLASHBACK_RETENTION_TARGET to set your flashback retention target (in minutes).

ALTER SYSTEM SET
DB_FLASHBACK_RETENTION_TARGET=1440

4. Shut down the database and restart in the MOUNT EXCLUSIVE mode.

5. Turn the flashback database feature on with the following command:

ALTER DATABASE FLASHBACK ON;

6. Use the ALTER DATABASE OPEN command to open the database.

SELECT FLASHBACK_ON FROM V$DATABASE;

Note: You can turn the feature off by using the ALTER DATABASE FLASHBACK OFF command while the database in the MOUNT EXCLUSIVE mode. When you do so, Oracle deletes all flashback database logs in the flash recovery area.

Note: If you don’t want certain tablespaces to be part of your flashback operations, issue the following command after setting the tablespace offline:

ALTER TABLESPACE USERS FLASHBACK OFF

Flashbacking a Database

1. Restart the database in the MOUNT (exclusive) mode then issue one of the commands:

FLASHBACK DATABASE TO SCN 5964663
FLASHBACK DATABASE TO BEFORE SCN 5964663
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)
FLASHBACK DATABASE TO SEQUENCE 12345

2. Open the database with READ ONLY option to check that the database flashed back to the correct time.

3. If you decide to go back further in time, you can flashback the database again.

4. If you determine that you flashed back too far into the past, you can use redo logs to roll forward.

5. Open the database with RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS

6. If you want to completely undo the effects of the flashback database operation, just use the command RECOVER DATABASE to perform a complete recovery of the database.

Displaying Flashback Storage Information

In order to estimate the space you need to add to your flash recovery area for accommodating the flashback database logs:

SELECT ESTIMATED_FLASHBACK_SIZE,
RETENTION_TARGET, FLASHBACK_SIZE FROM
V$FLASHBACK_DATABASE_LOG

To really know how far back you can flashback your database at any given time, you must query the V$FLASHBACK_DATABASE_LOG in the following manner:

SELECT OLDEST_FLASHBACK_SCN,
OLDEST_FLASHBACK_TIME FROM
V$FLASHBACK_DATABASE_LOG

The view V$FLASHBACK_DATABASE_STATS helps you monitor the I/O overhead of logging flashback data.

BEGIN_TIME and END_TIME - stands for the beginning and ending hourly time intervals for which the view’s statistics were collected. Oracle collects flashback data on an hourly basis for a maximum of 24 hours. If you issue a query on the table, however, it may return 25 rows, the 25th row being for the most recent fraction of time after the last (24th) row was logged in the view.

FLASHBACK_DATA - stands for the number of bytes of flashback data written during the interval.

DB_DATA - stands for the number of bytes of database data read and written during the interval.

REDO_DATA - stands for the number of bytes of redo data written during the interval.

ESTIMATED_FLASHBACK_SIZE - is identical to the value of the  ESTIMATED_FLASHBACK_SIZE column in the V$FLASHBACK_DATABASE_LOG view.