ORA-02002: error while writing to audit trail

ORA-02002: error while writing to audit trail

Error
ORA-02002: error while writing to audit trail

Cause
Error occurred due to insufficient space while writing the audit record in audit trail table.

Solution
1. On check the Alert log, we get the complete error as follows:
ORA-02002: error while writing to audit trail
ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0
ORA-01653: unable to extend table AUDSYS.CLI_SWP$16026a73$1$1 by 8192 in tablespace SYSAUX

We have three option:
1. Extend the existing tablespace.
2. Empty the AUDIT table
3. Disable the audit(its need restart of database)

1. Extend the existing tablespace SYSAUX
a) You found the tablespace name in alert log which is filled by Audit records.

set line 999 pages 999
col FILE_NAME format a50
col tablespace_name format a15
Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB" from dba_data_files where tablespace_name='SYSAUX';

TABLESPACE_NAME FILE_NAME                           AUT USEDSPACE GB MAXSIZE GB
--------------- ----------------------------------- --- ------------ ----------
SYSAUX          E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF   YES   31.9219    31.9999847

b) As see tablespace has one data file which is full. So, you need to add one more file.

alter tablespace SYSAUX add datafile 'E:\ORACLE\ORADATA\ORCL\SYSAUX02.DBF' size 1G autoextend on next 500M;

2. Empty the AUDIT trail table

-- take EXP backup of aud$ table.
TRUNCATE TABLE SYS.AUD$;

3. Disable the AUDIT
Note: before disable we need to implement any method from 1 or 2 points. So that it has enough space in tablespace for other records.

--check the audit parameter
SQL> show parameter audit_trail
NAME TYPE VALUE
------------ -------- ----------
audit_trail string DB
-- Disable the audit by setting
ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;
--Restart the database
Shutdown immediate
startup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.