Oracle 11g Total Recall – Flashback in the hands of Database Designers and Application Developers, at last. And: the end of Journalling Tables

One of the new features in the Oracle 11g database is called Oracle Total Recall. This is called marketing! I have read some weblogs where people – mostly DBAs – are surprised at all this attention for Oracle Total Recall, as it seems little more than the Flashback technology that since Oracle 9i has come to the foreground in Oracle 10g with Flashback Query, Flashback Table (even to before undrop) and Flashback Database. How much more does Oracle 11g give us in this area?

Well, in my opinion: in Oracle 11g, for the first time, Flashback has become a tool for Database Designers and Application Developers. And that is pretty important news.

im
Flashback- in a nutshell – is the ability
to reconstruct data from an earlier point in time, using the UNDO data produced by the Oracle RDBMS. UNDO data is a key element in Oracle’s multi-versioning mechanism that ensures read-consistency while maintaining high degree of concurrency. When DML operations such as insert, update or delete happen on data, Oracle writes the data into an undo tablespace that is used not only for transaction rollbacks but also guaranteeing read consistency in a concurrent environment. Flashback exposes functionality to DBAs and other users of the database that has been available internally for some two decades.

Until now, Flashback has been a toy for DBAs and perhaps Application Managers who used it to recover from errors – frequently human errors. Using flashback features you can query past versions of data as well as perform change analysis and self-service repair to recover from logical corruption while the database is online. Flashback before 11g was a somewhat crude tool whose scope was fairly limited to several hours, days or weeks at the utmost.

One big (steaming) pile

Flashback prior to 11g was based on UNDO data being retained for somewhat longer than used to be the case with the ROLLBACK segments. If you have every seen an ORA-1555 Snapshot too old error, you know that UNDO data at some point is lost, the space it occupied used for more recent UNDO data. Flashback technology has DBAs allocate more space to store UNDO data and recycle the space on a much lower frequency. The longer you keep the UNDO data, the older the transaction records are and the further back in time the Flashback allows you to go. This applies to all aspects of Flashback – query, versions, table and database. In a very active database – millions of transactions per hour for example – a lot of UNDO data is produced and a flashback scope of two days can already requires many Gigabytes of UNDO data and space while in slower moving environments that same volume of UNDO data might allow flashing back a year or longer.

The DBA specifies how much space there is available for UNDO data and indirectly thus determines how far back in time we can reach (though this is primarily dependent on the number and size of transactions in the database). If the DBA wants to make certain there is always at least enough UNDO data to go back a certain period of time – say one month – he or she can set the undo retention period by setting the UNDO_RETENTION initialization parameter. The RDBMS will try – but not guarantee – to honor the Retention Period. To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it.

If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. Trying to perform a transaction whose UNDO records lead to overflow of the UNDO area will fail. Only once UNDO records for data as long as ago as the retention time can start being purged can new transactions be committed. Until such time, the database is effectively read-only.

Growth of UNDO data can be fairly rapid. It includes all types of data changes in the database. UNDO data management does not discern between the top priority manipulation of Customer and Order records on the one hand and the staging tables for Import and Export jobs, entries into Logging tables, records created as part of Job Scheduling and Advanced Queuing – and probably even Materialized Views?. The UNDO data available for Flashback operations consists of one big steaming hotchpotch pile of all types of records. For some of those, Flashback to a few days in the past may be all that is required, while for others we may require years of historical data. Catering for such diverse requirements is not available with Flashback 10g and before.

The only thing we as Database Designers and Application Developers can be really sure of in terms of Flashback Query is that Oracle saves undo data at least until the transaction has been committed. So using Flashback we can look back as far as the beginning of the current transaction, which can be useful for business rules like ‘salaries may not be increased by more than 10% in a single transaction’. However, that particular piece of functionality is also available through Autonomous Transactions that allow accessing data outside of the context of the current transaction(‘s changes).

Putting Flashback in the hands of Database Designer and Application Developers

