ORA-09817: Write to audit file failed

Few Days ago I faced this error when I am trying to connect to my Oracle Database :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 14:58:31 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba
ERROR:
ORA-09817: Write to audit file failed.
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

After this I checked space available in the FS. I got this :-

bash-3.2$ df -g

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

/dev/hd4          10.38      3.88   63%    12155     2% /

/dev/hd2          12.25      6.76   45%    97364     6% /usr

/dev/hd9var        5.50      5.03    9%    11606     1% /var

/dev/hd3           5.12      2.52   51%      438     1% /tmp

/dev/hd1           8.12      0.00  100%    76570    29% /home

/dev/hd11admin      1.12      1.12    1%        5     1% /admin

/proc                 -         -    -         -     -  /proc

/dev/hd10opt       5.50      1.69   70%    32636     8% /opt

/dev/livedump      0.25      0.25    1%        4     1% /var/adm/ras/livedump

/dev/fs1v0disk1    200.00    183.22    9%    10805     1% /disk1

/dev/fs1v0disk2    200.00    183.39    9%     3655     1% /disk2

/dev/fs1v0disk3    100.00     39.36   61%     5251     1% /disk3

/dev/fs1v0archive    200.00    199.90    1%       12     1% /archive_log

/dev/fs1v0redo     15.00     15.00    1%        4     1% /log_redo_group_1

/dev/fs1v0oracle     15.00      0.00  100%    42003    41% /oracle

/dev/fs1v0pacs    100.00     96.45    4%    17900     1% /amit

/dev/fs1v0other    100.00     98.98    2%        5     1% /other

/dev/fs1v0backup    100.00     99.86    1%        5     1% /backup

I found out that /oracle mount point is 100% full where oracle writes its audit logs.

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------      -----------    ------------------------------
audit_file_dest                    string        /oracle/oracle/admin/amit/adump
audit_sys_operations        boolean    FALSE
audit_syslog_level             string
audit_trail                           string        DB

After further findings I found out that listener.log file is around 4 gb in size and Alert log folder is 3.5 GB in size. PFB steps to reduce size of both:-

1. How to empty/remove Listener.log file :-

bash-3.2$ cd /oracle/oracle/diag/tnslsnr/amit3/listener/trace
bash-3.2$ ls -ltr

total 379432
-rw-r-----    1 oracle   oinstall  4193857778 Feb 25 15:48 listener.log


bash-3.2$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 25-FEB-2013 15:50:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.


LSNRCTL> set log_status OFF
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to OFF
The command completed successfully
LSNRCTL>exit

bash-3.2$ pwd
/oracle/oracle/diag/tnslsnr/amit3/listener/trace
bash-3.2$ mv listener.log listener_bak.log
bash-3.2$ touch listener.log
bash-3.2$ls -ltr

total 379432
-rw-r-----    1 oracle   oinstall  4193857778 Feb 25 15:48 listener_bak.log

-rw-r-----    1 oracle   oinstall                    0 Feb 25 15:48 listener.log

bash-3.2$ cp listener_bak.log /disk1

bash-3.2$ rm listener_bak.log

bash-3.2$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 25-FEB-2013 15:50:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.


LSNRCTL> set log_status ON

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> exit

bash-3.2$

2. Remove old alert log files from Alert log folder :-

bash-3.2$ cd /oracle/oracle/diag/rdbms/amit/amit/trace

bash-3.2$ pwd

/oracle/oracle/diag/rdbms/pacs/pacs/trace

bash-3.2$ ls -ltr|wc -l

    1018

bash-3.2$ ls -ltr |tail

-rw-r-----    1 oracle   oinstall       1207 Feb 25 12:26 pacs_ora_45547758.trc

-rw-r-----    1 oracle   oinstall         63 Feb 25 12:29 pacs_ora_23920872.trm

-rw-r-----    1 oracle   oinstall       1231 Feb 25 12:29 pacs_ora_23920872.trc

-rw-r-----    1 oracle   oinstall         63 Feb 25 12:33 pacs_ora_45547548.trm

-rw-r-----    1 oracle   oinstall       1207 Feb 25 12:33 pacs_ora_45547548.trc

-rw-r-----    1 oracle   oinstall         63 Feb 25 12:40 pacs_ora_40435750.trm

-rw-r-----    1 oracle   oinstall       1232 Feb 25 12:40 pacs_ora_40435750.trc

-rw-r-----    1 oracle   oinstall        104 Feb 25 15:01 pacs_ckpt_7602350.trm

-rw-r-----    1 oracle   oinstall       1762 Feb 25 15:01 pacs_ckpt_7602350.trc

-rw-r-----    1 oracle   oinstall    1381033 Feb 25 16:00 alert_amit.log

Except alert_amit.log remove all alert log files to a dfferent mount point where you have space available.

After alert log files have been removed again check FS space status :-

bash-3.2$ df -g

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

/dev/hd4          10.38      3.90   63%    12154     2% /

/dev/hd2          12.25      6.76   45%    97364     6% /usr

/dev/hd9var        5.50      5.03    9%    11606     1% /var

/dev/hd3           5.12      2.52   51%      439     1% /tmp

/dev/hd1           8.12      0.00  100%    76570    29% /home

/dev/hd11admin      1.12      1.12    1%        5     1% /admin

/proc                 -         -    -         -     -  /proc

/dev/hd10opt       5.50      1.69   70%    32636     8% /opt

/dev/livedump      0.25      0.25    1%        4     1% /var/adm/ras/livedump

/dev/fs1v0disk1    200.00    183.22    9%    10805     1% /disk1

/dev/fs1v0disk2    200.00    183.39    9%     3655     1% /disk2

/dev/fs1v0disk3    100.00     39.36   61%     5251     1% /disk3

/dev/fs1v0archive    200.00    199.90    1%       12     1% /archive_log

/dev/fs1v0redo     15.00     15.00    1%        4     1% /log_redo_group_1

/dev/fs1v0oracle     15.00      7.87   48%    42012     3% /oracle

/dev/fs1v0pacs    100.00     96.45    4%    17900     1% /amit

/dev/fs1v0other    100.00     98.98    2%        5     1% /other

/dev/fs1v0backup    100.00     99.86    1%        5     1% /backup

bash-3.2$

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 16:02:00 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

I hope this article helped you.

Regards,

Amit Rath