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
The technical details around resolving issues like SQL*Net break/reset client errors in Oracle during SAP upgrades illustrate the complexities of maintaining data systems—similar to the challenges data scientists face when managing databases and performing data analysis. The Data Science Courses in Kansas by IIM SKILLS provide a foundation for understanding these critical database concepts, helping students learn to troubleshoot, optimize, and work effectively with data, thus preparing them for real-world data challenges. Data Science Courses in Kansas
ReplyDeleteUnderstanding the complexities of issues like Oracle session hangs during SAP upgrades requires a deep technical background in database management, similar to the skill set that Data Science Courses in Kansas by IIM SKILLS aim to develop. These courses cover essential data handling, analysis, and optimization techniques, preparing students to tackle real-world challenges in database performance and troubleshooting. Data Science Courses in Kansas
ReplyDeleteThanks for bringing a fresh approach to this topic! I’ve seen this covered elsewhere, but your insights on it really added something new. This was a great read.
ReplyDeletehttps://iimskills.com/data-science-courses-in-westminster/
Thanks for shedding light on the import hanging issue related to the 'SQL*Net break/reset client' wait event in Oracle! Your analysis of this INACTIVE wait event and its potential impacts on Upgrade/Support Package imports is incredibly helpful. Understanding these nuances can significantly aid DBAs in diagnosing and resolving such issues quickly. Great resource for database administrators!
ReplyDeleteData science Courses in hamburg
This note addresses a known issue with Oracle 11g on Windows during SAP upgrade or Support Package import, where sessions hang due to the "SQL*Net break/reset client" wait event. The bug, resolved in Oracle patch set 11.2.0.4, causes inactive sessions during critical upgrade phases. A workaround involves setting the Oracle parameter _use_zero_copy_io=false and rebooting the database. For a permanent solution, updating Oracle patches as per SAP Note 1635605 is recommended. This information is vital for maintaining smooth upgrade operations in SAP environments on Windows. Data science courses in Gurgaon
ReplyDeleteThis note provides guidance on resolving hangs during SAP upgrades or Support Package imports due to the "SQL*Net break/reset client" wait event in Oracle 11g on Windows. The issue, caused by a known bug in Oracle versions prior to 11.2.0.4, can be temporarily fixed by setting the Oracle parameter _use_zero_copy_io=false and restarting the database. For a long-term solution, users should apply the Oracle patches recommended in SAP Note 1635605. Data science courses in Gurgaon
ReplyDeleteNice article on SAP
ReplyDeleteData science Courses in London
This is a useful and detailed explanation of how to troubleshoot and resolve the "SQL*Net break/reset client" hang issue during Oracle upgrades or support package imports on MS Windows. It’s great that you’ve identified the specific Oracle bug and provided the workaround by setting the Oracle initialization parameter "_use_zero_copy_io=false". Data science courses in Visakhapatnam
ReplyDeleteThis is a helpful post detailing the issue of session hangs during SAP upgrades or support package imports due to the Oracle bug in 11g. The step-by-step instructions to identify the wait event and the workaround solution using the "_use_zero_copy_io" parameter are valuable. It’s great that the article also points to relevant SAP notes for further resolution. Thanks for sharing this!
ReplyDeleteData science courses in Gujarat
This post provides an excellent explanation of the issue with Oracle 11g causing hangs during SAP upgrades or support package imports. The step-by-step guide to diagnose the problem and the workaround solution using the "_use_zero_copy_io=false" parameter are really helpful. It's great to see the reference to relevant SAP notes for further troubleshooting. Thanks for sharing these insights!
ReplyDeleteData science courses in Gujarat