Visit Counter

Monday, January 5, 2015

Enable / Disable Flash Back Database Option



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.




[oracle@orapri bin]$ ./sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 6 02:37:02 2014

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

SQL> conn / as sysdba
Connected.
SQL> show recyclebin
SQL> show parameter recyclebin

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
recyclebin                   string on
SQL> show parameter db_recovery_file_dest

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest              string /ora/oracle/app/oracle/flash_r
                                     ecovery_area
db_recovery_file_dest_size         big integer 3882M


SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

SQL> alter system set db_recovery_file_dest='/ora/oracle/flashback';

System altered.

SQL> show parameter db_recovery

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest              string /ora/oracle/flashback
db_recovery_file_dest_size         big integer 2G

SQL>


SQL> show parameter undo_management

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
undo_management              string AUTO
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size            2212696 bytes
Variable Size             872418472 bytes
Database Buffers    268435456 bytes
Redo Buffers                9383936 bytes
Database mounted.
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.


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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size            2212696 bytes
Variable Size             872418472 bytes
Database Buffers    268435456 bytes
Redo Buffers                9383936 bytes
Database mounted.

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
off
SQL> alter database open;

Database altered.

No comments:

Post a Comment