jump to navigation

What is AUDSID June 17, 2010

Posted by mwidlake in internals.
Tags: , ,
trackback

If you look at v$session you see SID and SERIAL#, which most of us DBA-types know uniquely identify a session and allow you to kill it (*with the relevant permissions and knowledge you are not about to compromise a business process).

But what is AUDSID?

desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 SADDR                                              RAW(8)
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(8)
 USER#                                              NUMBER
 USERNAME                                           VARCHAR2(30)
 COMMAND                                            NUMBER
 OWNERID                                            NUMBER
 TADDR                                              VARCHAR2(16)
 LOCKWAIT                                           VARCHAR2(16)
 STATUS                                             VARCHAR2(8)
 SERVER                                             VARCHAR2(9)
 SCHEMA#                                            NUMBER
 SCHEMANAME                                         VARCHAR2(30)
 OSUSER                                             VARCHAR2(30)
...

AUDSID is a unique identifier for the session and is used in sys.aud$ , as the SESSIONID column. It is the leading column of the only index on sys.aud$

IND_OWNER   IND_NAME           TAB_NAME           PSN       COL_NAME
----------- ------------------ ------------------ --------- ------------
SYS         I_AUD1             AUD$               1         SESSIONID
                                                  2         SES$TID

All audit records for a session are recorded with the same SESSIONID/AUDSID. Activity can be audited at session level and statement level. If audited at session level, only a single record for that audited thing is recorded in the log. This is updated to indicate any new audited actions that occur on that thing for the duration of the session, so it is important that Oracle can efficiently identify and update that record, as the impact of SQL AUDIT on database functionality wants to be kept to a minimum.

How does AUDSID relate to SID, SERIAL#, USERNAME etc? Let’s have a quick look.

select sid, serial# ,audsid ,username,OSUSER,schemaname
from v$session
       SID    SERIAL#     AUDSID USERNAME             OSUSER                    SCHEMANAME
---------- ---------- ---------- -------------------- ------------------------- --------------
       485       4745     165550 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       487      23712     165546 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       497      40388          0                      oracle                    SYS
       498      19269          0                      oracle                    SYS
       502      13362     165432 EAAAAA               govanii                   EAAAAA
       505        407     163821 LEXAAAAA             oracle                    LEXAAAAA
       506       6302 4294967295 SYS                  widlake                   SYS
       511      11702     165518 OAAAAA               backerella                OAAAAA
       512      17076     165490 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       518       9066     165507 OAAAAA               Manoled                   OAAAAA
       519       6956     163976 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       521      15272 4294967295 SYS                  widlake                   SYS
       523       4825     163975 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       536      63941 4294967295 SYS                  backerella                SYS
       524      19740     165548 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       525        850     165549 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       526      45112     165516 XXX                  backerella                XXX
       527      11086     163963 LEX_AAAAAEX          oracle                    LEX_AAAAAEX
       529       1662     163818 LEX_AAAAA            oracle                    LEX_AAAAA
       530      62788 4294967295 SYS                  widlake                   SYS
       537          1          0                      oracle                    SYS
       540          1          0                      oracle                    SYS

It does not. But it is interesting to note that this database does not have auditing enabled but the AUDSID is still populated.

It is also interesting that all SYS accounts have either an AUDSID of 0 or the same AUDSID, 4294967295 {which is 1 less than 4294967295…1024*1024*4}. I might come back to that shared AUDSID at a later date…

All the “normal” AUDSIDs are unique and about the same value, 163900 to 165560.

If you look at sys.aud$ the SESSIONID increments as time goes on. By this I do not mean it is in order of SESSIONID, it is not, but as time goes by the SESSIONID increments. That sounds a lot like what you would see with a sequence to me. And in fact that is because the value IS from a sequence:

select * from dba_sequences where sequence_name = 'AUDSES$'

SEQUENCE_OWNER  SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY cycle O CACHE_SIZE LAST_NUMBER
--------------- --------------- ---------- ---------- ------------ ----- - ---------- -----------
SYS             AUDSES$                  1 2000000000            1 Y     N      10000      173695

So the AUDSID is a unique value that comes from a sequence and is used in the audit trail. All SYS accounts have the same AUDSID or have it set to zero.

Why do many sessions have the AUDSID set to zero? Because they are internal Oracle processes:

select sid, serial# ,audsid ,username,OSUSER,schemaname,program
from v$session
where AUDSID = 0
  SID    SERIAL#     AUDSID USERNAME     OSUSER       SCHEMANAME   PROGRAM
----- ---------- ---------- ------------ ------------ ------------ ----------------------------
  506       6887          0              oracle       SYS          oracle@db25.eng.ham.uk. (q001)
  526      45682          0              oracle       SYS          oracle@db25.eng.ham.uk. (q002)
  531         91          0 YYYYY        oracle       DLPP1        oracle@db25.eng.ham.uk. (J000)
  533          3          0              oracle       SYS          oracle@db25.eng.ham.uk. (CJQ0)
  537          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (q000)
  540          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (QMNC)
  546          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (MMNL)
  547          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (MMON)
  548          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (RECO)
  549          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (SMON)
  550          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (CKPT)
  551          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (LGWR)
  552          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (DBW0)
  553          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (MMAN)
  554          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (PSP0)
  555          1          0              oracle       SYS          oracle@db25.eng.ham.uk. (PMON)

