Oracle Audit Part 4 – Unified Auditing Problem

Part 4 in a loose series of blog posts about auditing; the new Oracle 12 feature. Unified auditing.monkey_examine

The intention behind Unified Auditing, as the name suggests, is to pull together all of the audit records from the disparate Oracle auditing locations into a single unified location. It captures standard audit information (including sys records normally written to the O/S),  FGA audit, record from Database Vault, Label Security, rman, etc.

Sounds great, and it is. 12.1 has a significant problem, but it seems a lot better in 12.2 (where I have not yet had time to spot any problems ūüôā )

The first thing that you need to know is that it is on by default.
It is gathering audit information in your 12C database right now, possibly duplicating any traditional auditing you may have switched-on yourself. By default the database is in “Mixed mode”, meaning you can use the old style of audit and unified auditing too. You need to re-link the oracle binary with the database down to enable exclusively-Unified auditing

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

(on windows, you need to move the correct DLL into place.)

So what is enabled by default?

1  select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME            ENABLED ENABLED_OPTION  ENTITY_NAME     ENTITY_ SUC FAI
--------------- ---------------------- ------- --------------- --------------- ------- --- ---
ALL USERS       ORA_SECURECONFIG       BY      BY USER         ALL USERS       USER    YES YES
ALL USERS       ORA_LOGON_FAILURES     BY      BY USER         ALL USERS       USER    NO  YES

What does that mean?

  1  select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE
  2    from audit_unified_policies
  3   where policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
  4*  order by 1,2,3,4,5;

POLICY_NAME          AUDIT_OPTION                             AUDIT_OPTION_TYPE  OBJECT_SCHEMA             OBJECT_NAME               OBJECT_TYPE
-------------------- ---------------------------------------- ------------------ ------------------------- ------------------------- --------------------
ORA_LOGON_FAILURES   LOGON                                    STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY PROCEDURE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE        SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY TABLE                          SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE LINK                      STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PROFILE                            STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ROLE                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER USER                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     AUDIT SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     BECOME USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY JOB                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY PROCEDURE                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY TABLE                         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DATABASE LINK                     STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DIRECTORY                         STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE EXTERNAL JOB                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PROFILE                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM                    SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ROLE                              STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY PROCEDURE                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY TABLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DATABASE LINK                       STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DIRECTORY                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE                  STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PROFILE                             STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ROLE                                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP USER                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      REMOTE_SCHEDULER_AGENT    ADD_AGENT_CERTIFICATE     PROCEDURE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      SYS                       DBMS_RLS                  PACKAGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXEMPT REDACTION POLICY                  SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE               SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY ROLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     LOGMINING                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     SET ROLE                                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     TRANSLATE ANY SQL                        SYSTEM PRIVILEGE   NONE                      NONE                      NONE

49 rows selected.

So that’s quite a lot of auditing on by default.

If you want to disable unified auditing policies, you need to use:

noaudit policy 
e.g. noaudit policy ora_secureconfig

So what’s the problem with Unified Auditing in 12.1?

Performance. It’s dreadful, and simply unusable.¬† Hwo about this simple select asking how much audit in the last 30 minutes from the UNIFIED_AUDIT_TRAIL view?

select count(*) from unified_audit_trail where EVENT_TIMESTAMP > systimestamp - interval '30' minutes;

It effectively blocked itself in a RAC environment, with one parallel query slave blocked by another – really not sure what was going on there but it never completed (well, I killed it after half a day).

I have seen a similar query in a non-RAC environment (or against table v$unified_audit_trail – which you really shouldn’t use as it can give an incomplete picture) run for well over an hour with a remarkably small data set. Not useful if you are trying to scrape content into an external monitor regularly…

MOS Article 2212196.1 explains how to resolve this in 12.1, by transferring all of the data to a table.

In 12.2, this is resolved by all data being stored in a table call AUDSYS.AUD$UNIFIED.
The table uses interval-based partitioning and is partitioned with a monthly interval:

