Document Tree
Document Properties
Kbid
2818G6
Last Modified
01-Sep-2023
Added to KB
22-Feb-2017
Public Access
Everyone
Status
Online
Doc Type
Guidelines
Product
  • ICM 7.10
  • ICM 11
Guide - Solutions to Known Issues With Oracle Database 11g and Higher (valid to 7.10)

Introduction

This guide is a collection of issues known to affect multiple/all versions of Intershop 7 that are using Oracle 11g or 12c. Solutions or (temporary) workarounds are provided, where applicable, to the best of our current knowledge. The following should be considered reading this document

  • The intended target audience is system administrators with advanced knowledge in Oracle Database 11g or higher.
  • Unless otherwise stated, issues shown here occur in all editions of Oracle (Standard Edition, Standard Edition One, Standard Edition 2, Enterprise Edition).
  • It is strongly recommended to review any MOS documents mentioned in connection with a certain issue, because meanwhile patches and/or solutions might be outdated and/or superseded.
  • Access to MOS documents generally requires an active OTN account, in some cases also an active CSI.

Glossary

AbbreviationTermDescription
CSICustomer Support IdentifierThe identifier of the support contract purchased from Oracle; needed to download patches and access certain areas in the support portal (MOS)
CTECommon Table ExpressionNamed temporary result set, defined within the scope of SQL statement
MOSMy Oracle SupportOracle's support portal and primary source of information about bugs and patches; requires an active OTN account (https://support.oracle.com)
OTNOracle Technology NetworkOracle's community network, providing access to documentation, knowledge and support
PSUPatch Set UpdateSet of patches to be installed on top of previously installed patch sets; e.g., updating from 11.2.0.4 to 11.2.0.4.1

SQL Loader Not Working on Windows With Oracle Client 12.1.0.2

This section replaces the outdated article with the ID 28E122 and the title SQLLDR not working on Windows with Oracle Client 12.1.0.2.

Affected Systems

Oracle 12.1.0.2 on Windows platforms (64-bit)

Symptoms

An attempt to execute sqlldr.exe (SQLLoader) fails due to a missing library (oranfsodm12.dll).

Solution / Workaround

This issue can be resolved by installing patchset 20315685 or later. Alternatively, either copy the missing DLL from a working 12.1.0.2 server installation on the same Windows platform. See MOS 1936068.1 for more details (requires an active Oracle CSI).

Note

It is also possible to use an older version of the Oracle client (11g), in order to work around this problem. This, however, may induce other issues and is not recommended as a permanent solution.

Sessions Hang Waiting on Event asynch descriptor resize

Affected Systems

Oracle XE (11g), 11.2.0.1 and 11.2.0.2 on all platforms

Symptoms

Symptoms are (very) long execution times of usually fast queries, with sessions executing them waiting on event asynch descriptor resize. Single wait times are typically very short, but lots of waits occur. This typically causes very high CPU usage and the sessions appears to hang (see MOS 9829397.8). Currently, only dbdelta (used to create a database difference report) seems to be affected by this bug.

Solution / Workaround

Patches are available and the issue is fixed in Oracle 11.2.0.3 and higher. If the problem occurs on Oracle XE, the only workaround is to disable asynchronous disk I/O (Oracle does not provide any patches for XE).

Note

In some cases this wait event might also indicate that affected SQL should be reviewed and tuned (see MOS 1273748.1 for details).

ORA-1652 Executing Queries Containing a WITH Clause

This section replaces the outdated article with the ID 23939F and the title Oracle server 11.2.0.3 can raise ORA-1652 when "WITH alias AS" queries are used.

Affected Systems

Oracle 11.2.0.3 and higher

Symptoms

If the optimizer chooses to materialize the result of a WITH clause (also known as subquery factoring or common table expression / CTE), Oracle implicitly creates a temporary table for this purpose. The execution plan would then contain an operation called TEMP TABLE TRANSFORMATION, as shown in the following example:

------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
...
|   2 |  TEMP TABLE TRANSFORMATION |                             |
|   3 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6601_F201F06C |
...

