Clarity database sequences out of sync after restoring Hot Backup

Products

• Clarity PPM On Premise

Issue/Introduction

We have restored a Clarity dataset backup, post that lot of errors seen pertaining to sequence .

Caused by: java.sql.SQLIntegrityConstraintViolationException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (NIKU.ODF_AUD_OBJ_INST_CONTEXT_PK) violated

Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (CLAP.CMN_SCH_JOBS_PK) violated

Environment

All supported clarity versions.

Cause

Sequence mismatch due to hot backup. 

Resolution

• Stop all the clarity services
• Reset all the sequences using the sql attached (Oracle & PostgreSQL) 

Additional Information

• After running the Postgres script, the last_value column in pg_sequences would be set to NULL. This is expected. When the sequence is accessed by the application, the number that was provided by the sequence would be populated in this column. It does not affect the ability of the sequence to provide the correct number when asked for.