WebCenter Capture 11.1.1.8.0 - reassociateSecurityStore(domain="your_domain", servertype="DB_ORACLE",datasourcename="jdbc/opss-ds", jpsroot="cn=jpsTestNode", join="false") fails with error JPS-00027: There was an internal error: java.sql.BatchUpdateException: ORA-00904: "ATTRKIND": invalid identifier

WebCenter Capture 11.1.1.8.0 - reassociateSecurityStore(domain="your_domain", servertype="DB_ORACLE",datasourcename="jdbc/opss-ds", jpsroot="cn=jpsTestNode", join="false") fails with error JPS-00027: There was an internal error: java.sql.BatchUpdateException: ORA-00904: "ATTRKIND": invalid identifier

If you are trying to execute reassociateSecurityStore command on one of the cluster nodes of WebCenter Capture 11.1.1.8.0 to resolve the issue detailed in this post, you may run into errors as below.

JPS-00027: There was an internal error: java.sql.BatchUpdateException: ORA-00904: "ATTRKIND": invalid identifier

wls:/your_domain/serverConfig> reassociateSecurityStore(domain="your_domain", servertype="DB_ORACLE",datasourcename="jdbc/opss-ds", jpsroot="cn=jpsTestNode", join="false")


Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root.
For more help, use help(domainRuntime)

Command FAILED, Reason: JPS-00027: There was an internal error: java.sql.BatchUpdateException: ORA-00904: "ATTRKIND": invalid identifier


Traceback (innermost last):
 File "<console>", line 1, in ?
 File "MW_HOME\fmw\product\111\ORACLE~1\common\wlst\jpsWlstCmd.py", line 1343, in reassociateSecurityStore
 File "MW_HOME\fmw\product\111\ORACLE~1\common\wlst\jpsWlstCmd.py", line 1314, in reassociateSecurityStoreImpl

       at weblogic.rjvm.ResponseImpl.unmarshalReturn(ResponseImpl.java:237)
       at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:223)
       at javax.management.remote.rmi.RMIConnectionImpl_1036_WLStub.invoke(Unknown Source)
       at weblogic.management.remote.common.RMIConnectionWrapper$16.run(ClientProviderBase.java:960)
       at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
       at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
       at weblogic.security.Security.runAs(Security.java:61)
       at weblogic.management.remote.common.RMIConnectionWrapper.invoke(ClientProviderBase.java:958)
       at javax.management.remote.rmi.RMIConnector$RemoteMBeanServerConnection.invoke(RMIConnector.java:1029)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:606)


javax.management.MBeanException: javax.management.MBeanException: JPS-00027: There was an internal error: java.sql.BatchUpdateException: ORA-00904: "ATTRKIND": invalid identifier



You may hit these errors because OPSS schema may not have been upgraded and hence is not at version 11.1.1.8.0.

Verify OPSS schema version by running below SELECT query.

SQL> SELECT OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY;

If OPSS schema version is not 11.1.1.8.0 (let's say eg: OPSS version is 11.1.1.7.0 with U flat set to N), then run ./psa and upgrade OPSS schema to 11.1.1.8.0

Once OPSS is upgraded to 11.1.1.8.0, run reassociateSecurityStore again.

reassociateSecurityStore(domain="your_domain", servertype="DB_ORACLE",datasourcename="jdbc/opss-ds", jpsroot="cn=jpsTestNode", join="false") 

This time it should execute without errors as below:

Re-associate to OPSS Security Store

cd ORACLE_COMMON_HOME\common\bin
./wlst.sh
connect("Admin_User","Admin_User_Password","t3://hostname:port")

wls:/your_domain/serverConfig> reassociateSecurityStore(domain="your_domain", servertype="DB_ORACLE",datasourcename="jdbc/opss-ds", jpsroot="cn=jpsTestNode", join="false")
Already in Domain Runtime Tree

Starting policy store reassociation.
The store and ServiceConfigurator setup done.
Schema is seeded into the store
Data is migrated to the store. Check logs for any failures or warnings during migration.
Data in the store after migration has been tested to be available
Update of in-memory jps configuration is done
Policy store reassociation done.
Starting credential store reassociation
The store and ServiceConfigurator setup done.
Schema is seeded into the store
Data is migrated to the store. Check logs for any failures or warnings during migration.
Data in the store after migration has been tested to be available
Update of in-memory jps configuration is done
Credential store reassociation done
Starting Keystore reassociation
The store and ServiceConfigurator setup done.
Schema is seeded into the store
Data is migrated to the store. Check logs for any failures or warnings during migration.
Data in the store after migration has been tested to be available
Update of in-memory jps configuration is done
Keystore reassociation done
Starting audit store reassociation
The store and ServiceConfigurator setup done.
Schema is seeded into the store
Data is migrated to the store. Check logs for any failures or warnings during migration.
Data in the store after migration has been tested to be available
Update of in-memory jps configuration is done
Audit store reassociation done
Jps Configuration has been changed. Please restart the application server.

wls:/your_domain/serverConfig>

Related Posts:

WebCenter Capture 11.1.1.8 Cluster - Workspace created or imported on one node is not visible on the other node and workspace gets deleted on restart of cluster node

WebCenter Capture 11.1.1.8 Cluster - Using Database Based Security Store

WebCenter Capture 11.1.1.8 Cluster - Using OID Based Policy Store

WebCenter Capture 11.1.1.8 Cluster - Creating OPSS schema for using Database Based Security Store