Embedded Files
Flashback Data Archive (FBDA)
Flashback Data Archive (FBDA)
Flashback Database
Flashback Database
Check
Check
SELECT flashback_on
FROM v$database;
Enable
Enable
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
2880 Minutes is 2 DaysALTER DATABASE FLASHBACK ON;
ARCHIVELOG Mode is a pre-requisite for Flashback Database.
FRA must be appropriately sized for the expected number of Flashback Logs
Disable
Disable
ALTER DATABASE FLASHBACK OFF;
Flashback Table
Flashback Table
To "undrop" a table...
FLASHBACK TABLE '%TABLE' TO BEFORE DROP;
To "undelete" rows...
ALTER TABLE '%TABLE' ENABLE ROW MOVEMENT;
FLASHBACK TABLE '%TABLE' TO TIMESTAMP TO_TIMESTAMP('2021-09-22 14:46:01','YYYY-MM-DD HH24:MI:SS');
NOTE: Be careful. This will shift the table back in time. Any new rows INSERTED/UPDATED after the initial DELETE will be lost.PERFORMANCE
On a sample table with 6852297 rows, deleting 6092207 rows with a single DELETE transaction leaving 760090 rows...
DELETE took just over 11 minutes. FLASHBACK took just under 17 minutes
On a sample table with 6852297 rows, deleting 6092207 rows with a single DELETE transaction leaving 760090 rows...
DELETE took just over 11 minutes. FLASHBACK took just under 17 minutes
Flashback Query
Flashback Query
To query contents of a table at an earlier point in time...
SELECT *
FROM mytable AS OF TIMESTAMP TO_TIMESTAMP('2021-07-16 22:36:00','YYYY-MM-DD HH:MI:SS')
WHERE mycolumn = 'myvalue';
You can also use something like the query below to generate SQL to undo the SQL you just did...
SELECT undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE TABLE_NAME = 'mytable';
Bibliography
Bibliography
https://www.oracle.com/database/technologies/high-availability/flashback.html https://docs.oracle.com/database/121/ARPLS/d_flashb_archive.htmhttps://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm
https://dbtut.com/index.php/2018/12/11/how-to-recover-dropped-tables-with-oracle-recyclebin/https://svenweller.wordpress.com/2020/07/02/7-secret-oracle-db-features-you-might-not-know/
https://dzone.com/articles/flashback-table-in-oracle-explained-with-examples
Flashback Databasehttps://www.thegeekdiary.com/how-to-use-flashback-database-in-oracle-data-guard-configuration/
Page updated
Google Sites
Report abuse