Problem: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM
This error generally results when the aud$ table has a next_extent value that is greater than the available contiguous space in the SYSTEM tablespace. The aud$ table records logins to the database so when this error occurs, new database user connections may error. The simple fix to immediately relieve the hang on the database is to add more space to the SYSTEM datafiles. However, the long-term fix is to analyze the next_extent size to ensure it is reasonable and it would also be a good practice to move the audit tables out of the SYSTEM tablespace and into an auxiliary tablespace or create a dedicated tablespace sized appropriately for the audit tables. Note that in version 12c and higher Oracle introduced unified auditing which stores data differently than in 11g. This blog goes over the Oracle RDBMS version 11g solution.
This blog will cover the steps and scripts to view the present configuration of the AUD$ table in 11g, how to move the AUD$ table to another tablespace outside of the SYSTEM tablespace and how to set up a purge job to continuously purge records for the audit trail every 24 hours.
Disclosure: The data displayed in these examples are from a test database for example purposes only. And in no way reflect an actual production system. Please do not rely on your results to match this test data.
The first step is to document the present configuration for the AUD$ table and the audit trail configuration details:
set lines 300
col owner for a10
col segment_name for a12
col segment_type for a10
col tablespace_name for a12
col SEGMENT_SUBTYPE for a10
select owner, segment_name, segment_type,SEGMENT_SUBTYPE, tablespace_name, bytes/1024/1024 from dba_segments
where segment_name IN ('AUD
, 'FGA_LOG
)
OWNER SEGMENT_NAME SEGMENT_TY SEGMENT_SU TABLESPACE_N BYTES/1024/1024
---------- ------------ ---------- ---------- ------------ ---------------
SYS AUD$ TABLE MSSM SYSTEM 21504.44
SYS FGA_LOG$ TABLE MSSM SYSAUX .0625
col owner for a20
select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name IN ('AUD
, 'FGA_LOG
)
LAST_ANAL OWNER TABLE_NAME NUM_ROWS
--------- -------------------- ------------------------------ ----------
01-DEC-18 SYS AUD$ 10,704,439.5
SYS FGA_LOG$
PROMPT the aud$ table in 11g contains two CLOB columns sqlbind and sqltext
select owner,index_name,index_type,LAST_ANALYZED from dba_indexes where table_name IN ('AUD
, 'FGA_LOG
)
and owner='SYS';
OWNER INDEX_NAME INDEX_TYPE LAST_ANAL
-------------------- ------------------------------ --------------------------- ---------
SYS SYS_IL0000000090C00040$ LOB
SYS SYS_IL0000000090C00041$ LOB
SYS SYS_IL0000106265C00013$ LOB
SYS SYS_IL0000106265C00028$ LOB
set line 200
col column_name format a30
col segment_name for a40
col table_name for a10
SELECT table_name, column_name, segment_name, a.bytes/1024/1024/1024 Gbytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name IN ('AUD
, 'FGA_LOG
);
TABLE_NAME COLUMN_NAME SEGMENT_NAME GBYTES
---------- ------------------------------ ---------------------------------------- ----------
AUD$ SQLTEXT SYS_LOB0000000501C00041$ .000061035
AUD$ SQLBIND SYS_LOB0000000501C00040$ .000061035
FGA_LOG$ LSQLBIND SYS_LOB0000106265C00028$ .00012207
FGA_LOG$ LSQLTEXT SYS_LOB0000106265C00013$ .00012207
PROMPT Tablespaces holding audit data
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD
, 'FGA_LOG
)
ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM
select count(*) from sys.AUD$;
COUNT(*)
----------
18,144,731.5
select count(*) from sys.FGA_LOG$;
COUNT(*)
----------
0
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * 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
SELECT * FROM dba_audit_mgmt_last_arch_ts;
no rows selected
select min(ntimestamp#) from sys.aud$;
MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
28-JAN-19 12.20.33.754071 PM
From the above details, we can see that the AUD$ table is currently contained in the SYSTEM tablespace and consumes 21 GB of space. There is no “last archive timestamp” set. And all the auditing parameters are still set as the defaults. Additionally, this table has not been analyzed since 2018 and either has the two LOB segments attached to it.
The next step is to move the AUD$ SYS owned table out of the SYSTEM tablespace and into a different tablespace, such as SYSAUX.
The script below will create the new AUD$ table in a different tablespace. We do not need to change the user, only the tablespace.
PROMPT We want to lower the next_extent value to 50 – 100 mb
Alter table sys.aud$ storage(next 50m);
PROMPT These two scripts moves table AUD$ and FGA$, change the value SYSAUX if you have a user defined tablespace
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'SYSAUX');
END;
/
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'SYSAUX');
END;
/
set lines 300
col owner for a10
col segment_name for a12
col segment_type for a10
col tablespace_name for a12
col SEGMENT_SUBTYPE for a10
select owner, segment_name, segment_type,SEGMENT_SUBTYPE, tablespace_name,bytes/1024/1024 from dba_segments
where segment_name='AUD
;
The last step is to create a regular purge job so all records over 1 year are continuously purged daily.
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 24);
END;
/
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PROMPT Partial Purge using LAST ARCHIVE TIMESTAMP
CREATE OR REPLACE PROCEDURE AUD_SET_LAST_ARCH_TS
AS
retention NUMBER;
BEGIN
retention := 365 /* days */;
SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => SYSTIMESTAMP - retention
);
END;
/
PROMPT This command will create a job that uses the timestamp procedure AUD_SET_LAST_ARCH_TS
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_SET_LAST_ARCH_TS',
schedule_name => 'SYS.MAINTENANCE_WINDOW_GROUP',
job_class => 'DEFAULT_JOB_CLASS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN AUD_SET_LAST_ARCH_TS(); END;',
comments => 'Job to purge all audit trails'
);
SYS.DBMS_SCHEDULER.ENABLE(name => 'JOB_SET_LAST_ARCH_TS');
END;
/
PROMPT This command will create the purge job
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24,
audit_trail_purge_name => 'JOB_PURGE_AUDIT_TRAIL',
use_last_arch_timestamp => TRUE
);
END;
/
PROMPT This command will enable the job so it starts running
BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
audit_trail_purge_name => 'JOB_PURGE_AUDIT_TRAIL',
audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/
PROMPT displays job status for audit trail
col job_name for a30
col job_frequency for a30
select * from DBA_AUDIT_MGMT_CLEANUP_JOBS
PROMPT displays information about the history of audit trail cleanup or purge events.
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;