HOW TO RESOLVE ORA-09925 ?

Problem:

I get the ORA-09925 error when trying to log on to SQL*Plus:
         ERROR:
        ORA-09925: Unable to create audit trail file
        Linux-x86_64 Error: 2: No such file or directory
        Additional information: 9925
       ORA-01075: you are currently logged on

Diagnose:

ORACLE was not able to create the file being used to hold audit trail records. that could be becaouse of

1) AUDIT_FILE_DEST is not writable
2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable
3) $ORACLE_HOME/rdbms/audit is not writable

 

Solution:

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
Linux Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/u01/app/oracle/admin/orcl/adump
$ ls -lad /u01/app/oracle/admin/orcl/adump
/u01/app/oracle/admin/orcl/adump not found
$ mkdir  -p /u01/app/oracle/admin/orcl/adump 
$ $ORACLE_HOME/bin/sqlplus -L “/ as sysdba”
SQL> startup nomount
ORACLE instance started.
SQL> shutdown normal
ORACLE instance shut down.

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

$ ls -ltr $ORACLE_BASE/admin/$ORACLE_SID
dr-xr-xr-x. 2 oracle oinstall 4096 Dec 9 14:15 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
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on

$ chmod +w  /u01/app/oracle/admin/orcl/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=MTS01
$ $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
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on

$ ls -ltr $ORACLE_HOME/rdbms/audit
dr–r–r–. 2 oracle oinstall  4096 Dec  8 11:13 audit
$ cd $ORACLE_HOME
$ chmod 755  ./rdbms/audit
$ $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