Environment
ZENworks Configuration Management
ZENworks Endpoint Security 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