ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH fails with "ORA-02019: connection description for remote database not found"

book

Article ID: 128026

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse (DWH) - Full Load fails with the below error message:

ERROR 2019-02-24 19:00:15,888 [Dispatch pool-5-thread-5 : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2019/02/24 19:00:15 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2019/02/24 19:00:15 - dwh_db_check_ - Couldn't execute SQL: UPDATE DWH_META_COLUMNS SET COL_MODIFIED = 1 WHERE (DWH_TABLE,DWH_COLUMN) IN
2019/02/24 19:00:15 - dwh_db_check_ -     (
2019/02/24 19:00:15 - dwh_db_check_ -     SELECT
2019/02/24 19:00:15 - dwh_db_check_ -     META_TGT_TAB_NAME,
2019/02/24 19:00:15 - dwh_db_check_ -     META_TGT_COL_NAME
2019/02/24 19:00:15 - dwh_db_check_ -     FROM
2019/02/24 19:00:15 - dwh_db_check_ -         (SELECT
2019/02/24 19:00:15 - dwh_db_check_ -             UPPER(A.DWH_TABLE_NAME) META_TGT_TAB_NAME,
2019/02/24 19:00:15 - dwh_db_check_ -             UPPER(A.DWH_COLUMN_NAME) META_TGT_COL_NAME,
2019/02/24 19:00:15 - dwh_db_check_ -             UPPER(A.ATTR_DATA_TYPE) META_TGT_DATA_TYPE,
2019/02/24 19:00:15 - dwh_db_check_ -             A.ATTR_DATA_SIZE META_TGT_DATA_SIZE,
2019/02/24 19:00:15 - dwh_db_check_ -             B.DWH_TABLE,
2019/02/24 19:00:15 - dwh_db_check_ -             B.DWH_COLUMN,
2019/02/24 19:00:15 - dwh_db_check_ -             B.ATTRIBUTE_DATA_TYPE DWH_DATATYPE,
2019/02/24 19:00:15 - dwh_db_check_ -             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
2019/02/24 19:00:15 - dwh_db_check_ -         FROM
2019/02/24 19:00:15 - dwh_db_check_ -             [email protected]_DWH A INNER JOIN DWH_META_COLUMNS B
2019/02/24 19:00:15 - dwh_db_check_ -         ON
2019/02/24 19:00:15 - dwh_db_check_ -             UPPER(A.DWH_TABLE_NAME) = B.DWH_TABLE AND UPPER(A.DWH_COLUMN_NAME) = B.DWH_COLUMN
2019/02/24 19:00:15 - dwh_db_check_ -         WHERE
2019/02/24 19:00:15 - dwh_db_check_ -             UPPER(A.ATTR_DATA_TYPE) LIKE '%VARCHAR%' AND UPPER(A.ATTR_TYPE) <> 'CLOB')
2019/02/24 19:00:15 - dwh_db_check_ -   WHERE META_TGT_DATA_SIZE<>DWH_DATA_SIZE)
2019/02/24 19:00:15 - dwh_db_check_ -
2019/02/24 19:00:15 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02019: connection description for remote database not found
2019/02/24 19:00:15 - audit table name -  & DWH_CFG_AUDIT
2019/02/24 19:00:15 - audit table name -  & DWH_CFG_AUDIT

Cause

Database link is not working correctly.

Environment

Release: All Supported
Component: Data Warehouse (DWH)

Resolution

Step 1: Recreate the DBLINK

You can do this with one of the following methods:

Option 1: NSA/CSA

  1. Connect to the CSA server
  2. Go to the Data Warehouse tab
  3. Click Save on the Data Warehouse page. This action will automatically recreate the DBLINK.
  4. Make sure you have no error appearing in CSA

Option 2: Manually recreate the DB link

Ask the DBA to recreate the Database link manually, providing the DB with the name of the link.

Step 2: Rerun the job

Once you applied one of the workarounds above, re-run a Full Load DWH and it should go through successfully.

Additional Information

See also: Data Warehouse (DWH) known issues for Clarity