Change Flashback mode In Oracle

Change Flashback mode In Oracle Database

by

Change Flashback mode In Oracle Database: In this article we will discuss how to Change Flashback mode In Oracle Database.

By using flashback technology we can restore the database to a particular point in past. It’s like time machine.
Here we will see , how to enable and disable flashback in oracle.
To enable flashback we need to set two parameters: DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.

Purpose:

Use the FLASHBACK DATABASE statement to return the database to a past time or system change number (SCN). This statement provides a fast alternative to performing incomplete database recovery.

Following a FLASHBACK DATABASE operation, in order to have write access to the flashed back database, you must reopen it with an ALTER DATABASE OPEN RESETLOGS statement.

Prerequisites:

You must have the SYSDBA system privilege. A flash recovery area must have been prepared for the database. The database must have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON statement unless you are flashing the database back to a guaranteed restore point. The database must be mounted but not open. In addition:

The database must run in ARCHIVELOG mode.

The database must be mounted, but not open, with a current control file. The control file cannot be a backup or re-created. When the database control file is restored from backup or re-created, all existing flashback log information is discarded.

The database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement ALTER TABLESPACE … FLASHBACK OFF.

Firstly, How to ENABLE FLASHBACK :

Make sure the database is in archive log mode :

SQL > select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
ORCL      ARCHIVELOG

SQL>
SQL>
SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area/ORCL’;

System altered.

SQL>
SQL>
SQL> alter system set db_recovery_file_dest_size=8016M;

System altered.

SQL>
SQL>
SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area/ORCL
db_recovery_file_dest_size           big integer 8016M

SQL>

Turn on flashback :

SQL>
SQL> select flashback_on from v$database;

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

SQL>
SQL>
SQL> alter database flashback on;

Database altered.

SQL>
SQL>
SQL> select flashback_on from v$database;

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

SQL>

Lastly, How to DISABLE FLASHBACK :

Turn off flashback :

SQL>
SQL> select flashback_on from v$database;

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

SQL>
SQL>
SQL> alter database flashback off;

Database altered.

SQL>
SQL>
SQL> select flashback_on from v$database;

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

SQL>

NOTE :-

If you are in 10g, then we need to enable/disable the flashback mode in mount stage follow the below given steps :

  • shutdown immediate;
  • startup mount;
  • alter database flashback off;
  • alter database open;

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.

Leave a Comment