In the ZENworks database on the Microsoft SQL server, if the unique constraint is not created on the serial number column of the zDevice table, duplicate serial numbers might be assigned to the devices that are connected to the Management Zone. Because of the duplicate serial numbers, the database migration from the Microsoft SQL database to the Oracle database might fail.
To identify and remove the duplicate serial number in the zDevice table, perform the following steps in the Microsoft SQL database:
Use the following query to identify the duplicate serial numbers in the Microsoft SQL database:
SELECT * FROM zDevice WHERE serialnumber IN (SELECT serialnumber FROM zDevice GROUP BY serialnumber HAVING COUNT(1) > 1)
If the duplicate serial numbers are found on the zDevice table, do one of the following:
Remove the device from the Management Zone and register the device again.
Modify the duplicate serial numbers so that each serial number is unique.
To resume the database migration, perform the following in the Oracle database:
Use the following query to identify the duplicate serial numbers in the Oracle database:
SELECT * FROM zDevice WHERE serialnumber IN (SELECT serialnumber FROM zDevice GROUP BY serialnumber HAVING COUNT(1) > 1)
Run the following SQL query to remove the duplicate entry from the target Oracle database:
begin
for rec in (select zuid from zDevice where serialnumber is null)
loop
update zDevice
set serialnumber=NVL(serialnumber,'_')||'_'||dbms_random.string('U', 10)
where zuid=rec.zuid;
commit;
end loop;
for rec in (select serialnumber from zDevice group by serialnumber having count(1) > 1)
loop
update zDevice
set serialnumber=serialnumber||'_'||dbms_random.string('U', 10)
where serialnumber=rec.serialnumber;
commit;
end loop;
If there are any duplicate serial numbers, run the following query to verify.
If there are any duplicate numbers, run select serialnumber from zDevice group by serialnumber having count(1) > 1; and remove the duplicate serial numbers.
If there are no duplicates serial numbers, resume with the migration process.
NOTE:It is recommends that you clean up the duplicate serial numbers data before starting the database migration. You can continue to use the existing Management Zone without any loss functionality.