Show Hide

 

Feedback

  • Contents
 

Run the Database Schema Upgrade Scripts (Oracle)

CIC Database Migration Assistant relies on a specific database schema to be in place on both the source and destination databases. Before you run CIC Database Migration Assistant, you must:

  • Upgrade the source CIC 2.4/3.0 database schema to the most recent (tested) CIC 3.0 database schema.

  • Upgrade the destination CIC 4.0 database schema to the most recent (tested) CIC 2015 R1 or later 0 database schema.

To upgrade the schemas, run a set of scripts placed on the CIC Database Migration client computer when you installed CIC Database Migration Assistant. Running these scripts ensures that each database matches the schema required for the latest version of CIC Database Migration Assistant.

There are two options for running the database schema upgrade scripts:

  • Follow prompts in CIC Database Migration Assistant to upgrade the database.

  • Run the database schema upgrade scripts manually before running CIC Database Migration Assistant.

Oracle customers use the following syntax from the command line:

  sqlplus <dba user>/<dba pw>@<service name> @ScriptName.sql
  <CIC DATA Tablespace> <CIC Index Tablespace>

Upgrade the CIC 2.4/3.0 database schema to the most recent CIC 3.0 database schema (Oracle)

The 3.0 Oracle Server scripts are in the CIC Database Migration Assistant\Scripts\oracle\source directory.

Run the scripts in the following order, one after another. Don't run them at the same time. Use the syntax and special directions provided for each script.

  1. Create_DB_Objects.sp

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Create_DB_Objects.sp

  2. Ora_UpgradeDBSchema.sql

    Note: You must have system permissions for the Ora_UpgradeDBSchema.sql script.

    Before you run this script, open the command prompt, change the directory to the location of the UpgradeDBSchema script, and run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_UpgradeDBSchema.sql <Data_Tablespace> <Index_Tablespace> <Schema_Owner> <Schema_OwnerPass> <Hostname:Port/Service>

  3. Ora_TrackerUpgrade.sql

    Note: You must have system permissions for the Ora_TrackerUpgrade.sql script.

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_TrackerUpgrade.sql <Data_Tablespace> <Index_Tablespace> <Schema_Owner> <Schema_OwnerPass> <Hostname:Port/Service>

  4. Ora_TrackerBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_TrackerBuild.sql <Data_Tablespace> <Index_Tablespace>

  5. Ora_IRBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_IRBuild.sql <Data_Tablespace> <Index_Tablespace>

  6. Ora_OptimizerBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_OptimizerBuild.sql <Data_Tablespace> <Index_Tablespace>

  7. Ora_CSSurveyBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_CSSurveyBuild.sql <Data_Tablespace> <Index_Tablespace>

  8. Ora_WorkflowBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_WorkflowBuild.sql <Data_Tablespace> <Index_Tablespace>

  9. Ora_IWPBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_IWPBuild.sql <Data_Tablespace> <Index_Tablespace>

  10. Ora_ICFinalize.sql

    Note: You must have system permissions for the Ora_ICFinalize.sql script.

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_ICFinalize.sql <Schema_Owner>

Complete duplicate/null clean up procedures on the CIC 2.4/CIC 3.0 database (Oracle)

The 3.0 Oracle cleanup scripts are in the CIC Database Migration Assistant\Scripts\oracle\source\dupcheck directory.

Run these scripts from the SQL command-line in the following order, one after another. Don't run them at the same time. Use the syntax and special directions provided for each script.

  1. Create_Sprocs.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Create_Sprocs.sql

  2. Execute_Sprocs.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Execute_Sprocs.sql <data tablespace> <index tablespace> <action code>

    Note: Action code is 0 or 1. 0 does not remove data from the 3.0 tables; it creates tables that store the duplicates that would have been deleted if 1 was selected. 1 removes the duplicates and stores them in tables created during the sproc execution.

The Create_Sprocs.sql and Execute_Sprocs.sql scripts create and run the following stored procedures in the order presented to remove duplicates and nulls for 3.0 data:

  • Nulls_IAChangeLog.sql

  • Nulls_IVRInteraval.sql

  • Nulls_SurveyObjectToSurveyPrompt.sql

  • Nulls_SurveyUserObjectSecurityPrompt.sql

  • Nulls_UserWorkGroups.sql

  • Delete_Dups_AgentActivityLog.sql

  • Delete_Dups_AgentQueueActivationHist.sal

  • Delete_Dups_IAChangeLsql

  • Delete_Dups_ICDirChangeLog.sql

  • Delete_Dups_ILineGroupStats.sql

  • Delete_Dups_ILineStats.sql

  • Delete_Dups_IPA_Flow_Notes.sql

  • Delete_Dups_IWrapUpStats.sql

  • Delete_Dups_LineConfig.sql

  • Delete_Dups_LineGroupLines.sql

  • Delete_Dups_SurveyConfigLog.sql

  • Delete_Dups_SurveyDetail.sql

  • Delete_Dups_SurveyQuestionEnum.sql

  • Delete_Dups_SurveyRule.sql

  • Delete_Dups_SurveyObjectToSurveyPrompt.sql

  • Delete_Dups_UserWorkGroups.sql

Upgrade the CIC 2015 R1 or later database schema to the most recent CIC 2015 R1 or later database schema (Oracle)

The 2015 R1 or later Oracle Server scripts are in the CIC Database Migration Assistant\Scripts\oracle\target directory.

Run the scripts in the following order, one after another. Don't run them at the same time. Use the syntax and special directions provided for each script.

  1. Create_User_Role.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @create_user_role.sql <Schema_Owner> <CIC_User> <CIC_READONLY> <Schema_Owner_Password> <CIC_User_Password> <CIC_READONLY_Password>

    Note: You must have system permissions for the Create_User_Role.sql script.

  2. Create_DB_Objects.sp

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Create_DB_Objects.sp

  3. Ora_ICBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_ICBuild.sql <Data_Tablespace> <Index_Tablespace>

  4. Ora_TrackerBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_TrackerBuild.sql <Data_Tablespace> <Index_Tablespace>

  5. Ora_IRBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_IRBuild.sql <Data_Tablespace> <Index_Tablespace>

  6. Ora_OptimizerBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_OptimizerBuild.sql <Data_Tablespace> <Index_Tablespace>

  7. Ora_CSSurveyBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_CSSurveyBuild.sql <Data_Tablespace> <Index_Tablespace>

  8. Ora_WorkflowBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_WorkflowBuild.sql <Data_Tablespace> <Index_Tablespace>

  9. Ora_DialerBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_DialerBuild.sql <Data_Tablespace> <Index_Tablespace>

  10. Ora_IWPBuild.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_IWPBuild.sql <Data_Tablespace> <Index_Tablespace>

  11. Ora_ICFinalize.sql

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> @Ora_ICFinalize.sql <Schema_Owner>

  12. SetUpMigratorUser.sql

    Note: You must have system permissions for the SetUpMigratorUser.sql script.

    This script includes the SYS.DBMS_CRYPTO package. Before you run SetUpMigratorUser.sql, ensure that <dba_user> is in the sysdba role. By default, only Oracle database users with the sysdba role have permissions to grant authority to the SYS.DBMS_CRYPTO package.

    Run this script from the SQL command-line using the following syntax:

    sqlplus <User>/<Password>@<Hostname>:<Port>/<Service> as sysdba @SetUpMigratorUser.sql <Target_Schema> <MigrationUserName> <MigrationUserPassword>