Clarity

Hi,

We have recently upgraded Clarity ppm from v14.4 to v15.5 successfully. But when installing PMO Accelerator (clarity/bin> admin content csk). It throws few error messages. do anyone have idea about it?

-------------------------------------------------------------------------------

ERROR 09-10 05:39:40,226 - Problem creating object or updating custom object table for object fiscaltimeperiod
10/9/18 5:39 AM (admin) com.niku.union.persistence.PersistenceException:
10/9/18 5:39 AM (admin) SQL error code: 2264
10/9/18 5:39 AM (admin) Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02264: name already used by an existing constraint
10/9/18 5:39 AM (admin) Executed:
10/9/18 5:39 AM (admin) alter table ODF_CA_FISCALTIMEPERIOD add constraint ODF_CA_FISCALTIMEPERIOD_pk primary key ( id ) using index tablespace INDX_LARGE
10/9/18 5:39 AM (admin) Derived from statement:
10/9/18 5:39 AM (admin) <?xml version="1.0" encoding="UTF-8"?>
10/9/18 5:39 AM (admin) <statement location="unionPMD.xml" id="union.dynamicSQL" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="none" xmlns="http://schemas.niku.com/2002/pmd">
10/9/18 5:39 AM (admin) <sql dbVendor="all">
10/9/18 5:39 AM (admin) <text>@dynamic@</text>
10/9/18 5:39 AM (admin) <param direction="IN" dynamicReplacement="@dynamic@" expressionListDelimiter=","/>
10/9/18 5:39 AM (admin) </sql>

------------------------------------------------------------------------------

10/9/18 5:59 AM (admin) ERROR 09-10 05:59:11,110 - Database Error thrown
10/9/18 5:59 AM (admin) com.niku.union.persistence.PersistenceException:
10/9/18 5:59 AM (admin) SQL error code: 4098
10/9/18 5:59 AM (admin) Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04098: trigger 'NIKU.NOK_SRM_ADD_CALENDAR_LINK' is invalid and failed re-validation

-------------------------------------------------------------------------------------

10/9/18 5:59 AM (admin) <statementRef id="xog.insertCmnUserSRMResource_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>
10/9/18 5:59 AM (admin) Using input:
10/9/18 5:59 AM (admin) {lastName=Admin, isExternal=0, resType=0, externalId=csk.admin, fullName=Admin, PMO, isActive=1, userId=5515000, firstName=PMO, uniqueName=csk.admin, execute::batch=false, parameterSet=[], externalSourceID=1, id=5524001, visitor=com.niku.xql2.XQLVisitor@71fac822, personType=300, parameters=[null:id:5524001:, null:lastName:Admin:, null:firstName:PMO:, null:uniqueName:csk.admin:, null:email:admin@mailserver.com:, null:personType:300:, null:isActive:1:, null:isExternal:0:, null:externalSourceID:1:, null:externalId:csk.admin:, null:resType:0:, null:userId:5515000:, null:fullName:Admin, PMO:], value=xog.insertCmnUserSRMResource, email=admin@mailserver.com}
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:2100)
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2204)
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2881)
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:869)
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:769)
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:577)
10/9/18 5:59 AM (admin) at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:307)
10/9/18 5:59 AM (admin) at com.niku.xql2.pmd.PMDDataSource.execute(PMDDataSource.java:214)

---------------------------------------------------------------------------

10/9/18 5:59 AM (admin) Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04098: trigger 'NIKU.NOK_SRM_ADD_CALENDAR_LINK' is invalid and failed re-validation
10/9/18 5:59 AM (admin) at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source)
10/9/18 5:59 AM (admin) at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source)
10/9/18 5:59 AM (admin) at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source)

---------------------------------------------------------------------

Error Applying XOG: Failure occurred while applying users/csk_admin.xml
10/9/18 5:59 AM (admin) Check E:\Clarity\logs\content\xog\csk\users/csk_admin_out.xml for errors
10/9/18 5:59 AM (admin) Failed to install content pack. ID: csk
E:\Clarity\META-INF\content\xog\csk\install.xml:242: exec returned: 2

Can you attach the log from E:\Clarity\logs\content\xog\csk\users/csk_admin_out.xml for errors

Also from what version you are upgrading? 

Yes, Suman. Upgraded v14.4 to v15.5 (base release15.5.0.225)

<?xml version="1.0" encoding="UTF-8"?>
<XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
<Object type="user"/>
<Status elapsedTime="0.564 seconds" state="FAILURE"/>
<Statistics failureRecords="1" insertedRecords="0" totalNumberOfRecords="1" updatedRecords="0"/>
<Records>
<Record>
<KeyInformation>
<column name="ALL">ALL RECORDS</column>
</KeyInformation>
<ErrorInformation>
<Severity>WARNING</Severity>
<Description>New Users Password will be Defaulted to Value niku2000</Description>
</ErrorInformation>
</Record>
<Record>
<KeyInformation>
<column name="externalSource">NIKU</column>
<column name="externalId">csk.admin</column>
<column name="userName">csk.admin</column>
</KeyInformation>
<ErrorInformation>
<Severity>FATAL</Severity>
<Description>This record has not been inserted</Description>
<Exception><![CDATA[SQL Exception with error code : 4098]]></Exception>
</ErrorInformation>
</Record>
</Records>
</XOGOutput>

Did you had PMO on 14.4? And did you grant all the rights to admin prior to installation of PMO. 

No Suman. We are doing it for the first time to get the new user experience.

Very few rights were missing, I have provided the remaining rights.

Should I run the command again?

admin content csk

ideally you should roll back prior to upgrade to 15.5 and in 14.4 you should install PMO and then upgrade to 15.5 and install PMO. 

It's a sandbox environment. Can I rollback to v15.5 base version (Clarity folder) and then start the pmo installation again?

Above method, we can perform in other environments.

Is that a good workaround for time being?

For sandbox its ok. 

Hi Suman,

I restored and tried again but still got the same error, despite providing our niku DB user alter trigger and any table command.

Any suggestions?

I see you raised a case too and team will work, there is something definitely missing 

Hi,

Sharing the resolution provided by Karl from CA.

1. Dropped ODF_CA_FISCALTIMEPERIOD_PK (constraint and index) as it was shared by the different object.

ALTER TABLE ODF_CA_FISCALTIMEPERIOD_ORIG(which be took backup) DROP constraint ODF_CA_FISCALTIMEPERIOD_PK;

DROP index ODF_CA_FISCALTIMEPERIOD_PK;

2.   Drop below Trigger as it wasn't working

----------------------------------------------------------------------

DROP TRIGGER NIKU.NOK_SRM_ADD_CALENDAR_LINK;

3. Alter the NLS_DATE_FORMAT parameter by following the below procedure:-
Error MEssage - "SQL Exception with error code : 1861"

Aka: ORA-01861: literal does not match format string
Issue - This almost exclusively happens due to the NLS_DATE_FORMAT parameter, and it is one that Oracle does not permit changing on the fly. It is not related to the other problem we faced or its solutions.

The steps to set this correctly would be as follows: 

A. Stop any CA PPM services (and other applications) connected to the database server instance.
B. Connect to the database as a 'sys' (preferred) or 'system' user able to make system changes (to be performed by DBA)
         a. CONNECT username/password AS SYSDBA
         b. alter system set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' scope=spfile;
         c. Shutdown and restart Oracle

C. Restart any applications that were previously connected and retry the steps for applying the csk content again. (admin content csk)

Thanks, Suman for your quick reply,

Br,

Varun Chopra