All RSA Identity Governance & Lifecycle Reviews fail to generate regardless of review type.
The database is Oracle 18.104.22.168.0.
TheRSA Appliance Updater package namedrsaimg_updater_2016Q3_SLES11_12C.tar.bz2 may or may not have been applied.
The following error is logged to the aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log)
08/12/2015 20:58:35.141 ERROR (Exec Task Consumer#0) [com.aveksa.server.review.ReviewDefinitionVersion] FAILED method=generateReport subTask=Get entitlements for review 322 com.aveksa.server.db.PersistenceException: java.sql.SQLException: ORA-20126: The creation of reviews failed.
ORA-01792: maximum number of columns in a table or view is 1000 Stored Procedure:Generate_Review execution aborted. Stack: ORA-06512: at "AVUSER.REVIEW_COMPONENT_CACHE", line 67 ORA-06512: at "AVUSER.REVIEW_COMPONENT_CACHE", line 3126 ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1812 ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903 ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917 ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1895 ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1537 ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1502 ORA-06512: at line 1
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1458) at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1329) at com.aveksa.server.db.PersistenceManager.runStoredProcedure(PersistenceManager.java:235) at com.aveksa.server.review.ReviewDefinitionVersion.generateReport(ReviewDefinitionVersion.java:801) at com.aveksa.server.review.ReviewDefinitionVersion.generateReport(ReviewDefinitionVersion.java:461) at com.aveksa.server.xfw.EntitlementReviewExecutor.executeTask(EntitlementReviewExecutor.java:60) at com.aveksa.server.xfw.TaskExecutor.execute(TaskExecutor.java:82) at com.aveksa.server.xfw.ExecutionTaskQueue$Worker.run(ExecutionTaskQueue.java:116) at java.lang.Thread.run(Thread.java:701) Caused by: java.sql.SQLException: ORA-20126: The creation of reviews failed.
Please refer to RSA Knowledge Base Article 000030327 -- Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the aveksaServer.log file for your specific deployment, if you are on a WildFly cluster or a non-WildFly platform. The aveksaServer.log may also be downloaded from the RSA Identity Governance & Lifecycle user interface (Admin > System > Server Nodes tab > under Logs.)
This is a known issue reported in engineering tickets ACM-56376 and ACM-69792.
The cause of this issue is due to an Oracle defect introduced in Oracle 22.214.171.124.0 by Oracle patch 19653859.
This issue is resolved in Oracle patch 19509982, DISABLE FIX FOR RAISING ORA-1702 BY DEFAULT. Ensure that you apply Oracle patch 19509982, or the latest cumulative Oracle patch set that includes this patch. Note that if Oracle patch 19509982 has been previously applied and Oracle is upgraded, Oracle patch 19509982 may need to be applied again even if it has already been applied.
If you are using RSA Identity Governance & Lifecycle with a remote Oracle database, then have your Database Administrator ensure that Oracle patch 19509982 is applied to your database.
If you are using an RSA Software or Hardware appliance with an RSA local database provided by RSA, and the Oracle database version is 126.96.36.199.0, then apply the latest RSA Appliance Updater. Go to RSALink and choose Download for your version. The latest Appliance updater will appear ready for download.
Instructions to apply the Oracle fix manually:
1. As user oracle stop RSA Identity Governance & Lifecycle and stop Oracle.
$ acm stop
$ acm stoporacle
2. Set your current directory to the directory where the patch is located and then run the opatch utility by entering the following commands:
$ cd <PATCH_TOP_DIR>/19509982
$ opatch apply
3. Verify whether the patch has been successfully installed by running the following command:
$ opatch lsinventory
4. Start Oracle and RSA Identity Governance & Lifecycle:
$ acm startoracle
$ acm start
You can verify that the correct Oracle patch is applied by running the opatch command on the system where Oracle is installed. Login as the Linux user that manages/installed Oracle (the default user is typically oracle), and run the opatch utility as shown below:
$ cd /u01/app/oracle/product/12.1.0/db_1/OPatch
$ opatch lsinv
Oracle Interim Patch Installer version 188.8.131.52.3
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/12.1.0/grid/oraInventory
from : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version : 184.108.40.206.3
OUI version : 220.127.116.11.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2015-08-28_18-48-24PM_1.log
Lsinventory Output file location :
Installed Top-level Products (1):
Oracle Database 12c 18.104.22.168.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 19509982 : applied on Thu Aug 20 17:16:35 EDT 2015
Unique Patch ID: 18265206
Created on 11 Feb 2015, 14:07:42 hrs PST8PDT
The output of opatch in this example shows that the fix for bug 9509982 has been installed on this instance.