Converting to Unicode on Oracle Databases Using Database Migration Assistant for Unicode

If your database supports it, you can convert your databases to Unicode using Database Migration Assistant for Unicode (DMU). It is a faster method than exporting and importing the non-Unicode files. You will need a minimum Apps version of 9.0, Tools version of 8.48, and DMU version of 2.1.2 or later.

Note:

A user with SYSDBA privilege, such as SYS can connect the DMU client to the database.

The database will need the PL/SQL package prvtdumi.plb, for instance:

sqlplus / as sysdba

SQL>@?/rdbms/admin/prvtdumi.plb

Note:

Java SE Development Kit (JDK) is required to run the DMU client. The main DMU documentation has details of the JDK.

To convert the database in Unicode using DMU:

  1. Verify the configuration requirements for DMU and install the required patches.

    1. Verify if the database and the operating system supports the current DMU release.

    2. Check for any restrictions for the databases to be migrated in the DMU Release Notes.

    3. Install required database patch. You can download the patch from My Oracle Support.

    4. Install the PL/SQL package in the database.

    5. Install Oracle XML DB component (XDB) in your database.

      Note:

      You can check if a valid XDB is installed in your system. To check, enter following in the command prompt:

      select comp_id, status from dba_registry;

      If you do not have XDB then to install XDB, enter the following in the command prompt:

      run catqm.sql, for instance as catqm.sql xdb SYSAUX TEMP NO.

  2. Setup the client for DMU and connect to the database:

    1. Install the JDK version required for the DMU version.

      Note:

      Make a note of the directory where you installed JDK. You will need to provide the path to it in the DMU.

    2. Install the DMU software from Oracle Technology Network download page.

    3. Start the DMU. When you start the DMU for the first time, it asks for the JDK installation directory.

    4. Create a database connection. Enter user Id, password and the network connection data of the database administrator; the target database host name, the TNS listener port, and the database service name. The user must have the SYSDBA privilege in the database.

      Note:

      Before connecting to the DMU client, grant SYSDBA privilege to your user using a password file from orapwd utility.

      For Windows, this file must be named ORACLE_HOME\database\PWD<ORACLE_SID>.ora, and for Unix this file must be named ORACLE_HOME/dbs/orapw<ORACLE_SID>.

  3. Run the Unicode migration in DMU:

    1. Install the DMU repository.

      Note:

      DMU will prompt you to update the NLS_LENGTH_SEMANTICS in init.ora at the end of the conversion.

    2. Start the migration process of the database to Unicode. The utility guides you through the three steps to Scan, Cleanse, and Convert.

      Note:

      If issues are reported during the scan, fix the issues outside of DMU and then re-scan until the scan reports no issues. PeopleSoft does not support cleansing data using the DMU Cleansing Editor. Use PeopleSoft Pure Internet Architecture or Application Designer to fix reported issues. For more information on working with translated data, see Understanding Application Definition Translation.

    3. Validate that the character set in the database is in Unicode.

  4. Set NLS_LENGTH_SEMANTICS=CHAR in your init.ora and restart your database. PeopleSoft Unicode databases use character length semantics.

  5. Run Audit:

    Run the audits such as DDDAUDIT, SYSAUDIT, and an Alter Audit.

    Note:

    With DMU versions before 2.1.2, any tables reported in the alter audit should be rebuilt in Application Designer to ensure that fields defined as LONG are implemented as CLOB when needed. With DMU 2.1.2 and later, the alter audit should show that no changes are needed.

If you use the DMU method, you don’t need to rebuild tables and indexes, or do imports, unless tables are reported in the alter audit. After the migration, the character set will be AL32UTF8, and the VARCHAR2 columns lengths will specify the number of characters instead of the number of bytes. For instance “descr psmsgcatlang” will show that message_text is varchar2(100 char).

This method is also documented for PeopleTools 8.48 and above. You can access the document from My Oracle Support, Document ID: 1627714.1.

For more information on the Oracle Database Migration Assistant for Unicode on supported configuration, DMU documentation, and accessing DMU client for download, go to the Oracle Technology Network.