The real importance of Oracle 11g Total Recall, or Flashback Data Archive as its key constituent is called, lies in making Flashback Query, Versions, Transaction and Table available as part of the Database Design and as reliable component in Application Development. What we can do in 11g is specify at a per table level which data should be retained in Flashback Data Archives, apart from the big steaming pile of UNDO data currently used for all Flashback operations (and of course still required for Flashback Database). This means that we can specify for the really important tables, the tables that we want historical records for, that they should be retained. This fine grained approach means that with relative small volumes of Undo Data, we can get very long retention (and flashback) times for selected tables!

For the first time, we can include the (Flashback) Retention Time in the design of our tables. And have our applications rely on it. Any table that until now we used a custom Journal Table approach for, can now be set up with a Flashback Data Archive. And that is all it takes to get a full history of all data and changes to the table. A history that is read-only – so no tampering with history! Every Flashback Data Archive has a Retention Time defined against it. As soon as their are records older than the Retention Time, those are purged from the archive, automatically!

Collecting historical data through the Flashback Data Archive method has minimal performance overhead: undo records are already created for every transaction. A light weight process marks DML operations on tracked tables for archiving. A new background process – fbda – will collect the associated UNDO records from the UNDO tablespace and copy them to the Flashback Data Archive.

Making use of the historical data in Flashback Data Archives is done through the “normal” Flashback Query functions – the AS OF query clause prime amongst them.  Using those historical records for Data Mining purposes seems tremendously attractive: with no effort at all, we are now able to track changes in in many different types of records. With very little effort we can include those changes and all the states the records have gone through in reporting and analysis. It is really time for Application Developers and Database Designers to learn about Flashback Query!

Note: The one thing the Flashback Data Archive does not give us is a record of which database user caused a change in a record, so our table may still need a ‘last user modified by’ column.

Setting up a table for ‘Total Recall’

Starting the historical archive for any table is quite easy to achieve. We need to satisfy a few generic conditions:

• a tablespace managed by Automatic Segment Space Management is required
• Automatic UNDO management must be enabled
• we need to have the FLASHBACK ARCHIVE ADMINISTER system privilege in order to create a new FDA
• we have to create a Flashback Data Archive in a tablespace managed by ASSM

Many tables can share a Flashback Data Archive for their historical data. Note that the Retention Time is set at the FDA level rather than the individual table level. To create a Flashback Data Archive, execute a statement such as:

CREATE FLASHBACK ARCHIVE MIDTERM_ARCHIVE
TABLESPACE HISTORY
RETENTION 5 YEAR;

Following these generic steps, we have just a single action to execute for individual tables:

ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE MIDTERM_ARCHIVE;

And that is all! From this point on, all states of the records in the EMPLOYEES table will be kept for 5 years! Getting to these records is as easy as using the AS OF clause in our SQL Queries. For example, to see all employee records as they were 5 years ago – note that this query is not likely to succeed as our FLASHBACK DATA ARCHIVE as only just been created and our database does probably not have retained 5 years worth of UNDO Data – is as easy as:

select *
from   employees as of timestamp (systimestamp - 5*365+1) employees_5_years_ago

Using Flashback Versions Query we have a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed in the interval specified. You invoke the Flashback Versions Query functionality by using the VERSIONS BETWEEN clause of the SELECT statement.

Flashback Versions Query offers new additional columns that provide transaction details on the row data that allow us to pinpoint when and how data is changed in the Oracle database.

• VERSIONS_XID – The transaction id that created this version of the row
• VERSIONS_OPERATION – The action that created this version of the row (such as delete, insert, and update)
• VERSIONS_STARTSCN – The SCN in which this row version first occurred
• VERSIONS_ENDSCN — The SCN in which this row version was changed.

The Flashback Versions Query is a powerful tool for the DBA to run analysis and answer the question, ‘How did this happen?’

Resources

Oracle White Paper on Flashback Data Archive.

Oracle Flashback – Overview