Post Refresh, Load DWH and Load DWH access rights jobs Fail

book

Article ID: 193867

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

After a database refresh in PPM SaaS from a Production to Sandbox environment, the DWH jobs are no longer working.

Load Data Warehouse

and

Load Data Warehouse Access Rights

both fail with errors like below

ERROR 2020-06-21 23:44:59,245 [Dispatch Load Data Warehouse Access Rights : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below: 
2020/06/21 23:44:57 - audit table name -  & DWH_CFG_AUDIT
2020/06/21 23:44:59 - Oracle? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 
2020/06/21 23:44:59 - Oracle? - Couldn't execute SQL: BEGIN
2020/06/21 23:44:59 - Oracle? - DWH_SEC_RIGHT_LOAD(P_DBLINK => 'DWHschemaName_PPMschemaName',
2020/06/21 23:44:59 - Oracle? -                    P_ARRAY_SIZE => 50000);
2020/06/21 23:44:59 - Oracle? - END;
2020/06/21 23:44:59 - Oracle? - 
2020/06/21 23:44:59 - Oracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_SEC_RIGHT. SQLERRM : ORA-02019: connection description for remote database not found
2020/06/21 23:44:59 - Oracle? - ORA-06512: at "DWHschemaName.DWH_SEC_RIGHT_LOAD", line 42
2020/06/21 23:44:59 - Oracle? - ORA-06512: at line 2

Cause

This is due to the DB_LINK not being named correctly in the sandbox environment after the database refresh.
The Broadcom DBA team has taken steps to refresh their process with the team to ensure this does not happen again in the future with database refreshes.

Environment

Release : 15.6.1

Component : CA PPM SAAS APPLICATION

Resolution

Make sure the DB_LINK is in the following naming format (this is for Clarity SaaS only):

PPMschemaName_DWHschemaName.