转到底部 |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterInformation 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:
The output should be:
---------- ------
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.
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;
/