Load DWH job fails with ORA-01843 error

Products

• Clarity PPM On Premise

Issue/Introduction

After running the Full Load Data Warehouse job, you are getting an error containing the following:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month

Environment

Release : Any

Component : Clarity Data Warehouse

Cause

nls_date_format Oracle database parameter is not configured as per the requirements.

Resolution

Compare your database parameters with the required settings for your release, with special focus on the nls_date_format parameter.

If you have granted V_$PARAMETER rights to Clarity and DWH schemas, you can check the current parameters if you go to Administration > Security and Diagnostics > Health Report, and check both Database and Data Warehouse tabs and correct the Database Parameters settings that are marked with a red diamond.

If you have not granted V_$PARAMETER rights to those schemas, check with your Database Administrator.

Additional Information

If the issue is with NLS_DATE_FORMAT, to resolve this, try one of the following solutions:

Basic reset of NLS_DATE_FORMAT:
1. Run the command:

alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile

2. Restart the database (the change requires a restart)

Advanced reset of NLS_DATE_FORMAT

0. create pfile from spfile;
1. Edit the pfile ora file and change this to
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'

2. Then recreate the SPFILE.
- Edit the pfile
- Shutdown the instance
- Create spfile from pfile
- Startup the database