About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Friday, October 26, 2012

How to enable Flashback in oracle database 11g

Flashback in Oracle Database

Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.

View past states of data or move database objects to previous state means you have performed some operations like  DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.

How to enable FLASHBACK in Oracle Database 11G R1 and below versions

1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured. To configure PFB steps :-

SQL> show parameter db_recovery_file_dest

NAME                                  TYPE           VALUE
------------------------------------       ----------- -       -----------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size     big integer     0

Currently flashback is disabled. To enable :-

A. Set db_recovery_file_dest_size initialization parameter.

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

B. After db_recovery_file_dest_size parameeter has been set, create a location in OS where your FLASHBACK logs will be stored.

bash-3.2$ cd /orcl_db
bash-3.2$ mkdir FLASHBACK
bash-3.2$ pwd
/orcl_db/FLASHBACK

C. Now set db_recovery_file_dest initialization parameter.

SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK';    ##########For Standalone database##########

System altered.


SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK' sid='*';    ##########For RAC database##########

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE           VALUE
------------------------------------      -----------         ------------------------------
db_recovery_file_dest            string           /orcl_db/FLASHBACK
db_recovery_file_dest_size     big integer    2G

3. Create an Undo Tablespace with enough space to keep data for flashback operations. More often users update the database more space is required.

4. By default automatic Undo Management is enabled, if not enable it. In 10g release 2 or later default value of UNDO management is AUTO. If you are using lower release then PFB to enable it:-

SQL> alter system set undo_management=auto scope=spfile;

System altered

5. Shut Down your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

6. Startup your database in MOUNT mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.

7. Change the Flashback mode of the database

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

SQL> alter database open;

Database altered.


FLASHBACK mode of the database has been enabled.

How to disable FLASHBACK in Oracle Database 11G R1 and below versions

1. Shut Down your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

2. Startup your database in MOUNT mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.

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


SQL> alter database open;

Database altered.


FLASHBACK mode of the database has been disabled.


How to enable/disable FLASHBACK in Oracle Database 11G R2 and above versions.

From 11GR2 we donot have to bounce the database to alter flashback.


1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured. To configure PFA steps.

3.  TO enable or disable flashback , we can change this while database is in open mode. PFB


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select flashback_on from v$database;

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

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

I hope this article helped you.

Regards,
Amit Rath

15 comments:

  1. Thanks very much, In addition our database should be in archive_mode!

    ReplyDelete
  2. Thanks very much. We must put our database in archive mode before we work on flashback!
    SQL> alter database archivelog;(only when our database is in mount mode- SQL>startup mount;).
    check the archive mode by issueing query SQL>select log_mode from v$database;

    ReplyDelete
    Replies
    1. Hi Moven,

      Thanks for writing in. Yes its mandatory to have database in Archivelog mode for Flashback.

      Delete
  3. don't you have to set db_flashback_retention_target before enabling flashback database? I think it is a must.

    ReplyDelete
    Replies
    1. Hi,

      db_flashback_retention_target default value is 1 day (1440) . So you don't have to explicitly set this value while enabling flashback.
      But if you do want to change this value while enabling flashback , you can do this by below query:-

      alter system set db_flashback_retention_target=2880(2days) sid='*';

      Let me know if I clarified your doubt.

      Thanks
      Amit Rath

      Delete
  4. Thanks, very well documented..

    ReplyDelete
  5. Well documented with the steps...Keep it Up & happy learning ...

    ReplyDelete
  6. in oracle 12c if You disable flashback by 'alter database flashback off' command You're automatically shutdown database.In this case You can find such error:
    ORA-01264: Unable to create archived log file name
    ORA-01262: Stat failed on a file destination directory
    Linux-x86_64 Error: 2: No such file or directory

    ReplyDelete