Flashback functionalities provide fast and flexible data recovery.

Type of flashback recovery:

• Flashback Database (We can revert database at a past time)
• Flashback Drop (Reverses the effects of a DROP TABLE statement)
• Flashback Table (Reverses a table to its state at a previous point in time)
• Flashback Query (We can specify a target time and then run queries, viewing results and recover from an unwanted change)
• Flashback Transaction Query (We can view changes made by a transaction during a period of time.)

Requirement for Flashback:

• Database must be in Archive log mode
• Must have flash recovery area enable

Why database must be in archive log mode? Because archive logs are used in the Flashback operation.

Why flash back recovery area required? Because flashback logs can only be store in this area.

Important: In RAC Database, flashback recovery area must be store is clustered file system or in ASM.

How to Enable Flash Recovery Area?

Set two parameter db_recovery_file_dest and db_recovery_file_dest_size.

SQL> alter system set db_recovery_file_dest=' ';

SQL> alter system set db_recovery_file_dest_size=2048m

How to Enable Flashback database?

Mount the database (not open), set parameter DB_FLASHBACK_RETENTION_TARGET and issue the ALTER DATABASE FLASHBACK ON command.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

SQL> ALTER DATABASE FLASHBACK ON;

Note :

• Default retation target is 1440 (One Days).
• If we want to retain flashback logs to perform a 48 hour flashback, set the retention target to 2880 minutes (2 days x 24 hours/day x 60 minutes/hour)
• By default, flashback logs are generated for all permanent tablespaces
• We can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE users FLASHBACK OFF;

We can re-enable flashback logging for a tablespace later with this command:

SQL> ALTER TABLESPACE users FLASHBACK ON;

Important:

• If we disable Flashback Database for a tablespace, then we must take its datafiles offline before running FLASHBACK DATABASE.
• We can enable Flashback Database not only on a primary database, but also on a standby database.