Converting to Unicode on Oracle Databases

Converting a PeopleSoft system on an Oracle database to Unicode may require creating a new Oracle instance with a Unicode character set. If your current database uses byte semantics (typical for PeopleSoft 8.9 applications or earlier), you must also create new column sizes for PeopleSoft columns created as VARCHAR2.

For more information on CREATE DATABASE syntax information:

See Oracle Database SQL Language Reference SQL Statements: CREATE CLUSTER to CREATE JAVA CREATE DATABASE

For small databases, the simplest way of converting a PeopleSoft database on Oracle to Unicode is to export the entire database using PeopleSoft Data Mover, create a new Oracle instance using a Unicode character set, and re-import the entire database using PeopleSoft Data Mover. The details of this process are not provided in this appendix.

For larger databases, a better process is to use PeopleSoft Data Mover to pre-build the structure of the PeopleSoft tables and then use Oracle Data Pump to move the bulk of the data between the old and new Oracle instances. The details of these processes are provided in this appendix.

The Oracle Database Migration Assistant for Unicode (DMU) is a migration tool that can implement character set migration without exporting and importing data. This method does not require creating a new Oracle instance with a Unicode character set, as it modifies the existing instance. You can use DMU instead of Data Mover for converting Oracle databases to Unicode. It is a simpler, faster utility that also provides a validation mode to identify any data that may have issues converting to Unicode.

If you are using the DMU tool for your database conversion, then you can skip to the detailed information on this utility in the section Converting to Unicode on Oracle Databases later in this topic, or else read the following sections on the other available methods to migrate Oracle databases to Unicode.

This section describes how to use PeopleSoft Data Mover to export the PeopleSoft database structure to a DAT file.

Sign into the PeopleSoft database using PeopleSoft Data Mover in user mode(non-bootstrap mode), and run the following script.

SET NO TRACE;
SET OUTPUT output_file.dat;
SET NO DATA;
EXPORT *;

The DAT file created by PeopleSoft Data Mover will contain only the structure of your PeopleSoft tables and indexes, not the data.

Save the file created by this process. It will be referred to as the structural export in this appendix.

This section provides an overview of exporting database contents and discusses how to:

• Set the NLS_LANG variable.

• Export database contents using Oracle Data Pump.

• Export database contents using the Oracle Export utility.

SELECT VALUE FROM SYS.V_$NLS_PARAMETERS
WHERE PARAMETER ='NLS_CHARACTERSET';

expdp username/password@SID dumpfile=filename 
schemas=OWNERID,ps

 expdp system/manager@HR  dumpfile=exp.dmp schemas=SYSADM,ps

This section provides an overview of creating a new Oracle database instance and discusses how to:

• Create the new Oracle database instance.

• Pre-create objects in the new Oracle database instance.

Creating the new database instance involves creating the directories to hold the database, the init <SID>.ora file, and the tnsnames.ora file. On windows,the oradim command is used to start the instance such as.

oradim -new sid HR -intpwd manager -startmode auto -pfile 
C:\HR\initHR.ora

Sign into the Oracle SQL utility of your choice using the database administrator ID and password., run the startup nomount command such as this:

startup nomount pfile=c:\HR\initHR.ora

The first four are run as sysdba (for instance,sqlplus / as sysdba), and the others are run as the system user ( for instance, sqlplus system / manager).

Some of these scripts must be edited to contain the information specific to your database . Check the sizes of the DBF files in the xxDDL.sql script and ensure they will be large enough for the new database.

select * from nls_database_parameters where parameter in 
('NLS_LENGTH_SEMANTICS','NLS_CHARACTERSET');

set ORACLE_SID=HR

oradim -new -sid HR -intpwd manager -startmode auto -pfile C:\HR\initHR.ora

Sign on to the newly created Oracle database with PeopleSoft Data Mover in bootstrap mode using the database administrator ID and password.

Once signed in, execute the following Data Mover script, substituting output_file with the name of the structural export file you created in PeopleSoft Data Mover previously:

SET NO TRACE;
SET NO DATA;
SET INPUT output_file;
SET LOG log_file;
SET UNICODE ON;
SET STATISTICS OFF;
SET ENABLED_DATATYPE 9.0;
IMPORT *;

This script creates all the tables in the original PeopleSoft database in the new database but with no rows of data. You can speed up the data load time by adding the command SET NO INDEX; If you do that, remember to create them in Application Designer later.

This section provides an overview of importing database contents and discusses how to:

• Clear the NLS_LANG variable.

• Import database contents using Oracle Data Pump.

• Create an import parameter file for the Oracle Import utility.

• Run the Oracle Import utility.

• Rebuild PeopleSoft temporary tables.

 sqlplus / as sysdba

impdp system/manager@HR dumpfile=exp.dmp schemas=SYSADM,ps content=data_only

alter database datafile 'filename.DBF'resize new_size;

CREATE_TEMP_TABLE *

When the import has completed successfully, you must specify in your PeopleSoft system that the database is now a Unicode database. To do this, sign into the Oracle SQL utility of your choice using the database owner ID. Run the following SQL statement:

UPDATE PSSTATUS SET UNICODE_ENABLED=1;

Since you set ENABLED_DATATYPE previously, you also need to specify that the database uses Oracle CLOB and BLOB fields, as follows:

UPDATE PSSTATUS SET DATABASE_OPTIONS=34;

Before signing into the new PeopleSoft Unicode database, edit the GRANT.SQLscript (provided in your PeopleTools installation in the scripts directory) to include the connect ID you created previously. Then, run GRANT.SQL to set up the appropriate grants to the connect ID.

Sign into the Oracle SQL utility of your choice using the database administrator ID and password. Run the GRANT.SQL script.

You should now be able to configure your connection with Configuration Manager, and log in with Application Designer. It is recommended to check the database integrity by running the SQR audits DDDAUDIT and SYSAUDIT.

When the PSSTATUS table is imported from a non-Unicode export file, the UNICODE_ENABLED flag is 0.

Therefore, before re-running any PeopleSoft Data Mover imports, or any time PSSTATUS is re-imported from a non-Unicode export file, set the UNICODE_ENABLED flag to 1.

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.

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.

   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.