Managing AUD$ without DBMS_AUDIT_MGMT

Question

I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on Oracle DB version 12c (12.2.0.1.0).

With time (in 3 years) my AUD$ table grew to 500GB in size, especially because of it's 2 CLOB columns (SQLBIND and SQLTEXT).

I want to shrink storage consumed by AUD$ to minimum, without losing any audit data.

I came up with the following plan:

1. CREATE TABLESPACE TS_AUDIT_HISTORY

2. CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY

3. CREATE partitioned TABLE AUDIT_USER.AUDIT_HISTORY_TABLE in tablespace TS_AUDIT_HISTORY with:

   • 3.1. COMPRESS HIGH on LOB's (SQLBIND and SQLTEXT)

   • 3.2. COMPRESS HIGH on all partitions

4. INSERT INTO AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; COMMIT;

5. TRUNCATE TABLE SYS.AUD$

I haven't implemented my plan on any Production environment yet, since almost everyone advices against any other solution but DBMS_AUDIT_MGMT - which I don't want to use because it doesn't compress table's data / CLOB's).

But I have implemented my plan on a Development environment and results were amazing - After compressing I got from 500GB -> 50GB which is like 90% compress ratio !

My questions:

A) How 'smart' is to truncate SYSTEM's table AUD$, can it have any other impact besides the fact that user's won't be able to log in?

B) When I will be TRUNCATEing AUD$, table will be locked, which will prevent users to log in! - any way to 'bypass' that, since users being able to log in to DB is very important?

C) What do you think of my plan?

Any input / concern / comment is welcome.

STATUS UPDATE:

I successfully created a new tablespace, new user (with default new tablespace), new table with partitions and compression on it as well as on the LOB's and inserted all the data from SYS.AUD$ to the new table -I had to use parallel hint here and it still took me 6 hours. Afterwards I truncated AUD$. Original AUD$ was 500GB in size and now the new one (with all the records of course) is only 40GB in size. You were right guys, Truncate SYS.AUD$ finished in like 1 or 2 seconds.

So now I saved like 460GB.... but not really. Coz now these 460GB can be used only in AUD$'s tablespace (SYSAUX) and not in other tablespaces :/ I really hoped that TRUNCATE TABLE AUD$ DROP STORAGE; actually releases the allocated storage, but it does not. I can see that SYSAUX is 460GB less in size now, but these 460GB didn't free in ASM storage.

The only 100% way to reallocate the storage to be available as Usable storage in ASM as far as i know is:

1. Drop a table (which is of course not an option for AUD$)

2. export / import table (which is not an option since Oracle does not allow that for AUD$ table, already tried to export it with expdp but it didn't work).

So how do I actually make the 'freed' / deallocated storage available to ASM / other tablespaces?

Answer 1

A), B) - You performed the same in the test environment, so you have an estimation of how long it takes. If it is short enough to do it during operation, go for it. If not, you can do it during a scheduled downtime. Typically users do not log in every second or so, sessions get created then they are reused by applications or connection pools. If you have frequent logins to the database, that is flawed design. Also, what is more important? The audit or the users being able to log in. You can temporary disable auditing.

C) - COMPRESS HIGH requires the Advanced Compression option to be licensed. If you have the necessary license, go for it. If not, then you will have some explaining to do.

Generally about audit: instead of moving the audit inside the database, you should be working on removing it from the database. Storing and handling audit data inside the source database is a pain in the ass. The amount of audit data stored inside the database should be kept as low as possible. You have 500 GB of unneeded data that is included in your backups, copied to clone databases. You should regularly archive then delete audit data from the database. In my opinion, instead of storing any audit data in the database, sending the audit data to a remote server through syslog is a much better solution.