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

book

Article ID: 107893

calendar_today

Updated On:

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 [email protected] 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

Recreate the DBLink between the Clarity PPM Transactional database and the DWH database to resolve this problem, as per the "Set Up a Database Link for the Data Warehouse" of the 15.7.1 install Guide

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