# Genesys Quality Management Suite 8.1.520 : Database Migration to Oracle

Created and last modified by

This section covers the scripts and procedures necessary to migrate Call Recording and Quality Manager database data between the embedded PostgreSQL database and an external Oracle database; both PostgreSQL to Oracle and Oracle to PostgreSQL migration can be performed.

For customers with existing Genesys Quality Management Suite deployments using PostgreSQL, the pattern of deployment and migration depends on the current version and products installed. See the Deployment and Migration Scenarios section.

## Deployment and Migration Scenarios

The following scenarios illustrate the basic tasks necessary to accomplish a successful migration to an Oracle database for the given installed software versions. These scenarios use Genesys Quality Management Suite 8.1.510 as the final target version; however the minimum target version is 8.1.480, in order to leverage the Oracle database.

### Call Recording Only (Existing version: 4.1 - 4.7.x)

- Create a new 8.1.520 Installation with Oracle.
  Upgrade the existing Call Recording PostgreSQL database to the latest minor version using the database scripts provided with the official Call Recording ISO in the `/opt/callrec/dbscripts/updates` directory.
- Perform Call Recording database migration of calls to 8.1.520 (using `/opt/callrec/bin/dbmigration` script included with the 8.1.510 installation, with a correctly configured `/opt/callrec/etc/migration.xml` file for PostgreSQL to Oracle migration).

### Call Recording 4.4 + Quality Manager [OR] Call Recording (4.6.x – 5.1.x) + Quality Manager

- Create new 8.1.520 Installation with Oracle
- Upgrade existing Call Recording PostgreSQL database to latest minor version; for example 8.1.447 for Call Recording version 8.1.440 (using database scripts provided with official Call Recording ISO in `/opt/callrec/dbscripts/updates` directory).
- Perform Call Recording database migration of calls to 8.1.520 (using `/opt/callrec/bin/dbmigration` script included with the 8.1.51X installation).
- Upgrade existing Quality Manager PostgreSQL database to version 8.1.510 (using `/opt/callrec/bin/scmigration2` script in the 8.1.51x installation, with a correctly configured`/opt/callrec/etc/migration.xml` file for PostgreSQL to PostgreSQL migration).
- Perform Quality Manager database migration from PostgreSQL to Oracle (using `/opt/callrec/bin/scmigration2` script in the 8.1.51x installation, with a correctly configured `/opt/callrec/etc/migration.xml` file for PostgreSQL to Oracle migration).

## Important Migration Requirements

- Quality Manager migration from PostgreSQL to Oracle requires a SOURCE installation of Genesys Quality Management Suite 8.1.48x (or higher), due to schema incompatibilities with earlier database versions.For Quality Manager 8.1.450 – 8.1.47x migration to Oracle, it is therefore necessary to FIRST upgrade the earlier Genesys Quality Management Suite version to Genesys Quality Management Suite 8.1.48x (or higher) before attempting data migration.
  Refer to the supported upgrade procedure.
- Migrated Quality Manager evaluations do not play without separate (Call Recording) migration of the calls used in the evaluations.
- Russian characters must be correctly encoded in Unicode UTF-8, otherwise each character uses two character spaces, and overspills the allotted size for tables, hence the migration fails.
- With PostgreSQL columns that are NOT NULL and have an empty value, Oracle automatically sets these columns to NULL and tries to insert them which causes failure of the null constraint.

### Migration Overview

Before running the migration scripts, the target database must be empty; if any data does exist from an earlier migration, this is likely to be overwritten.

The following migration procedure is based on the migration of an existing Genesys Quality Management Suite 8.1.51x installation with embedded PostgreSQL database to Oracle. A functional, empty Oracle database instance is assumed, with no pre-created Call Recording or Quality Manager schema.

The migration scripts create two separate Oracle schema for Call Recording and Quality Manager.

The entire migration process is performed at the command line, logged in as the root user with full permissions. A working knowledge of XML syntax is assumed.

### Call Recording Database Migration from PostgreSQL to Oracle

Source database:

PostgreSQL database for an existing Call Recording 8.1.410 (or higher) installation (PostgreSQL 8.4 or higher is required for Genesys Quality Management Suite (8.1.46x or higher) installations).

Target database:

Empty Oracle 11g (or higher) database.

### Call Recording Database Migration from Oracleto PostgreSQL

Source database:

Oracle: 11g (or higher) database for an existing Genesys Quality Management Suite 8.1.48x (or higher) installation.

Target database:

