Clarity PPM - Load Data Warehouse job fails with ORA-01017

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse job fails with the following error message.

 

Caused by: java.sql.SQLInvalidAuthorizationSpecException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01017: invalid username/password; logon denied

Environment

All supported versions of Clarity PPM.

Cause

This could be caused by one of the following:

1. A refresh of the environment done from another system, and the Username and Password details for DWH not updated in properties.xml or CSA
2. An expired DB password
3. Incorrect login information provided in the DB Link including a typo/incorrect password.
   
   
   • To prevent this from being the cause, after a change has been made to the DB link, check system date or any other data from source DB using DB link
   • Running the following query: Select sysdate from [email protected]<DWHSchema>_<ClaritySchema>; or other similar query should be run after any DB link changes to make sure the credentials are correct.

Resolution

Solution 1: Check to make sure you can connect via SQL using Properties.xml user name/password

1. Obtain the Username and Password that is stored in the properties.xml
2. Try to login using these exact same credentials, through a SQL Developer client
3. If the login fails with the exact same error, then it is established that the credentials are wrong.
4. Contact your system's DBA to obtain/reset the DB password
5. If the login succeeds in step 2, proceed to solution 2

Solution 2: Check for any issues with incorrect / typos in the log in information provided in the DB Link

1. Run the following query as the DWH User: Select sysdate from [email protected]<DWHSchema>_<ClaritySchema>
2. Results should look similar to the below:

SYSDATE
---------------
14-APR-20
    3.  If there are any errors or data does not return in the query, proceed to next step
    4.  Recreate the DB Link ensuring that the user name and password are correct (check for any typos including special characters or spaces)
    5.  Rerun the below query to validate the sysdate is returned now
      Select sysdate from [email protected]<DWHSchema>_<ClaritySchema>
    6. Run the Load DWH Access Rights job, then the Load DWH job - Full
    Note: A restart of the Clarity PPM services should not be needed

Solution 3: Restart the Clarity PPM services

1. Only if Solution 1 and Solution 2 do not come back with any issues, restart all the Clarity PPM Services
2. Run the Load Data Warehouse job again

Additional Information

See KB: 141061 for tips on how to optimize use of Self Service for Clarity PPM