Migrating the Database Schemas

This section provides the procedures used to migrate the OpenText 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 OpenText PPM database schema, because Object Migrator might reside in the same database, or even the same schema, as OpenText PPM.

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

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

If OpenText 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 OpenText PPM schemas.

How to migrate the database schemas

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.

Export the OpenText 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 OpenText PPM database schema to export.

Export the RML schema.
Create the new OpenText 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.
Create the new OpenText 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

To import data from the export file that you created earlier into the new empty OpenText 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 OpenText PPM database schema previously exported.
`<Target_Schema>`	represents the name of the new OpenText PPM database schema
`<Export_Filename>`	represents the name of the file that contains the export. The file name must have the `dmp` extension (for example, `kntaExport.dmp`).

Import the RML export file.

Create the RML-related packages in the RML schema:

```
cd<PPM_Home>/install_950/rml
```

sqlplus <RML_Username>/<RML_Password>@<SID>@rml_driver.sql

Grant privileges to the OpenText 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 OpenText PPM objects in the RML schema.
```
sqlplus <RML_Username>/<RML_Password>@SID @RMLSetupInRMLSchema.sql <PPM_Username>
```
Configure the database schema to ensure appropriate access to rebuild optimizer statistics.

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

To provide the necessary grants and permissions to the OpenText PPM user, run the GrantSysPrivs.sql script as SYS DBA.
```
SQL> @GrantSysPrivs.sql <PPM_Username>
```
If the Extension for Oracle E-Business Suite is in use and Object Migrator resides in the same schema as OpenText PPM, complete the Object Migrator migration.

For assistance, contact Software Support Web site (https://softwaresupport.softwaregrp.com/).
If you are using the Extension for Oracle E-Business Suite, refresh the Primary Object Migrator Host definition.

Note: To validate any invalid OpenText 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 OpenText PPM database schema account.
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 OpenText PPM Configuration parameters.
2. Run the kUpdateHTML.sh script after to apply your changes to the server.conf file. For details, see kUpdateHtml.sh.
Start the PPM Server (see Start and stop the PPM Server on a single-server system).