Manual cleaning of Oracle audit records

Manual cleaning of 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. Even so, it is easy to cause performance problems if you are not careful. The system tablespace is also exposed. The following describes how to strip the audit from the system tablespace and clear the Oracle audit records for your reference.

I. Audit 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 the current Oracle 11gR2

-- The following query shows that 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

Ii. Modify the audit storage tablespace
Add a tablespace to store audit logs.
SQL> CREATE tablespace audit_data datafile '/home/oracle/app/oradata/orcl/audit01.dbf'
2 SIZE 100 M autoextend on next 50 M;

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 the tablespace for storing audit data
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 message: Although we have used auto-extended tablespaces, the system still prompts insufficient space.

-- View the current audit data size, as shown in the following code: 1152 MB
SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name = 'aud $ ';

SEGMENT_NAME BYTES/1024/1024
----------------------------------------
AUD $1152

-- Adjust the data file size below
SQL> alter database datafile '/home/oracle/app/oradata/orcl/audit01.dbf' resize 1200 m;

Database altered.

-- Set the tablespace for storing audit data again.
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 entire process took 2m23s, mainly during which data was relocated.

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 preceding query, we can see that the AUD $ originally located in the system tablespace was migrated to AUDIT_DATA.
-- Correspondingly, the use of the AUDIT_DATA tablespace has increased, while the usage of the SYSTEM tablespace 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 TRAIL1
Iii. Clear audit records
Set the clearing interval through this process
SQL> BEGIN
2 DBMS_AUDIT_MGMT.init_cleanup (
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
4 default_cleanup_interval = & gt; 120/* hours */);
5 END;
6/

PL/SQL procedure successfully completed.

-- The following strictly checks 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 'lesham' 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 (*)
----------
5908086

SQL> select min (ntimestamp #) from aud $;

MIN (NTIMESTAMP #)
---------------------------------------------------------------------------
20-AUG-14 06.11.09.901253 AM

-- Set the 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 specified archive Interval
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS
-----------------------------------------------------------------------------------------------------------
Standard audit trail 0 09--15 -15 01.27.17.000000 PM + 00:00

-- Manually clear audit logs 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
SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

-- You can also create a purge Job to clear archived historical audit records.
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.

-- Job is used for cleaning in this test. Note that the above purge job is not created using DBMS_SCHEDULER.CREATE_JOB.
-- Execute a job to clean up the archive. After observation, because the redo log size is 50 MB, the switchover is frequent and takes 19 minutes.
-- With the Checkpoint not complete wait event, 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 (*)
----------
12

-- Check 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.

Iv. Summary
A. For Oracle 11g, the audit function is enabled by default. Therefore, if it is required to be enabled, consider the performance impact;
B. When audit is enabled, we recommend that you detach the audit from the system or sysaux tablespace and use a separate tablespace;
C. For the removal of historical audit logs, the performance impact during the clearing period should be considered;
D. the relocation process has started when DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION is called. If the audit log is large, the IO impact should be considered;
E. archiving should be set for clearing audit logs. Archived audit logs will be cleared;
F. You can also use the trunate table aud $ reuse storage and deallocate unconventional methods.