Database migration

Project Portal 2015 requires the use of the AL32UTF8 character set for the database. Upgrading the character set in-place from the previous WE8ISO8859P15 character set is not supported by Oracle, so it is necessary to create a new database and copy the data using exp and imp.

Since the length of all fields of type VARCHAR in the old schema have been defined in bytes, it is necessary that those fields’ types be upgraded to have their lengths defined in characters instead — otherwise values at their maximum length containing non-ASCII characters will no fit inside the fields in the multi-byte database.

The exact steps for doing the database migration may vary depending on the environment, but the general steps are:

1. Run the script upgrade_varchars.sh against the original database. This modifies all VARCHAR fields’ type in-place, so the database must be backed up before running the script.
   NOTE: Fields indexed by Oracle text cannot be upgraded, so all Oracle text indexes must be dropped (using ot_index_admin.sh) before running the script.
2. Set-up your Oracle environment for the export, setting the environment variable NLS_LANG to american_america.WE8ISO8859P15. Either access to the Oracle server, or the Oracle full client is required to be able to export and import the database.
3. Export the 3.4-database using exp:
   
   exp userid=krono34dbuser name/krono34dbpassword@krono34db indexes=n rows=y constraints=n file=kd34db.dmp log=kd34db.log
4. Import the exported 3.4-database to the 2015-database.
   
   imp userid=krono40dbuser name/krono40dbpassword@krono40db fromuser=krono34dbuser name touser=krono40dbuser name file=kd34db.dmp log=pp2012imp.log