What is Oracle Flashback Technologies

Understanding Oracle Flashback Technologies

What is oracle Flashback Technologies?

The Flashback features offer the capability to query historical data, perform change analysis, and perform self-service repair to recover from logical corruptions while the database is online. With Oracle Flashback Technology, you can indeed undo the past.

How to Enable Flashback?

Flashback was introduced in 10g and up to 11gR1 you need to clean mount the database to enable the

flashback. It means you need downtime to enable the flashback

before 11gR2

login as sysdba

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT EXCLUSIVE;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;  << for how long the flashback logs are retained

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;   << maximum limit on size flashback log can use in DB_RECOVERY_FILE_DEST location

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=/ora_data01/orcl/recovery/;  << location where the flashback logs are written

SQL> ALTER DATABASE FALSHBACK ON;

SQL> ALTER DATABASE OPEN;

11gR2 and after

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;  << for how long the flashback logs are retained

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;   << maximum limit on size flashback log can use in DB_RECOVERY_FILE_DEST location

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=/ora_data01/orcl/recovery/;  << location where the flashback logs are written

SQL> ALTER DATABASE FALSHBACK ON;

SQL> ALTER DATABASE OPEN;

Notice the difference. starting from 11gR2 you dont need to shutdown your database to enable the flashback

How to Check if the FALSHBACK is enabled?

SQL> select flashback_on from v$database;

Which background process is responsible for flashback logs writing?

RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

What is Flashback Buffer?

flashback buffer is a designated area in SGA (just like log buffer) to hold the flashback logs. RVWR process flush the log from flashback buffer to disk

When are the flashback logs deleted?

• If the flash recovery area has enough space, then a flashback log is deleted whenever necessary to satisfy the flashback retention target.
• If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is overwritten.
• If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is overwritten instead.
• If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
• No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.
• When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.
• When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.

What feature Flashback technology offers?

Enabling flashback offers following featuers

Flashback Database: restore the entire database to a specific point-in-time, using Oracle-optimized flashback logs, rather than via backups and forward recovery.

Flashback Table: easily recover tables to a specific point-in-time, useful when a logical corruption is limited to one or a set of tables instead of the entire database.

Flashback Drop: recover an accidentally dropped table. It restores the dropped table, and all of its indexes, constraints, and triggers, from the Recycle Bin (a logical container of all dropped objects).

Flashback Transaction: undo the effects of a single transaction, and optionally, all of its dependent transactions. via a single PL/SQL operation or by using an Enterprise Manager wizard.

Flashback Transaction Query:  see all the changes made by a specific transaction, useful when an erroneous transaction changed data in multiple rows or tables.

Flashback Query: query any data at some point-in-time in the past. This powerful feature can be used to view and logically reconstruct corrupted data that may have been deleted or changed inadvertently.

Flashback Versions Query: retrieve different versions of a row across a specified time interval instead of a single point-in-time.

Total Recall: efficiently manage and query long-term historical data. Total Recall automatically tracks every single change made to the data stored inside the database and maintains a secure, efficient and easily accessible archive of historical data.

FRA and Flashback Queries

To find Location, quota, in use/reclaimable space, number of files

SQL>SELECT * FROM v$recovery_file_dest;

For each file type, percent of FRA space it uses and is reclaimable and number of files of that type

SQL>SELECT * FROM v$recovery_area_usage;

Estimated space used by Flashbacklogs

SQL>SELECT estimated_flashback_size FROM v$flashback_database_log;