Review generation fails for all review types with an 'ORA-01792: maximum number of columns in a table or view is 1000' error in the log file in RSA Identity Governance & Lifecycle

[#1]

Article Number

000031110

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle 
RSA Version/Condition: 6.9.1, 7.0.x, 7.1.x, 7.2.0
Platform (DB):  Oracle 12.1.0.2.0
 

Issue

All RSA Identity Governance & Lifecycle Reviews fail to generate regardless of review type.

SYMPTOMS:

• The database is Oracle 12.1.0.2.0.
• The RSA Appliance Updater package named rsaimg_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.)
 

Cause

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 12.1.0.2.0 by Oracle patch 19653859.
 

Resolution

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 12.1.0.2.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.

Image description
 

Image description

 

Workaround

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

Notes

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 12.1.0.1.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    : 12.1.0.1.3
OUI version       : 12.1.0.2.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 : 
/u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-08-28_18-48-24PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.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
   Bugs fixed:
     19509982

--------------------------------------------------------------------------------
OPatch succeeded.

The output of opatch in this example shows that the fix for bug 9509982 has been installed on this instance.