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/updatesdirectory. - Perform Call Recording database migration of calls to 8.1.520 (using
/opt/callrec/bin/dbmigrationscript included with the 8.1.510 installation, with a correctly configured/opt/callrec/etc/migration.xmlfile 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/updatesdirectory). - Perform Call Recording database migration of calls to 8.1.520 (using
/opt/callrec/bin/dbmigrationscript included with the 8.1.51X installation). - Upgrade existing Quality Manager PostgreSQL database to version 8.1.510 (using
/opt/callrec/bin/scmigration2script in the 8.1.51x installation, with a correctly configured/opt/callrec/etc/migration.xmlfile for PostgreSQL to PostgreSQL migration). - Perform Quality Manager database migration from PostgreSQL to Oracle (using
/opt/callrec/bin/scmigration2script in the 8.1.51x installation, with a correctly configured/opt/callrec/etc/migration.xmlfile 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) |
|---|---|
| URL to running configuration manager,for example Use this method OR |
| Use a configuration file, for example Use this method OR |
| Check and count the CRC for each file. WARNING: this heavily impairs migration performance |
| Test mode, don't modify files or database. Displays all operations to be performed. |
| Specify the export database configuration group, for example |
| Display usage help. |
| Specify the import database configuration group, for example |
| Limit number of calls processed at one time. Default value: |
| log4j properties file to define the logging properties (doesn’t exist by default) for example Similar to all Call Recording tool/script log4j parameters (see similar |
| What to migrate – select from the following options:
Important! |
| -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) |
|---|---|
| URL to running configuration manager ,for example, Use this method OR |
| Use a configuration file, for example Use this method OR |
| Specify the export database configuration group, for example, |
| Display usage help. |
| Specify the import database configuration group, for example |
| Limit number of evaluations processed at one time. Default value: |
| log4j properties file to define the logging properties (doesn’t exist by default) for example Similar to all Call Recording |
| What to migrate, select from the following options: Important: Playing 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.