The activity of PMON, SMON, CKPT is not audited of course. And job schedulers doing nothing do not have their activity audited either.

Oh, you remember I said AUDSID was unique? Well, check back at the sequence. It wraps and has a maximum value of 2,000,000,000. It is going to start repeating values after 2 billion sessions. Make a note to ensure you have cleaned up your audit trail before you hit your 2 billion and first session…

Advertisements

Comments»

1. PdV - June 17, 2010

So…Has anyone ever hit that 2 billion max value and cycled the sequence ?

Assume one logon per second on badly configured systems…

select 2000000000 / ( 3600 * 24 * 366 ) as days from dual ;
… eh some 63 years … ?
(are my numbers and zeros correct ?)

Even on truly disastrous systems with 10 logons per seconds, you can run for 6 years or so.

hm
Try resisting the oracle marketing and upgrade FUD for that long.

mwidlake - June 17, 2010

Would the sequence get reset at upgrade time? I suspect not so this might have been a problem for someone at some point 🙂 A quick google did not find any such occurance but I did find someone suggesting setting max value to 32000 when recreating the sequence. Now that could wrap around quite soon.

2. PdV - June 17, 2010

Indeed, I realize an in place upgrade will probably not re-set the sequence, but at some point you will re-create a new database and re-load the data, or even implement a totally different system.

Any given database only lives so long.
Either real-world demands or marketing FUD will some day force a re-fresh of your technology-stack.

Which, btw, coins the question: do we know of databases the went from v6 via catupgr all the way to 11 ??

mwidlake - June 18, 2010

It would be interesting to know what the “oldest” 11g database is, ie if there are any where the database started on 6 and has since been upgraded as opposed to having a full export and re-import. I have to say, I can’t rememeber the last time I did an upgrade like that – but then for the last 10,15 years I have either been working on disagreeably large systems or there was a team of competent DBAs who did all that for me…

3. Gary - June 17, 2010

AUDSID is also what you get from select sys_context(‘USERENV’,’SESSIONID’) from dual

If you use that for your own auditing (ie recording it in non-system schemas) then the fact that it is sequence based (rather than, say, time-based) could have some interesting effects if you move schema data between prod/test/dev. You do a prod copy to dev, then the sequence on ‘dev’ catches up and overlaps with the sequence values already used in prod.

mwidlake - June 18, 2010

Odd you should mention that Gary. See my next post! And that is a nice point about moving data from one system to another.

4. Who am I? {What is my SID} « Martin Widlake's Yet Another Oracle Blog - June 18, 2010

[…] Posted by mwidlake in internals. Tags: data dictionary, SQL trackback Yesterday, I posted about what AUDSID is. One of the comments, from Gary Myers, included the info that you can get AUDSID via sys_context […]

5. Amit - June 20, 2010

Martin,

Nice post. I came across AUDSID, while creating a database logon trigger. It’s logic was like below

  select sid into lsid 
  from v$session where audsid = userenv('sessionid') ;
  Query V$SESSIOn for this ausid and have one more column comparison
  perform alter session

Problem with above was that , dbms_job started failing as they have audsid =0 and above query returned multiple values . Also sys sessions have value of 4294967295,which can again return multiple values. This turned out to be very bad code and lead to many jobs failing 😦
So above query should always have

select sid into lsid 
from v$session 
where audsid = userenv('sessionid') 
and audsid!=0 and audsid!=  4294967295

Regards
Amit

mwidlake - June 20, 2010

Hi Amit,

Thanks for that example.
I think the code in your comment got slightly mangled so I have tried to correct it, I hope that is OK.

Great care is needed with Logon Triggers, ensuring that they work fine for all users (for all systems you put them on). I hope you found the issues on a test system and not a production one!

Martin

6. SID stays same, AUDSID alters « Martin Widlake's Yet Another Oracle Blog - June 24, 2010

[…] internals. Tags: data dictionary, sql*plus trackback In a couple of recent posts I described what AUDSID is and how you check out what you SID and AUSID […]

7. sys - July 26, 2010

One more thing that I noticed about the AUDSID for parallel query slaves is the same as their parent query coordinator session.

mwidlake - July 26, 2010

Interesting. I never checked that but it sort of makes sense. The slaves should not log any activity under a different audsid, they are part of that session. But I can’t think that a slave process would ever need to write any audit out. It seems to be done at statement parsing time (soft parsing too, not just hard parsing).

Thanks for that “sys”

8. bernard polarski - February 16, 2016

bump:

On my production machine (add points for better reading):

16/02/16>select last_number from dba_sequences where sequence_name = ‘AUDSES$’ ;

LAST_NUMBER
———–
512.150.413

mwidlake - February 16, 2016

You are 25% of the way to wrapping 🙂


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: