After refreshing a Clarity database backup, the application does not create objects or insert data into the intended tables

Document ID : KB000011285
Last Modified Date : 02/04/2019
Show Technical Document Details
Issue:

Post refresh in a Clarity PPM environment, running into various issues. The issues that may be seen include but not limited to the following actions:

  • Cannot log in
  • Cannot run jobs
  • Cannot run processes
  • Cannot create baselines
  • Cannot create cost plans
  • 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)
Environment:
This refers to a backup export (exp or expdp)
Cause:

These symptoms can occur when a hot backup was performed for later refresh/restore, evidenced by app-ca and bg-ca logs. If the backup was taken while the Application and Background Services were running there is a chance that data was 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. Although hot back-ups are fully support by the database, there still may be an issue with table sequences.

Resolution:

To prevent this issue, Broadcom Support recommends a back-up to be taken when there is little to no activity in the application and ideally when the services are stopped.

Microsoft SQL Server Databases:
In MSSQL Server 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 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. The next table sequence number will need to be updated using a simple update statement.

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, you may need to restart the application and background services. Often the old sequence value is cached in memory on the application server and therefore a restart will be needed to ensure that the new value in the database is used.

Oracle Databases:
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 following queries: 

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;

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.

Additional Information:
This could be avoided in future by adding the following to the datapump (expdp) par file
FLASHBACK_TIME="TO_TIMESTAMP(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'), 'DD-MM-YYYY HH24:MI:SS')"

This is discussed at:
https://dba.stackexchange.com/questions/161836/sequence-number-shows-a-lower-value-after-dump-file-import