In the last article, we examined a common, manual method for enabling auditing on tables. With Oracle Database 11g, Total Recall is a built-in product that allows the same functionality. Total Recall stores the undo information for any audited table in a separate tablespace using flashback archiving. First, a privileged user needs to create a flashback archive:

SQL> CREATE TABLESPACE fbda 
   2    DATAFILE '/u02/app/oracle/oradata/orcl/fbda01.dbf' 
   3    SIZE 100M;

Tablespace created.

SQL> CREATE FLASHBACK ARCHIVE fbda 
   2    TABLESPACE fbda 
   3    RETENTION 1 YEAR;

Flashback archive created.

Then any user that needs the audit ability can be granted privileges on the flashback archive:

GRANT FLASHBACK ARCHIVE ON fbda TO fbda_user;

 

 

After that, the user can switch any table to use the flashback archive:

SQL> CREATE TABLE test AS
   2    SELECT object_id, object_name, status, created 
   3      FROM all_objects;

Table created.

SQL> ALTER TABLE test FLASHBACK ARCHIVE fbda;

Table altered.

Now, we can test out the Total Recall functionality. First, turn on AUTOTRACE and make some changes to the audited table:

SQL> SET AUTOTRACE ON
SQL> UPDATE test
   2    SET object_name = SUBSTR( object_name, 1, 29) || '-'
   3  WHERE MOD(object_id, 10) = 0;

5573 rows updated.

Execution Plan
----------------------------------------------------------                      
Plan hash value: 839355234                                                                                                                                      
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | UPDATE STATEMENT   |      |   556 | 17236 |   111   (1)| 00:00:02 |     
|   1 |  UPDATE            | TEST |       |       |            |          |     
|*  2 |   TABLE ACCESS FULL| TEST |   556 | 17236 |   111   (1)| 00:00:02 |     
---------------------------------------------------------------------------     

Statistics
----------------------------------------------------------                      
        226  recursive calls                                                    
       4238  db block gets                                                      
        439  consistent gets                                                    
          0  physical reads                                                     
    1622808  redo size                                                          
        830  bytes sent via SQL*Net to client                                   
        856  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
          8  sorts (memory)                                                     
          0  sorts (disk)                                                       
          5573  rows processed

If we compare this to the results from the manual audit, things look very similar to the non-audited output. However, there are a lot more recursive calls, so something is happening behind the scenes. Looking at portions of a tracefile, we see the following happening:

*********************************************************************

UPDATE test
   SET object_name = SUBSTR( object_name, 1, 29) || '-'
 WHERE MOD(object_id, 10) = 0

call     count       cpu    elapsed       disk      query        rows
------- ------  -------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0           0
Execute      1      0.06       0.06          0        388        5573
Fetch        0      0.00       0.00          0          0           0
------- ------  -------- ---------- ---------- ----------  ----------
total        2      0.06       0.06          0        388        5573

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  TEST (cr=388 pr=0 pw=0 time=0 us)
   5573   TABLE ACCESS FULL TEST (time=28366 us cost=111 size=17236)

*********************************************************************

SQL ID: 07hrbk6d0s7jr
Plan Hash: 0
insert into SYS_MFBA_STAGE_RID 
values
 (:OBJN, :RID, :ESCN)

call     count       cpu    elapsed       disk      query        rows
------- ------  -------- ---------- ---------- ----------  ----------
Parse       11      0.00       0.00          0          0           0
Execute     11      0.00       0.00          0         41        5573
Fetch        0      0.00       0.00          0          0           0
------- ------  -------- ---------- ---------- ----------  ----------
total       22      0.00       0.00          0         41        5573

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=3 pr=0 pw=0 time=0 us)

*********************************************************************

SQL ID: 1x01y3nyvthzz
Plan Hash: 0
insert into SYS_MFBA_TRACKED_TXN 
values
 (:USN, :SLOT, :SEQ)

call     count       cpu    elapsed       disk      query        rows
------- ------  -------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0           0
Execute      1      0.00       0.00          0          1           1
Fetch        0      0.00       0.00          0          0           0
------- ------  -------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)

*********************************************************************

So it’s clear that there are operations going on behind the scenes to enable the auditing. Compared to the manual audit, however, the performance is much better. The audit data is stored in a compressed format to minimize the storage impact.

We can use normal flashback queries to see the differences in the data. A simple example shows that the update worked as expected:

SQL> SELECT COUNT(*) 
   2   FROM
   3   (SELECT * FROM test
   4     MINUS
   5    SELECT * FROM test 
   6        AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR));

  COUNT(*)                                                                      
----------                                                                      
5573

Finally, we try to drop a table that has auditing enabled:

SQL> DROP TABLE test PURGE;
DROP TABLE test PURGE

           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

One of the features of Total Recall is that users can add auditing, but it can only be disabled by SYS or a user that has the FLASHBACK ARCHIVE ADMINISTER privilege. So connecting as SYS, we can turn off auditing:

SQL> ALTER TABLE fbda_user.test NO FLASHBACK ARCHIVE;

Table altered.

 

 

Then the table can be dropped as usual:

SQL> DROP TABLE test PURGE;

Table dropped.

 

 

Even though Total Recall is fairly integrated, it does have some drawbacks. As shown above, certain DDL operations are restricted against audited tables. Additionally, if the flashback archive tablespace is full, no changes can be made to the audited tables. Finally, Total Recall requires a separate license in addition to Oracle 11g Enterprise Edition.

 

Link to script that contains the examples in this post.

 

 

 

 

 

 

 

 

Comment