How to Configure Flashback Database in oracle 12c

Issue the following steps to configure your database with flashback recovery:

// Ensure flashback recovery settings by querying ‘v$database’ dictionary view, as follows:
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO

// Set db_recovery_file_dest system parameter for flashback recovery file destination:
[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> alter system set db_recovery_file_dest=’/u01/flash_recovery’ scope=both;

System altered.

// Set db_recovery_file_dest_size system parameter for total size limit for flashback recovery.
SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

// Ensure changes:
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/flash_recovery
db_recovery_file_dest_size big integer 10G

// Bounce database to mount state:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.

// Turn on flashback recovery by issuing following command:
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

Oracle error “ORA-38707: Media recovery is not enabled” due to database is not in archive log mode.

Here is tutorial to convert your database to archivelog mode.

// After media recovery enabled, issue the following:

SQL> alter database flashback on;

Database altered.

// Open database:
SQL> alter database open;

Database altered.

// Set Flashback Retention Target ( In Minutes ):

SQL> alter system set db_flashback_retention_target=2880;

System altered.

// Ensure changes:
SQL> SELECT NAME, FLASHBACK_ON FROM V$DATABASE;

NAME FLASHBACK_ON
——— ——————
ORCL YES

Cheers!! Your database has configured with flashback recovery option.

Leave a Reply

%d bloggers like this: