In this post, we will discuss the Flashback technology in Oracle, its architecture, Oracle Flashback query, the syntax to use Oracle Flashback query
What is Flashback technology in Oracle?
Oracle has introduced Flashback Technology that lets you view past states of database objects or return database objects to a previous state without using point-in-time media recovery.
With flashback features, you can do the following:
- Perform queries that return past data
- Perform queries that return metadata that shows a detailed history of changes to the database
- Recover tables or rows to a previous point in time
- Automatically track and archive transactional data changes
- Roll back a transaction and its dependent transactions while the database remains online
Architecture
Oracle Flashback features use 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.
Undo data is persistent and survives a database shutdown. 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 the following actions:
- Roll back active transactions
- Recover terminated transactions by using a database or process recovery
- Provide read consistency for SQL queries
Here in this article, we will be discussing the Oracle flashback query feature which is very useful. Some of the use cases are
- Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
- Comparing current data with the corresponding data at some time in the past. For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
- Checking the state of transactional data at a particular time. For example, you can verify the account balance on a certain day.
What is the Oracle Flashback query?
Flashback Query allows the contents of a table to be queried concerning a specific point in time, using the as of clause. So if you have deleted certain rows and committed them, you can still recover them using the Oracle flashback query provided the undo tablespace is big enough and undo data is still present
Syntax to Use Oracle Flashback Query
select * from <table-name> AS OF TIMESTAMP TO_TIMESTAMP('<date>',<timestamp format>);
Examples
SELECT *
FROM employes AS OF TIMESTAMP TO_TIMESTAMP('2017-08-29 11:34:12', 'YYYY-MM-DD HH24:MI:SS');
Or
We can set the timestamp format for the session
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';
SELECT *
FROM employes AS OF TIMESTAMP TO_TIMESTAMP('29-AUG-2017 11:34:12.000');
or
select * from emp as of timestamp sysdate-1/24;
Important points On Oracle flashback query
(1) We can insert this flashback data in the table also
create table emplyees_bk as SELECT *
FROM employes AS OF TIMESTAMP TO_TIMESTAMP('29-AUG-2017 11:34:12.000');
(2) We can use with where condition also
SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
(3) You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.
CREATE VIEW hour_ago AS
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
Key Features
- Time Travel Within Data: You can query a table’s state as it was at a specific time in the past.
- Recover from Mistakes: Ideal for recovering from accidental data updates or deletions.
- Minimal Performance Impact: Since it uses existing undo information, there’s no significant additional load on the system.
- Easy to Use: Implemented with simple extensions to the SQL SELECT statement.
Considerations
- Retention Period: The ability to flashback depends on the retention period of the undo data. If the undo data for the required time has been purged, you cannot flashback to that point.
- Permissions: Appropriate privileges are required to use Flashback Query.
- Not a Backup Replacement: While useful, it’s not a substitute for regular backups.
Applications
- Data Correction: Quickly correct accidental data modifications.
- Auditing: Check the state of data at a specific point in the past for auditing purposes.
- Comparative Analysis: Compare current data with past data for trend analysis or troubleshooting.
Hope you like this content on the Oracle flashback query
Related Articles
Recover drop/truncate/delete table on primary using flashback on standby database
Top 10 Useful Queries for Flashback Database
Oracle Flashback Database
oracle database administration tutorial