In today’s article, I will be giving you information about Oracle Flashback Technologies.
In case of Logical Corruptions, it directly resolves corrupted data rather than dealing with Backup & Recovery.
The overall view of Flashback Technologies is as follows.
Flashback Query: Shows what the data was like at a certain time.
UNDO uses TABLESPACE.
Flashback Version Query: Shows all changes in the data within a certain time period.
UNDO uses TABLESPACE.
Flashback Transaction Query: Shows the changes made in the database at the transaction level.
It uses UNDO and REDO data.
It also gives who made the change and when, and the UNDO_SQL that will undo the change.
Flashback Transaction Backout: Rolls back a certain transaction and its related transactions.
It is mandatory to enable Supplemental Logging.
Flashback Table: Takes the table back to a certain time.
The database remains ONLINE at the time of flashback.
UNDO fetches from TABLESPACE. If the data is lost in UNDO TABLESPACE and the Flashback feature is not activated, good riddance.
The user who will perform the operation must have FLASHBACK ANY TABLE or FLASHBACK TABLE authority.
Row Movement must be enabled in the table to be flashbacked.
The FLASHBACK TABLE command runs a single transaction. Therefore, exclusive DML Lock occurs.
Statistics of the table are not flashbacked.
Current indexes and connected objects are maintained.
It cannot be done in FLASHBACK TABLE system tables.
UNDO and Redo occur during FLASHBACK TABLE.
Flashback Drop: Retrieves a table from the trash.
Flashback Database: Brings the database back to a certain time.
It uses Flashback Logs.
Flashback Data Archive: It is the storage of changes in the table for the retention period determined at the table level.
The user who will perform the flashback must have FLASHBACK authority.
UNDO parameters used in the flashback process are as follows.
[Backup] SQL> show parameter undo
NAME TYPE VALUE
undo_management string AUTO: Specifies whether segment retrieval from Undo Tablespace will be automatic or not when needed.
undo_retention integer 900: This is the minimum period for which UNDO will be kept. Its value is in seconds. The correct value is calculated as follows.
undo_tablespace string UNDOTBS1: Indicates UNDO TABLESPACE. Size must be set correctly.
There is no such thing as exact undo information to be kept as written in the UNDO_RETENTION parameter.
If UNDO is desired to be kept definitive, then TABLESPACE RETENTION GUARANTEE is used.
11:47:41 [Primary-2] SQL> alter tablespace UNDOTBS1 retention guarantee;
11:48:07 [Primary-2] SQL> alter tablespace UNDOTBS2 retention guarantee;