What is Flashback feature in Oracle Database?

Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.

Points to Remember

- Flashback Database command can be run either from SQLPLUS or using RMAN Utility.

- FLASHBACK DATABASE command can be used to rewind the database to a target time, SCN or a log sequence number.

- Flashback command works by undoing the changes made to the data files that exist when you run the command.

- Flashback can fix only logical failures, not physical failures.

- If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.

- Avoid using FLASHBACK DATABASE with a target time or SCN that coincides with a NOLOGGING operation, it can cause block corruption.

How to enable Flashback Database feature in Oracle?

 Prerequisites for Flashback Database and Guaranteed Restore Points

Flashback Database

Configure the following database settings before enabling Flashback Database:

-Your database must be running in ARCHIVELOG mode.

-You must have a fast recovery area enabled.

-For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM.

Guaranteed Restore Points

To use guaranteed restore points,the COMPATIBLE initialization parameter must be set to 10.2.0 or greater.

Steps to enable Flashback Database:

1. Connect to sqlplus as sysdba and set  the desired value for Flashback retention target using below command.

SQL>ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Here, Flashback retention target is set to window of 3 days(4320 Minutes), default value is 1 day (1440 Minutes).

2. Enable the Flashback Database feature for the whole database using the following command:

SQL>ALTER DATABASE FLASHBACK ON;

3.Use the following command to check if Flashback Database is enabled for your target database:

SQL>SELECT FLASHBACK_ON FROM V$DATABASE;