MANUAL segment management on tablespace for AUD$ and FGA_LOG$ could lead to database hanging with gc

MANUAL segment management on tablespace for AUD$ and FGA_LOG$ could lead to database hanging with gc current request waits (文档 ID 1434614.1)

转到底部

---

In this Document

Symptoms

Changes

Cause

Solution

References

---

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

• A general hang on the database is described by users
• No new sessions are coming into the database.  Only users with sys or sysdba role can connect
• Wait events for most of chains include gc buffer busy acquire, gc current request. The same block is referenced on the wait event
• If a hanganalyze is generated, the current session involved in the wait is an insert into aud$

Example of the chain generated by hanganalyze command level 3.  Note that 11.2, dia0* process will be generated if a hang is detected.  It will include also dump of hanganalyze:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
      instance: 2 (xxx.xxx2)
      os id: 28840
      process id: 288, oracle@xxx2
      session id: 6
      session serial #: 3103
}
is waiting for 'gc buffer busy acquire' with wait info:
{
      p1: 'file#'=0x1
      p2: 'block#'=0x3fd8e
      p3: 'class#'=0x1
      time in wait: 32 min 43 sec
      timeout after: never
      wait id: 2
      blocking: 0 sessions
      current sql: 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, i
     short stack:
        ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-kcbzwb()
        +1567<-kcbgcur()+17031<-ktbgcur()+123<-ktsfbget()+224<-ktsf_gsp()+1458<-kdtgsp()
        +779<-kdtgsph()+2229<-kdtgrs()+273<-kdtInsRow()+1129
        <-__PGOSF802_qerltcNoKdtBufferedInsRowCBK()+293<-qerltcSingleRowLoad()+279
        <-qerltcFetch()+379<-insexe()+682<-opiexe()+5548<-opiodr()+910
        <-__PGOSF175_rpidrus()+211<-skgmstack()+148<-rpiswu2()+638<-rpidrv()+1384<-rpiexe()+74<-audins()+13
     wait history:
        * time between current wait and wait #1: 0.001387 sec
        1. event: 'SQL*Net message from client'
            time waited: 0.000915 sec
            wait id: 1 p1: 'driver id'=0x54435000
            p2: '#bytes'=0x1
            * time between wait #1 and #2: 0.000011 sec
        2. event: 'SQL*Net message to client'
            time waited: 0.000001 sec
            wait id: 0 p1: 'driver id'=0x54435000
            p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
       instance: 2 (xxx.xxx2)
       os id: 25023
       process id: 208, oracle@xxx
       session id: 5034
       session serial #: 11151
}
which is waiting for ' gc current request' with wait info:
{
       p1: 'file#'=0x1
       p2: 'block#'=0x3fd8e
       p3: 'id#'=0x2000001
       time in wait: 120 min 16 sec
       timeout after: never
       wait id: 3
       blocking: 80 sessions
       current sql: 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, i
       short stack:
         ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-__poll()+47<-ssskgxp_poll()+40<-sskgxp_select()+263<-skgxpiwait()+2617
         <-skgxpwaiti()+1550<-skgxpwait()+162<-ksxpwait()+2523<-ksliwat()+12857
         <-kslwaitctx()+163<-ksxprcv_int()+6050<-ksxprcvimdwctx()+27<-kcllkopb()+11031
         <-kclgrlk()+728<-kcbzib()+20377<-kcbgcur()+8526<-ktbgcur()+123<-ktsfbget()+224
         <-ktsf_gsp()+1458<-kdtgsp()+779<-kdtgsph()+2229<-kdtgrs()+273<-kdtInsRow()+1129
         <-__PGOSF802_qerltcNoKdtBufferedInsRowCBK()+293<-qerltcSingleRowLoad(
       wait history:

For 11.1 version, hanganalyze can be manually generated during the hang:

sqlplus '/ as sysdba'
oradebug  unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
exit

The syntax is for RAC environment, as gc buffer busy acquire wait is RAC specific wait.

Changes

none

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. Testing has shown that under such conditions, placing the AUD$ and FGA_LOG$ tables in its own tablespace, which uses automatic segment space management, reduces the space related wait events.

Solution

First, make sure this is not a case of insufficient memory, as the symptoms may be similar.  If there is plenty of memory along with current session involved in the wait is an "insert into aud$", then use the following solution to move AUD$ and FGA_LOG$ to its own tablespace (AUD_TBS and FGA_TBS here as an example) or refer to Note 1328239.1 How To Move The DB Audit Trails To A New Tablespace Using DBMS_AUDIT_MGMT?

To verify the segment space management policy currently in use by the AUD$ and FGA_LOG$ tables, use the following query:

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 in ('AUD$','FGA_LOG$');

The output should be:

TABLE_NAME SEGMEN
---------- ------
FGA_LOG$   AUTO
AUD$       AUTO

 
If one or both of the AUD$ or FGA_LOG$ tables return "MANUAL",  move the tables to its own tablespace (recommend to invoke this procedure in non-peak hours as this could be a resource intensive operation). Pre-create tablespace AUD_TBS and FGA_TBS if they do not exist.

--this moves table AUD$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUD_TBS');
END;
/
--this moves table FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,audit_trail_location_value => 'FGA_TBS');
END;
/

References

NOTE:1328239.1 - How To Move The DB Audit Trails To A New Tablespace Using DBMS_AUDIT_MGMT?