This post is also available in: Português
Oracle Database 11g comes with some audit options turned on by default, which over time can exhaust the SYSTEM tablespace without the DBA knowledge. However, this same version of database now offers some DBMS packages that can greatly facilitate the life of the administrator who wants to migrate audit to another tablespace or change the retention period of the data. Until version 10gR2, it was necessary to make this change in a more archaic way, which can be checked at Doc ID 1019377.6 on Oracle Support.
To begin, make sure your audit options are turned on.
In my case, I activated for all sys operations and configured to be saved also the run SQL (DB_EXTENDED). This can vary from case to case. Place the desired values and restart your database.
SQL> SHOW parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u02/admin/rmanbkp/adump audit_syslog_level string audit_sys_operations boolean TRUE audit_trail string DB_EXTENDED
Let's first find in which tablespace are the audit tables.
SQL> SELECT OWNER, 2 SEGMENT_NAME, 3 TABLESPACE_NAME 4 FROM DBA_SEGMENTS 5 WHERE SEGMENT_NAME IN ('AUD$', 'FGA_LOG$'); OWNER SEGMENT_NAME TABLESPACE_NAME ------------------------------ -------------------------------------------------------------------------------- ------------------------------ SYS FGA_LOG$ SYSTEM SYS AUD$ SYSTEM
Now let's check if the automatic purge infrastructure process of the audit data has already been initialized.
SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.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 / NO SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------- DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL AUDIT FILE MAX AGE 5 OS AUDIT TRAIL AUDIT FILE MAX AGE 5 XML 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 10 rows selected
If it is already enabled, skip to the next step. Otherwise, we will then initialize the infrastructure and set the cleaning interval (not the retention interval) for every 1 day (24 hours).
SQL> BEGIN 2 SYS.dbms_audit_mgmt.init_cleanup( 3 audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, 4 default_cleanup_interval => 24 /* hours */); -- PARAMETER NOT USED ON 11GR2 (FUTURE USE) 5 END; 6 / PL/SQL procedure successfully completed
Rechecking.
SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.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 SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------- DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL AUDIT FILE MAX AGE 5 OS AUDIT TRAIL AUDIT FILE MAX AGE 5 XML 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 DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL DEFAULT CLEAN UP INTERVAL 24 FGA AUDIT TRAIL 12 rows selected
Note the appearance of the last 2 rows with the value "DEFAULT CLEAN UP INTERVAL" set to 24 hours.
The next step is the creation of a unique tablespace to store audit data:
SQL> CREATE TABLESPACE AUDTBS DATAFILE '/u02/oradata/ORACL/audtbs01.dbf' SIZE 128M 2 AUTOEXTEND ON NEXT 64M MAXSIZE 2G 3 NOLOGGING default NOCOMPRESS ONLINE PERMANENT BLOCKSIZE 8K 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO; Tablespace created
Now we will move the tables AUD$ and FGA_LOG$ to the new tablespace created. It is recommended that these tables are empty to optimize the process. Truncate if possible or leave your data and wait.
SQL> BEGIN 2 SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( 3 audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4 audit_trail_location_value => 'AUDTBS'); 5 END; 6 / PL/SQL procedure successfully completed SQL> SQL> BEGIN 2 SYS.DBMS_AUDIT_MGMT.set_audit_trail_location( 3 audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, 4 audit_trail_location_value => 'AUDTBS'); 5 END; 6 / PL/SQL procedure successfully completed
Time to see if the tables were really moved. Check if the values of the parameter "DB AUDIT TABLESPACE" in the first query has been changed.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------- DB AUDIT TABLESPACE AUDTBS STANDARD AUDIT TRAIL DB AUDIT TABLESPACE AUDTBS FGA AUDIT TRAIL AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL AUDIT FILE MAX AGE 5 OS AUDIT TRAIL AUDIT FILE MAX AGE 5 XML 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 DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL DEFAULT CLEAN UP INTERVAL 24 FGA AUDIT TRAIL 12 rows selected SQL> SQL> SELECT OWNER, 2 SEGMENT_NAME, 3 TABLESPACE_NAME 4 FROM DBA_SEGMENTS 5 WHERE SEGMENT_NAME IN ('AUD$', 'FGA_LOG$'); OWNER SEGMENT_NAME TABLESPACE_NAME ------------------------------ -------------------------------------------------------------------------------- ------------------------------ SYS FGA_LOG$ AUDTBS SYS AUD$ AUDTBS
Now let's create a new Scheduler that will daily mark all audit records prior to 90 days as filed. Thus, the cleaning process can clean them.
SQL> BEGIN 2 DBMS_SCHEDULER.CREATE_JOB ( 3 job_name => 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP', 4 job_type => 'PLSQL_BLOCK', 5 job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => SYSDATE-90); END;', 6 start_date => sysdate, 7 repeat_interval => 'FREQ=HOURLY;INTERVAL=24', 8 enabled => TRUE, 9 comments => 'Create an archive timestamp' 10 ); 11 END; 12 / PL/SQL procedure successfully completed
Checking if the job was created.
SQL> SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%'; OWNER JOB_NAME JOB_STYLE JOB_CREATOR JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL ENABLED STATE SYSTEM NLS_ENV COMMENTS ------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SYS DAILY_AUDIT_ARCHIVE_TIMESTAMP REGULAR SYS PLSQL_BLOCK BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_ 22/05/13 13:29:13,000000 -03:00 FREQ=HOURLY;INTERVAL=24 TRUE SCHEDULED TRUE NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp
Finally, we will create the process to daily clean the records that were marked by the previous job.
SQL> BEGIN 2 SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( 3 AUDIT_TRAIL_TYPE => SYS.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
Checking if the job was created.
SQL> SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS; JOB_NAME JOB_STATUS AUDIT_TRAIL JOB_FREQUENCY -------------------------------------------------------------------------------- ---------- ---------------------------- -------------------------------------------------------------------------------- DAILY_AUDIT_PURGE_JOB ENABLED STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24 SQL> SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%'; OWNER JOB_NAME JOB_STYLE JOB_CREATOR JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL ENABLED STATE SYSTEM NLS_ENV COMMENTS ------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SYS DAILY_AUDIT_ARCHIVE_TIMESTAMP REGULAR SYS PLSQL_BLOCK BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_ 22/05/13 13:29:13,000000 -03:00 FREQ=HOURLY;INTERVAL=24 TRUE SCHEDULED TRUE NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp SYS DAILY_AUDIT_PURGE_JOB REGULAR SYS PLSQL_BLOCK BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(1, TRUE); END; 22/05/13 13:30:13,003284 AMERICA/RECIFE FREQ=HOURLY;INTERVAL=24 TRUE SCHEDULED TRUE NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Audit clean job = 'Daily_Audit_Purge_Job'
Now, I recommend removing all of the default audit options of Oracle and reapplying them only for executions "Whenever Successful", not commands that execution failed for lack of privileges.
SQL> noaudit all; SQL> noaudit all privileges; SQL> noaudit exempt access policy; Noaudit succeeded Noaudit succeeded Noaudit succeeded sql> audit all by access whenever successful; -- Add 27 AUDIT options sql> audit alter any table by access whenever successful; sql> audit create any table by access whenever successful; sql> audit drop any table by access whenever successful; sql> audit create any procedure by access whenever successful; sql> audit drop any procedure by access whenever successful; sql> audit alter any procedure by access whenever successful; sql> audit grant any privilege by access whenever successful; sql> audit grant any object privilege by access whenever successful; sql> audit grant any role by access whenever successful; sql> audit audit system by access whenever successful; sql> audit create external job by access whenever successful; sql> audit create any job by access whenever successful; sql> audit create any library by access whenever successful; sql> audit create public database link by access whenever successful; sql> audit exempt access policy by access whenever successful; sql> audit alter user by access whenever successful; sql> audit create user by access whenever successful; sql> audit role by access whenever successful; sql> audit create session by access whenever successful; sql> audit drop user by access whenever successful; sql> audit alter database by access whenever successful; sql> audit alter system by access whenever successful; sql> audit alter profile by access whenever successful; sql> audit drop profile by access whenever successful; Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded
Verifying..
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS order by 3; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER ANY PROCEDURE BY ACCESS NOT SET ALTER ANY TABLE BY ACCESS NOT SET ALTER DATABASE BY ACCESS NOT SET ALTER PROFILE BY ACCESS NOT SET ALTER SYSTEM BY ACCESS NOT SET ALTER USER BY ACCESS NOT SET CLUSTER BY ACCESS NOT SET CONTEXT BY ACCESS NOT SET CREATE ANY JOB BY ACCESS NOT SET CREATE ANY LIBRARY BY ACCESS NOT SET CREATE ANY PROCEDURE BY ACCESS NOT SET CREATE ANY TABLE BY ACCESS NOT SET CREATE EXTERNAL JOB BY ACCESS NOT SET CREATE PUBLIC DATABASE LINK BY ACCESS NOT SET CREATE SESSION BY ACCESS NOT SET CREATE USER BY ACCESS NOT SET DATABASE LINK BY ACCESS NOT SET DIMENSION BY ACCESS NOT SET DIRECTORY BY ACCESS NOT SET DROP ANY PROCEDURE BY ACCESS NOT SET DROP ANY TABLE BY ACCESS NOT SET DROP PROFILE BY ACCESS NOT SET DROP USER BY ACCESS NOT SET EXEMPT ACCESS POLICY BY ACCESS NOT SET GRANT ANY OBJECT PRIVILEGE BY ACCESS NOT SET GRANT ANY PRIVILEGE BY ACCESS NOT SET GRANT ANY ROLE BY ACCESS NOT SET INDEX BY ACCESS NOT SET MATERIALIZED VIEW BY ACCESS NOT SET MINING MODEL BY ACCESS NOT SET NOT EXISTS BY ACCESS NOT SET PROCEDURE BY ACCESS NOT SET PROFILE BY ACCESS NOT SET PUBLIC DATABASE LINK BY ACCESS NOT SET PUBLIC SYNONYM BY ACCESS NOT SET ROLE BY ACCESS NOT SET ROLLBACK SEGMENT BY ACCESS NOT SET SEQUENCE BY ACCESS NOT SET SYNONYM BY ACCESS NOT SET SYSTEM AUDIT BY ACCESS NOT SET SYSTEM GRANT BY ACCESS NOT SET TABLE BY ACCESS NOT SET TABLESPACE BY ACCESS NOT SET TRIGGER BY ACCESS NOT SET TYPE BY ACCESS NOT SET USER BY ACCESS NOT SET VIEW BY ACCESS NOT SET 47 rows selected SQL>
Done, now you no longer need to worry about the uncontrolled growth of your SYSTEM tablespace. The audit is isolated in a dedicated tablespace.
Have you enjoyed? Please leave a comment or give a 👍!
5 comments
1 pings
Skip to comment form
good documnet. it worked for me in my prod enviroenment.
Thanks.
do you know how to reset the PARAMETER_VALUE, I put 150 and I need this value set 24
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE AUDTBS STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE AUDTBS FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML 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
DEFAULT CLEAN UP INTERVAL here(24) STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL here (24 )
Author
Which parameter value you need to change from 24 to 150?
Great!!! Thanks for very clear explanation. Any way to take backup of aud$ table.
Hello, how can i change the default_cleanup_interval?
[…] https://www.dbarj.com.br/en/2013/05/changing-audit-tablespace-create-purge-job-11g/ […]