New Features of Oracle 11g flash back data archiving (1)

Introduction

Oracle Database 10 Gb provides five new flash back functions: Flash back version query, flash back transaction query, flash back deletion, flash back table and flash back database. Oracle Database 11gR1 provides an interesting new flash back function: Flash back data archiving, which allows an Oracle database administrator to maintain a record, records changes to all tables within the specified time range.

Many years ago-in fact, in the last century-I was a contractor for the human resources system of Western beverage manufacturers in the United States. One day, the Director of Human Resources asked me, "How do we configure a delta table for our employee data so that we can track all changes to employee data ?" The answer in my head is like the "magic map" CNN used to show and analyze the voting results during the recent U.S. presidential election:

I want to create a table named EMPLOYEES_HISTORY. Apart from adding a change in the date and time of the corresponding time stamp, it is exactly the same as the columns in the Employees table.

I want to put this new table in a separate tablespace.

I want to apply a foreign key to the EMPLOYEES_HISTORY table with integrity constraints to ensure data consistency between the two tables.

Finally, I want to create an AFTER... FOR EACH ROW trigger. Whenever an INSERT, UPDATE, or DELETE transaction occurs on the Employees table, the delta record will be inserted into the EMPLOYEES_HISTORY table.

Then some potential problems began to linger on me. What would happen when I did this? I kept asking myself if someone:

Disable the trigger on the EMPLOYEES_HISTORY table and forget to enable it again. What will happen?

You need to add a new column to the EMPLOYEES table or modify the data type, but you forgot to modify the corresponding column in The EMPLOYEES_HISTORY table synchronously. What will happen?

Although the EMPLOYEES and EMPLOYEES_HISTORY tables are modified in the same way, what happens if you forget to modify the trigger?

What happens if you want to delete the columns in the EMPLOYEES table or mark them as UNUSED?

What happens when the EMPLOYEES_HISTORY table is accidentally TRUNCATE?

What happens when the EMPLOYEES_HISTORY table is accidentally deleted? Even worse, what happens when the EMPLOYEES table is deleted?

The simple answer to these questions is that if these things happen, I have to explain to the customer why the delta data in the EMPLOYEES_HISTORY table is lost.

Use flash back data archiving to achieve "Full Oracle revocation"

If I have Oracle 11 GB, I will directly tell the customer that I can recover data in a few minutes, and Oracle 11gR1's new flashback data archiving (FBDA) function to create an independent Warehouse to keep the historical records of data changes in the table.

FBDA information is stored in a set of independent objects to track the transaction history of the target table. These objects are stored in one or more tablespaces. The FBDA table name is automatically generated by the system, you can view it in the DBA_FLASHBACK_ARCHIVE_TABLES view. Once FBDA is enabled on the table, you can view the history of all the reserved transactions. In this way, you do not need to create a corresponding history tracking table for the key transaction table, this is troublesome because complicated triggers are also compiled after the history tracking table is created to ensure that the historical data is precisely tracked.

The capture of historical information is also very efficient, because the Oracle 11g specially modified the kernel to minimize the performance overhead of historical data retention. In addition, the historical data is stored in compressed format, this reduces storage requirements. In addition, as long as FBDA is enabled on the table, Oracle will no longer invalidate the historical data (such as deleting or clearing the target table) or stop capturing (such as deleting columns in the target table ).

The following explains how FBDA works:

Flash back data archiving (FBDA) is a new background process that tracks and archives historical data of tables with FBDA enabled, it automatically collects and writes original data to the specified flash data archive through asynchronous processing.

When any data changes in the table that enables FBDA, FBDA first queries the UNDO data stored in the database buffer. If the data is still there, FBDA will use it, if the UNDO data has been removed from the database buffer, FBDA tries to retrieve the data from the UNDO segment in the UNDO tablespace.

When FBDA captures changed data, it sorts out the rows in the table with FBDA enabled, and then writes these rows to the historical table in FBDA. The data in these tables is compressed, just like the internal partitions.

Interestingly, FBDA does not include the original index for capturing data. You can create another index for historical data in FBDA.

Automatic retention policy: Based on similar retention requirements, historical table data in the FBDA object can be aggregated. Oracle 11g also provides a method to automatically clean data in FBDA, once the specified retention period is exceeded, the cleanup is automatically performed. Multiple tables can share the same data retention and cleanup policy, because FBDA consists of one or more tablespaces, therefore, you can create multiple FBDA, with each FBDA specifying a different retention period. In this way, you can create multiple retention policies that meet different requirements. Below are some common examples:

Reserved for ordinary short-term history queries for 90 days

Reserved for a normal long-term history query for one year

Reserved for national laws for 20 years