Oracle: “LOGOFF BY CLEANUP” Action Name in DBA_AUDIT_TRAIL

Developer reports problems with a Tomcat-Server. Something with established connection-pool-sessions which suddenly cease to work, but apparently have not disconnected. Blaming the database as the cause for that mess.

So, I checked the DBA_AUDIT_TRAIL for that servers connections. There were tons of normal “LOGON”- / “LOGOFF”-actions recorded, but also occasionally a bunch of “LOGOFF BY CLEANUP”-actions. “CLEANUP” to me smelled like PMON cleaned up some abandoned connections whose client-sides died away. To verify my assumption I checked the Oracle support docs at MOS (Doc ID 274697.1), which states:

(...)
"LOGOFF BY CLEANUP" Action Name in SYS.AUD$ Table
-------------------------------------------------
This audit entry is written by PMON when it clears a dead process/session once
the DBA enabled auditing on :

CREATE SESSION or
CONNECT or
SESSION

When a server process dies abruptly, it would not have got the opportunity to
put the audit record.

The PMON finds this dead process and starts cleaning up this dead process.
At this time, PMON writes this audit record indicating the process termination
was abnormal.

The known situations when we have a dead process is when the server process is
either killed at the OS level or within the database using the ALTER SYSTEM KILL
SESSION command.
When the server process cannot contact the client, the server process exits
gracefully.
This does not constitute a LOGOFF BY CLEANUP record, but a LOGOFF record.
(...)

This seemed to prove me wrong, as this note says that the problem lies on the DB-server-side. So I started off examining my otherwise smooth running DB-Server for anomalies, being the root of that evil. But in vain. “LOGOFF BY CLEANUP”-actions still happened leaving me without a clue.

Remembering my first assumption I set up a test-case for to check what action would be really recorded in the audit-trail when I simply kill my connected sqlplus-process at the client-side. According to the note above, this should end up in a “gracefully exited server process” with a plain “LOGOFF” record. But guess what…

After some testing I found the above MOS-Note to be wrong and misleading. According to my tests, the opposite of the note’s assertion is true.

As I presumed, the “LOGOFF BY CLEANUP” is recorded, when the client-side vanishes without a clean disconnect. It’s the client-process that “dies abruptly” – not the server’s one.
Killing the session via “ALTER SYSTEM KILL SESSION” or killing the server-process at OS-Level leaves no trace in the audit-log – contradicting the note above.

Tested with Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production.

Advertisements

One Response to Oracle: “LOGOFF BY CLEANUP” Action Name in DBA_AUDIT_TRAIL

  1. juan says:

    This bugged me for a long while too. My database got its audit folder full, which gave me lots of trouble, and had to do some purging script on cron.

    Then I discovered, it was full of audit files with Action 102, and, just as you, did my own test and discovered that when i close sqlplus abnormally, it writes an audit file like that.

    So I am assuming it’s getting filled up by the jdbc connections that don’t close their connections like their should (bad programmers)

    Nice note

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: