Sequences Issue with Clarity Database after Refresh
search cancel

Sequences Issue with Clarity Database after Refresh

book

Article ID: 11285

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

During post-refresh, one or more of the following issues occur:

  • Cannot log in / Cannot run jobs/ Cannot run processes
  • Unable to upgrade Clarity
  • Cannot create baselines, cost plans, projects, programs, Cannot edit portfolios
  • Unique Constraint Violations when performing actions in the application 
  • Rate Matrix edits cannot be performed/ Time Slicing job fails
  • System Error is thrown attempting to schedule jobs
  • Logs will contain errors such as:
    ORA-00001: unique constraint (PPM.TABLE_NAME_PK) violated (example table name: CMN_SEC_ASSGND_OBJ_PERM_PK)

Cause

  • Although hot back-ups are possible on the database, there may still be an issue with table sequences.
  • These symptoms can occur when a hot backup was performed for later refresh/restore
  • If the backup was taken while the Application and Background services are up, there is a possibility of data being changed.
  • If the table sequence was backed-up prior to the table data, it is possible for the sequence to be lower then the maximum record ID in the table, which causes the mismatch

Resolution

Microsoft SQL Server:
The table sequences are stored in a one row table with a prefix of CMN_SEQ. Below are examples of checking the sequence for common tables.  This is not inclusive of all table sequences.

select * from CMN_SEQ_PRTASK;
select max(prid) FROM PRTASK;

select * from CMN_SEQ_PRTEAM; 
select max(prid) from PRTEAM; 

select * from CMN_SEQ_PRTIMEENTRY; 
select max(prid) from PRTIMEENTRY; 

If the above query returns a lower number than the bottom query, then the table sequence number is out of sync with the maximum data record id.
The next table sequence number will need to be updated using an update statement.

Here is an example:

update CMN_SEQ_PRTASK
set last_id = ??????? (here you can enter a number 1000 higher than the max id from the prtask table)

After updating the LAST_ID column, restart the APP and BG services. Often the old sequence value is cached in memory on the application server, therefore a restart will be needed to ensure that the new value in the database is used.

Oracle:
In Oracle many sequences are stored in a standard oracle sequence. Most of which are standardized as the <tablename> followed by an _S1 suffix.

SELECT TABLE_NAME_S1.nextval FROM dual ; 
SELECT MAX(ID) FROM TABLE_NAME ; 


If the top query returns a lower number than the bottom query then the table sequence number is out of sync with the maximum data record Id. This will need to be updated. You can run the sequence check again (perhaps several times) because each time it is executed, the actual table sequence value is increased by 1.

Alternatively, to increase the next value in the sequence by a higher amount, run the 4 following queries one by one (replace TABLE_NAME with actual table name) : 

ALTER SEQUENCE TABLE_NAME_S1 increment by 10000; --- Use a bigger number here if the MAX(ID) is over 10,000 greater than the next value in the sequence.
SELECT TABLE_NAME_S1.nextval FROM dual; 
ALTER SEQUENCE TABLE_NAME_S1 increment by 1; 
SELECT TABLE_NAME_S1.nextval FROM dual;

Note: You must run all four statements one by one

  • Some sequences are stored in the PRCOUNTER table. In the PRCOUNTER table, the PRNAME equates to the table name and PRLASTID equates to the last value used in the sequence.
  • When in doubt, please contact the DBA team to assist with resolving the database sequence issue.
  • After correcting the sequences, the services have to be restarted.

Additional Information

Powered by Wolken Software