Oracle’s Flashback Technology is a powerful feature in Oracle Database 19c that allows you to view past states of database objects or return database objects to a previous state without using point-in-time media recovery. It’s like having a time machine for your database.
Flashback Technology uses the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user runs an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data. Undo data is persistent and survives a database shutdown.
By using flashback features, you can use undo data to query past data or recover from logical damage. Besides using it in flashback features, Oracle Database uses undo data to perform actions like rolling back active transactions, recovering terminated transactions by using database or process recovery, and providing read consistency for SQL queries.
Flashback Technology is a group of Oracle Database features that includes Flashback Query, Flashback Version Query, Flashback Transaction Query, and others. These features provide a rich set of tools that allow you to view and manipulate past states of data, making it easier to recover from errors and data loss.
The DB_FLASHBACK_RETENTION_TARGET parameter in Oracle 19c specifies the upper limit (in minutes) on how far back in time the database may be flashed back. Its default value is 1440 minutes, which is equivalent to one day. You can modify this parameter using the ALTER SYSTEM command.
Flashback features require the Flash Recovery Area (FRA) to be enabled
However, it’s important to note that the Flashback features, including FLASHBACK DROP, require the Flash Recovery Area (FRA) to be enabled. The FRA is a disk space location that Oracle uses to store recovery-related files. It’s defined by the DB_RECOVERY_FILE_DEST parameter and its size is set by DB_RECOVERY_FILE_DEST_SIZE. Oracle recommends that the FRA be located on a separate storage device from the database files.
Remember, the effectiveness of the Flashback features and the DB_FLASHBACK_RETENTION_TARGET parameter is directly related to the configuration and availability of the FRA
SQL> SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ NO SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. Database mounted. SQL> ALTER DATABASE FLASHBACK ON; Database altered. SQL> ALTER DATABASE OPEN; Database altered. --DB_FLASHBACK_RETENTION_TARGET is by default 1440 minutes (24 hours). SQL> ALTER SYSTEM SET db_flashback_retention_target = 4320; -- 72 hours System altered.
The following errors will be raised in case that the recovery area is not enabled.
SQL> ALTER DATABASE FLASHBACK ON; ALTER DATABASE FLASHBACK ON * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38709: Recovery Area is not enabled.
Finally, enable Oracle Flashback Drop to enable the recovery of a dropped table.
ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE; SQL> SHOW PARAMETER RECYCLEBIN NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string ON