Flashback Technology

1) Oracle flashback transaction query lets you view changes made by a single transaction or by all the transactions during a period of time.

2)Oracle flashback table returns a table to its state at a previous point in time.

3)Oracle flashback Drop reverses the effects of a drop table statement.

4)Oracle flashback database provides a more efficient alternative to database point in time recovery.

To Check the Flashback status:-

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
PROD      NO

-->archive log should be enabled.

--->show parameter undo_retention

---->show parameter DB_FLASHBACK_RETENTION_TARGET

---->show parameter db_recovery_file_dest

----->show parameter db_Recovery_file_dest_size

To Enable flashback feature on database?

1)database must be started with spfile.

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/home/db/tech_st/11.1.0/db
                                                 s/spfilePROD.ora

2)database must be in archivelog mode.

3)undo_management showld be auto

SQL> show parameter undo_management

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

4)set the recovery file destination or flashback area which will contain all flashback logs depending on the undo retention period.

5)set the recovery file destination size.This is the hard limit on the total space to be used by target database recovery files created in the flash recovery area.

6)Set the flashback retention target.This is the upper limit (in minutes) on how far back in time the database may be flashed back.How far back one can flashback a database depends on how much flashback data oracle has kept in the flash recovery area.

SQL> alter system set db_recovery_file_dest_size=3G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/home/db/tech_st/11.1.0/flashback' scope=both;

System altered.

SQL> alter system set db_flashback_retention_target=1440 scope=both;

System altered.

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

PROD      NO

we need to set alter flashback in mount state.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1318172 bytes

Variable Size             436208356 bytes

Database Buffers          620756992 bytes

Redo Buffers               13049856 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

PROD      YES