DBA Hub

📋Steps in this guide1/2

Purge AUD$ Table Using DBMS_AUDIT_MGMT

Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.

oracle configurationintermediate
by OracleDba
18 views
1

Overview

Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails. The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier. Follow below steps for puring aud$ table. :

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS AUD$ TABLE SYSTEM 176


SQL>
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TS_AUDIT');
END;
/

SQL> 2 3 4 5

PL/SQL procedure successfully completed.

SQL>
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS AUD$ TABLE TS_AUDIT 176

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000
 
SQL>
SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE TS_AUDIT STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL
 
14 rows selected.

SQL>
BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 12 /* hours */);
END;
/
 
PL/SQL procedure successfully completed.
2

Section 2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

YES

PL/SQL procedure successfully completed.

SQL>
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-30);
END;
/ 2 3 4 5 6
 
PL/SQL procedure successfully completed.
 

SQL> COLUMN audit_trail FORMAT A20
SQL> COLUMN last_archive_ts FORMAT A40
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS DATABASE_ID CONTAINER_GUID
-------------------- ------------ ---------------------------------------- ----------- ---------------------------------
STANDARD AUDIT TRAIL 0 11-OCT-23 11.29.39.000000 AM +00:00 552547857 183E654F83D9063AE0540010E0202A24

BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/

SQL[SYS@TCRMDB03]SQL>>]BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'PURGE_DB_AUDIT_TRAILS',
use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'set_last_archive_aud_timestamp',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

SQL>
SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL

SQL> SQL>
SQL> begin
dbms_audit_mgmt.set_audit_trail_property (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,
audit_trail_property_value => 300000);
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.
SQL> SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE 300000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL

Comments (0)

Please to add comments

No comments yet. Be the first to comment!