Oracle Total Recall for Tracking History

Oracle 11g Flashback Data Archive (Oracle Total Recall), meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities — say, the trail of changes to customer’s data needs to be tracked. Such requirements becomes trivial with Total Recall, eliminating a huge chunk of coding and testing effort.

This article will show you an algorithm to track history in the absence of Total Recall, and a demo of achieving the same result effortlessly with Total Recall.

Without Total Recall: Tracking History the Old-Fashioned Way

Let’s take the typical audit trail requirement: in an application maintains customer data in table CUSTOMER, any change to the data — e.g. change of name, location, etc. – needs to be recorded with the timestamp of the change.

The traditional design pattern to implement this feature:

1. Create a shadow table of CUSTOMER, say CUSTOMER_HIST, with all the columns of CUSTOMER + additional version, timestamp columns as required. The unique key of CUSTOMER_HIST would be a combination of the primary key of CUSTOMER + the version number.
2. For any update to CUSTOMER, trigger an insert in CUSTOMER_HIST to preserve the row’s values before update.
3. A SELECT on CUSTOMER_HIST with the primary key of CUSTOMER, sorted by date, gives the full audit trail of changes to a given customer’s data.

With Total Recall, implementing audit trail requires no additional coding to maintain history. A case study on table CUSTOMER to demonstrate how this can be done.

Step 1: Create Flashback Data Archive (FBDA)

Connect as administrator with the following privileges:

System Privilege: FLASHBACK ARCHIVE ADMINISTER
Object Privilege: FLASHBACK ARCHIVE

Create a flashback archive and add it to a tablespace to store data modifications. A retention policy can be specified to say how long the history needs to be maintained. For this case study, we’re using a retention policy of a year.

SQL> create tablespace fbda_tbs
     2   datafile'/app/trdemo/fbda_tbs.dbf'
     3   size 500M autoextend on;

Tablespace Created.

SQL> create flashback archive fbda
    2    tablespace fbda_tbs retention 1 year;

Flashback archive created.

Step 2: Assign Candidate Table to FBDA

Connect as schema owner and add the candidate table for history maintenance into the flashback archive.

SQL> -- Candidate table for history tracking
 Name              Null?    Type
 ----------------- -------- ------------
 CUST_ID                    NUMBER(3)
 NAME                       VARCHAR2(20)
 CITY                       VARCHAR2(20)
 CREATED_DTTM               TIMESTAMP(6)
 UPDATED_DTTM               TIMESTAMP(6)

SQL> select * from customer;

CUST_ID NAME         CITY    CREATED_DTTM UPDATED_DTTM
------- ------------ ------- ------------ ------------
      1 John Smith   Seattle 20-JAN-2014  20-JAN-2014
                             12:34:10     12:37:24

      2 Paul Taylor  Chicago 20-JAN-2014  20-JAN-2014
                             12:34:11     12:34:11

SQL>-- Enable history tracking
SQL> alter table customer
  2  flashback archive fbda;

Table altered.

Step 3: Update Rows in Candidate Table — and Watch the Action!

We’ll perform two updates to the main table:

1. Change the city of Cust Id 1 from Seattle to Austin
2. Change the name of Cust Id 1 from ‘John Smith’ to ‘John Smythe’

After each update, we’ll check the contents of the table CUSTOMER and its history.

SQL> -- Update 1: Change city
SQL> update customer
  2  set city = 'Austin'
  3    , updated_dttm = systimestamp
  4  where cust_id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> -- After Update 1:
SQL> -- Data in main table
SQL> select * from customer
  2  where cust_id = 1;

CUST_ID NAME         CITY    CREATED_DTTM UPDATED_DTTM
------- ------------ ------- ------------ ------------
      1 John Smith   Austin  20-JAN-2014  20-JAN-2014
                             12:34:10     12:37:24
SQL> -- After Update 1:
SQL> -- Data in archive
SQL> select *
  2  from customer
  3  versions between timestamp
  4  to_timestamp('20-JAN-2014 12:34:10',
  5               'DD-MON-YYYY HH24:MI:SS')
  6          and systimestamp
  7  where cust_id = 1;

CUST_ID NAME         CITY    CREATED_DTTM UPDATED_DTTM
------- ------------ ------- ------------ ------------
      1 John Smith   Austin  20-JAN-2014  20-JAN-2014
                             12:34:10     12:37:24

      1 John Smith   Seattle 20-JAN-2014  20-JAN-2014
                             12:34:10     12:34:10

After Update 1 (change city), the record for Cust Id 1 in the main table reflects the update (city = Austin), and the archive shows two records: one pre-update (city = Seattle) the other post-update (city = Austin). Now we’ll apply another change, to the name this time: after this one, the archive should show three records, one for each change.

SQL> -- Update 2: Change name
SQL> update customer
  2  set name = 'John Smythe'
  3    , updated_dttm = systimestamp
  4  where cust_id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> -- After Update 2:
SQL> -- Data in main table
SQL> select * from customer
  2  where cust_id = 1;

CUST_ID NAME         CITY    CREATED_DTTM UPDATED_DTTM
------- ------------ ------- ------------ ------------
      1 John Smythe  Austin  20-JAN-2014  20-JAN-2014
                             12:34:10     13:21:37


SQL> -- After Update 2:
SQL> -- Data in archive
SQL> select *
  2  from customer
  3  versions between timestamp
  4  to_timestamp('20-JAN-2014 12:34:10',
  5               'DD-MON-YYYY HH24:MI:SS')
  6          and systimestamp
  7  where cust_id = 1;

CUST_ID NAME         CITY    CREATED_DTTM UPDATED_DTTM
------- ------------ ------- ------------ ------------
      1 John Smythe  Austin  20-JAN-2014  20-JAN-2014
                             12:34:10     13:21:37

      1 John Smith   Austin  20-JAN-2014  20-JAN-2014
                             12:34:10     12:37:24

      1 John Smith   Seattle 20-JAN-2014  20-JAN-2014
                             12:34:10     12:34:10

The entire trail of changes is available with Total Recall – each update to the main table shows up as a new record in the archive.

Summary

Oracle Total Recall tracks all transactional changes to a table – this feature can be very useful for addressing the typical end-user requirements of auditing/history maintenance for tables, with next to no coding and testing effort.