ORA-09925: Unable to create audit trail file

[Valarie Lim]

Currently my server is running HP-UX 11.11, with Oracle9i.

Every night we shut down the oracle DB to perform backup. After backup, sometimes when database is coming up, the error "ORA-09925: Unable to create audit trail file" will appear. The database will be mounted but not open. Will need to restart the DB to make sure DB is OK.

My file/directories permission are already set properly.
Is this an oracle9i bug?

Please Help!
Yew Meng
IS this

[Yogeeraj_1]

hi,

this problem may be related to disk access/corruption problem.

This error indicates that you are unable to write to the audit directory. You have checked your audit directory and there is plenty of freespace available. You have even removed the aud$ files from the directory and this does not seem to help. All permissions are also correctly set.

(see attached metalink document)

run fsck against the filesystem, unmount and mount the filesystem.

Hope this helps!

Yogeeraj

[Yogeeraj_1]

hi again.

1. sorry forgot to attach note.

2. It can also be a disk space problem.

Make space available in $ORACLE_HOME/rdbms/audit by removing files that are not needed

or Make sure the directory exists and is readable by oracle

or Change init.ora audit_file_dest to an existing directory.

3. What other errors do youhave in your alert log?

Regards
Yogeeraj

[T G Manikandan]

What is the destination diractory specified for the parameter user_dump_dest in the init.ora file.

Make sure that the directory exist and it has the permissions for oracle user.

Revert

[Valarie Lim]

yogeeraj,

Think my filesystem should be consistent. This error only happens once in while (like two weeks).
fsck was not run.

T G,

as a matter of fact, Oracle9i was upgraded from 8i this year. Therefore there are two oracle user in the system.
oracle8 and oracle9.

The owner for user_dump_dest=/directory

is actually own by oracle8:dba.

permission is set at
drwxrwxr-x.

Is this a concern?

The frustrating part is that it happens intermittenly.

Thanks for the help!

[T G Manikandan]

What is

ORACLE_BASE
ORACLE_HOME

REvert

[T G Manikandan]

ALso this could be of

the inodes.
check your OS /var/adm/syslog/syslog.log file for any messages.

Please revert

[Yogeeraj_1]

hi,

To check the INODES for the device.
use:
df -i

Regards
Yogeeraj

[Valarie Lim]

syslog.log shows nothing.

The ORACLE_HOME=/oracle9

could not get hold of ORACLE_BASE. think it should be /oracle9 also.

attached is the output of inodes for /oracle9 filesystem where the audit trail resides.

By the way, DB auditing is not enabled in the init.ora.
Why is there is audit trail in /oracle9/rdbms/audit?

[T G Manikandan]

the administrative user connections are logged to files created by Oracle and stored in $ORACLE_HOME/rdbms/audit directory.

This does not have to have the audit_trial enabled or auditing enabled.

Because auditing make entries in the tables of the database.
When the database is down and the administrative accouts startup and do anything on the instance it is logged here.

Thanks

[Valarie Lim]

T G,

Thanks for the explanation!

Is there any known cause to what I am facing now?

Any resolution?

Thanks!
Yew Meng

[T G Manikandan]

What about these two permissions
$ls -l $ORACLE_HOME/rdbms/audit
drwxr-xr-x 2 oracle dba 9216 Dec 17 16:06 audit
$ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 38514400 Oct 5 05:33 oracle


Check you have proper permissions on the executable


Thanks

[T G Manikandan]

Also make sure that the $ORACLE_HOME/rdbms/audit exists and is not a softlink to another directory.

REvert

[Yogeeraj_1]

hi yew meng,

According to Oracle Metalink, the only possibilities for this error occuring are:

a. $ORACLE_HOME/rdbms/audit directory is full or
b. $ORACLE_HOME/rdbms/audit directory doesn't exist

This error happens only on Unix systems. Oracle tries to create an audit file (in releases 7.1.3 onwards) even if auditing is not enabled. By default this information is written to $ORACLE_HOME/rdbms/audit or to the directory specified by init.ora parameter audit_file_dest.

To fix this:
1. Ensure the directory $ORACLE_HOME/rdbms/audit or the directory specified by the init.ora parameter AUDIT_FILE_DEST exists, is writable by the oracle software owner and that the disk is not full. Make space available in $ORACLE_HOME/rdbms/audit or AUDIT_FILE_DEST by removing files that are not needed.
2. Check $ORACLE_HOME/bin/oracle has permission of 6755. If not, then correct the permissions by executing this from the $ORACLE_HOME/bin directory:
chmod 6755 oracle

If this is not the case, most probably you are facing a bug! Then i would suggest that you create an iTAR or contact your Oracle Support

Hope this helps!

Best Regards
Yogeeraj

[Valarie Lim]

I have lots of space in the /oracle9 filesystem.
therefore space may not be the issue here.

ORACLE_HOME=/oracle9

file permission for /oracle9/rdbms/audit is
drwxrwxr-x

as for /oracle9/bin/oracle
file permission is rwxrwx--x
not the wan specifed by TG
rwsr-s--x or 6755.

Is this a concern?

Thanks!

[T G Manikandan]

The permission for the Oracle executable which I mentioned has setuid and setgid permissions.

make a copy of the present file as it is

and change the permissions of the $ORACLE_HOME/bin/oracle
to

$chown 6755 $ORACLE_HOME/bin/oracle


$ls -l $ORACLE_HOME/rdbms/audit
drwxr-xr-x 2 oracle dba 9216 Dec 17 16:06 audit
$ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 38514400 Oct 5 05:33 oracle

Also check the audit dir for the proper permissions.

REvert once things are done and the results.

Thanks

[Steven E. Protter]

If you had a disk problem, you'd probably not even have a usable database. You would be doing a database recovery.

Though not familiar with Oracle 9i, we're still 8i, We lost an entire database and had to go to backups and apply archive logs last two weeks ago.

The disk the oracle db was sitting on did not fail. There was a meltdown of another disk higher up the scsi chain and it interfered with disk operations of the disk oracle was on and we went down.

The audit file in question above might be on a bad disk, but in general, most shops put oracle on large, reliable disk arrays.

Are there any lbolt messages when you hit dmesg? Even if there are any, even on scsi devices unrelated to the actual oracle disks could be messing you up.

In the above mentioned failure, we lost access to a Seagate Xiotech disk array which isn't sharing SCSI addresses with anything. It's a SCSI device hung off its own fiber card. I guess all SCSI devices are related somehow, but in our case the box is old and the Fibre card has to be unplugged to do an ignite recovery.

Probably a most unhelpful post, but good luck to you.

[Valarie Lim]

TG,

noted on your point.
But dont think i will change the oracle permission binaries without consulting the DBA. once relevant changes made will keep you posted.

Steven,

Thanks for sharing your experience.
That's what I initially thought also. However not much came out from syslog and dmesg.

FYI, the oracle9 binaries sits on a FC60 disk array wiht RAID 5 configured over 4 x 73 GB disks.

Thanks Again! &
Merry Christmas!