CA PPM: Datamart Extraction job fails with error in NBI_EXTR_PCF_SP

Document ID : KB000006476
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

The job fails with the BG log error:

java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - Executing NBI_EXTR_PCF_SP: ORA-20000: Error in NBI_EXTR_PCF_SP - executing NBI_PROJECT_CURRENT_FACTS_SP: ORA-20000: Error in NBI_PROJECT_CURRENT_FACTS_SP - Calling NBI_PRTF_FM_SP since FM is enabled: ORA-01403: no data found

ORA-06512: at "PPM1510.NBI_EXTRACT_SP", line 362

ORA-06512: at line 1 

Cause:

This issue is caused by an interruption in the Datamart Extraction job, specifically in the stored procedure NBI_PROJECT_CURRENT_FACTS_SP which calls stored procedure NBI_PRTF_FM_SP. Within the NBI_PRTF_FM_SP, the index NBI_PRTF_FM_U1 is dropped but if the interruption occurs after it is dropped, it does not recover, therefore the index will need to be manually recreated.

Resolution:

1.Log in as the database user.

2.Run:

select * from user_indexes where table_name = 'NBI_PRTF_FM' 

If the index(NBI_PRTF_FM_U1) does not exist, run command:

execute CMN_CREATE_INDEX_SP('NBI_PRTF_FM','NBI_PRTF_FM_U1','UNIQUE','PROJECT_ID','the_index_tablespace');

3. Rerun the Datamart Extraction job.

Additional Information:

https://communities.ca.com/thread/241700657
CA PPM Tech Tip: How to check the Datamart (Datamart Extraction and Datamart Rollup jobs)