Manually clean up Oracle audit records

Oracle database audit is very powerful, usually including standard audit (including user level audit and system level audit) and fine-grained audit. However, carelessness can cause performance problems. At the same time, it will burst the system table space. The following content describes how to split the audit from the system tablespace and clean up the Oracle audit records for your reference.
1、 Audit related configuration
-- Environment
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /home/oraprod/app/product/11.2
.0/dbhome_1/rdbms/audit
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
Audit_trail string DB -- this value is the default configuration of current Oracle 11gr2
--As you can see from the following query, the current audit is in the system tablespace
SQL> col segment_name FOR a10
SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =‘AUD$‘;
OWNER                          SEGMENT_NA TABLESPACE_NAME
------------------------------ ---------- ------------------------------
SYS                            AUD$       SYSTEM
2、 Modify audit storage table space
Add a new table space to store audit logs
SQL> CREATE tablespace audit_data datafile ‘/home/oracle/app/oradata/orcl/audit01.dbf‘
2  SIZE 100M autoextend ON NEXT 50M;
SQL> @tbs_free.sql
TABLESPACE_NAME                USED (MB FREE (MB TOTAL (M PER_FR
------------------------------ -------- -------- -------- ------
AUDIT_DATA                            1    1,199    1,200  100 %
SYSAUX                            1,133       77    1,210    6 %
SYSTEM                            1,875       15    1,890    1 %
--Set audit data storage table space
SQL> BEGIN
2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
4      AUDIT_TRAIL_LOCATION_VALUE => ‘AUDIT_DATA‘
5);
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-46267: Insufficient space in ‘AUDIT_DATA‘ tablespace, cannot complete
Operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1576
ORA-06512: at line 2
--Error prompt: Although we use auto expand table space, we still don't have enough space
--View the current audit data size as follows: 1152mb
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=‘AUD$‘;
SEGMENT_NAME              BYTES/1024/1024
------------------------- ---------------
AUD$                                 1152
--Next, resize the data file
SQL> alter database datafile ‘/home/oracle/app/oradata/orcl/audit01.dbf‘ resize 1200m;
Database altered.
--Set the audit data storage table space again ok
SQL> BEGIN
2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
4      AUDIT_TRAIL_LOCATION_VALUE => ‘AUDIT_DATA‘
5);
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:23.10
--The whole process took 2m23s, mainly during the data relocation

SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =‘AUD$‘;
OWNER                          SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            AUD$                           AUDIT_DATA
SQL> @tbs_free.sql
TABLESPACE_NAME                USED (MB FREE (MB TOTAL (M PER_FR
------------------------------ -------- -------- -------- ------
AUDIT_DATA                        1,153       47    1,200    4 %
SYSAUX                            1,143       67    1,210    6 %
SYSTEM                              724    1,166    1,890   62 %
--From the above query, it can be seen that the original AUD $in the system tablespace has been migrated to audit data
--Accordingly, audit data table space usage has increased while system table space usage has decreased
--View audit data dictionary configuration information
SQL> col PARAMETER_NAME FOR a30
SQL> col PARAMETER_VALUE FOR a15
SQL> col AUDIT_TRAIL FOR a20
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
2  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
3  WHERE audit_trail = ‘STANDARD AUDIT TRAIL‘;
PARAMETER_NAME                 PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- --------------------
DB AUDIT TABLESPACE            AUDIT_DATA      STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000           STANDARD AUDIT TRAIL
3、 Clear audit records
Use this procedure to set the clear interval
SQL> BEGIN
2    DBMS_AUDIT_MGMT.init_cleanup(
3      audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
4      default_cleanup_interval => 120 /* hours */);
5 END;
6 /
PL/SQL procedure successfully completed.
--Next, strictly verify whether audit log clearing is enabled
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2    IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
3      DBMS_OUTPUT.put_line(‘YES‘);
4 ELSE
5      DBMS_OUTPUT.put_line(‘NO‘);
6 END IF;
7 END;
8 /
YES
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=‘AUD$‘;
SEGMENT_NAME        BYTES/1024/1024
------------------- ---------------
AUD$                           1152
SQL> select ‘Leshami‘ As author,‘http://blog.csdn.net/leshami‘ as Blog from dual;
AUTHOR BLOG
------- ----------------------------
Leshami http://blog.csdn.net/leshami
SQL> select count(*) from AUD$;
COUNT (*)
----
Five million nine hundred and eight thousand and eighty-six
SQL> select min(ntimestamp#) from aud$;
MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
20-AUG-14 06.11.09.901253 AM
--Set archive interval
SQL> BEGIN
2  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
3  audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
4  last_archive_time => SYSTIMESTAMP-10);
5 END;
6 /
PL/SQL procedure successfully completed
--View the set archive interval
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL            0 09-OCT-15 01.27.17.000000 PM +00:00
--Manually clean up the audit log by calling DBMS AUDIT MGMT.clean audit trail
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
DBMS_AUDIT_MGMT.clean_audit_trail
This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the
SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.
--You can also clean up archived historical audit records by creating a purge job
SQL> BEGIN
2    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
3      AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
4      AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
5      AUDIT_TRAIL_PURGE_NAME     => ‘Daily_Audit_Purge_Job‘,
6      USE_LAST_ARCH_TIMESTAMP    => TRUE
7);
8 END;
9 /
PL/SQL procedure successfully completed.

--In this test, jobs are used for cleaning. Note, the above purge jobs are not created by DBMS ﹣ scheduler.create ﹣ jobs
--The execution job is used to clean up the archive. Through observation, because the redo log size is 50MB, the switching is frequent, which takes 19 minutes
--At the same time, there is a checkpoint not complete waiting event. It can be seen that the redo size is too small
SQL> exec DBMS_SCHEDULER.RUN_JOB(job_name => ‘SYS.DAILY_AUDIT_PURGE_JOB‘);
PL/SQL procedure successfully completed.
Elapsed: 00:19:26.38
SQL> select count(*) from AUD$;
COUNT (*)
----
Twelve
--It is found that the space is not released after cleaning
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=‘AUD$‘;
SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
AUD$                                      1152
SQL> alter table sys.aud$ shrink space cascade;
alter table sys.aud$ shrink space cascade
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table sys.aud$ enable row movement;
Table altered.
SQL> alter table sys.aud$ shrink space cascade;
Table altered.
SQL> alter table sys.aud$ disable row movement;
Table altered.
--The following query shows that the space has been released
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=‘AUD$‘;
SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
AUD$                           .0625
Four, summary
a. For Oracle 11g, the audit function is enabled by default, so if it must be enabled, the performance impact should be considered;
b. When audit is enabled, it is recommended to separate audit from system or sysaux table space and use separate table space;
c. For the removal of historical audit logs, the performance impact during the removal period should be considered;
d. The process of calling DBMS audit mgmt.set audit trail location has started the relocation process. If the audit log is very large, IO impact should be considered;
e. To clear the audit log, you need to set an archive first, and the archived audit log will be cleared;
f. It can also be handled in an unconventional way through the trusted table AUD $reuse storage and deallocate.
Copyright notice: This article is the original article of blogger, welcome to spread, spread please note the source.
Manually clean up Oracle audit records