RE: AUD$ TIMESTAMP and LOGOFF_TIME are equal

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Tue, 29 Dec 2009 14:31:49 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0164779B_at_WIN02.hotsos.com>

Maybe this is an 11.1.0.7 (with RAC?) thing. We collect this data from many systems and have not seen anything like this before.

Thanks for this test on your system Rumpi. This gives us some food for thought.

• Ric

-----Original Message-----
From: Rumpi Gravenstein [mailto:rgravens_at_gmail.com] Sent: Tuesday, December 29, 2009 2:21 PM To: Ric Van Dyke
Cc: Oracle L
Subject: Re: AUD$ TIMESTAMP and LOGOFF_TIME are equal

<snip>

Data collected from AUD$ via the DBA_AUDIT_SESSION view has the TIMESTAMP and LOGOFF_TIME equal for the vast majority of the sessions collected.
</snip>

I haven't looked at this much, but what I see is what looks like separate audit records for the logon and logoff events. The logoff record has these values being equal. Could it be that auditing is only configured to capture the logoff?

Here's what I see on our 4 node RAC -- 11.1.0.7.0:

  SELECT t.instance_number,
         t.sessionid,
         t.action_name,
         t.timestamp,
         t.logoff_time

    FROM DBA_AUDIT_SESSION t
   WHERE timestamp > SYSDATE - 1

         AND (instance_number, sessionid) IN
                ( SELECT instance_number, sessionid
                   FROM (  SELECT instance_number, sessionid
                             FROM DBA_AUDIT_SESSION
                            WHERE timestamp > SYSDATE - 1
                         GROUP BY instance_number, sessionid
                     HAVING COUNT (*) > 1) v
                 WHERE ROWNUM <= 10
                 )

ORDER BY t.instance_number, t.sessionid, t.timestamp

INSTANCE_NUMBER                        SESSIONID
       ACTION_NAME                  TIMESTAMP              LOGOFF_TIME
1                                      326827663
       LOGOFF                       12/28/2009 10:42:02 PM 12/28/2009
10:42:02 PM
1                                      326827663
       LOGON                        12/28/2009 10:42:02 PM
1                                      326827668
       LOGON                        12/28/2009 10:45:02 PM
1                                      326827668
       LOGOFF                       12/28/2009 10:45:02 PM 12/28/2009
10:45:02 PM
2                                      326817677
       LOGON                        12/28/2009 10:40:05 PM
2                                      326817677
       LOGOFF                       12/28/2009 10:40:16 PM 12/28/2009
10:40:16 PM
2                                      326817695
       LOGON                        12/28/2009 10:41:32 PM
2                                      326817695
       LOGOFF BY CLEANUP            12/28/2009 10:44:46 PM 12/28/2009
10:44:46 PM
2                                      326817703
       LOGOFF                       12/28/2009 10:45:01 PM 12/28/2009
10:45:01 PM
2                                      326817703
       LOGON                        12/28/2009 10:45:01 PM
3                                      326808132
       LOGOFF                       12/28/2009 10:44:55 PM 12/28/2009
10:44:55 PM
3                                      326808132
       LOGON                        12/28/2009 10:44:55 PM
4                                      326784532
       LOGON                        12/28/2009 10:40:06 PM
4                                      326784532
       LOGOFF                       12/28/2009 10:40:16 PM 12/28/2009
10:40:16 PM
4                                      326784537
       LOGON                        12/28/2009 10:40:28 PM
4                                      326784537
       LOGOFF                       12/28/2009 10:41:37 PM 12/28/2009
10:41:37 PM
4                                      326784541
       LOGON                        12/28/2009 10:41:14 PM
4                                      326784541
       LOGOFF                       12/28/2009 10:42:24 PM 12/28/2009
10:42:24 PM
4                                      326784554
       LOGOFF                       12/28/2009 10:45:04 PM 12/28/2009
10:45:04 PM
4                                      326784554
       LOGON                        12/28/2009 10:45:04 PM

-- 
Rumpi Gravenstein
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 29 2009 - 14:31:49 CST