How to Enable Flashback in Oracle Database

Flashback database is related to protecting data from loss and overcoming problems like user error, corruption, etc. Flashback has faster point-in-time recovery also called Database point-in-time recovery(DBPITR). Make sure the database is in archive log mode.

By configuring flashback, we can directly apply the application patch on the database,
if something is wrong then roll back it.

Check Flashback Database is enabled.

select flashback_on from v$database;

Pre-requisites for configuring the flashback on the Database:

a. Database must be in archive log mode.

Archive log list;

--For configure in Archive mode:
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;

b. Automatic undo tablespace management must be on

SQL> show parameter undo

-- If not in AUTO then change it as follows:
SQL> alter system set undo_management=auto scope=spfile;

Enable the Flashback Database:

1. Set the db_recovery_file_dest parameter.

ALTER SYSTEM SET db_recovery_file_dest='C:\flashback' scope=spfile;

2. Set the db_recovery_file_dest_size parameter for size allocated for flashback recovery.

ALTER SYSTEM SET db_recovery_file_dest_size=20G scope=spfile;

3. Configure the DB_FLASHBACK_RETENTION_TARGET Parameter in minutes.

-- For 1 days retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 days
-- For 2 day retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880; # 2 days
-- For 3 day retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

4. Enable the flashback database.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Disable the Flashback at the database level:

1. Shutdown the database and start the database at the mount stage

--Shutdown the database 
Shutdown immediate;
--Start the database at mount stage
Startup mount

2. Disable with ALTER command and open the database.

-- Disable the alter database.
alter database flashback OFF;

--Open the database:
alter database open;

Enable or disable the flashback for tablespace

 ALTER TABLESPACE users FLASHBACK OFF;
 ALTER TABLESPACE users FLASHBACK ON;