Under certain circumstances, these temporary tables, such as SYS.SYS_TEMP_0FD9D6601_F201F06C in the example above, will only be removed if the session that created them is closed. This, however, might never happen because application servers keep some connections in an open state, unless they are being shut down. Therefore, these temporary tables may accumulate and occupy (lots) of space in the temporary tablespace that is assigned to the user creating executing the queries with CTEs.

Solution / Workaround

This is not a bug and the only way to prevent implicit creation of such temporary tables is to force the optimizer to avoid  temp table transformations. There are multiple ways to achieve this:

  • Disable the subquery materialization globally (affects all statements running in the instance).

    ALTER SYSTEM SET "_with_subquery" = inline;

    It is recommended to disable subquery materialization on statement-level, as shown below, because doing so on a global level may cause unwanted side effects.

  • Disable subquery materialization for specific queries by adding a hint, for example:

    WITH cte AS (
     SELECT /*+ INLINE */ foo
     FROM bar
    )
    SELECT ...

Long Delays or Connection Resets When Establishing Database Connections via JDBC

Affected Systems

Oracle XE (11g), 11.2.0.1 or higher on all platforms (but mostly on Unixoid ones)

Symptoms

The authentication method used when establishing a connection to a database has changed in Oracle's JDBC drivers version 11.2.0.1. It now requires the operating system to provide enough entropy (i.e., random numbers) in order to work properly. If, for whatever reason, there is a lack of entropy the authentication will block until enough is available. If blocking takes too long then network/socket timeouts may occur and/or you may notice errors like

java.sql.SQLRecoverableException: IO Error: Connection reset
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:467)
Caused by: java.net.SocketException: Connection reset
 at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:404)
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:385)

Errors like the one above are not necessarily caused by a lack of entropy. They might also be related to problems with the network, a firewall or configuration of the server.

Solution / Workaround

In general, this problem can be solved by implementing a method to create or provide more entropy by the operating system. Usually this is done by running a background process (daemon). Most Linux distributions, for example, provide one of the following packages

  • rng-tools (enable use of hardware RNG)
  • haveged (feed randomness based on the HAVEGE algorithm into /dev/random)

Alternatively, though not recommended, you can use the non-blocking /dev/urandom instead of /dev/random. While the former provides less random numbers, it should be good enough for most practical purposes. You can use one of the following methods to achieve this:

  • Set JAVA_OPTS in $IS_HOME/bin/tomcat.sh to $JAVA_OPTS\Djava.security.egd= file:///dev/urandom
  • Set securerandom.source in ${JAVA_HOME}/jre/lib/security/java.security to /dev/urandom

    This approach will affect all Java processes on the system and may cause unwanted side-effects. Intershop therefore recommends to configure this in $IS_HOME/bin/tomcat.sh, as shown above.

Possible Data Corruption If SQL Using the Same Object Names in Multiple Schemas

This section replaces the outdated article with the ID 2G3590.

Affected Systems

Oracle 11.2.0.3.3 (i.e., 11.2.0.3 with PSU 3) or lower on all platforms, two or more database schemas with identical or similar sets of database objects (e.g., staging/replication setups)

Symptoms

If a system is affected by the bug and

  • A statement refers to some database object (table, view, synonyms) without using a fully qualified object name and/or
  • The unqualified object name resolves to another database object in a different database schema and/or
  • Sessions in different database schemas execute identical SQL statements

SQL statements may be executed in the wrong database schema. This may cause one or more of the following results:

  • SELECT statements return wrong results
  • DML statements (INSERT, UPDATE, DELETE, ...) result in data corruption
  • Unexpected errors, such as integrity constraint violations (e.g., ORA-02291) or objects/segments "disappear" (ORA-942)
  • Parse errors (e.g. ORA-904)

The effects of this bug may be very subtle and hard to diagnose, because performing some action in the wrong schema does not necessarily cause an error. Intershop therefore strongly recommends to patch or to upgrade your database in case it may be affected by this bug.

Solution / Workaround

More information and fixes related to the original bug can be found in MOS 13080778.8. The fix for this bug, however, introduced another issue that was fixed in another patch (see MOS 16344871.8). It is generally advised to install the latest patchset and/or PSU available for your platform, regardless of whether you are already affected by these bugs or not.


