(this is my article published in SOUG newsletter 1/2006)
Oracle 10g comes with a number of flashback technologies. You can use these functionalities to go back to a point in time in the past on different levels in the event of a logical error. You can, on row-, table- and even on database level undo human errors now.
A variety of functionalities are summarized under the name of flashback, which in fact use very different technologies.
This article deals with all of these functionalities.
Let us first take a short look at the history of flashback functionalities.
Starting with Oracle 9i already the first flashback functionalities were made available:
The Oracle 9i Release 1 package DBMS_FLASHBACK enables a DBA to reset a session to a point in time in the past:
exec dbms_flashback.enbale_at_time (sysdate-10/(24*60))
resets the current session to a point in time 10 minutes ago. From now on, when the session opens a cursor, it will see the read consistent image of the committed information in the database as of 10 minutes ago. This was not possible for a session which is connected as sys. With Oracle 10g it is possible.
Flashback query, an Oracle 9i Release 2 functionality, enables us to select values from a table as of a point in time in the past:
SELECT *
FROM hr.employees
AS OF TIMESTAMP (sysdate-10/(24*60));
And already in Oracle 9i it was possible to create a table as a copy of another table as of a point in time in the past.
CREATE TABLE hr.emps
AS
SELECT *
FROM hr.employees
AS OF TIMESTAMP
(sysdate-10/(24*60));
This functionality was improved in Oracle 10g with a new technology (further details at the bottom of this page under flashback table).
All these Oracle 9i flashback functionalities use data from undo segments. Thus, precondition for all of them to work is that all before-images needed for a query must be still available. The new in Oracle 9i dynamic initialization parameter UNDO_RETENTION provides us with the possibility, to tell the server how long before images should be preserved at least, even after commit. In Oracle 9i there is no guarantee that all these images will be available for the period of undo_retention. The server will try its best, however under space pressure in the undo tablespace it will overwrite before images even before undo_retention is over. If before-images, which are needed for a flashback query or flashback transaction query are missing, an ORA-1555 snapshot too old will occur.
This is how it was in ORACLE 9i. In Oracle 10g we now can have an undo tablespace with guaranteed undo_rentention. If such an undo tablespace runs full, the application will stop because no further before-images can be saved in the undo segments. So we can now make it directive that the server will have to keep all the before images needed. It is not just a request anymore.
The implementation of undo_retention was modified in 10g R1 and again in 10g R2. Oracle can now auto tune undo_retention, which is another step towards a self-tuning database. This parameter will become a hidden _parameter in future releases probably.
Starting with Oracle 10g a number of new flashback extensions have been implemented such as:
flashback versions query
flashback transaction query
flashback database
flashback drop
flashback table
Let’s take a look at them one by one.
Flashback versions query is an extension of flashback query. Now we can look at all the different versions, which a particular row had over a period of time in the past defined by a pair of SCNs ore timestamps:
SELECT employee_id, salary, versions_xid
FROM hr.employees
VERSIONS BETWEEN SCN MINVALUE AND SCN MAXVALUE
WHERE department_id=90;
This query will return all the versions of the salaries in the employees table, which are still available. The versions_xid pseudocolumn returns the ID of the transaction.
Once we have retrieved the ID of a transaction, we can now use it in order to find out what else this particular transaction has changed, by using the Static Data Dictionary View flashback transaction query:
desc flashback_transaction_query
XID RAW(8) Transaction identifier
START_SCN NUMBER Transaction start system change number (SCN)
START_TIMESTAMP DATE Transaction start timestamp
COMMIT_SCN NUMBER Transaction commit system change number (null for active transactions)
COMMIT_TIMESTAMP DATE Transaction commit timestamp (null for active transactions)
LOGON_USER VARCHAR2(30) Logon user for the transaction
UNDO_CHANGE NUMBER Undo system change number (1 or higher)
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
SELECT ””||rowid||”” AS king_rowid
FROM hr.employees
WHERE employee_id=100;
SELECT xid,
commit_scn,
TO_CHAR(commit_timestamp,
‘yyyymmddhh24mihhsscc’)
AS commit_timestamp,
operation
FROM flashback_transaction_query
WHERE table_name = ‘EMPLOYEES’
AND table_owner = ‘HR’
AND row_id =& king_rowid
ORDER BY commit_timestamp;
This will return all the versions of a single row and the transaction IDs. It can be useful for instance in a multi developer environment to find out who changed what and what else was change by a specific transaction:
SELECT *
FROM flashback_transaction_query
WHERE xid = HEXTORAW (‘&XID’);
To query the view flashback_transaction_query you need the select any transaction system privilege
Flashback versions query and flashback transaction query again use undo data.
Another new flashback technology was introduced in Oracle 10g release 1 to extend the functionality of flashback at table level. It is called flashback table and it uses a mechanism called row movement, which was introduced for partitioned tables already in Oracle 8i to permit updating a partition key and let the row move from partition to partition.
With row movement enabled for a table in Oracle 10g we can flashback the table to a point in time or SCN in the past. Row movement just gives the permission to Oracle to change the ROWID of a row. A ROWID is assigned on commit and is usually immutable.
If you find out that you have chosen the wrong point in time you can simply flashback the table again. Here is how it works:
ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
FLASHBACK TABLE hr.employees TO TIMESTAMP to_timestamp(‘2005-27-10:19:00:00’, ‘YYYY-DD-MM:HH24:MI:SS’).
A flashback table to a specific SCN is also possible.
Flashback Database again is something completely different from all the other flashback functionalities from an implementation point of view. It enables the “roll back” of a complete database, including the data dictionary.
You first have to enable flashback logging before you can use flashback database. This is done by an entry in the controlfile (ALTER DATABASE FLASHBACK ON 
. The database needs to be running in ARCHIVELOG mode.
There are three relevant parameters for flashback database. All of them are dynamically changeable:
recovery_file_dest (location of the Flash recovery area)
recovery_file_dest_size (size of the Flash recovery area)
db_flashback_retention_target (time in minutes how far you can flashback the database)
The technology used behind the scenes are so called flashback logs which are written from the buffer cache to the new flashback buffer (max. 16MB) in the SGA. From there the also new background process CTWR copies them to a file which you can imagine as some kind of giant rollback segment in the flash recovery area on disk. This area can optionally be used by multiple databases and does not only contain the flashback logs but can be used for all other files needed for backup and recovery as well, such as archived redologs, backupsets and image copies. In this area Oracle creates a structure of subfolders for every database using this flash recovery area and for the different kinds of files stored in it including the flashback logs.
These flashback logs are just images of changed blocks stored on disk. Once the server starts generating them you can ‘rollback’ the entire database to a point in time or an SCN in the past. Like a rewind button of a tape-recorder. The amount of flashback logs that needs to be preserved on disk is determined by the initialization parameter flashback_retention, as well as the amount of DMLs happening on the system.
Now a TRUNCATE TABLE by mistake is not a case for a full restore and a point in time recovery any more. We simply ‘rewind’ the database to before the truncate statement. The server will, in a first step flashback the database to a point in time short before the truncate by using the flashback logs for restore. And then, as a second step, recover the database forward to the desired point in time or SCN by applying redo from archived redo logs. We can open the database READ ONLY first in order to check if the flashback database was successful. We can try again multiple times if the point in time we have flashbacked to was the wrong one.
The flash recovery area, which holds the flashback logs is self-maintaining. The system will delete flashback logs as soon as they are not needed any more.
Oracle recommends the use of this new disk area to store all information needed for backup and recovery, such as flashback logs, archivelogs, image copies and backupsets.
A flashback database statement will fail if there is not enough flashback logs to rollback the database to the desired point in time in the past.
Flashback Drop uses an entirely different technology:
We now have a “recycle bin” in Oracle where a dropped table ends up.
We can restore a dropped table from the recycle bin and we even can query a dropped table from within the recycle bin.
How does this work?
When a table is dropped in 10g the space it uses is freed for reuse by other segments. You can in fact see the freed extents of the dropped table in DBA_FREE_SPACE.
However, Oracle will try to not overwrite this space as long as possible. And as long as this has not happened yet, we can read from the table residing in the recycle bin.
We can do so by using the table’s new name. It has just been renamed internally and we now have to use the new name to query it. We can see which objects are in the recycle bin by querying DBA_RECYCLEBIN.
A DROP TABLE … PURGE statement bypasses the recycle bin and the table will be gone straight away.
This new drop-behavior can be switched off in Oracle 10g R2 in order to get the same behavior as we had in former releases by setting the initialization parameter recyclebin to off. It was a hidden _parameter in Oracle 10gR1.
Starting with Oracle 10g release 2 we can even flashback the database to a previous incarnation which means we can rollback the system to an SCN which was created before the last open resetlogs. A flashback database in Oracle 10g R1was restricted to the same incarnation of the database.
Additionally we now have the option to create so-called named restore points and even guaranteed named restore points. Like savepoints in a transaction named restore points can be used as “saved points” for the entire database to which you can go back to any time. This is a handy alternative to the use of SCNs or timestamps.
Again flashback logs are used. This functionality works even without having enabled flashback logging enabled.
Flashing back a database can save recovery time dramatically because in fact there is no need for a full restore like before Oracle 10g with an incomplete recovery. On the other hand flashback logging has a drawback, which Oracle describes as a minor one: its cost in performance is in the single digit range.
All together we have a lot of different functionalities now, which can be useful to repair logical errors. Now it is ‘just’ a matter of having enough space available on disk to store all the information need for flashback.
For further information please do not hesitate to contact me by email:
lutz.hartmann@oracle.com
Senior Instructor Oracle Education
Baden, Switzerland
November 2005
Posted by Lutz Hartmann 
Posted by Lutz Hartmann 
Posted by Lutz Hartmann 
