Dear Friens back to my Oracle journey after long time. This was interview question ask to my friend during the interview last week.Lots of questions relate with flashback and 10g / 11g new features.
As requested by him decided to put new post on Flashback database.
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;
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;
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.
Prerequisites for Flashback recovery:
- Must have FLASHBACK ANY TABLE system privilege or must have FLASHBACK object privilege on the table.
- Must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
- Row movement must be enabled on the table.
SQL>ALTER TABLEENABLE ROW MOVEMENT;
Flashback Drop:( Reverses the effects of a DROP TABLE statement)
Reverse the Drop table ->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP;
Assign a new name to the restored table->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO;