Tag: DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION

SYMPTOM

RAC database hangs for a short period of time with high “gc buffer busy acquire” and “gc buffer busy request” wait events by sql id =”4vs91dcv7u1p6″:

insert into sys.aud$( sessionid, entryid, statement, ntimestamp#, 
userid, userhost, terminal, action#, returncode, obj$creator, 
obj$name, auth$privileges, auth$grantee, new$owner, new$name, 
ses$actions, ses$tid, logoff$pread, logoff$lwrite, logoff$dead, 
comment$text, spare1, spare2, priv$used, clientid, sessioncpu, 
proxy$sid, user$guid, instance#, process#, xid, scn, auditid, 
sqlbind, sqltext, obj$edition, dbid) values(:1, :2, :3, 
SYS_EXTRACT_UTC(SYSTIMESTAMP), :4, :5, :6, :7, :8, :9, :10, :11, 
:12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, 
:25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36)

AWR report shows AUD$ table is the segment with the highest “Global Cache Buffer Busy” wait event:

CAUSE

With AUDIT_TRAIL set for database (AUDIT_TRAIL=db), and the AUD$ and FGA_LOG$ tables located in a dictionary segment space managed SYSTEM tablespace, “gc” wait events are sometimes observed during heavy periods of database logon activity.

SOLUTION

Move AUD$ and  FGA_LOG$ tables onto an ASSM tablespace like SYSAUX. On production, make sure to run it during off peak hours.

set pagesize 120
set linesize 180
SELECT T.TABLE_NAME, TS.SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLES T, DBA_TABLESPACES TS
WHERE TS.TABLESPACE_NAME = T.TABLESPACE_NAME
AND T.TABLE_NAME = 'AUD$';

TABLE_NAME SEGMEN
---------- ------
AUD$       MANUAL

select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT 
  from DBA_TABLESPACES 
 where TABLESPACE_NAME='SYSAUX'; 

TABLESPACE_NAME  SEGMEN 
---------------- ------ 
SYSAUX           AUTO

select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME
  from dba_lobs
 where OWNER='SYS' and TABLE_NAME='AUD$';
      
TABLE_NAME TABLESPACE_NAME SEGMENT_NAME
---------- --------------- -------------------------
AUD$       SYSTEM          SYS_LOB0000000384C00040$$
AUD$       SYSTEM          SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name
  from dba_segments 
 where segment_name in (
       select INDEX_NAME 
         from dba_indexes 
        where OWNER='SYS'
          and TABLE_NAME='AUD$');

SEGMENT_NAME             SEGMENT_TYPE TABLESPACE_NAME
------------------------ ------------ ---------------
SYS_IL0000000384C00041$$ LOBINDEX      SYSTEM
SYS_IL0000000384C00040$$ LOBINDEX      SYSTEM

Move AUD$ table:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.

Move FGA_LOG$ table if needed:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.

Check the results.

select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME
from dba_lobs
where OWNER='SYS' and TABLE_NAME='AUD$';

TABLE_NAME TABLESPACE_NAME SEGMENT_NAME
---------- -------------- --------------------------
AUD$       SYSAUX          SYS_LOB0000000384C00040$$
AUD$       SYSAUX          SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name
  from dba_segments 
  where segment_name in (
        select INDEX_NAME 
          from dba_indexes 
         where OWNER='SYS'
         and TABLE_NAME='AUD$');

SEGMENT_NAME             SEGMENT_TYPE TABLESPACE_NAME
------------------------ ------------ ---------------
SYS_IL0000000384C00041$$ LOBINDEX     SYSAUX
SYS_IL0000000384C00040$$ LOBINDEX     SYSAUX

select count(*) from dba_objects where status!='VALID';

COUNT(*)
--------
0