000034672 - ORA-04063: package body "AVUSER.SRSECTION_SYSTEM" error and unable to generate Aveksa Statistics Report (ASR) after an upgrade to RSA Identity Governance and Lifecycle 7.0.1

Document created by RSA Customer Support Employee on Jan 18, 2017Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000034672
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: 7.0.1
Platform: Wildfly
Product Description: Access Certification Manager
IssueThe issue has been found in generating an Aveksa Statistic Report (ASR)  after to upgrade RSA Identity Governance and Lifecycle version 7.0.1. 
The ASR report is always in a Started state.
Refer to the aveksaSever.log, where the following errors have been found:
01/10/2017 03:29:16.509 ERROR (SystemReport) [com.aveksa.server.system.SystemReport] ASR Params: 
[Lcom.aveksa.server.db.ProcedureParameter;@6a560991
com.aveksa.server.db.PersistenceException: java.sql.SQLException:
ORA-04063: package body "AVUSER.SRSECTION_SYSTEM" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.SRSECTION_SYSTEM"

ORA-06512: at "AVUSER.SYSTEM_REPORT", line 49
ORA-06512: at "AVUSER.SYSTEM_REPORT", line 234
ORA-06512: at line 1
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1520)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1378)
at com.aveksa.server.db.PersistenceManager.runStoredProcedure(PersistenceManager.java:255)
at com.aveksa.server.system.SystemReport.backgroundGenerateReport(SystemReport.java:97)
at com.aveksa.server.system.SystemReport.access$000(SystemReport.java:30)
at com.aveksa.server.system.SystemReport$1.run(SystemReport.java:42)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-04063: package body "AVUSER.SRSECTION_SYSTEM" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.SRSECTION_SYSTEM"
ORA-06512: at "AVUSER.SYSTEM_REPORT", line 49
ORA-06512: at "AVUSER.SYSTEM_REPORT", line 234
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:205)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeCallableStatement(PersistenceServiceProvider.java:1555)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1483)
... 6 more
01/10/2017 03:29:16.510 ERROR (SystemReport) [SystemErr] Exception in thread "SystemReport"
01/10/2017 03:29:16.510 ERROR (SystemReport) [SystemErr] java.lang.RuntimeException: com.aveksa.server.db.PersistenceException:
java.sql.SQLException:
ORA-04063: package body "AVUSER.SRSECTION_SYSTEM" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.SRSECTION_SYSTEM"
ORA-06512: at "AVUSER.SYSTEM_REPORT", line 49
.
.

  1. Logon to the RSA Identity Governance and Lifecycle UI, then go to Admin > System and click on the Diagnostic tab.
  2. Under the Invalid Database Objects, the following is seen:
Object Name:  SRSECTION_SYSTEM
Object Type:  INVALID

  1. After clicking the Recompile Invalid Objects button the UI still displays the same information about the Object Name and Object Type.
  2. Using the oracle user, logon to SQL*Plus.
  3. Try to recompile the package of SRSECTION_SYSTEM by using the following command.  A warning of Package Body altered with compilation errors will be returned, as shown:
SQL> alter package avuser.SRSECTION_SYSTEM compile body.
Warning: Package Body altered with compilation errors.

  1. Run the following SQL statement to look at the dba_errors table with filter to "SRSECTION_SYSTEM".
SQL> SELECT name,type,SEQUENCE,LINE,TEXT,MESSAGE_NUMBER FROM dba_errors WHERE name=’SRSECTION_SYSTEM’;

  1. The output will be as follows:
201          9
PL/SQL: SQL Statement ignored
ERROR                  0
AVUSER
SRSECTION_SYSTEM                                PACKAGE BODY      5
       205         48
PL/SQL: ORA-00942: table or view does not exist
ERROR                  0
AVUSER
SRSECTION_SYSTEM                               PACKAGE BODY      6
       205         9
PL/SQL: SQL Statement ignored
ERROR                  0
AVUSER
SRSECTION_SYSTEM                               PACKAGE BODY      7
       209         55
PL/SQL: ORA-00942: table or view does not exist
ERROR                  0
AVUSER
SRSECTION_SYSTEM                               PACKAGE BODY      8
       209         9
PL/SQL: SQL Statement ignored
ERROR                  0
AVUSER
SRSECTION_SYSTEM                               PACKAGE BODY      9
       213         54
PL/SQL: ORA-00942: table or view does not exist
ERROR                  0
AVUSER
SRSECTION_SYSTEM                               PACKAGE BODY      10
       213         9
PL/SQL: SQL Statement ignored
ERROR                  0
181 rows selected
SQL>
CauseThe issue may occur because this is a new database. If so, appropriate privileges may not have been granted to Oracle System views to the AVUSER.


 
ResolutionTo resolve, follow the steps below to grant the appropriate privileges to Oracle System views.
GRANT SELECT ON dba_pending_transactions TO AVUSER; 
GRANT SELECT ON  pending_trans$ TO AVUSER; 
GRANT SELECT ON  dba_2pc_pending TO AVUSER; 
GRANT EXECUTE ON dbms_xa TO AVUSER; 
GRANT READ, WRITE ON directory AVEKSA_EXPORTIMPORT_DIRECTORY TO AVUSER; 
GRANT SELECT ON  dba_free_space TO AVUSER; 
GRANT SELECT ON  gv_$parameter TO AVUSER;

  1. After granting these privileges, running ASR reports should complete successfully.
Please see the RSA Identity Governance and Lifecycle Database Setup and Management Guide for more information.

Attachments

    Outcomes