Oracle 11g flash back data archiving new features details

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 ...... The for each row trigger inserts a delta record in the EMPLOYEES_HISTORY Table whenever an INSERT, UPDATE, or DELETE transaction occurs on the Employees 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 ).

1 2 3 4 5 6 7 Next Page