Empty PostgreSQL 8.4 (or higher) database.

### Quality Manager Database Migration from PostgreSQL to Oracle

Source database:

PostgreSQL database for an existing Genesys Quality Management Suite 8.1.48x (or higher) installation.

Target database:

Empty Oracle 11g (or higher) database.

### Quality Manager Database Migration from Oracle to PostgreSQL

Source database:

Oracle: 11g (or higher) database for an existing Genesys Quality Management Suite 8.1.48x (or higher) installation.

Target database:

Empty PostgreSQL 8.4 (or higher) database.

## Call Recording Migration

Edit the migration configuration XML file at `/opt/callrec/etc/migration.xml` as follows:

### Source Database Pool

Within the `Database` node, create and insert a new database pool, representing the source (‘from’) database (in this case PostgreSQL), using the following code (with values for `host`, `port`, `dbName`, `username`, `password` updated appropriately):

    <Pool name="callrec50xsource" poolType="cz.zoom.util.db.pool.ibatis.IbatisPool">
     <Url host="localhost" port="5432" dbName="callrec"/>
     <Login userName="callrec" password="callrec"/>
     <Connections max="20" init="1" timeOut="5"/>
     <SpecificSetting>
     <Value name="sqlMapClass">
    cz.zoom.callrec.tools.migration.db.version50.SqlMap</Value>
     </SpecificSetting>
    </Pool>

Note that the `sqlMapClass` value must be correct, reflecting the correct version (`version50` = Call Recording database version 8.1.510 ) and database driver (PostgreSQL).

The pool names used can differ, as long as they are unique and correctly referenced later.

### Target Database Pool

Create and insert a second new database pool below the first, representing the target (‘to’) database (in this case Oracle), using the following code (with values for `host`, `port`, `dbName`, `username`, `password` updated appropriately):

    <Pool name="callrec50xtarget" poolType="cz.zoom.util.db.pool.ibatis.IbatisPool">
     <Url host="oracle.mycompany.com" port="1521" dbName="zoomdb"/>
     <Login userName="callrec" password="callrec"/>
     <Connections max="20" init="1" timeOut="5"/>
     <SpecificSetting>
     <Value name="sqlMapClass">
    cz.zoom.callrec.tools.migration.db.version50.oracle.SqlMap</Value>
     </SpecificSetting>
    </Pool>

The `sqlMapClass` value must reflect the correct version and database driver. For Oracle, this value would be:
`cz.zoom.callrec.tools.migration.db.version50.oracle.SqlMap`

For PostgreSQL, this value would be the same as used for the earlier source pool, that is:
`cz.zoom.callrec.tools.migration.db.version50.SqlMap`

### Source and Target Assignment

Finally, the new source and target database pools need to be correctly assigned for the migration operation. This is achieved by adding the following two nodes in the `SpecifiedConfiguration` section:

#### Export Node

Within the first `Group` node (with `name` value set as `export`) add the following `EqualGroup` node, ensuring the `dbPool` value reflects the source database pool name you defined earlier:

    <EqualGroup name="export">
     <Value name="name">cr50xsource</Value>
     <Value name="dbPool">callrec50xsource</Value>
     <Value name="class">
    cz.zoom.callrec.tools.migration.db.version50.ExportImpl</Value>
    </EqualGroup>

The `class` value should again represent the correct version (8.1.510 here) and database driver (PostgreSQL here). The 8.1.510 Oracle class value would be:

`cz.zoom.callrec.tools.migration.db.version50.oracle.ExportImpl`

The name value used (`cr50xsource`) can be any permitted within XML syntax rules, and is the export reference name used later when running the migration scripts.

#### Import Node

Similarly, within the second `Group` node (with name value set as imports) add the following `EqualGroup` node, ensuring the `dbPool` value reflects the target database pool name you defined earlier:

    <EqualGroup name="import">
     <Value name="name">cr50xtarget</Value>
     <Value name="dbPool">callrec50xtarget</Value>
    <Value name="class">
    cz.zoom.callrec.tools.migration.db.version50.oracle.ImportImpl
    </Value>
    </EqualGroup>

Once again, ensure the correct `class` value is used (the class here representing database version 8.1.520 for the Oracle driver). The equivalent class value for the 8.1.520 PostgreSQL database driver would be:

`cz.zoom.callrec.tools.migration.db.version50.ImportImpl`

The name value used (`cr50xtarget`) can be any permitted within XML syntax rules, and is the import reference name used later when running the migration scripts.

### Run the Migration Script

After saving the migration.xml file, the Call Recording migration script can be run. This takes the following form:

    /opt/callrec/bin/dbmigration [-config <config> | -configfile <configfile>] [-countCRC] [-dryrun] [-export <name>] [-import <name>] [-limit <limit>] [-logger <logger>] [-migrate <options>] [-nobind]

The parameters and options are as follows:

Parameter | Option(s)
--- | ---
`-config <config>` | URL to running configuration manager,for example `//localhost:30400/migration`. Use this method OR `–configfile`.
`-configfile <configfile>` | Use a configuration file, for example `/opt/callrec/etc/migration.xml`. Use this method OR `-config`.
`-countCRC` | Check and count the CRC for each file. WARNING: this heavily impairs migration performance
`-dryrun` | Test mode, don't modify files or database. Displays all operations to be performed.
`-export <export>` | Specify the export database configuration group, for example `cr50xsource`.
`-help` | Display usage help.
`-import <import>` | Specify the import database configuration group, for example `cr50xtarget`.
`-limit <limit>` | Limit number of calls processed at one time. Default value: `1000`.
`-logger <logger>` | log4j properties file to define the logging properties (doesn’t exist by default) for example `/opt/callrec/etc/migration.log4j.properties` Similar to all Call Recording tool/script log4j parameters (see similar `xxxx.log4j.properties files in the /opt/callrec/etc/` directory)
`-migrate <migrate>` | What to migrate – select from the following options: all – both OLD Quality Manager & Call Recording (note that this option removes OLD Quality Manager if it exists in the target database) `callrec` – all Call Recording data `calls` – Call data `roles` – User roles `scorecard` – all OLD Quality Manager data `scorecardUsers` – OLD Quality Manager users `qforms` – OLD Quality Manager questionnaires only `scUsersQforms` – OLD Quality Manager users and questionnaires Important! All Quality Manager data migrated using the Call Recording migration script relates to ‘OLD’ Quality Manager (that is. the desktop MS Access-based version for Call Recording versions 8.1.430 and earlier). All Quality Manager-related options in the Call Recording migration script are therefore disabled for Call Recording source database versions of 8.1.46x and above. The separate Quality Manager migration script should be used for migrating Web-based Quality Manager data (the Quality Manager included with Genesys Quality Management Suite 8.1.46x)
-nobind | Do not attempt to bind to the RMI registry. This option is only enabled in exceptional circumstances, normally it should be ignored. Default is to bind to RMI.

### Sample (minimal)

    /opt/callrec/bin/dbmigration -migrate callrec -export cr50xsource -import cr50xtarget

It is recommended to try a test run of the script using the -dryrun option (see the parameters above), before attempting a ‘real’ data migration.

After running the ‘real’ migration, use an Oracle database administration tool, such as Oracle SQL Developer or TOAD, to verify that the migration has taken place.

## Quality Manager Migration

Quality Manager migration configuration is very similar to the earlier Call Recording method. Quality Manager can either be migrated from or to the same Oracle database (but different schema) as Call Recording, or from/to a completely different Oracle database. In this case, the former default scenario is used, which migrates Quality Manager from an embedded PostgreSQL database to the same Oracle database as Call Recording (but different schema).

Once again, edit the migration configuration XML file at `/opt/callrec/etc/migration.xml` as follows.

### Source Database Pool

Within the `Database` node, create and insert a new database pool, representing the source (‘from’) Quality Manager database (in this case PostgreSQL), using the following code (with values for `host`, `port`, `dbName`, `username`, `password` updated appropriately):

    <Pool name="scorecard50xsource" poolType="cz.zoom.util.db.pool.ibatis.IbatisPool">
     <Url host="localhost" port="5432" dbName="callrec"/>
     <Login userName="wbsc" password="wbsc"/>
     <Connections max="20" init="1" timeOut="5"/>
     <SpecificSetting>
     <Value name="sqlMapClass">
    cz.zoom.scorecard.business.data.SqlMap</Value>
     </SpecificSetting>
    </Pool>

Note that the `sqlMapClass` value must be correct (and is different to that for the Call Recording version).

For PostgreSQL, this value would be:
`cz.zoom.scorecard.business.data.SqlMap`

For Oracle, this value would be:
`cz.zoom.scorecard.business.data.xmlOracle.SqlMap`

The pool names used can differ, as long as they are unique and correctly referenced later.

### Target Database Pool

Create and insert a second new database pool below the first, representing the target (‘to’) Quality Manager database (in this case Oracle), using the following code (again with values for `host`, `port`, `dbName`, `username`, `password` updated appropriately):

    <Pool name="scorecard50xtarget" poolType="cz.zoom.util.db.pool.ibatis.IbatisPool">
     <Url host="oracle.mycompany.com" port="1521" dbName="zoomdb"/>
     <Login userName="wbsc" password="wbsc"/>
     <Connections max="20" init="1" timeOut="5"/>
     <SpecificSetting>
     <Value name="sqlMapClass">
    cz.zoom.scorecard.business.data.xmlOracle.SqlMap</Value>
     </SpecificSetting>
    </Pool>

The `sqlMapClass` value must be correct as in the sample.

For Oracle, this value would be the same as used for the earlier source pool:
`cz.zoom.scorecard.business.data.xmlOracle.SqlMap`

For PostgreSQL, this value is:
`cz.zoom.scorecard.business.data.SqlMap`

### Source and Target Assignment

The new Quality Manager source and target database pools need to be correctly assigned for the migration operation. However, unlike the earlier Call Recording method, a complete new `SpecifiedConfiguration` node must be created within the `Configuration` node, which then contains the export and import nodes.

For clarity, the whole new `SpecifiedConfiguration` node is shown below, which should be added after the first (Call Recording) SpecifiedConfiguration node (with name value migration), but still within the `Configuration` node.

    <SpecifiedConfiguration name="scorecardMigration">
     <Group name="exports">
     <EqualGroup name="export" egName="sc50xsource">
     <Value name="dbPool">scorecard50xsource</Value>
     <Value name="class">cz.zoom.scorecard.migration.ExportImpl</Value>
     </EqualGroup>
     </Group>
     <Group name="imports">
     <EqualGroup name="import" egName="sc50xtarget">
     <Value name="dbPool">scorecard50xtarget</Value>
     <Value name="class">cz.zoom.scorecard.migration.ImportImpl</Value>
     </EqualGroup>
     </Group>
    </SpecifiedConfiguration>

For the first `Group` node (with name value set as exports), ensure the `EqualGroup` node’s `dbPool` value reflects the Quality Manager source database pool name defined earlier. Similarly, within the second `Group` node (with `name` value set as imports) ensure that the `EqualGroup` node’s `dbPool` value reflects theQuality Manager target database pool name defined earlier.

The export and import Quality Manager `EqualGroup` configuration nodes are the same as for Call Recording, apart from two minor differences:

- The name property for `EqualGroup` nodes is here renamed to `egName`
- The class values do not change depending on database type and version.

### Run the Migration Script

After saving the changes made to the `migration.xml` file, the Quality Manager migration script can now be run. This takes the following form:

    /opt/callrec/bin/scmigration2 [-config <config> | -configfile <configfile>] [-export <name>] [-import <name>] [-limit <limit>] [-logger <logger>] [-migrate <options>]

The parameters and options are as follows:

Parameter | Option(s)
--- | ---
`-config <config>` | URL to running configuration manager ,for example, `//localhost:30400/migration.` Use this method OR `–configfile`.
`-configfile <configfile>` | Use a configuration file, for example `/opt/callrec/etc/migration.xml.` Use this method OR`-config`.
`-export <export>` | Specify the export database configuration group, for example, `sc50xsource`.
`-help` | Display usage help.
`-import <import>` | Specify the import database configuration group, for example `sc50xtarget`.
`-limit <limit>` | Limit number of evaluations processed at one time. Default value: `1000`.
`-logger <logger>` | log4j properties file to define the logging properties (doesn’t exist by default) for example`/opt/callrec/etc/scmigration2.log4j.properties` Similar to all Call Recording `tool/script` log4j parameters (see similar `xxxx.log4j.properties` files in the `/opt/callrec/etc/` directory)
`-migrate <migrate>` | What to migrate, select from the following options: `all`: all Quality Manager data (users, questionnaires, evaluation data) `users`: users only `questforms`: questionnaires only `usersquestforms`: users and questionnaires only Important: Playing Evaluations Migrated Quality Manager evaluations do not play without separate (Call Recording) migration of the calls used in the evaluations.

#### Sample (minimal)

    /opt/callrec/bin/scmigration2 -configurl //localhost:30400/migration -export sc50xsource -import sc50xtarget -migrate all -limit 1000

After running the migration, use an Oracle database administration tool, such as Oracle SQL Developer or TOAD, to verify that the migration has taken place.