Configure the Flashback for the Database in Oracle

Flashback database is related to protecting data from loss and overcoming the 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 archivelog mode.

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

Check Flashback Database is enabled.

select flashback_on from v$database;

Pre-requisites for configuring the flashback on the Database:

a. Database must in archivelog 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 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;