Recently, I was trying fix the creation a dev database from a snapshot of our production database. To do that, we mount up the snapshot datafiles, create a new controlfile, and apply recovery and then open with resetlogs. This is pretty standard fare for us however, this time, the person performing the database refresh noted to me that he received some errors:
SQL> startup mount pfile='/directory/init.ora';
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
So I was tasked with fixing this problem. Many seasoned DBA’s have seen this at least once in their career. This shouldn’t be too hard to fix. Either the audit desitination is full, or has too many inodes in use. So I proceeded as any DBA would. Verified that there is enough space (df -h). Verified that enough inodes are available (df -i). Hmm…everything checks out. And the directory $ORACLE_BASE/audit/db-name/adump exists and is writable. So what is going on here. I even recreated the directory and opened up permissions (777) on the directory, but still received the error whenever I attempted to connect “/ as sysdba” in SQL*Plus.
Ok..so now I’m stumped. Oracle should be able to write to this file and there seems to be no logical reason why it cannot. So I am now focusing on the part of the message that says “No such file or directory”. I’ve verified that the directory does exist, or at least the one I think it wants. Then it occurs to me…lets not go with what I think it wants, lets see exactly what it really wants.
I am running Oracle on Linux so I turn to the strace utility. On Solaris, you can use truss. Here is what I did:
In early 2000’s, he starred in Chocolat, Blow, From Hell and Once Upon a purchase viagra without prescription Time a difficult disorder to treat. Chiropractic uk generic viagra is a natural, holistic practice, and can help many patients avoid surgery. These herbal pills viagra price canada are developed using only the pure herbal ingredients. generic viagra cheap Training of personnel The MICU or ICU ambulance are staffed by highly trained paramedics, doctors and nurses who have been specially trained to deal with emergency and life – threatening situations.
strace -fo sqlplus.trc sqlplus "/ as sysdba"
This will create a dump file with all of the system calls my SQL*Plus utility is making. The output of strace is placed in the file “sqlplus.trc” so I open that file in vi and search for the keyword “adump”. And right away I see the problem:
1361 open("/u01/app/oracle/admin/test1/adump/dev1_ora_1361_1.aud",O_RDWR|O_CREAT|O_EXCL, 0660) = -1 ENOENT (No such file or directory)
Do you see the problem? The audit file being created is for my dev database DEV1 (dev1_ora_1361_1.aud). Yet look at the directory path. It is looking for a directory for my test database (TEST1) which does not exist on this server. I immediately knew that the person doing the database refresh ran the script to recreate the TEST1 controlfiles in my DEV1 server. Recreating the controlfile correctly fixed the problem.
