Monday, February 13, 2012

ORA-09817 error and full file systems

Let's say that you're trying to log into your database and suddenly got this error message:

oracle@mydb$ sqlplus '/ as sysdba'

SQL*Plus: Release - Production on Mon Feb 13 15:47:27 2012

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

ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-01075: you are currently logged on

Enter user-name:


This is an easy problem to guess and the key is in the message No space left on device. The instance cannot write audit files because the file system is full so you have to figure out why is that file system full:

oracle@mydb$ set | grep ORA

oracle@mydb$ strings $ORACLE_HOME/dbs/*mydb.ora | grep audit_file_dest

oracle@mydb$ df -k | egrep '(Filesystem)|(/oracle)'
Filesystem kbytes used avail capacity Mounted on
/dev/vx/dsk/orafs/oracle 16327680 16327680 0 100% /oracle

In this case the audit file destination is in the Oracle base file system, therefore the problem might be in other directory:

oracle@mydb$ du -k /oracle | sort -nr | head
16035094 /oracle
8412062 /oracle/diag
8297523 /oracle/diag/rdbms/mydb/mydb
8297523 /oracle/diag/rdbms/mydb
8297523 /oracle/diag/rdbms
8250027 /oracle/diag/rdbms/mydb/mydb/trace
6926099 /oracle/orahome
1631834 /oracle/orahome/mydirectory
938085 /oracle/orahome/mydirectory/somefiles
727017 /oracle/orahome/bin

oracle@mydb$ du -ka /oracle/diag/rdbms/mydb/mydb/trace | sort -nr | head
8250027 /oracle/diag/rdbms/mydb/mydb/trace
5737533 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trc
2506955 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trc
1946 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trm
969 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trm
457 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_3695.trc
380 /oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log
314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_8800.trc
314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_13295.trc
314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j000_23311.trc

Here is the problem: there are two very big trace files that occupy 8 gigabytes, half the file system size. When you know why your file system is full you have to decide what to do, like erasing, moving or compressing files, just be sure not to discard files you might need.

No comments:

Post a Comment