Figure 4 shows a base account table and its corresponding audit table. The base table holds current data with an update timestamp reflecting when the last update was performed, and the audit table holds the data as it looked before it was updated. It contains the update timestamp (UPDATE_TSP), which is the time the last update to the data occurred, and the entry timestamp (ENTRY_TSP), which is the time the current update or delete occurred and the row was placed in the audit table. There’s also a delete flag to indicate that the row was deleted from the base table and now the before image resides in the audit table.
For a point-in-time extract, let’s say you want to see your account balance on 12/24/02. You could use the query in Figure 5, which will look for the account information on or before that day. This query would return the following data from your audit table:
1234 350.00 Market 1111 01/01/02-00:00:00
Range images: There’s also the possibility of using these same tables to do range images. Let’s say you want to see how the account looked at a particular point in time, plus any changes since then. You could use the query in Figure 6, which would return the result from your account audit table (see Figure 7). This capability lets you view data as it changes over time.
Application Data Corruption Recovery
Table auditing and “as of ” imaging can be used to remove data that’s been corrupted by errant application programs. This is referred to as logical recovery, and it lets readers of the data see an image of data before it had been corrupted. This allows for instantaneous recovery from data corruption without incurring an outage. Sound like magic? Read on.