Serena Support recently issued an alert on our web site to address a possible Dimensions sequencing issue on Oracle databases around unique identifiers. We anticipate only a small number of customers may be affected but it is important to take proactive steps to avoid future failure. If your database is affected and not addressed, the impact may leave Dimensions unable to add new items into the library. This problem can be avoided by following the simple steps outlined in Solution S140907 from our knowledge base. If you have already completed this action following the alert posting, there is no additional action needed.
Though we have not identified the root cause of the problem, we have identified that on rare occasions a rogue value is inserted into a database column containing a sequence number. At a later time, when the Dimensions dmdba issues the sseq command, it will scan all columns containing these sequence numbers and reset the Oracle sequence generator to a number higher than the highest used number, skipping any unused values. In some cases, this results in moving the Oracle sequence generator by millions or billions of values (leaving nearly all of the lower values unused). The database upgrade script that upgrades the schema from one version to another also calls the sseq command, which may also cause this jump in values to occur. Our testing has identified a specific instance of this occurring when a customized trigger was improperly requesting a new unique identifier, but we cannot state with certainty that this is the only cause.
As previously stated, this has only occurred in a few environments. To identify when a sequencing jump may have occurred, Solution S140907 provides a SQL query that will return the next available sequence number. If that value is greater than 2 billion, you should contact us for a further examination of your Dimensions database records. Support has SQL scripts you can run that we will help you resolve the sequencing jump before it impacts Dimensions activity. These scripts will take several hours to complete and Dimensions will need to be shut down during the process. Preemptively addressing this potential issue will allow any necessary action to be completed during a scheduled down time.