Migrating the Database Schemas

This section provides the procedures used to migrate the PPM database schemas from one database to another.

What to consider before migrating the database schemas

  • Export and import tools

    Make sure that the export and import tools you use are either the same version, or the export tool version is earlier than the import tool version. Using incompatible versions of export and import tools causes errors in instance migration.

  • If you use the Extension for Oracle E-Business Suite

    If you have Deployment Management Extension for Oracle E-Business Suite, you must consider the location of your Primary Object Migrator Host when migrating the PPM database schema, because Object Migrator might reside in the same database, or even the same schema, as PPM.

    Migrating the schema does not require migrating the Object Migrator instance because the integration method in PPM can be refreshed to use the existing Object Migrator installation. If Object Migrator shares a database with PPM, and you intend to migrate it as well as PPM, the destination database must support Object Migrator. (For more information, see the Object Migrator Guide.)

    Unless PPM and Object Migrator share the same schema, the migration of Object Migrator is completely separate from the migration of PPM, and should be completed before you migrate the PPM database. Contact Software Support Web site (https://softwaresupport.softwaregrp.com/) for instructions on how to perform this migration.

    If PPM and Object Migrator share the same schema and you want to migrate both, you must coordinate the migration activities. Contact Software Support Web site (https://softwaresupport.softwaregrp.com/) for instructions.

    Regardless of the configuration, refresh the integration definition after you migrate the PPM schemas.

How to migrate the database schemas

  1. Stop the PPM Server.

    For information about how to stop the PPM Server, see Start and stop the PPM Server on a single-server system.

  2. Export the PPM database schema to a file by running the expdp command as shown in the following example.

    $ORACLE_HOME/bin/expdp USERID=system/<Password>@<DB> DUMPFILE=<Export_Filename> DIRECTORY=<Dump_Dir> schema=<Source_SCHEMA> LOG=export_knta_950.log

    where

    <Password>

    represents the password for the system user on the Oracle database
    <DB>

    represents the database connect string
    <Export_Filename>

    represents the name of the file that is to contain the export. The filename must have the dmp extension (for example, kntaExport.dmp).
    <Dump_Dir>

    represents database dump directory. To create the directory, run the following:

    create directory DUMP_DIR as `c:/dump_dir';
    <Source_Schema>

    represents the name of the PPM database schema to export.

  3. Export the RML schema.

  4. Create the new PPM database schema:

    1. Run the CreateKintanaUser.sql script (located in the <PPM_Home>/install_950/ppm950/system directory) from SQL*Plus as the SYSTEM user.

      Example:

      SQL> @CreateKintanaUser.sql PPM_User PPM_Password Data_Tablespace Index_Tablespace TEMP_Tablespace Clob_Tablespace

    2. Run the GrantSysPrivs.sql script (located in the ppm950/sys directory) from SQL*PLUS as the SYS DBA user.

  5. Create the new PPM RML database schema.

    To create a new, empty RML database schema in the target database, run the CreateRMLUser.sql script (located in the ppm950/sys directory) from SQL*PLUS as the SYSTEM user.

    Example

    SQL> @CreateRMLUser.sql Rml_User Rml_Password Rml_data_tablespace Rml_temp_tablespace

  6. To import data from the export file that you created earlier into the new empty PPM database schema, run the impdp command, as shown in the following example.

    $ ORACLE_HOME/bin/impdp USERID=system/<Password>@<DB> DIRECTORY=<Dump_Dir> REMAP_SCHEMA=<Source_Schema>:<Target_Schema> DUMPFILE=<Export_Filename> LOG=import_knta_950.log

    where

    <Password>

    represents the password for the system user on the Oracle database
    <DB>

    represents the database connect string
    <Dump_Dir>

    represents database dump directory.

    <Source_Schema>

    represents the name of the PPM database schema previously exported.
    <Target_Schema>

    represents the name of the new PPM database schema
    <Export_Filename>

    represents the name of the file that contains the export. The filename must have the dmp extension (for example, kntaExport.dmp).

  7. Import the RML export file.

  8. Create the RML-related packages in the RML schema:

    1. cd<PPM_Home>/install_950/rml
    2. sqlplus <RML_Username>/<RML_Password>@<SID>@rml_driver.sql

  9. Grant privileges to the PPM RML database schema:

    Note: You can find the following scripts in the <PPM_Home>/install_950/rml directory.

    • To set up the permissions between the two.

      sqlplus <PPM_Username>/<PPM_Password>@SID @RMLSetupInPPMSchema.sql <RML_Username>

    • To create synonyms to PPM objects in the RML schema.

      sqlplus <RML_Username>/<RML_Password>@SID @RMLSetupInRMLSchema.sql <PPM_Username>

  10. Configure the database schema to ensure appropriate access to rebuild optimizer statistics.

    Note: If PPM and Object Migrator share the same database schema, the PPM database schema is referred to as the PPM account, and the Object Migrator schema is referred to as the Object Migrator account.

    To provide the necessary grants and permissions to the PPM user, run the GrantSysPrivs.sql script as SYS DBA.

    SQL> @GrantSysPrivs.sql <PPM_Username>

  11. If the Extension for Oracle E-Business Suite is in use and Object Migrator resides in the same schema as PPM, complete the Object Migrator migration.

    For assistance, contact Software Support Web site (https://softwaresupport.softwaregrp.com/).

  12. If you are using the Extension for Oracle E-Business Suite, refresh the Primary Object Migrator Host definition.

    Note: To validate any invalid PPM database objects generated during link regeneration, run the RecompileInvalid.sql script, which is located in the <PPM_Home>/install_950 directory. Run this script from SQL*Plus connected as the new PPM database schema account.

  13. Reconfigure the PPM Server to connect to the new database schema:

    1. Open the <PPM_Home>/server.conf file, add update the server configuration parameters described in PPM Configuration parameters.

    2. Run the kUpdateHTML.sh script after to apply your changes to the server.conf file. For details, see kUpdateHtml.sh.

  14. Start the PPM Server (see Start and stop the PPM Server on a single-server system).