The Blog from the DBA Classroom

By: Joel Goodman

Archive for May, 2009

Is Your Audit Trailing Behind?

Posted by Joel Goodman on 14/05/2009

Here is a recent twist to an old problem. The Oracle Audit Trail is written to a data dictionary table “sys.aud$” which is in the system tablespace. Over the years and releases many DBAs have wished to move “sys.aud$” (or “system.aud$” if OLS is installed) to another tablespace when auditing is heavily used to reduce system tablespace I/O. Since the advent of Fine Grain Auditing a similar desire has existed for “sys.fga_log$” which is used to contain fine grain auditing rows. Technically these are sys owned tables but do not contain metadata of the same sort as contained in other base tables and some do not consider them to be proper data dictionary tables.

Moving one or both of these tables to another tablespace is technically possible, but the situation is somewhat risky. Published technote 72460.1 explains how this is done :

1.  Restart the instance with AUDIT_TRAIL = none.

2.  Log in to SQL/PLUS as sys.

connect sys/<password>

3. Copy aud$ to system schema (or another schema) in another tablespace.

create table system.aud$ tablespace <tablespace name>as select * from aud$;

4.Create an index on the correct columns.

create index system.i_aud1 on system.aud$(sessionid, ses$tid);

5. Temporarily rename sys.aud$.

rename sys.aud$ to aud$_temp;

6. Create a view belonging to sys called aud$ based on the new aud$ table.

create view sys.aud$ as select * from system.aud$;

7. Login as system.

connect system/<password>

8. Grant appropriate privileges on the new view and table.

grant all on system.aud$ to sys with grant option;
grant delete on system.aud$ to delete_catalog_role;

But the same technote also contains other important statements:

1. That relocation of aud$ is not supported, although changing the default storage parameters except for “INITIAL” is supported.

2. That the technote was published externally because of “customer demand”.

3. Errors may occur if the tablespace containing the audit is offline.

In effect this is saying ” DBAs, you are on their own if you move aud$ but here is how to do it and it works in most cases”.

But there is good news. One of the requirements of the 10g and 11g Audit Vault feature was to provide the facility to manipulate the Audit Trail and this resulted in the creation of package DBMS_AUDIT_MGMT which provides the needed functionality for all audit trail managmenet including purging, table relocation and a few others. But use of the package outside of Audit Vault was not originally supported.  But from some of the restrictions have been relaxed so that support now exists for moving aud$ and fga_log$ to another tablespace. Furthermore there are patches for older versions from onwards to install this package as well.

Here is the procedure to move the audit trails:

Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–

The possible values for audit_trail_type are listed below but not all these types are suitable for tablespace relocation as some are used in other procedures within the package such as when purging which is beyond the scope of this post:

1. AUDIT_TRAIL_AUD_STD  - in sys.aud$

2. AUDIT_TRAIL_FGA_STD  - in sys.fga_log$

3. AUDIT_TRAIL_DB_STD   - both 1 and 2 above

4. AUDIT_TRAIL_OS       - OS audit trail

5.  AUDIT_TRAIL_XML      - Audit in XML OS files

6. AUDIT_TRAIL_FILES    - both 4 and 5 above

7. AUDIT_TRAIL_ALL      - 1,2,4,5 above

In addion to the DBMS_AUDIT_MGMT package there are also some dictionary views and the one we are interested in is:

Current Audit location Settings:

SQL> select audit_trail,parameter_name,parameter_value

—————————- ——————– ——————–

Have a look at technote 731908.1 for full details and don’t leave your audit behind.


Posted in Oracle | 7 Comments »