GUI shows initialization error "ORA-04063: package body "AVUSER.UTILITIES_PKG" has errors" after patching to 7.5.2 P07 in RSA Governance & Lifecycle

[#1]

Article Number

000068213

Applies To

SecurID Governance & Lifecycle 7.5.2 P07

Issue

After patching to version 7.5.2 P07, the GUI is showing the following initialization error:
Image description

And the following error is shown in the patch.log:

SQL/line#: /home/oracle/wildfly-24.0.1.Final/domain/servers/img-server-1/tmp/vfs/deployment/deployment99a9ca9d5e1f36c2/aveksa.war-13593c541ef9436/WEB-INF/database/migration/createReportViews.sql(1):
Start time [Wed Jun 07 19:51:53 EDT 2023]
begin Report_Views_Pkg.create_av_report_views; end;
java.sql.SQLException: ORA-04063: package body "AVUSER.UTILITIES_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.UTILITIES_PKG"
ORA-06512: at "AVUSER.REPORT_VIEWS_PKG", line 708
ORA-06512: at line 1

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    ...
Caused by: Error : 4063, Position : 0, Sql = begin Report_Views_Pkg.create_av_report_views; end;, OriginalSql = begin Report_Views_Pkg.create_av_report_views; end;, 
Error Msg = ORA-04063: package body "AVUSER.UTILITIES_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.UTILITIES_PKG"
ORA-06512: at "AVUSER.REPORT_VIEWS_PKG", line 708
ORA-06512: at line 1

Cause

This is a known issue with some database deployment types where additional grants need to be provided for some RSA internal tables to facilitate feature enhancements in 7.5.2 P07.

This is a known issue in the following type of RSA Governance & Lifecycle deployment:

• Customer-Supplied Remote Oracle Database

This issue is not known to occur in the following types of RSA Governance & Lifecycle deployments:

• RSA-Provided Local Oracle Database
• RSA-Provided Remote Oracle Database
• RSA GL Cloud
• Customer-Supplied AWS RDS Oracle Database (where the "SecurID Governance & Lifecycle 7.5.2 AWS Installation and Clustering Guide" has been followed)

Resolution

For Customer-supplied Oracle Databases, grants are normally applied manually during creation of the database using guidance in the G&L 7.5.2 Database Setup and Management Guide.  G&L 7.5.2 product documentation will be updated to reflect the additional grants required for 7.5.2 P07.

If you have installed or upgraded to 7.5.2 using the original product guides and when you patch to 7.5.2 P07 (or later), you are required to manually update the AVUSER schema and apply the additional grants.  This is a one time operation on the database.  The steps provided below to update the AVUSER schema can be followed PRIOR to applying/upgrading to patch P07, or AFTER you have encountered the error.

Update the AVUSER schema:

1.  Login as SYS user (or another user with SYSDBA privilege) in SQLPLUS (or another database tool like SQL Developer) and run the below script to grant Permissions on the below objects to AVUSER.

NOTE: If the schema name is other than the AVUSER, replace the value for v_username with the appropriate SCHEMA name.

DECLARE
v_username varchar2(100):= 'AVUSER';
BEGIN 
-- Create neede grants for 7.5.2P07
EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ' || v_username || '';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ' || v_username || '';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ' || v_username || '';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.DBMS_LOCK TO ' || v_username || '';
-- Recompile the Utilities Package
EXECUTE IMMEDIATE 'ALTER PACKAGE AVUSER.Utilities_Pkg COMPILE PACKAGE';
EXECUTE IMMEDIATE 'ALTER PACKAGE AVUSER.Utilities_Pkg COMPILE BODY';
END;
/ 

2.  Confirm that the grants were applied, run the following command.  The output should list the four grants given in the previous step (also see Notes section).

NOTE:  If your SecurID Governance & Lifecyle database schema is configured with a user other than the default AVUSER, please replace 'AVUSER' in the command with the appropriate user.

SELECT owner, table_name FROM table_privileges WHERE grantee = 'AVUSER' and table_name in ('DBA_HIST_DATABASE_INSTANCE', 'DBA_HIST_SNAPSHOT','DBMS_WORKLOAD_REPOSITORY','DBMS_LOCK') ORDER BY owner, table_name;

3.  Confirm that there are no invalid objects in the AVUSER schema. Run the below query.

NOTE:  If your SecurID Governance & Lifecyle database schema is configured with a user other than the default AVUSER, please replace 'AVUSER' in the command with the appropriate user.

select * from all_objects where owner='AVUSER' and status<>'VALID' and object_name ='UTILITIES_PKG';

4.  If you have not applied the patch yet you may now apply 7.5.2 P07 (or later).  If you attempted to apply the patch and it failed with the error, apply the patch 7.5.2 P07 (or later) again using the original procedure.

Notes

If you suspect you have encountered this issue or are susceptible to it, the following scripts can be run to validate your system requires remediation to successfully apply the patch P07 on your SecurID Governance & Lifecycle 7.5.2.

Run the following select queries as SYS user (or another Oracle user with SYSDBA privilege) in SQLPLUS (or another database tool like SQL Developer):

NOTE:  If your SecurID Governance & Lifecyle database schema is configured with a user other than the default AVUSER, please replace 'AVUSER' in the command with the appropriate user.
 

SELECT owner, table_name FROM table_privileges WHERE grantee = 'AVUSER' and table_name in ('DBA_HIST_DATABASE_INSTANCE', 'DBA_HIST_SNAPSHOT','DBMS_WORKLOAD_REPOSITORY','DBMS_LOCK') ORDER BY owner, table_name;

 

• If the result for the above command matches with the output below, your SecurID Governance & Lifecycle deployment is NOT affected by the issue in this article.

Image description

• If the result for the above command is empty or does not match with the above good output, your SecurID Governance & Lifecycle deployment is AFFECTED by this issue.