How to Enable/Configure and Disable Flashback (On/Off) Feature in Oracle

advertisements

_____________________________________________________________________________________________________________________

Pre-requesites:
a. Database must in archivelog mode.
b. Automatic undo tablespace management must be on
c. There should be enough space (depends on your requirement) in undo tablespace.
Steps
1. Verify the flashback feature is on or not using the flashback_on column in the v$database
SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------
NO
2. Make sure your database in archivelog mode.
SQL> archive log list;
Database log mode              Archive Mode

3. Set the db_recovery parameters
db_recovery_file_dest with valid direcotory and db_recovery_file_dest_size with a valid size
SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> alter system set  db_recovery_file_dest_size=10g scope=spfile;

System altered.

SQL>  alter system set db_recovery_file_dest='/u01/FRA' scope=spfile;

System altered.

SQL>  alter system set  db_recovery_file_dest_size=10g;

System altered.

SQL>  alter system set db_recovery_file_dest='/u01/FRA';

System altered.

SQL>  show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/FRA
db_recovery_file_dest_size           big integer 10G

4. Verify the undo management is set AUTO. If not use following command to set it as auto.

alter system set undo_management=auto scope=spfile;
System altered

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------------undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
5. Check the values for flashback retention parameter

SQL>  show parameter flash;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     0

6. Shutdown and then mount the database

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size                  2237776 bytes
Variable Size            8254393008 bytes
Database Buffers         2415919104 bytes
Redo Buffers               16924672 bytes
Database mounted.
SQL>  select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

7. Set the Flashback retention parameter

SQL>  ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880; # 2days = 2*24*60

System altered.

SQL>  show parameter flashb

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_flashback_retention_target        integer     2880

8. Make the flashback feature on

SQL>alter database flashback ON;
Database altered.
 
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
Database altered.

Disable the flashback feature for Oracle database

1. Shutdown and then mount the database

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.
 
Total System Global Area 1.0689E+10 bytes
Fixed Size                  2237776 bytes
Variable Size            8254393008 bytes
Database Buffers         2415919104 bytes
Redo Buffers               16924672 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.

_____________________________________________________________________________________________________________________

Website Stats