CA PPM: After refreshing/restoring a database backup, the application does not create objects or insert data into the intended tables

Document ID : KB000011285
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

Various symptoms can occur when a hot backup was performed for later refresh/restore, evidenced by app-ca and bg-ca logs. They include but not limited to the following actions:

-Cannot log in
-Cannot run jobs
-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

Question:

What can be done after issue occurs only in the destination environment where the database was refreshed?

Answer:

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.

CA 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 PRJ_BLB_SLICES_S1.nextval FROM dual ; 
SELECT MAX(ID) FROM PRJ_BLB_SLICES ; 

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.

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.