...
PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)
 (PARTITION "AUD_UNIFIED_P0"  VALUES LESS THAN (TIMESTAMP' 2014-07-01 00:00:00')
...

Because it is a table, we can index it.It works like a table. It’s quick.

In my next audit article, I’ll look to explain how unified auditing works in a bit more depth, now that the performance no longer renders it unusable to queries.

Oracle Audit Control part 3 ‚Äď OS files

Just realized that I wrote a follow-up to Part 2 two years ago and never posted it! So here’s the stripped-out key facts to round off auditing control a bit more:

Deleting O/S audit files is, like, totally necessary. In Oracle 12C (12.1), they are created by default as AUDIT_SYS_OPERATIONS defaults to TRUE now, so you are going to be filling up your AUDIT_FILE_DEST whether you realized it or not.

[NOTE: In theory, this could end up being a performance problem as well as a space problem if you have many millions files in the AUDIT_FILE_DEST. In Oracle 11G, when allocating an AUDIT file, it would check to see if a file for that SPID already existed and add an incremental number, The act of calling vx_dirbread and vx_dirscan can get terribly slow if there are lots of files to wade through. This mechanism is different in 12C but I have not traced the kernel calls to see if the vx_ calls have gone away.]

To cleanup the O/S, we use the same commands as we did in Part 2, but changed to use an AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS

You also need to be aware of the RAC_INSTANCE_NUMBER. For database-level audit, it’s irrelevant. For single instance, it is also irrelevant, but with RAC, this parameter tells you which node to clean up as they are all individual installs with their own audit files (assuming a Grid Infrastructure install per node.)

Commands:

-- Show the BEFORE sizes
host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
begin

-- Loop around every instance in the RAC cluster and run the command
for i in (select inst_id from gv$instance)
loop

-- cleanup AUDIT_FILE_DEST (operating system audit files)
-- set cleanup for 60 days
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
LAST_ARCHIVE_TIME => systimestamp-60,
RAC_INSTANCE_NUMBER => i.inst_id );

-- And cleanup based upon that date
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);

end loop;

end;
/

-- And check the sizes AFTER
host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*

 


 

Output:

15:58:56 SYS @ ORCL1 > host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
776 /u01/app/oracle/admin/ORCL/adump
15:58:56 SYS @ ORCL1 > host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
694 /u01/app/oracle/admin/ORCL/adump
15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 > begin
15:58:56   2
15:58:56   3   for i in (select inst_id from gv$instance)
15:58:56   4   loop
15:58:56   5
15:58:56   6      -- cleanup AUDIT_FILE_DEST (operating system audit files)
15:58:56   7      -- set cleanup for 60 days
15:58:56   8      DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
15:58:56   9      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
15:58:56  10      LAST_ARCHIVE_TIME => systimestamp-50,
15:58:56  11      RAC_INSTANCE_NUMBER => i.inst_id );
15:58:56  12
15:58:56  13      -- And cleanup based upon that date
15:58:56  14      DBMS_AUDIT_MGMT.clean_audit_trail(
15:58:56  15        audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
15:58:56  16        use_last_arch_timestamp => TRUE);
15:58:56  17
15:58:56  18   end loop;
15:58:56  19
15:58:56  20  end;
15:58:56  21  /
PL/SQL procedure successfully completed.

15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 >
15:58:56 SYS @ ORCL1 > host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*
354 /u01/app/oracle/admin/ORCL/adump
15:58:57 SYS @ ORCL1 > host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
364 /u01/app/oracle/admin/ORCL/adump
15:58:57 SYS @ ORCL1 >
 


 

 
And just one final bit. If you change the default

AUDIT_TRAIL=DB to
AUDIT_TRAIL=DB, EXTENDED

the audit writes the first 2,000 characters of the SQL command to DBA_AUDIT_TRAIL.SQL_TEXT, so you get the full text of the SQL command audited, rather than just the action. Very handy!

Auditing Read-Only Standbys

If your company has a passing interest in database security, you are probably running some sort of session auditing at the very least [audit session] (although this can also be useful for troubleshooting connectivity issues). There’s a reasonable chance you’re also running some level of object auditing, or even DML access auditing if your employer so dictates:

Check access/change of objects in the DB
  1  select audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  union
  4  select privilege, success, failure
  5  from dba_priv_audit_opts
  6* order by 2,1
