Home » RDBMS Server » Server Administration » ORA-09817: Write to audit file failed. (Oracle 11g RAC Cluster)
ORA-09817: Write to audit file failed. [message #413778] Fri, 17 July 2009 11:31 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 #413801 is a reply to message #413784] Fri, 17 July 2009 12:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ThomasG wrote on Fri, 17 July 2009 18:41
There should be a more detailed OS error code in the alert log.



Have you checked that yet?
Re: ORA-09817: Write to audit file failed. [message #413803 is a reply to message #413795] Fri, 17 July 2009 12:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #413807 is a reply to message #413801] Fri, 17 July 2009 12:52 Go to previous messageGo to next message
jaycosmo
Messages: 5
Registered: July 2009
Junior Member

ORA-12012: error on auto execute of job 139
ORA-09817: Write to audit file failed.
ORA-06512: at "CRS_PROD.SECTOR", line 2503
ORA-06512: at line 1


Not sure if this helps as much.
Re: ORA-09817: Write to audit file failed. [message #413809 is a reply to message #413807] Fri, 17 July 2009 13:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #413814 is a reply to message #413803] Fri, 17 July 2009 13:36 Go to previous messageGo to next message
jaycosmo
Messages: 5
Registered: July 2009
Junior Member
You may be right. We did not consider other instances writing to the same file. We are incorporating those scenarios. Let's see how that goes.
Re: ORA-09817: Write to audit file failed. [message #414326 is a reply to message #413814] Tue, 21 July 2009 11:20 Go to previous message
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
Previous Topic: pipe put wait event
Next Topic: Upgrading DB from 9i to 10g
Goto Forum:
  


Current Time: Sun Dec 04 16:30:59 CST 2016

Total time taken to generate the page: 0.15775 seconds