Clarity: Datamart Extraction job failed - Error in NBI_RF_SP - : ORA-01403: no data found

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

A running instance of Datamart Extraction Job fails on restart of the Background (BG) services. After restart all the new instances start to fail as well.
We found out that the issue was with the Stored Procedure 'NBI_RF_SP'. This Stored Procedure fetches information about the Index 'NBI_R_FACTS_U1' on the Table 'NBI_R_FACTS', drops the index and then recreates the same. During this process if BG is restarted and the Index creation fails, the later instances of the Datamart Job start failing due to the NO_DATA_FOUND exception in the SELECT query. (because the index does not exist any longer). For a workaround we recreated the Index separately before executing the job again, and the issue was resolved temporarily. The Stored procedure should check if the index exists before attempting to execute the 'drop' action.

Steps to Reproduce:

1. On customer-specific dataset, execute the 'Datamart Extraction' job (it will be known to take some amount of time to process)
2. During the processing of this job, the Background Services may stop suddenly at a point when it is executing the 'NBI_RF_SP' procedure
3. The 'NBI_R_FACTS_UI' index will not be recreated due to the interruption in service
4. Once the BG service is back up, execute the 'Datamart Extraction' job again
5. It will fail due to the missing 'NBI_R_FACTS_UI' index - check BG log file for full error message

Expected Result: Job should not fail due to missing index because the procedure is going to drop it and recreate it anyway.
Actual Result: Job fails due to missing index

Solution:

WORKAROUND:

Manually create the index prior to executing the 'Datamart Extraction' job after the BG services are up.

1. Get the Table Space Name
   SELECT NVL(tablespace_name,'')
   FROM user_indexes
   WHERE table_name = 'NBI_R_FACTS'
   AND index_name='NBI_R_FACTS_U1' ;
2. Recreate the Index
   CMN_CREATE_INDEX_SP('NBI_R_FACTS','NBI_R_FACTS_U1','UNIQUE','FACT_DATE, RESOURCE_ID',<tablespace_name>) ;

 

STATUS/RESOLUTION:

CLRT-73338: Resolved in CA PPM 14.1 

 

Environment

Release: ESPCLA99000-14.2-Clarity-Extended Support Plus
Component: