| ORA-09817: Write to audit file failed. [message #413778] |
Fri, 17 July 2009 11:31  |
jaycosmo
Messages: 5
Registered: July 2009
|
Junior Member |
|
|
There are three nodes in our RAC system. We've enabled fine grained auditing on some tables. Audits are collected in extended xml format. See e.g. of audit policy below:
SQL> BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'TEST',
policy_name => 'policy_name',
object_name => 'TEST2',
statement_types => 'SELECT',
audit_trail => DBMS_FGA.XML + DBMS_FGA.EXTENDED
);
END;
/
PL/SQL procedure successfully completed.
Problem:
When you clear out the xml files from the audit location for maintenance reasons, the next time you connect using the global SID you this error (e.g.):
SQL> conn test/tespw@crs
SQL> select * from test2;
select * from test2
*
ERROR at line 1:
ORA-09817: Write to audit file failed.
However, when you connect to say instance 1, auditing works without any issues. (eg.)
SQL> conn test/testpw@crs1
Connected.
SQL> select * from test;
ID
----------
33
33
2 rows selected.
I don't think this is space issue. We have over 90% of space available so this is highly out of question. I also doubt it is an OS related issue, but I could be wrong. Alert log reports the same error code.
Please help me out if you can with any pointers, I've been on this issue for days. Thanks.
-yaw
|
|
|
|
| Re: ORA-09817: Write to audit file failed. [message #413784 is a reply to message #413778] |
Fri, 17 July 2009 11:41  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
There should be a more detailed OS error code in the alert log.
oracle ~ $ oerr ORA 09817
09817, 00000, "Write to audit file failed."
// *Cause: ORACLE was unable to write an entry to the file used as the
// audit trail.
// *Action: Check the UNIX error number for a possible operating system error.
// If there is no error, contact Oracle Support Services.
Could it be that when you "clean out" the audit files, you delete the one that is currently written to? How do you "clean them out"?
|
|
|
|
| Re: ORA-09817: Write to audit file failed. [message #413795 is a reply to message #413784] |
Fri, 17 July 2009 12:02 |
jaycosmo
Messages: 5
Registered: July 2009
|
Junior Member |
|
|
The clean outs are done through a cron job. The script is smart enough not to delete active processes. We figured the file names have server process IDs as a suffix, (e.g. ora_32442.xml will not be deleted if the process 32442 is still active).
dmesg | tail produces
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO
After talking to the linux admin, this had been an issue before the audits were enabled. But I'm not sure if this is the root cause.
|
|
|
|
|
|
| Re: ORA-09817: Write to audit file failed. [message #413803 is a reply to message #413795] |
Fri, 17 July 2009 12:25 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| jaycosmo wrote on Fri, 17 July 2009 19:02 | The clean outs are done through a cron job. The script is smart enough not to delete active processes. We figured the file names have server process IDs as a suffix, (e.g. ora_32442.xml will not be deleted if the process 32442 is still active).
|
Are you sure it is smart enough? I'm no RAC Guru, but when you have a cluster of different servers writing to the same audit directory, maybe the script just checks for local processes, and deletes the files of still running processes of another server?
I would perhaps add an additional condition to the script, that only files older than X days are deleted.
|
|
|
|
|
|
| Re: ORA-09817: Write to audit file failed. [message #413809 is a reply to message #413807] |
Fri, 17 July 2009 13:12 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Isn't there any additional OS Error code AFTER that part?
If not, then the only option would be as mentioned in the error "Action":
| Quote: | If there is no error, contact Oracle Support Services.
|
|
|
|
|
|
|
| Re: ORA-09817: Write to audit file failed. [message #414326 is a reply to message #413814] |
Tue, 21 July 2009 11:20 |
jaycosmo
Messages: 5
Registered: July 2009
|
Junior Member |
|
|
Does anyone know how xml files are managed by RAC? For instance how long will oracle write to an xml file, basically when will it be safe to delete an audit file? I've looked around and can't seem to find any good documentation.
-yaw
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
