Deploy Clarity on an existing database, error in BG service

[Anon Anon]

Hello Everybody,

I'm trying to deploy clarity on an existed database, this is what I've done:
1. Install Clarity: v12.0.4
2. Attach a copy from a database on another server
3. Problem found on niku as it was an orphaned database user, fixed
4. NSA configuration says database is available, but still asking me to install Database, usually I don't need to do this, if I'm connecting to an existed database


So, now BG service can't be started, Clarity give me page not found, and the bg log is giving me this:

I think this is a problem with connecting to the database, but I tried to log in to SQL Management studio using 'niku' database user, and it looks I have the proper access, but still nothing happened

ERROR 2012-02-25 19:06:28,939 [WrapperSimpleAppMain] niku.security (none:none:none) UserSessionCache.insertToPersistence:PMD error
com.niku.union.persistence.PersistenceException:
SQL error code: 2812
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'CMN_SESSIONS_INS_ID_SP2'.
Executed:
{call CMN_SESSIONS_INS_ID_SP2(?,?,?,?,?)}
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement location="securityPMD.xml" id="security.insertUserSession_stmt" batchable="true" 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">
<sql dbVendor="all">
<text>
{call CMN_SESSIONS_INS_ID_SP2(?,?,?,?,?)}
</text>
<param name="userId" type="long" direction="IN" expressionListDelimiter=","/>
<param name="userName" type="string" direction="IN" expressionListDelimiter=","/>
<param name="sessionToken" type="string" direction="IN" expressionListDelimiter=","/>
<param name="num" type="long" direction="OUT" expressionListDelimiter=","/>
<param name="newSessionId" type="string" direction="OUT" expressionListDelimiter=","/>
</sql>
</statement>

Referenced by:
<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="security.insertUserSession_stmt" inputSource="map" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" xmlns="http://schemas.niku.com/2002/pmd"/>

Using input:
{userName=unknown, sessionToken=48da58b3:135b57a569e:-80001330189588189, userId=7, newSessionId=null}


at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:1800)

at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:1858)

at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2334)

at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:701)

at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:610)

at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:450)

at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:221)

at com.niku.security.cache.UserSessionCache.insertToPersistence(UserSessionCache.java:246)

at com.niku.security.service.AuthenticationService.init(AuthenticationService.java:325)

at com.niku.union.bgp.NikuBGServer.initServer(NikuBGServer.java:79)

at com.niku.union.bgp.NikuBGServer.main(NikuBGServer.java:184)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:240)

at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'CMN_SESSIONS_INS_ID_SP2'.

at com.ca.clarity.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.ca.clarity.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)

at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)

at com.ca.clarity.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)

at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)

at com.ca.clarity.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)

at com.ca.clarity.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)

at com.ca.clarity.jdbc.base.BaseStatement.postImplExecute(Unknown Source)

at com.ca.clarity.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)

at com.ca.clarity.jdbc.base.BaseStatement.commonExecute(Unknown Source)

at com.ca.clarity.jdbc.base.BaseStatement.executeInternal(Unknown Source)

at com.ca.clarity.jdbc.base.BasePreparedStatement.execute(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68)

at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$c476e394.execute(<generated>)

at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2313)

... 14 more
ERROR 2012-02-25 19:06:28,939 [WrapperSimpleAppMain] bgp.JobLogger (none:none:none)
java.lang.Exception: Problem : com.niku.union.security.SecurityException: Database error while inserting user session

at com.niku.union.bgp.NikuBGServer.initServer(NikuBGServer.java:92)

at com.niku.union.bgp.NikuBGServer.main(NikuBGServer.java:184)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:240)

at java.lang.Thread.run(Thread.java:595)

[Deactivated User]

Could not find stored procedure 'CMN_SESSIONS_INS_ID_SP2

suggests that the db is not OK.
Is it from the exact same version?
Do the other services start OK?
Does that SP exists in the database?

Is there a reason why you can't reinstall everyhting in NSA/CSA?

Martti K.

[Legacy User]

My guess is that your schemas are not set up correctly or your security is not correct.

[Anon Anon]

Hi,

Thanks all, I forgot to update the topic, I deleted the niku user and recreate it then added a user in niku database and it's solved, don't use the orphaned users query it's useless do it manually

Thanks

[Carlens Faustin]

I know this is old but I think I am experiencing a similar issue since I am trying to do the same thing but with Clarity 14.3
What do you mean by then added a user in niku database, probably a dumb question but what user are you referencing to?
I am getting this error 

ERROR 2020-09-10 12:01:05,209 [http-nio-80-exec-1] niku.security (clarity:unknown:-1:none) UserSessionCache.get:PMD error
com.niku.union.persistence.PersistenceException: 
SQL error code: 4063
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04063: view "NIKU.CMN_USER_SESSION_V" has errors

