000031110 - All RSA Identity Governance and Lifecycle reviews fail with error ORA-01792 when using Oracle 12.1.0.2.0

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000031110
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: 6.9.1, 7.0.0, 7.0.1
Platform: Oracle 12.1.0.2.0
 
Issue

All reviews fail with error ORA-01792: maximum number of columns in a table or view is 1000.   The aveksaServer.log file shows the following exception:


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.



  • This error occurs when using Oracle 12.1.0.2.0.
  • This issue may occur after applying the RSA Q3 Appliance updater. 
CauseThis issue occurs due to a failure of certain SELECT queries, due to a problem with Oracle fix 19653859 introduced in Oracle 12.1.0.2.0.  This is corrected by applying Oracle fix 19509982.  
 
ResolutionEnsure that you apply Oracle patch 19509982, or the latest cumulative Oracle patch set that includes this patch. Note that if Oracle fix 19509982 has been previously applied, and Oracle is upgraded, Oracle fix 19509982 may need to be applied again even if it has already been applied.  The fix is called DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT (Patch 19509982).
 

Notes:
  • If you are using an RSA Software or Hardware appliance with a remote Oracle database, then you are responsible for ensuring that Oracle patch 19509982 is applied correctly.
  • If you are using an RSA Hardware or Software Appliance with an RSA local database provided by RSA, then you should apply the RSA Appliance Updater Q4 or later. The RSA Q3 Appliance Updater package named rsaimg_updater_2016Q3_SLES11_12C.tar.bz2 will update Oracle and apply fix 19509982 if this patch has not already been applied.  If fix 19509982 has already been applied and the RSA Q3 Appliance Updater is used to update the appliance, the 19509982 fix will not be re-applied and must be applied manually (see the Workaround section in this document).
  • The RSA Q4 Appliance Updater  package rsaimg_updater_2016Q4_SLES11_12C.tar.bz2 will correctly apply the 19509982 fix even if it has been applied previously. 
WorkaroundInstructions to apply the Oracle fix manually:
1. Stop ACM 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 ACM:


$ acm startoracle
$ acm start
NotesFor more information refer to the following Oracle Support document entitled Select Statement Throws ORA-01792 Error (Doc ID 1951689.1).
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 #19509982 has been installed on this instance.

Attachments

    Outcomes