Oracle-DB: Link between audit trail and active session history

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#).

Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail.

But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail.
This prevents from combining session info of both sources after the session is closed.

There is a possible way to link audit trail with ASH by establishing a logon trigger for that.
The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session.
So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID. 

This logon trigger does it:
CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE
-- Put unique session identifier into client-id to have it also in DBA_AUDIT_TRAIL.Client_ID for LOGOFF-records
-- works than as missing link between Active Session History and Audit Trail
-- Peter Ramm, OSP Dresden, 2021-01-05
BEGIN
  -- Use alternative public package instead of SYS_CONTEXT to get the serial#
  sys.DBMS_SESSION.Set_Identifier('SID = '||DBMS_DEBUG_JDWP.CURRENT_SESSION_ID||', Serial# = '||DBMS_DEBUG_JDWP.CURRENT_SESSION_SERIAL);
END;
/

Comments

Post a Comment

Popular posts from this blog

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet
Read more

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo
Read more