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 export Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionSQL> show parameter auditNAME type value =-------------------------------------- audit_file_dest string/home/oraprod/app/product/11.2. 0/dbhome_1/rdbms/auditaudit_sys_operations boolean FALSEaudit_syslog_level stringaudit_trail string DB -- this value is the default configuration of the current Oracle 11gR2 -- as shown in the following query, the current audit is in the system tablespace SQL> col segment_name FOR a10SQL> SELECT owner, segment_name, tablespace_name FROM dba_segments WHERE segment_name = 'aud $ '; OWNER SEGMENT_NA TABLESPACE_NAME =-------- specified SYS AUD $ SYSTEM

Ii. Modify the audit storage tablespace

CREATE tablespace audit_data datafile '/home/oracle/app/oradata/orcl/audit01.dbf' 2 SIZE 100 M autoextend on next 50 M; SQL> @ tbs_free.sqlTABLESPACE_NAME USED (MB FREE (MB TOTAL (M PER_FR bytes -------- ------ AUDIT_DATA 1 1,199 1,200 100% 1,133 SYSAUX 1,210 77 6% 1,875 SYSTEM 1,890 15 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 completeoperationORA-06512: at "SYS. DBMS_AUDIT_MGMT ", line 1576ORA-06512: at line 2 -- error prompt. Although we use auto-extended tablespace, the system still prompts insufficient space-view the current audit data size, as shown in the following code: 1152 MBSQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name = 'aud $ '; SEGMENT_NAME BYTES/1024/1024 ------------------------------------- AUD $1152 -- SQL> alter database datafile '/home/oracle/app/oradata/orcl/audit01.dbf 'resize 1200 m; database altered. -- set the tablespace Where audit data is stored again. OK SQL> BEGIN 2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (3 AUDIT_TRAIL_TYPE => AUDIT, 4 AUDIT_TRAIL_LOCATION_VALUE => 'audit _ data' 5; 7/PL/SQL procedure successfully completed. elapsed: 00:02:23. 10 -- the entire process took 2m23s, mainly during which data was migrated. SQL> SELECT owner, segment_name, tablespace_name FROM dba_segments WHERE segment_name = 'aud $ '; OWNER SEGMENT_NAME TABLESPACE_NAME specified parameter sys aud $ AUDIT_DATASQL> @ tbs_free.sqlTABLESPACE_NAME USED (mb free (mb total (M PER_FR limit -------- ------ AUDIT_DATA 1,153 1,200 4% SYSTEM 1,143 1,166 1,890 62% -- as shown in the preceding query, the AUD $ originally located in the system tablespace was migrated to AUDIT_DATA -- the corresponding AUDIT_DATA tablespace has been used and increased, the usage of SYSTEM tablespace decreases-view audit data dictionary configuration information SQL> col PARAMETER_NAME FOR a30SQL> col PARAMETER_VALUE FOR a15SQL> col AUDIT_TRAIL FOR a20SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL 2 from audit 3 WHERE audit_trail = 'standard audit trail '; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL created --------------- analytic db audit tablespace AUDIT_DATA standard audit traildb audit clean batch size 10000 STANDARD AUDIT TRAIL

Iii. Clear audit records

In this process, set the clearing interval SQL> BEGIN 2 DBMS_AUDIT_MGMT.init_cleanup (3 audit_trail_type => upper, 4 default_cleanup_interval => 120/* hours */); 5 END; 6/PL/SQL procedure successfully completed. -- The following strictly checks whether the audit log has enabled SQL> SET SERVEROUTPUT ONSQL> BEGIN 2 IF DBMS_AUDIT_MGMT.is_cleanup_initialized (optional) THEN 3 DBMS_OUTPUT.put_line ('yes '); 4 ELSE 5 DBMS_OUTPUT.put_line ('no'); 6 end if; 7 END; 8/YESPL/SQL procedure successfully completed. SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name = 'aud $ '; SEGMENT_NAME BYTES/1024/1024 ----------------------------- AUD $ 1152SQL> 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 (*) ---------- 5908086SQL> select min (ntimestamp #) from aud $; MIN (NTIMESTAMP #) --------------------------------------------------------------------------- 20-AUG-14 06.11.09.901253 AM -- sets the archive interval SQL> BEGIN 2 rows (3 audit_trail_type => rows, 4 last_archive_time => SYSTIMESTAMP-10); 5 END; 6/PL/SQL procedure successfully completed -- view the set archiving interval SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts; AUDIT_TRAIL RAC_INSTANCE AUDIT ------------------ ------------ upgrade standard audit trail 0 09-oct-1501.27.17.000000 PM + -- manually clear AUDIT logs BEGIN commit (audit_trail_type => BEGIN, BEGIN => TRUE) by calling AUDIT; END; /DBMS_AUDIT_MGMT.clean_audit_trailThis 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 create a purge Job to clear archived historical audit records. SQL> BEGIN 2 records (3 AUDIT_TRAIL_TYPE => tables, 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, a job is used for cleanup. Note that the above purge job is not created using DBMS_SCHEDULER.CREATE_JOB. Execute a job to clean up the archive. observe that the redo log size is 50 MB, switching is frequent and takes 19 minutes-with a Checkpoint not complete wait event, we can see 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. 38SQL> 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 $ 1152SQL> alter table sys. aud $ shrink space cascade; alter table sys. aud $ shrink space cascade * ERROR at line 1: ORA-10636: row movement is not enabledSQL> 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.