AUDIT_OPTION                                       SUCCESS                                  FAILURE
-------------------------------------------------- ---------------------------------------- -----------------
ALTER ANY PROCEDURE                                BY ACCESS                                BY ACCESS
ALTER ANY TABLE                                    BY ACCESS                                BY ACCESS
ALTER DATABASE                                     BY ACCESS                                BY ACCESS
ALTER PROFILE                                      BY ACCESS                                BY ACCESS
ALTER SYSTEM                                       BY ACCESS                                BY ACCESS
ALTER TABLE                                        BY ACCESS                                BY ACCESS
ALTER USER                                         BY ACCESS                                BY ACCESS
AUDIT SYSTEM                                       BY ACCESS                                BY ACCESS
CREATE ANY JOB                                     BY ACCESS                                BY ACCESS
CREATE ANY LIBRARY                                 BY ACCESS                                BY ACCESS
CREATE ANY PROCEDURE                               BY ACCESS                                BY ACCESS
CREATE ANY TABLE                                   BY ACCESS                                BY ACCESS
CREATE EXTERNAL JOB                                BY ACCESS                                BY ACCESS
CREATE LIBRARY                                     BY ACCESS                                BY ACCESS
CREATE PROCEDURE                                   BY ACCESS                                BY ACCESS
CREATE PUBLIC DATABASE LINK                        BY ACCESS                                BY ACCESS
CREATE SESSION                                     BY ACCESS                                BY ACCESS
CREATE TABLE                                       BY ACCESS                                BY ACCESS
CREATE USER                                        BY ACCESS                                BY ACCESS
DATABASE LINK                                      BY ACCESS                                BY ACCESS
DIRECTORY                                          BY ACCESS                                BY ACCESS
DROP ANY PROCEDURE                                 BY ACCESS                                BY ACCESS
DROP ANY TABLE                                     BY ACCESS                                BY ACCESS
DROP PROFILE                                       BY ACCESS                                BY ACCESS
DROP USER                                          BY ACCESS                                BY ACCESS
EXEMPT ACCESS POLICY                               BY ACCESS                                BY ACCESS
GRANT ANY OBJECT PRIVILEGE                         BY ACCESS                                BY ACCESS
GRANT ANY PRIVILEGE                                BY ACCESS                                BY ACCESS
GRANT ANY ROLE                                     BY ACCESS                                BY ACCESS
PROFILE                                            BY ACCESS                                BY ACCESS
PUBLIC DATABASE LINK                               BY ACCESS                                BY ACCESS
PUBLIC SYNONYM                                     BY ACCESS                                BY ACCESS
ROLE                                               BY ACCESS                                BY ACCESS
SYSTEM AUDIT                                       BY ACCESS                                BY ACCESS
SYSTEM GRANT                                       BY ACCESS                                BY ACCESS
USER                                               BY ACCESS                                BY ACCESS
CREATE JOB                                         BY SESSION                               BY SESSION

And you’re probably writing it into a database table [AUDIT_TRAIL=’DB’]

so how does that work if you open a Dataguard database read only? You are writing into sys.aud$ on the Primary, and that table is replicated to the Standby. So what happens?

From Oracle 11G, if you are running Dataguard and opening the standby up for read access, you may not notice the line in the ALERT log which reads:

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

So it starts writing down O/S audit trail files for all of your auditing options (well, the session connect and DML audit options – you can’t run DDL in a r/o DB). You might want to go and see just how many files it has written to [audit_file_dest], as you may be surprised at just how many are in there.

You may, one day, either run out of space or (more worryingly) have so many millions of files that it causes a performance problem when Oracle access the O/S directory. You might want to think about some sort of periodic clean-up job.

Oracle Audit Control part 2 – purging records

Now that you have got your audit table somewhere a little more sensible (i.e. not in the SYSTEM tablespace), there’s probably a policy about how many audit records should be kept.

Thoughtfully, the DBMS_AUDIT_MGMT package provides some of what you need to keep the audit records in check. However, a little more thought by Oracle would have helped. Lets see what I mean.

 

First we need to initialise for audit control. You can check to see if this has already been done as follows:

SET SERVEROUTPUT ON
 BEGIN
 IF
  DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
  DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
 ELSE
  DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
 END IF;
END;
/

NOTE: To do this for Fine-Grained auditing, you need to use the constant DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD instead, and check on table FGA_LOG$.

If cleanup is not initialised, you need to set it up as follows:

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP
 (AUDIT_TRAIL_TYPE         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL => 999 );
END;
/

Some VERY important things to note here:

  1. The DEFAULT_CLEANUP_INTERVAL doesn’t do anything (up to and including Rel 12.1). It’s for “future use”, apparently. However, if it is not specified, it has been associated with bugs in relation to cleanup to last timestamp not working and not cleaning anything up.
  2. If you have not already moved the audit tables AUD$ / FGA_LOG$ out of the SYSTEM tablespace, to any other tablespace this will move them for you, right now, whether desired or not, into SYSAUX.
  3. If you DEINIT_CLEANUP, it does not move the tables back to SYSTEM.

 