Executed:
 select  us.id,
                us.token,
                us.user_name,
                us.pwd,
                us.force_pwd_change,
                us.first_name,
                us.last_name,
                us.email_address, us.locale,
                us.timezone,
                us.last_pwd_change,
                us.bad_login_count,
                us.is_ldap,
                l.language_code,
                lu.lookup_code user_status,
                r.id resource_id,
                r.unique_name,
                us.home_url,
                us.nav_state,
                fm.id favorites_menu_id,
                us.screen_reader_opt,
                us.high_contrast_ui,
                us.user_uid
        from    cmn_user_session_v us left outer join
                    cmn_menu_items fm on  fm.principal_id = us.id
                                      and fm.principal_type = ?
                                      and fm.container_type_code = ?
                                      and fm.parent_menu_id is null,
                cmn_languages l,
                cmn_lookups lu,
                srm_resources r 
        where   us.language_id = l.id
        and     us.user_status_id = lu.id
        and     r.user_id = us.id
        and     us.token=? 
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement location="securityPMD.xml" id="security.getUserSession_stmt" 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">
  <sql dbVendor="all">
    <text>
        select  us.id,
                us.token,
                us.user_name,
                us.pwd,
                us.force_pwd_change,
                us.first_name,
                us.last_name,
                us.email_address, us.locale,
                us.timezone,
                us.last_pwd_change,
                us.bad_login_count,
                us.is_ldap,
                l.language_code,
                lu.lookup_code user_status,
                r.id resource_id,
                r.unique_name,
                us.home_url,
                us.nav_state,
                fm.id favorites_menu_id,
                us.screen_reader_opt,
                us.high_contrast_ui,
                us.user_uid
        from    cmn_user_session_v us left outer join
                    cmn_menu_items fm on  fm.principal_id = us.id
                                      and fm.principal_type = ?
                                      and fm.container_type_code = ?
                                      and fm.parent_menu_id is null,
                cmn_languages l,
                cmn_lookups lu,
                srm_resources r @READUNCOMMITTED@
        where   us.language_id = l.id
        and     us.user_status_id = lu.id
        and     r.user_id = us.id
        and     us.token=?
      </text>
    <param type="string" direction="IN" constantValue="USER" expressionListDelimiter=","/>
    <param type="string" direction="IN" constantValue="MENU" expressionListDelimiter=","/>
    <param name="sessionToken" type="string" direction="IN" expressionListDelimiter=","/>
  </sql>
</statement>

Referenced by:
<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="security.getUserSession_stmt" inputSource="map" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" xmlns="http://schemas.niku.com/2002/pmd"/>

Using input: 
{sessionToken=7Pd3hjZAysblzMTIc4NIb2NumOQKrB07APdRxyiIHzw0}

	at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:2085)
	at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2189)
	at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2823)
	at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:869)
	at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:769)
	at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:577)
	at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:307)
	at com.niku.security.cache.UserSessionCache.retrieveFromPersistence(UserSessionCache.java:232)
	at com.niku.security.cache.UserSessionCache.get(UserSessionCache.java:160)
	at com.niku.security.service.AuthenticationService.get(AuthenticationService.java:161)
	at com.niku.security.service.AuthenticationService.get(AuthenticationService.java:143)
	at com.niku.union.web.filter.ClarityContentFilter.doFilter(ClarityContentFilter.java:121)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.niku.union.web.filter.XSSFilter.doFilter(XSSFilter.java:80)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.niku.union.web.filter.GZIPResponseFilter.doFilter(GZIPResponseFilter.java:138)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.niku.union.web.filter.TenantSetupFilter.doFilter(TenantSetupFilter.java:50)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.niku.union.web.filter.CharsetFilter.doFilter(CharsetFilter.java:45)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.filters.AddDefaultCharsetFilter.doFilter(AddDefaultCharsetFilter.java:88)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at com.niku.union.web.valves.ErrorPageRedirectValve.invoke(ErrorPageRedirectValve.java:65)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:516)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1086)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:659)
	at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:223)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1558)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1515)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04063: view "NIKU.CMN_USER_SESSION_V" has errors

[Nika Hadzhikidi]

Hi Carlens

Your error is different:

Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04063: view "NIKU.CMN_USER_SESSION_V" has errors

Please ask your DBA to compile the view CMN_USER_SESSION_V and let you know the exact errors they face (they may be able to address them)

Hope this helps -Nika

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------

[Carlens Faustin]

Hi Nika,

Thank you for your answer. Also when i tried accessing Clarity after the services where started i got a "HTTP 403 - Access tothe specified resource has been forbidden" that is what prompted me to look into the logs, hoefully that fixes the issue. I will get in touch with the DBA and see what comes out of it. 

Thanks,
Carlens