2020 Upgrade fails on Oracle database - ORA-02296: cannot enable (ZENADMIN.) - null values found

  • 7024466
  • 02-Mar-2020
  • 02-Mar-2020

Environment

ZENworks Configuration Management
ZENworks Endpoint Security Management

Situation

System update fails while running preglobal sql query on Oracle database.

[ERROR] [02/12/2020 16:01:48.326] [7951] [SQLUtils] [1] [root] [SystemUpdate] [] [Attempt 5 of executing SQL (DECLARE
[java.sql.SQLIntegrityConstraintViolationException: ORA-02296: cannot enable (ZENADMIN.) - null values found
ORA-06512: at line 17

In German:  failed with exception : ORA-02296: (ZENWORKS.) kann nicht aktiviert werden. Null-Werte gefunden.

Resolution

To determine in advance whether the problem will occur, run this db query:

select * from StorageDevicePolicy where PortableAccessID is null;

To fix:

Backup the database then run and commit the following SQL query before running the system update:

UPDATE StorageDevicePolicy SET PortableAccessID = 1 where PortableAccessID is null;
commit;

Additional Information

from pre-global.log


[DEBUG] [02/12/2020 16:01:46.981] [7951] [SQLUtils] [1] [root] [SystemUpdate] [] [Executing DECLARE
        obj_exists PLS_INTEGER;

        begin
        select count(*) into obj_exists
        from User_tab_columns
        where upper(table_name) = upper('zFRPAccount');

        if (obj_exists = 0)
        THEN EXECUTE IMMEDIATE 'create table zFRPAccount (
        ZUID raw(16) not null,
        MobileDeviceUID raw(16) not null,
        SerialNumber varchar2(100 char) not null,
        IMEI varchar2(100 char) not null,
        EmailId varchar2(255 char) not null,
        UniqueID varchar2(100 char) not null,
        primary key(ZUID))';
    END IF;
    end; --> Attempt : 1] [] [] [] [PreGlobalAction]
[DEBUG] [02/12/2020 16:01:47.311] [7951] [SQLUtils] [1] [root] [SystemUpdate] [] [Execution complete.] [] [] [] [PreGlobalAction]
[INFO] [02/12/2020 16:01:47.313] [7951] [SystemUpdateExecutor] [1] [root] [SystemUpdate] [] [Successfully executed action : /system-updates/v_20_0_0/oracle/frpAccount.sql of type : EXEC_SQL for version : v_20_0_0] [] [] [] [PreGlobalAction]
[DEBUG] [02/12/2020 16:01:47.313] [7951] [SystemUpdateExecutor] [1] [root] [SystemUpdate] [] [Executing (ID=>13, Schema => ZCM, Action => EXEC_SQL, Version => v_20_0_0, Entry => /system-updates/v_20_0_0/oracle/zesm_update.sql)] [] [] [] [PreGlobalAction]
[DEBUG] [02/12/2020 16:01:47.314] [7951] [SQLUtils] [1] [root] [SystemUpdate] [] [Got 6 SQLs to execute.] [] [] [] [PreGlobalAction]
[DEBUG] [02/12/2020 16:01:47.314] [7951] [SQLUtils] [1] [root] [SystemUpdate] [] [Executing DECLARE
  col_exists PLS_INTEGER;
  col_already_exists EXCEPTION;
  pragma EXCEPTION_INIT(col_already_exists, -1430);
  allready_null EXCEPTION;
  PRAGMA exception_init ( allready_null, -1442 );
BEGIN
  SELECT COUNT(1)
  INTO col_exists
  FROM user_tab_columns
  WHERE upper(table_name) = upper('StorageDevicePolicy')
        AND upper(column_name) = upper('PortableAccessID');

  IF ( col_exists = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE StorageDevicePolicy ADD PortableAccessID number(10,0) default 1 not null';
  ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE StorageDevicePolicy MODIFY PortableAccessID number(10,0) default 1 not null';
  END IF;

EXCEPTION
  WHEN col_already_exists OR allready_null THEN
    NULL;
END; --> Attempt : 1] [] [] [] [PreGlobalAction]
[INFO] [02/12/2020 16:01:47.648] [7951] [SQLUtils] [1] [root] [SystemUpdate] [] [Attempt 1 of executing SQL (DECLARE
  col_exists PLS_INTEGER;
  col_already_exists EXCEPTION;
  pragma EXCEPTION_INIT(col_already_exists, -1430);
  allready_null EXCEPTION;
  PRAGMA exception_init ( allready_null, -1442 );
BEGIN
  SELECT COUNT(1)
  INTO col_exists
  FROM user_tab_columns
  WHERE upper(table_name) = upper('StorageDevicePolicy')
        AND upper(column_name) = upper('PortableAccessID');

  IF ( col_exists = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE StorageDevicePolicy ADD PortableAccessID number(10,0) default 1 not null';
  ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE StorageDevicePolicy MODIFY PortableAccessID number(10,0) default 1 not null';
  END IF;

EXCEPTION
  WHEN col_already_exists OR allready_null THEN
    NULL;
END;) failed with exception : ORA-02296: cannot enable (ZENADMIN.) - null values found

Feedback service temporarily unavailable. For content questions or problems, please contact Support.