What is AUDSID June 17, 2010
Posted by mwidlake in internals.Tags: audit, data dictionary, performance
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…
—
Martin Widlake's Yet Another Oracle Blog 
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.
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.
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 ??
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…
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.
Odd you should mention that Gary. See my next post! And that is a nice point about moving data from one system to another.
[…] 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 […]
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 sessionProblem 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!= 4294967295Regards
Amit
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
[…] 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 […]
One more thing that I noticed about the AUDSID for parallel query slaves is the same as their parent query coordinator session.
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”
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
You are 25% of the way to wrapping 🙂
[…] https://mwidlake.wordpress.com/2010/06/17/what-is-audsid/ […]