Howto upgrade Oracle EM 12c with Database Vault enabled

This post is also available in: Português

In recent days, I updated the EM 12c release 12.1.0.3.0 to 12.1.0.4.0. However, the database where the repository was placed had Oracle Database Vault enabled and I was not even allowed to temporarily disable it during the upgrade (security policy of the company).

The best way to have proceeded with the installation without facing problems would be shutting down Oracle Database Vault.

The repository was running an Oracle Database 11.2.0.4 updated with PSU 3.

When I started the installation process, in the repository update step, I came across the following error:

ORA-01031: insufficient privileges
ORA-06512: at line 21
File:/u01/app/oem/em12cR4/Middleware/oms/sysman/admin/emdrep/sql/core/latest/admin/admin_create_cloud_engine_user.sql
Statement:declare 
    l_sql varchar2(512);
    l_count number;
BEGIN
    select count(*) into l_count from all_users where username = upper('CLOUD_ENGINE_USER');
    IF (l_count = 0) THEN
        l_sql := 
            'CREATE USER CLOUD_ENGINE_USER ' ||
            ' IDENTIFIED BY "dbaspdgs10rio" ' ||
            ' DEFAULT TABLESPACE MGMT_ECM_DEPOT_TS ' ||
            ' TEMPORARY TABLESPACE TEMP '||
            '  PASSWORD EXPIRE  ACCOUNT LOCK';

        execute immediate l_sql; 
    END IF;

EXCEPTION WHEN OTHERS THEN 
    IF SQLCODE = -01920 THEN 
        NULL;
    ELSE
        raise;
    END IF;
END;

User SYS cannot create an account when DV is on. To resolve this error, I decided to temporarily grant dv_acctmgr and dv_owner role access to the users SYS and SYSMAN (both participate in the upgrade task). After that, I clicked RETRY and passed the problem:

grant dv_acctmgr to sys;
grant dv_acctmgr to sysman;

grant dv_owner to sys;
grant dv_owner to sysman;

However, this was not enough. Later on, I had errors with another privilege:

ORA-47410: Realm violation for GRANT on CONNECT
File:/u01/app/oem/em12cR4/Middleware/oms/sysman/admin/emdrep/sql/core/latest/admin/admin_grants_repos_user.sql
Statement:GRANT connect, resource TO SYSMAN

The problem here was the moment that the SYS user attempted to grant CONNECT role in grant for the SYSMAN user (by the way, SYSMAN already had this grant).

To resolve this step, I temporarily added the SYS user in the realm 'Database Vault Account Management':

BEGIN
  DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
  REALM_NAME => 'Database Vault Account Management',
  GRANTEE => 'SYS',
  AUTH_OPTIONS => DVSYS.DBMS_MACUTL.G_REALM_AUTH_OWNER);
END;
/

By clicking on RETRY, I didn't have any more problems and the installation of the new version followed transparently to the end.

The best way to update would have been disabling Oracle Database Vault before the start of the task, but with these workarounds, you can update it even with Vault enabled.

Finally, revoke the temporarily granted access:

revoke dv_acctmgr from sys;
revoke dv_acctmgr from sysman;

revoke dv_owner from sys;
revoke dv_owner from sysman;

BEGIN
  DVSYS.DBMS_MACADM.DELETE_AUTH_FROM_REALM(
  REALM_NAME => 'Database Vault Account Management',
  GRANTEE => 'SYS');
END;
/

+8