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 processedIf 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(*)
----------
5573Finally, 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 tableOne 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.