OK, we are initialised. We could call the creation of a purge job, which will wipe out all of our audit records (every 24 hours in this example), but that would be an unlikely requirement.

BEGIN
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 AUDIT_TRAIL_PURGE_INTERVAL => 24,
 AUDIT_TRAIL_PURGE_NAME => 'Purge_AUD$',
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

 

It’s MORE likely we want to wipe out the last N days worth of records. To do this we need to set the point (LAST_ARCHIVE_TIMESTAMP) from which we want to retain records and wipe out everything before that. So lets set for a 30 day retention.

BEGIN
 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 LAST_ARCHIVE_TIME => systimestamp-30,
 RAC_INSTANCE_NUMBER => 1 );
END;
/

And check it

select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL            RAC_INSTANCE LAST_ARCHIVE_TS
---------------------- ------------ ------------------------------------
STANDARD AUDIT TRAIL              0 17-MAY-14 11.00:01.000000 PM +00:00

Excellent. Now we create a job as before with “USE_LAST_ARCH_TIMESTAMP => TRUE” and all is good, EXCEPT that nothing is moving the timestamp forward.
The job will be called, purge the old records and that’s it. When it is next invoked, the timestamp will not have moved on. We therefore need another job to move the timestamp on… SO why bother setting up a job with these automatic routines if it doesn’t automate all of the requirement? Bit annoying that. I just create my own scheduled job with 2 calls, and forget the built-in (half a) job aspect of the management system:

BEGIN
 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 LAST_ARCHIVE_TIME => systimestamp-30,
 RAC_INSTANCE_NUMBER => 1 );

 DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

OK, the audit management system is pretty good; it deletes in batches, it works well, and it doesn’t need much from Oracle to make it much better. 7/10. Good but could do better.

You need to check out the associated views which show you the basic system config and what’s going on:

DBA_AUDIT_MGMT_CLEAN_EVENTS  Displays the cleanup event history
DBA_AUDIT_MGMT_CLEANUP_JOBS  Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_CONFIG_PARAMS Displays the currently configured audit trail properties
DBA_AUDIT_MGMT_LAST_ARCH_TS  Displays the last archive timestamps set for the audit trails

OK – that should keep things nice and tidy in the database. What about the audit files on the OS? .
Find out about that in Part 3

Oracle Audit Control part 1 – location of the audit table

One of the oldest problems with the Auditing capabilities within Oracle is that the SYS.AUD$ table resides in the SYSTEM tablespace. Unless you are rigorous in ensuring that your audit records are routinely pruned to keep the table manageable, it can single-handedly make the SYSTEM tablespace enormous.

Historically, we used to move the table and its associated objects to a new tablespace ourselves. In Oracle 7 is was a drop and re-create. Later we performed an alter table … move; command, coupled with an alter index rebuild. However, some bits frequently¬†got left behind doing this…

In Oracle 10, a new package appeared:¬†DBMS_AUDIT_MGMT. The procedure SET_AUDIT_TRAIL_LOCATION allowed you to move the table to a new tablespace. It didn’t work properly. It didn’t move indexes or LOB segments, and shouldn’t be used. However, roll on Oracle 11 and the (obvious) bugs have been ironed-out.

First of all, moving the table (NOTE: If the table is big, this may take quite a while. Only do this at a period of low system activity to avoid potential locking issues at the start and end of the move):

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');
END;
/

This works a treat in Oracle 11 and 12 for the standard audit trail, and for fine-grained auditing. It successfully moved every object associated with SYS.AUD$.

select owner,table_name,tablespace_name from dba_tables where table_name = 'AUD$'
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------
SYS                            AUD$                           SYSAUX

select owner,table_name,tablespace_name from dba_lobs where table_name = 'AUD$'
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------
SYS                            AUD$                           SYSAUX
SYS                            AUD$                           SYSAUX

select owner,table_name,tablespace_name from dba_indexes where table_name = 'AUD$'
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------
SYS                            AUD$                           SYSAUX
SYS                            AUD$                           SYSAUX

WARNING! Oracle still say that AUD$ should be in the system tablespace for upgrades. I can’t find anything that supercedes that, despite moving the table now being supported by an official package that works.

 

 

%d bloggers like this: