17.1 Preparing to Move the Data

Before migrating the data from the MS SQL database to Oracle database, do the following:

  • Run the ZENworks Diagnostic Center (ZDC) and check for schema differences.

    If the ZENworks database tables are not present under the default database schema DBO, then to avoid any issues, contact Micro Focus Customer Center before upgrading your zone.

    To find out the database scheme, run the following query in the Microsoft SQL database: SELECT distinct(SCHEMA_NAME(schema_id)) as OWNER FROM sys.objects WHERE type='U'

    IMPORTANT:If the unique constraint is not created on the serial number column of the zDevice table, database migration from the Microsoft SQL database to the Oracle database might fail. For information, see Troubleshooting the database migration failure, duplication of serial numbers in the zDevice table.

  • Ensure that the license state of ZENworks is Active. The product must be installed and running either in the licensed version or the evaluation version.

  • Ensure that the Oracle database is installed on a device that does not have ZENworks installed.

  • Ensure that the "USERS"/USER-CREATED tablespace has sufficient space to create and store the ZENworks database schema. The tablespace requires a minimum of 100 MB to create ZENworks database schema without any data in it and an appropriate additional space depending upon the size of the database to be migrated. For more information on tablespaces, refer to the section Prerequisites for Oracle in ZENworks Server Installation Guide.

  • Ensure that the NLS_CHARACTERSET parameter is set to AL32UTF8 and the NLS_NCHAR_CHARACTERSET parameter to AL16UTF16 by running the following query at the database prompt:

    select parameter, value from nls_database_parameters where parameter like '%CHARACTERSET%';
  • Ensure that the sharename, filename, and path columns do not contain blank strings in the NC_VRBSOFTWARE table.

  • (Conditional) If you want to migrate the database by creating a new user schema, ensure that the following additional requirements are met:

    • You must be aware of the database administrator credentials.

      NOTE:Ensure that the database administrator has the following privileges:

      • GRANT ALL on DBMS_REDEFINITION with GRANT option;

      • GRANT ALL on DBMS_DDL with GRANT option;

    • A tablespace must already exist for associating to the Oracle access user

  • You can choose to migrate the database by using an existing user schema that resides on a server in your network in the following scenario:

    • The database administrator create two user schemas with the necessary rights and you get the credentials for both user schemas from the database administrator. In this case, the database administrator credentials are not required to migrate the database.

    If you want to migrate the database by using an existing user schema, ensure that the following additional requirements are met:

    • Ensure that the user schemas must have the following rights to create the database.

      • CREATE SESSION
      • CREATE_TABLE
      • CREATE_VIEW
      • CREATE_PROCEDURE
      • CREATE_SEQUENCE
      • CREATE_TRIGGER
      • DBMS_REDEFINITION
      • DBMS_DDL
      • DBMS_LOCK
    • Ensure that the quota for the user schemas is set to Unlimited for the tablespaces to be used.

  • Manually stop the ZENworks services running on all the ZENworks Servers in the Management Zone. For more information, see Step 2.a in the Backing Up the Embedded Sybase SQL Anywhere Database on a Windows or Linux Server.

  • Ensure that your external MS SQL database service is running.

  • (Optional) The status of database migration is logged into the novell-zenworks-configure.log file. By default, only the messages of the type Info and Severe are logged. If you want other message types (such as Finer, Finest, and Warning) to also be logged into the file, do the following in the novell-zenworks-configure.properties file:

    1. Set the value of Logger.logLevel to the appropriate message type.

      For example, if you want messages of the type Finest to be logged:

       #Logger.logLevel   = FINEST
    2. Uncomment the line by removing the “#” as follows:

      Logger.logLevel   = FINEST

    The novell-zenworks-configure.properties file is located in %ZENWORKS_HOME%\conf\ on Windows and in /etc/opt/novell/zenworks/ on Linux.

IMPORTANT:For better performance depending on the database size and available resource, you can increase the batchsize in the db-migration-mssql-to-oracle.properties file. It is located in %ZENWORKS_HOME%\novell\zenworks\conf\ on Windows and in /etc/opt/novell/zenworks/conf/ on Linux.