Oracle Flashback technology was introduced in Oracle 9i and added on to higher version. It provides a set of features using which we can view as well as recover data from Logical corruptions. Most of the Flashback technologies depend on the available UNDO data to retrieve older data.
Some of the Advantages of Flashback Technology are faster database point in time recovery and less performance overhead.Also we need to take care of the parameters that should be set for Flashback which are given below:
· DB_FLASHBACK_RETENTION_TARGET: Time limit for the deleted data to be retained
Alter System Set DB_FLASHBACK_RETENTION_TARGET=4320
· DB_RECOVERY_FILE_DEST_SIZE: Size limit for the maximum data that can be retained.
Alter System Set DB_RECOVERY_FILE_DEST_SIZE=536870912
· DB_RECOVERY_FILE_DEST: Location where the data needs to be retained
Alter System Set DB_RECOVERY_FILE_DEST='/Source/File/’
Flashback technology gives six different ways to fix logical corruption.
a) Oracle Flashback Query:
The Flashback query feature is used to views set of row at a specified point of time in the past. This feature uses the UNDO data (stored based on the undo retention parameter) to view the historical data and reconstruct the data if it was lost accidentally.
Useful when you accidentally delete number of rows and commit the changes.
Select .. As Of SCN | Timestamp
SELECT * FROM DEPT
AS OF TIMESTAMP (Mention The Timestamp Here)
SELECT * FROM Ftest As Of SCN (Mention The SCN Here)
b) Oracle Flashback Version Query
This features helps to view all the versions of all the rows that ever existted in one or more tables in between two points in time or system change numbers (SCN).This feature also depends on UNDO data.
Select .. Versions Between SCN | Timestamp And SCN | Timestamp
Version Between SCN (Referencing A Start And End SCN)
Version Between TIMESTAMP (Referencing A Start And End Timestamp)
) Oracle Flashback Drop
Flashback drop is used to restore tables and depended objects accidentally dropped. After restoring the table will be renamed as its original whereas the indexes will have system generated names.
FLASHBACK TABLE DEPT TO BEFORE DROP;
Before doing flashback confirm that the dropped object has not been purged
Select OBJECT_NAME,ORIGINAL_NAME From User_Recyclebin;
d) Oracle Flashback Database
This feature is used to recover the database from logical corruptions to specified point in time/scn. To flashback the database you must have the SYSDBA privilege.
Flashback Database To SCN | Restore Point
FLASHBACK DATABASE TO TIMESTAMP(Timestamp value)
FLASHBACK DATABASE TO SCN (SCN valule)
We can also create a restore point and restore the database to the restore point.
CREATE RESTORE POINT dept_restore;
FLASHBACK DATABASE TO RESTORE POINT dept_restore;
e) Oracle Flashback Transaction Query
Flashback Transaction query allows viewing changes made by single transaction or all transactions during a period of time .This feature is used during perform analysis, audit transactions to check data at transaction level.
Transaction Query Features uses the FLASHBACK_TRANSACTION_QUERY view for retrieving transaction information.
SELECT Logon_User, Operation, Start_Timestamp, Undo_Sql
WHERE Xid In (
FROM Emp BETWEEN TIMESTAMP (Systimestamp - Interval '6' Minute) AND Systimestamp);
f) Oracle Flashback Table
Flashback Table feature helps to recover table or set of tables to particular timestamp or SCN to reverse unwanted updates.
To recover the table we need to set the below things.
- Requires undo data.
- Row movement must be enabled.
- Must have SELECT, INSERT, DELETE, and ALTER privileges on the table
Flashback Table .. To Scn | Timestamp
Flashback Table Dept To Timestamp (Timestamp Value)
Read the most discussed topics of DATAWAREHOUSE CONCEPTS- TOP 10 TOPICS OF 2014
**********Please share your valuable comments on Oracle Flashback technology