Dump import fails with ORA-00600: internal error code, arguments: [kqrhsh] / IMP-00003 ORACLE error 600 encountered

Affected Systems

Oracle 12.1.0.1 on all platforms. Other releases (11.2.0.3 and later) may also be affected.

Symptoms

An attempt to import a schema from a dump fails with

IMP-00003: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [kqrhsh], [51], [], [], [], [], [], [], [], [], [], []

A dump import is most likely not the only affected operation, but it is the most reliable way to reproduce the issue. You will also find messages like this in the alert log of the affected instance:

DDE: Problem Key 'ORA 600 [kqrhsh]' was completely flood controlled (0x4)
Further messages for this problem key will be suppressed for up to 10 minutes

Solution

Patch 17183227 seems to fix this issue and is available for various platforms and Oracle releases (see MOS 1596302.1 for more details). There is no known workaround for this issue.

Users Unable to Log On to the Database

Affected Systems

Oracle 12.2.0.1 and above on all platforms.

Symptoms

All database authenticated users are getting ORA-01017 (invalid username/password; logon denied).

Solution / Workaround

This is caused by initialization parameter sec_case_sensitive_logon being set to false by default in (all) ICM setups, which is incompatible with the new default settings of SQL*Net, starting with Oracle 12.2. There are two possible solutions (see MOS 2040705.1 for more details)

  1. Enable case-sensitive logons (recommended)

    ALTER SYSTEM SET sec_case_sensitive_logon = true;

    Modify SQLNET.ALLOWED_LOGON_VERSION_(SERVER|CLIENT) by adding the following line to $ORACLE_HOME/network/admin/sqlnet.ora on the database server:

    SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
    SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

Text indexes cannot be rebuild/dropped

Affected Systems

Oracle 12.1.0.1 and above on all platforms.

Symptoms

Various application features can be affected and will hang, time out or produce errors (e.g., (db{init|migrate|prepare}, replication). In general, one or more context indexes cannot be dropped, because there's a (subtle) locking conflict between the DROP INDEX and a background process (KTSJ, or Wnnn, aka. space management slaves).

Solution / Workaround

Run the following command as a privileged user (SYS, SYSTEM). Make sure to adjust the predicate (WHERE name LIKE ...) to identify locks on the object (index) you're interested in:

COL owner FOR a30
COL type FOR a30
COL name FOR a30

SELECT 
 session_id, 
 owner, 
 type, 
 name, 
 mode_held, 
 mode_requested 
FROM 
 dba_ddl_locks 
WHERE 
 name LIKE '%CATEGORY_AV_STR_VAL%'; -- replace with the name of the index you're interested in (in this case it was CATEGORY_AV_STR_VAL$1)

If the query produces no results, then this is not the problem and you have to contact a database administrator. If it does and the lock is in shared mode (mode_heldShare) on the $R table of the index (DR$<index_name>$R),  use the session_id to identify which process holds the lock:

SELECT
 sid,
 serial#,
 last_call_et,
 program
FROM
 v$session
WHERE
 module = 'KTSJ'
 AND sid = <session_id from query above>

If the query

  • returns a result and 
  • program is something like oracle@<server name> (Wnnn), where n can be a digit or letter, and
  • program is not oracle@<server_name> (SMCO)

then, and only then, it's safe to kill this process in order to release the lock. For example:

ALTER SYSTEM KILL SESSION '123, 12345' IMMEDIATE; -- replace 123 and 12345 with the values of <sid> and <serial#>, respectively (see query above)

It is generally not advisable to kill a (background) process, unless it's undoubtely identified as a KTSJ worker slave using the method described above. If the wrong process gets killed, the entire instance might fail.

After the blocking process is gone, you should be able to drop (and recreate) the index, or rerun dbmigrate/dbprepare.

Permanent Solution

It seems the problem can be avoided by turning off space pre-allocation, which can be done setting a "hidden" initialization parameter.

ALTER SYSTEM SET "_enable_space_preallocation" = 0
Disclaimer
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.
The Intershop Knowledge Portal uses only technically necessary cookies. We do not track visitors or have visitors tracked by 3rd parties. Please find further information on privacy in the Intershop Privacy Policy and Legal Notice.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.