Full Load DWH Job Fails with error- ORA-12154: TNS:could not resolve the connect identifier specified

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse Full Load Job fails with below error:

Error 7/25/18 4:06 PM dwh_db_check_ - An error occurred executing this job entry : Couldn't execute SQL: UPDATE DWH_META_COLUMNS SET COL_MODIFIED = 1 WHERE (DWH_TABLE,DWH_COLUMN) IN ( SELECT META_TGT_TAB_NAME, META_TGT_COL_NAME FROM (SELECT UPPER(A.DWH_TABLE_NAME) META_TGT_TAB_NAME, UPPER(A.DWH_COLUMN_NAME) META_TGT_COL_NAME, UPPER(A.ATTR_DATA_TYPE) META_TGT_DATA_TYPE, A.ATTR_DATA_SIZE META_TGT_DATA_SIZE, B.DWH_TABLE, B.DWH_COLUMN, B.ATTRIBUTE_DATA_TYPE DWH_DATATYPE, SUBSTR(B.ATTRIBUTE_DATA_TYPE, INSTR(B.ATTRIBUTE_DATA_TYPE,'(', 1)+1,INSTR(B.ATTRIBUTE_DATA_TYPE,')',1)-(INSTR(B.ATTRIBUTE_DATA_TYPE,'(', 1)+1)) DWH_DATA_SIZE FROM DWH_META_COLUMNS@DBLINK A INNER JOIN DWH_META_COLUMNS B ON UPPER(A.DWH_TABLE_NAME) = B.DWH_TABLE AND UPPER(A.DWH_COLUMN_NAME) = B.DWH_COLUMN WHERE UPPER(A.ATTR_DATA_TYPE) LIKE '%VARCHAR%' AND UPPER(A.ATTR_TYPE) <> 'CLOB') WHERE META_TGT_DATA_SIZE<>DWH_DATA_SIZE) [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12154: TNS:could not resolve the connect identifier specified

This may happen after a server migration (but not only). 

Environment

Release: All Supported Clarity releases
Component: ODDWH

Resolution

1.Recreate the DBLink between the Clarity PPM Transactional database and the DWH database to resolve this problem, as per the Installation Guide documentation, e.g.

https://techdocs.broadcom.com/us/en/ca-enterprise-software/business-management/clarity-project-and-portfolio-management-ppm-on-premise/16-2-2/installing-and-upgrading/Install-With-Oracle.html

2.Restore the DWH database with a blank schema

Any time a server is getting migrated, the database link must be recreated