Troubleshooting
Problem
Oracle RAC Database sequencing issue - Sequence numbers are out of order
Symptom
MCS instances that are moved to an Oracle RAC database architecture can face issues with the sequences being out of order in processes such as cart config lines, copy line functionality, etc. The items are out of sequence. This can be attributed to the fact that there is more than one node in an Oracle RAC and that the order flag for the sequence is N (default).
For example, there are three oracle RAC nodes (N1, N2, N3). The Oracle sequence is the following for each node:
Oracle sequence for N1 = 100
Oracle sequence for N2 = 120
Oracle sequence for N3 = 140
If a request comes to N2 first, then the sequence would be between 120 and 140.
If the next request comes to N1 the sequence would be between 100-120. If N1 hits the upper limit of 120 then the next sequence would be between 160-180 (because for N3 it has a cached sequence between 140-160).
The sequence cache size can also be changed. It is 20 by default. If these numbers need to be in sequence then set the order flag to Y, but note that there is some performance impact.
The behavior is very similar to what is explained in the following blog. it is also mentioned in various Oracle forums.
http://www.pythian.com/news/383/sequences-in-oracle-10g-rac
Error Message
When copying/moving lines from a basket to another it does not copy them in the same order as they have been added. In the case of a search index build, search index names are not as per sequence. The random nature of sequence generation can be attributed to how Oracle RAC behaves similar to the sequence order being set as 'no-order'.
Resolving The Problem
As per the research from Support, change the order_flag parameter of the appropriate sequence to true, and the cache size should be increased (i.e - 40). It is suggested not to change it for all the sequences but change it only for the sequence in question as there can be a performance impact.
Historical Number
NFX7274
Product Synonym
[<p><b>]Escalation ID[</b><p>];79436;[<p><b>]Severity[</b><p>];Normal;[<p><b>]Type[</b><p>];NormalFix;[<p><b>]Build[</b><p>];7..x, 8.x
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21551760