How to resolve ORA-09925 ?

This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual…

There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit].

1) AUDIT_FILE_DEST is not writable

$ env
_=/usr/bin/env
ORACLE_SID=FOO
TERM=dtterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3
PWD=/u01/app/oracle/product/10.2.0/db_3
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -
root's Password:
# mkdir /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.

2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable ❗

$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump
drwxr-xr-x 2 root dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 09:02:29 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 09:02:48 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> quit

3) $ORACLE_HOME/rdbms/audit is not writable

$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 08:48:09 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit
drwxr-x--- 2 root dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 08:49:12 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> quit

4 thoughts on “How to resolve ORA-09925 ?”

  2. Neil

    Generic UNIX and Oracle.
    Specific Installation: Solaris Sparc 64bit/Oracle 9.2.06

    I have for a couple of months periodically been troubled by an intermittent ORA-09225. Every now and then on one db instance one of these would pop up with the accompanying message “SVR4 Error: 13: Permission denied”.

    Cause:
    Sys Admins (root) were required to secure the audit trail files (SOX compliance) so created a once a day job to ‘chown root’ then ‘chmod 444’ each audit file so that DBA could still report on the contents of the files but not change them.

    Problem exhibited when audit filenames were duplicated and Oracle could not overwrite an existing root/444 file. Old audit files were removed after six months so all possible filenames were never exhausted.

    Other db instances did not exhibit this characteristic as there was very little maintenance and no regular jobs that generated auditable events. The database in question had a daily job that required the script to log in “/ as sysdba”, thus generating multiple audit files per day.

    Solution:
    Change root job to move the files out of the way so that there would be no further filename conflict.

    General comment:
    Don’t question how effective a once a day job is at protecting the audit files from a compromised Oracle account. It obviously isn’t effective at all. Compliance is compliance and the company auditors went away happy with another item checked off their list.

  3. David B

    Thanks soooo much for this post!! I am also on AIX and my ?/rdbms/audit dir some how disapeared. I kept getting the ora-09925 during login. After reading your blog I did not even see the audit dir in the rdbms dir. I created it and everything just started working.

    So thanks again and have a great weekend!!

Comments are closed.