Purge OS .aud file with SQL*Plus

Deleting .aud files may be a problem when you cannot reach server directly from OS side (security or sometimes firewall problems). 

However deleting them all without any additional checking, what is an easiest solution, was not something that I wanted. Additionally I was forced to leave database settings in the same setup as they were before purging files, what needs additional programming as well. This is why I was forced to introduce purge period (defined as timestamp variable) and introduce some additional code, detailedly explained inside script as comments, that will run on any env that I could find.

And this is how this So I was forced to wrote small script called purge_os_aud.sql which can directly delete .aud file on OS level, directly from SQL*Plus.

Live example

Situation on OS at the beginning:

@some_server:/orabase/product/12cR2/db_1/rdbms/audit > ls -ltr
total 30
-rw-r-----   1 oracle     dba            787 Dec  9 00:03 SOME_DB_ora_29590_20201209000317028931143795.aud
-rw-r-----   1 oracle     dba            814 Dec  9 00:03 SOME_DB_ora_29767_20201209000328231292143795.aud
-rw-r-----   1 oracle     dba            812 Dec  9 00:03 SOME_DB_ora_29590_20201209000328069648143795.aud
-rw-r-----   1 oracle     dba            816 Dec  9 00:03 SOME_DB_ora_29845_20201209000334147381143795.aud
-rw-r-----   1 oracle     dba            812 Dec  9 00:04 SOME_DB_ora_299_20201209000427939461143795.aud
-rw-r-----   1 oracle     dba            812 Dec  9 00:04 SOME_DB_ora_294_20201209000427414852143795.aud
-rw-r-----   1 oracle     dba            812 Dec  9 00:04 SOME_DB_ora_844_20201209000459038354143795.aud
-rw-r-----   1 oracle     dba            814 Dec  9 00:05 SOME_DB_ora_1117_20201209000507772692143795.aud
-rw-r-----   1 oracle     dba            814 Dec  9 00:05 SOME_DB_ora_1121_20201209000508072602143795.aud
-rw-r-----   1 oracle     dba            816 Dec  9 00:24 SOME_DB_ora_11275_20201209002414644280143795.aud
-rw-r-----   1 oracle     dba            816 Dec  9 00:24 SOME_DB_ora_11290_20201209002415858573143795.aud
-rw-r-----   1 oracle     dba            816 Dec  9 00:24 SOME_DB_ora_11285_20201209002415146598143795.aud
-rw-r-----   1 oracle     dba            816 Dec  9 00:27 SOME_DB_ora_13301_20201209002753537037143795.aud
-rw-r-----   1 oracle     dba            816 Dec  9 00:27 SOME_DB_ora_13305_20201209002753756343143795.aud
-rw-r-----   1 oracle     dba            881 Dec  9 05:18 SOME_DB_ora_24140_20201209051822299229143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:30 SOME_DB_ora_1003_20201209053043613964143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:31 SOME_DB_ora_1237_20201209053118186852143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:33 SOME_DB_ora_2144_20201209053328373759143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:38 SOME_DB_ora_4381_20201209053814441437143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:40 SOME_DB_ora_5934_20201209054017131313143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:43 SOME_DB_ora_7378_20201209054357866854143795.aud
-rw-r-----   1 oracle     dba            881 Dec  9 05:50 SOME_DB_ora_11079_20201209055048991541143795.aud
-rw-r-----   1 oracle     dba            882 Dec  9 05:57 SOME_DB_ora_14130_20201209055707639335143795.aud
-rw-r-----   1 oracle     dba            881 Dec  9 06:20 SOME_DB_ora_26917_20201209062009264057143795.aud
-rw-r-----   1 oracle     dba            877 Dec  9 06:27 SOME_DB_ora_398_20201209062714864529143795.aud
@some_server:/orabase/product/12cR2/db_1/rdbms/audit >

Purging
Now task to do this is pretty easy:

purge_os_aud.sql [before SYSTIMESTAMP]

07:13:58 SYS@SOME_DB>@purge_os_aud "to_timestamp ('09.12.2020 06:00:00.000000', 'dd.mm.yyyy hh24:mi:ss.FF')"

Purging OS audit files, CONTAINER_CURRENT, older than "to_timestamp ('09.12.2020 06:00:00.000000', 'dd.mm.yyyy hh24:mi:ss.FF')"
Please wait ...

OS AUDIT TRAIL temporary set to: 09.12.2020 06:00:00.000000000
AUDIT_TRAIL_OS filesystem on "/orabase/product/12cR2/db_1/rdbms/audit" cleared.

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.70

07:14:06 SYS@SOME_DB>

AFTER execution:

@some_server:/orabase/product/12cR2/db_1/rdbms/audit > ls -ltr
total 30
-rw-r-----   1 oracle     dba            816 Dec  9 00:24 SOME_DB_ora_11275_20201209002414644280143795.aud
-rw-r-----   1 oracle     dba            879 Dec  9 05:40 SOME_DB_ora_5934_20201209054017131313143795.aud
-rw-r-----   1 oracle     dba            882 Dec  9 05:57 SOME_DB_ora_14130_20201209055707639335143795.aud
-rw-r-----   1 oracle     dba            881 Dec  9 06:20 SOME_DB_ora_26917_20201209062009264057143795.aud
-rw-r-----   1 oracle     dba            877 Dec  9 06:27 SOME_DB_ora_398_20201209062714864529143795.aud
@some_server:/orabase/product/12cR2/db_1/rdbms/audit >

Is something wrong?

As some of you may noticed, still some .aud files were not deleted on OS side.
Why?
IMHO, reason is active sessions that are still running. You can check them with next script:

SELECT v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
  FROM cdb_unified_audit_trail u 
       FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY v.con_id, v.name, v.open_mode
ORDER BY v.con_id;

The End

Since 11g, Oracle introduced some nice methods for such a tasks. All code placed here was tested against 12.1 database, so on earlier releases this code may not work.

Hope this helps someone.

Cheers!