Hello fellow community members,
I've had an interesting issue with a customer and wanted to share the solution with all of you:
System error in CA PPM UI when filtering on portlets including dates, or running jobs including dates. When looking in the app-ca log, error message:
ORA-01861: literal does not match format string
NLS_DATE_FORMAT should be set correctly on the database level.
Check the CSA HealthReport and see if you see a correct date format and other settings. If not you may have to correct it. Sometimes it displays correctly even when not set properly.
Make sure the OS locale is set to US and database OS also set to US. Make sure the other NLS parameters are set accordingly as per Installation guide, especially NLS_TERRITORY.
If the issue is only with NLS_DATE_FORMAT, to resolve this, try the following solutions:
Basic reset of NLS_DATE_FORMAT:
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile
Advanced reset of NLS_DATE_FORMAT (if basic does not work)
- Edit the pfile
- Shutdown the instance
- Create spfile from pfile
- Startup the database
Now posted as KB as well, TEC1066367
Thank you for sharing Nika!
NLS_DATE_FORMAT reset (CA PPM on Oracle throws ORA-01861: literal does not match format string in app-ca.log on any actions in UI that involve dates (such as filters, power filters etc.))