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 ping
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/ […]