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

How to enable Archivelog mode in Oracle database 11g

Mode of Logging

There are two types of logging modes in Oracle database :-

1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.

How to check log mode in Oracle database 11g :-

[cognos@rac1 u02]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012

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

Enter user-name: /as sysdba

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

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /backup/orcl/
Oldest online log sequence     1
Current log sequence           1
SQL> select name,log_mode from v$database;

NAME      LOG_MODE
--------- -    -----------
ORCL      NOARCHIVELOG

Currently the ORCL database is in NOARCHIVELOG mode

To change the Oracle database in ARCHIVELOG mode. PFB below mentioned steps:-

1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile;

System altered.

Note -- To change this parameter while database is open, your database has to run with SPFILE, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE

2. After this you need to shut down your database and start again in MOUNT mode

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archive
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      ARCHIVELOG

Database changed to ARCHIVELOG mode.

Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database immediately because in recovery scenarios you can recover your database from the last backup taken in this mode.

#############################################################################

To change the Oracle database in NOARCHIVELOG mode. PFB below mentioned steps:-

1.Shutdown your running database.
SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Start 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> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u02/archive

Oldest online log sequence     1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

---------      ------------

ORCL      NOARCHIVELOG

Database changed to NOARCHIVELOG mode.

I hope this article helped you.

Regards,

Amit Rath