Symptom
*** This note is only applicable in platform MS Windows ***
*** Bug 13017584 is already fixed in Oracle patch set 11.2.0.4 and higher ***
During upgrade, phase DDIC_UPG, ACT_UPG or SHADOW_IMPORT_INC hang.
OR
During Support Package import, main import(IMPORT_PROPER) phase hang.
When you check the Oracle session via ST04/ST04old or OS level, you will see that sessions are in status INACTIVE with wait event SQL*Net break/reset to client.
You can follow the steps below to identify the current wait event which corresponding to the hanging session:
2. Then, check the wait event for the hanging session.
*** Bug 13017584 is already fixed in Oracle patch set 11.2.0.4 and higher ***
During upgrade, phase DDIC_UPG, ACT_UPG or SHADOW_IMPORT_INC hang.
OR
During Support Package import, main import(IMPORT_PROPER) phase hang.
When you check the Oracle session via ST04/ST04old or OS level, you will see that sessions are in status INACTIVE with wait event SQL*Net break/reset to client.
You can follow the steps below to identify the current wait event which corresponding to the hanging session:
- First you need to identity the PID for the hanging session, example R3trans.
2. Then, check the wait event for the hanging session.
- From R3 level:
ST04/ST04old -> detailed analysis session -> oracle sessions
OR
DBACockpit (as of SAP Basis Release 7.00 Support Package 12) -> Performance -> Wait Event Analysis -> Session Monitor
REMARK: Only ACTIVE sessions will be displayed in DBACockpit. This is not useful as we need to find INACTIVE sessions too.
- From OS level:
sqlplus "/as sysdba"
set pagesize 200
set linesize 200
set linesize 200
SELECT
S.SID,
S.STATUS,
S.PROCESS,
SUBSTR(DECODE(SW.WAIT_TIME, 0, SUBSTR(SW.EVENT, 1, 35), 'CPU'),
1, 30) ACTION,
DECODE(ST.SQL_TEXT, NULL, AA.NAME,
SUBSTR(ST.SQL_TEXT, 1, 32)) SQLTEXT,
S.MODULE,
S.PROGRAM
FROM
V$SESSION S, V$SESSION_WAIT SW, V$SQLTEXT ST, AUDIT_ACTIONS AA
WHERE
S.STATUS in ('ACTIVE', 'INACTIVE') AND S.SID = SW.SID AND
S.SQL_HASH_VALUE = ST.HASH_VALUE (+) AND
S.SQL_ADDRESS = ST.ADDRESS (+) AND ST.PIECE (+) = 0 AND
AA.ACTION = S.COMMAND
ORDER BY
S.STATUS;
S.SID,
S.STATUS,
S.PROCESS,
SUBSTR(DECODE(SW.WAIT_TIME, 0, SUBSTR(SW.EVENT, 1, 35), 'CPU'),
1, 30) ACTION,
DECODE(ST.SQL_TEXT, NULL, AA.NAME,
SUBSTR(ST.SQL_TEXT, 1, 32)) SQLTEXT,
S.MODULE,
S.PROGRAM
FROM
V$SESSION S, V$SESSION_WAIT SW, V$SQLTEXT ST, AUDIT_ACTIONS AA
WHERE
S.STATUS in ('ACTIVE', 'INACTIVE') AND S.SID = SW.SID AND
S.SQL_HASH_VALUE = ST.HASH_VALUE (+) AND
S.SQL_ADDRESS = ST.ADDRESS (+) AND ST.PIECE (+) = 0 AND
AA.ACTION = S.COMMAND
ORDER BY
S.STATUS;
Environment
- SAP Netweaver Release Independent
- Support Package Tools(SPAM) for ABAP
- SAPup for ABAP
- SAP Enhancement Package Installer (SAPehpi)
- Software Update Manager (SUM)
Reproducing the Issue
The problem can be reproduced when Oracle patch set lower than 11.2.0.4 is running on MS Windows.
Cause
Known bug in Oracle 11g.
Resolution
There is a known bug in the Oracle 11g. Please check SAP note 1635605.
To prevent this error, apply the mentioned SAP Bundle Patches /Oracle patch collections as per mention in the note 1635605.
If you already encounter this issue during upgrade or support package import then set the Oracle initialization parameter " _use_zero_copy_io=false" as a workaround and reboot the database instance.
To prevent this error, apply the mentioned SAP Bundle Patches /Oracle patch collections as per mention in the note 1635605.
If you already encounter this issue during upgrade or support package import then set the Oracle initialization parameter " _use_zero_copy_io=false" as a workaround and reboot the database instance.
See Also
1635605 - Client hangs on insert into table with SecureFile LOB
Keywords
SPAM, SUM, EHP, support package, upgrade, SHADOW_IMPORT_INC, hang,
performance, slow, main import, IMPORT_PROPER, DDIC_UPG, ACT_UPG, SQL
Net break, reset client, Oracle, 11g, 11.2, bug, Windows,
NT, _use_zero_copy_io, Oracle client, SecureFile LOB, SecureFile,
Header Data
Released On | 15.12.2014 06:32:19 | |||
Release Status | Released to Customer | |||
Component | BC-UPG-OCS-SPA Support package tools for ABAP | |||
Other Components |
| |||
Priority | Normal | |||
Category | Problem |
I would really like to read some personal experiences like the way, you've explained through the above article. I'm glad for your achievements and would probably like to see much more in the near future. Thanks for share.
ReplyDeletepython Training institute in Pune
python Training institute in Chennai
python Training institute in Bangalore
Awesome..You have clearly explained …Its very useful for me to know about new things..Keep on blogging..
ReplyDeleteBest Devops online Training
Online DevOps Certification Course - Gangboard
I have picked cheery a lot of useful clothes outdated of this amazing blog. I’d love to return greater than and over again. Thanks!
ReplyDeleteData Science Training in Indira nagar
Data Science Training in btm layout
Data Science Training in Kalyan nagar
Data Science training in Indira nagar
Data science training in bangalore
I really appreciate this post. I’ve been looking all over for this! Thank goodness I found it on Bing. You’ve made my day! Thx again!
ReplyDeleteAWS Training in pune
AWS Online Training
Thanks for sharing.., Tutorial is just awesome..It is really helpful for a newbie like me.
ReplyDeleteI am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging.
aws training in chennai | aws training in annanagar | aws training in omr | aws training in porur | aws training in tambaram | aws training in velachery
This post is so interactive and informative.keep update more information...
ReplyDeleteGerman Classes in Tambaram
German Classes in chennai