2

I am not seeing this in the documentation, but what happens when the Oracle 10.2 audit trail is full? Does it just not making any more audit records? Overwrites the oldest records? Shuts down the database? And is there any way I can change that?

Yes, I know 10.2 is not supported, but I don't have control over that. And yes there are procedures in place that should never need me to find out first-hand. But in cases of emergency failure of the auditing operations, what is the default behavior if the audit trail becomes full?

1
  • 3
    Your database halts... at least mine did long ago when I still had the table in the SYSTEM tablespace. Commented Jun 26, 2017 at 20:01

3 Answers 3

2

To further expand on my comment, the sys.aud$ table is stored, by default, in the SYSTEM tablespace. When the SYSTEM tablespace fills, your database halts.

There are a few options available to you to manage this, but these will depend on what version of 10g you have installed. As of the 10.2.0.5 patch, a new package, DBMS_AUDIT_MGMT was included that allows you to move the sys.aud$ table to a user Tablespace. If you are at this patch-level, move the audit table, plain-and-simple. Filling a user tablespace makes write operations against said user tablespace halt, but it won't bring everything else down like the SYSTEM tablespace filling will. This functionality was backported, but not to all 10.2 versions. You can check Doc ID 731908.1 on support.oracle.com to get all the details.

If you don't have the ability to upgrade to 10.2.0.5, you will need to regularly purge the sys.aud$ table by some other means so that it doesn't continue to grow and consume space. If it gets too unruly and you find you do need to "shrink" the table, you will likely need to TRUNCATE it to release the white space back to the tablespace. I've tried shrinking this file using the typical trick of enabling row movement and shrinking the table, but because records are often appended to the end of the sys.aud$ table and are added regularly, it often doesn't shrink much, if at all, which is why a TRUNCATE operation is often the most effective method here to free up some space.

Finally, your last option is to just disable the Audit. If it's not needed, this is probably the easiest option available to you.

1

Your database essentially freezes and does not allow any new connections or transactions that would be writing into the audit logs. Your only bet would be to connect directly on the server, e.g. "sqlplus / as sysdba" and then add space (or truncate it if auditing is not important to that database).

For this reason we have scripts to backup aud$ and truncate it regularly. We don't want horrible freezing going on in production...

You also want to move aud$ away from SYSTEM, something like this:

alter table sys.aud$ move tablespace aud_data;
alter table sys.aud$ move lob (sqlbind) store as (tablespace aud_data);
alter table sys.aud$ move lob (sqltext) store as (tablespace aud_data);
alter index sys.I_AUD1 rebuild tablespace aud_idx;
0

SYS.AUD$ is used for auditing certain aspects of the Oracle Database instance. The information is normally stored in the SYSTEM tablespace. The SYSTEM tablespace again is based on files that are stored on a filesystem. This can be either a Windows file system or a Linux file system.

A tablespace can consist of either a single BIGFILE definition or multiple smaller files that make up the SYSTEM tablespace.

The file(s) can be configured for auto-growth or be restricted to a certain size.

In the end files will eventually fill up because of the limits of the tablespace file(s) definition(s) or because of the limitations of the storage where the tablespace files are located.

The default behavior of the database instance is, if the tablespaces are full and (the) SYSTEM would want to write data to any of the tables contained in a tablespace be it SYS.AUD$ or anything else SYSTEM related, is to come to a screeching halt.

The reasoning behind this is (possibly) to ensure that anybody tampering with the system and being audited could not circumvent auditing by generating "noise" and filling up the AUDIT tablespace and then "hacking" into the system.

Depending on who set up your instance there may be jobs in place that will automatically purge the data in AUDIT logs (SYS.AUD$). If not, you can find a sample job in the following Oracle Note:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.