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

SQL Loader 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).

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).

ORA-1652 Executing Queries Containing a WITH Clause

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)

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

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)

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_held = Share) 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)

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