Flashback Database setup in Oracle

Enable Flash back on :

Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected.

[oracle@Linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015

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


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

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Lets check if Flash back is enabled or not :

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
NO                 OPEN

Since it is not enabled. Lets turn flash back ON:

SQL> alter database flashback on;

Database altered.


SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
YES                OPEN

Confirm the size of DB_RECOVERY_FILE_DEST_SIZE (Better keep it at least 30GB+ until it is Dev or Test environment)

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 20000M

I want to set it to 30GB:

SQL> alter system set db_recovery_file_dest_size=30G  ;

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G

Lets verify the location of recovery area. This is where the flashback logs are stored:

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 30G

You can reset the location by simple command :

 alter system set db_recovery_file_dest= '/u01/app/oracle/flash_recovery/oraflash';


 Lets confirm the retention period :

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440 

You can Change the retention period :

sql > alter system set db_flashback_retention_target=2880;  -- 2days

Note : This retention period is in minutes so 1440/60 = 24 hours. 




If you have a standby Database configured:
Activating flashback logging on the standby database Flashback operations, such as activating or accessing restore points, can only be undertaken in the MOUNT stage of the database. An active recovery will also first have to be canceled.


Manual method First cancel the recovery on the standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Then activate Flashback:

 ALTER DATABASE FLASHBACK ON;

Then restart the recovery: 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Deactivating To deactivate flashback mode, use the command:

 ALTER DATABASE FLASHBACK OFF;