1738989 - Upgrade/Support Package import hang due to INACTIVE wait event "SQL*Net break/reset client" in Oracle

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:

1. First you need to identity the PID for the hanging session, example R3trans.    

          Using Windows Task Manager, Ctrl+Alt+Del -> Start Task manager -> "Processes" tab
    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

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;

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.

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	BC-DB-ORA Oracle BC-UPG-TLS-TLA Upgrade tools for ABAP
Priority	Normal
Category	Problem