My Oracle Support Banner

Locks On Hot Object With RAC 12.2 X vs.11.2.0.4 Current SQL: select spare6 from user$ where user#=:1 (Doc ID 2564792.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database Appliance Software - Version 12.2.1.1.0 to 12.2.1.4.0 Bare Metal [Release 12.2]
Information in this document applies to any platform.

Symptoms

An upgrade of a previously working application no longer works resulting in locking.

Evidence pointed to the following query as occurring at the same time as the Hang in the Application query.
Other Applications or load testing on the same configuration did not reproduce the problem.

The problem query seen in AWR report included:

Other waits reported including

or as seen in hang analyze outputs 

Altering the database from RAC ( two node or more cluster) to single-instance avoided the problem all other things being equal.

The application queries, data set were identical in pre-upgrade and after going to single instance from RAC.

Changes

 In this case the following changes occurred:

The upgrade in this case was to a new OS, New HW, RAC from single-instance, new Database version and no testing.
Determining the source of the problem with this many changes was not trivial.

Of these changes ONLY the following is directly related to the problem.

Change in OS.

Originally The change in the database version could not be totally ruled out.
However, the RDBMS version is actually tied to the OS version which happened to be updated at the same time
If the upgrade was only the OS and not the RDBMS then we would not have encountered the problem

Another factor which was significant was from single-instance to RAC (without testing or design considerations).
The reason this was significant is that the problem ONLY reproduced for this configuration if using RAC:
The problem did not occur using Single-Instance.

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.