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!
