In our workshop, my colleague deleted more than 10,000 rows. And he then commits the changes. After some time, he identified the deleted rows mistakenly. Finally, using the flashback Oracle concept, he did recover the data.
Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects orreturn database objects to a previous state without using point-in-time media recovery.
Perform queries that return metadata (detailed history of changes to the database).
Recover tables or rows to a previous point in time.
Automatically track and archive transactional data changes.
ROLLBACK transactions and their dependent transactions while the database remains online.
The architecture of Flashback Table
SQL Query to recover data
Here is the example SQL query. You can use it to recover data from the flashback table to a specific point in time.
SELECT * FROM employeesAS OF TIMESTAMPTO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')WHERE last_name = 'Chung';
To execute the query, you need privileges.
Conclusion
In the event of accidental data deletion or changes, Oracle’s Flashback Table feature proves to be a powerful tool for recovering lost or mistakenly modified data.
select * from <table> as of timestamp sysdate – interval '1' hour minus select * from <table>; To recover these, insert the result of this query! insert into <table> select * from <table> as of timestamp sysdate – interval '1' hour minus select * from <table>; Note this will include all rows deleted in the past hour.
Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
The chief differences between normal flashback logging and logging for guaranteed restore points are related to when blocks are logged and whether the logs can be deleted in response to space pressure in the fast recovery area. These differences affect space usage for logs and database performance.
The Flashback features give you the capability to query historical data, perform change analysis, and perform the self-service repair to recover from logical corruptions while the database is online. With Oracle Flashback Technology, you can indeed undo the past.
Flashback transaction query - lets you retrieve historical data for a given transaction and the SQL code to undo the transaction. Flashback table - recovers a table to its state at a past point in time, without having to perform a point in time recovery.
Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. With flashback features, you can: Perform queries that return past data.
When we delete the rows from the table using the DELETE command, the extents that are allocated to the table will not be released and the table still holds them, whereas when using TRUNCATE with DROP STORAGE clause (which is default), it will release the space back to tablespace.
Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks: Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter.
Open the Start menu, type "This pc" , and hit Enter on your keyboard. Right-click on your USB flash drive and select "Properties" . Go to the "Previous Versions" tab and look at the list of available recovery options. Select a previous version and click "Restore" .
Flashback logs are stored as Oracle-managed files in the fast recovery area and cannot be created if no fast recovery area is configured. You must enable flashback logging before the target time for flashback by issuing the SQL statement ALTER DATABASE ... FLASHBACK ON .
After a Flashback Database operation, the database must be opened with resetlogs in order to make it writeable. alter database open resetlogs; A standby database does not need to be opened with resetlogs. Just startup mount.
You can determine how far back you can flash back the database by querying the V$FLASHBACK_DATABASE_LOG view. If insufficient data remains in the database to perform the flashback, then you can use standard recovery procedures to recover the database to a past point in time.
Introduction: My name is Fr. Dewey Fisher, I am a powerful, open, faithful, combative, spotless, faithful, fair person who loves writing and wants to share my knowledge and understanding with you.
We notice you're using an ad blocker
Without advertising income, we can't keep making this site awesome for you.
