Mir Sayeed Hassan – Oracle Blog

How to Enable & Disable flashback in Oracle database 11gR2(11.2.0.4.0)

Flashback in Oracle Database

It’s a technology in Oracle database to perform restore the database to a particular point in past.

In case if you have lost the data like DML & want to retrieve, you can retrieve the rollback operation through flashback without using point in time media recovery.

Enable flashback of database

Note: Database should be in archive log mode

To enable flashback, Set the mandatory parameter
- DB_RECOVERY_FILE_DEST
- DB_RECOVERY_FILE_DEST_SIZE

[oracle@testdb-pridb ~]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 4 13:08:18 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set db_recovery_file_dest='/u02/oradata/fast_recovery_area/';
System altered.

SQL> alter system set db_recovery_file_dest_size=50G;
System altered.

SQL> show parameter db_recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02/oradata/fast_recovery_area/
db_recovery_file_dest_size big integer 50G

Enable/Turn on flashback:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Disable Flashback

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

Important Note:

You can enable/disble flashback live on 11g & above, If your DB is prior to 11g then you need to shutdown db & perform in mount mode.

====Hence tested & verified in our test env=====