Skip to content

Converting PeopleSoft Non-Unicode application to Unicode on Oracle Databases

July 20, 2010
Unicode PeopleSoft Application:

==============================

This is for Multilungual implementation with one or more language application.In layman terms the database character set are to hold the world language wich need more more space for holding languages base like Japase,Chinese,Latin etc.,

Non-Unicode PeopleSOft Application:

===================================

Mostly for Western English and some simplified languges like Portuguse,spanish implementaion this means the space to store the charecter set in database is minimal

Below are the steps to conver Non-Unicode PeopleSoft Application to Unicode on Oracle Databases

This section discusses how to:

· Export database table structures using PeopleSoft Data Mover scripts.

· Export database contents using the Oracle Export utility.

· Create new Oracle10g database instances using the UTF-8 character set.

· Pre-create Unicode database table structures using PeopleSoft Data Mover scripts.

· Import database contents using Oracle Import.

· Specify Unicode databases in PeopleSoft systems.

· Create Views.

· Rerun Oracle database imports.

Understanding Converting to Unicode on Oracle Databases:

========================================================

Converting a PeopleSoft 8.x/9.X database on Oracle to Unicode requires creating a new Oracle instance with

a UTF-8 character set and new column sizes for PeopleSoft columns created as VARCHAR2.

For small databases, the simplest way of converting a PeopleSoft database on Oracle to Unicode is to

export the entire database using Data Mover, create a new Oracle instance using the UTF-8 character

set, and re-import the entire database using Data Mover.

For larger database, a better process is to use Data Mover to pre-build the structure of the tables and

then use Oracle export/import to move the bulk of the data between the old and new Oracle instances.

The following sections describe how to convert a PeopleSoft 8.x/9.x database on Oracle to Unicode.

Note: The information described is valid on PeopleSoft 8.x databases. It is not possible to convert a

database to Unicode on PeopleTools 7.x or earlier releases.

1. Export the database table structure using PeopleSoft Data Mover.

2. Export the database contents using the Oracle Export utility.

3. Create a new Oracle10g database instance using the UTF-8 character set.

4. Pre-create the Unicode database table structure using PeopleSoft Data Mover.

5. Import the database contents using the Oracle Import utility.

6. Enable the Unicode database in the PeopleSoft system.

7. Create views.

Step 1)Exporting Database Table Structures Using PeopleSoft Data Mover

=======================================================================

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

Note. This process exports the database table structure only and does not include the database contents.

Sign onto the PeopleSoft 8.x/9.x database using Data Mover in non-bootstrap mode, and run the following

script.

SET NO TRACE;

SET OUTPUT ;

SET NO DATA;

EXPORT *;

The DAT file created by Data Mover will not be large – possibly 20-30Mb, as it will contain only the

structure of your PeopleSoft tables, views and indexes– not the data.

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

Step 2)Exporting Database Contents(Table data) Using the Oracle Export Utility:

================================================================================

This section discusses how to:

· Set the NLS_LANG variable.

· Create an export parameter file.

· Export database contents.

Understanding Exporting Database Contents Using the Oracle Export UtilityOn the server machine running the Oracle 8i database, use the Oracle export command (exp) to export

all objects owned by the PeopleSoft OwnerID.

Setting the NLS_LANG Variable

Before running the export process, verify that the character set component of the NLS_LANG setting is

set to match the character set of the database. NLS_LANG is an environment variable on Unix and a

registry setting under HKEY_LOCAL_MACHINE\Software\Oracle on Windows.

For example, if the database character set is WE8ISO8859P1, NLS_LANG should be set to

AMERICAN_AMERICA.WE8ISO8859P1, or another language combination with WE8ISO8859P1 as the

character set component.

To verify the current character set of the Oracle database, run the following SQL statement in SQL*Plus

while logged in as a database administrator:

SELECT VALUE FROM SYS.V_$NLS_PARAMETERS

WHERE PARAMETER = ‘NLS_CHARACTERSET’

/

Creating an Export Parameter File

After you have set the NLS_LANG environment variable appropriately based on your instance’s current

character set, create an export parameter file similar to the following example, substituting the appropriate

username/password of the Owner ID and the location and name of the export file to be created.

This file can be created with any text editor.

Note: Ensure that the files system on which you will be creating the export file has sufficient space

available to accommodate all the data in the PeopleSoft database. A good estimate of the space needed

is to calculate the amount of space currently taken by TABLES owned by the PeopleSoft OwnerID and

adding 20 percent. You can get this data from querying the DBA_FREE_SPACE and DBA_DATA_FILES

catalog views. Remember that INDEX data is not stored in export files, only the index definition. If you

have insufficient space on any one file system to store the export file, you can use the filesize parameter

of the Oracle Export Utility. See your Oracle Utilities guide for more information on multi-file exports.

userid=/

buffer=100000

file=.dmp

rows=Y

indexes=Y

log=.txt

compress=Y

owner=

Exporting Database Contents

After you create the export parameter file as described in the previous section, call the Oracle export

using the following command, substituting with the name of the export parameter file.

exp parfile=

The export (.dmp) file created by this process will be referred to as the “database export” in this

document.

Step 3)Creating New Oracle10g Database Instances Using the UTF8 Character set:

==============================================================================

This section discusses how to:

· Create a new Oracle8i database instance using the UTF -8 character set.

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

Creating New Oracle Database Instances

Next, you must create a new Oracle instance with a Unicode encoding (UTF-8) which will become the

new PeopleSoft Unicode database.

This database must contain the same tablespaces as the original database, if one or more objects owned

by the PeopleSoft OwnerID were in those tablespaces.

For example, if the original database had 10 tablespaces, and PeopleSoft used five, you must create

those five tablespaces with the same names (but not necessarily the same file paths) in the new Unicode

instance.

When you create the new Oracle instance, be sure to specify CHARACTER SET UTF8 at the end of the

CREATE DATABASE command. See the Oracle SQL Reference guide for syntax information on the

CREATE DATABASE SQL command.

Pre-Creating Objects in New Oracle Database Instances

You must pre-create the following objects in the new Oracle database instance:

· The SYSTEM tablespace (automatically created by the CREATE DATABASE command).

· The PSTEMP tablespace.

· Sufficient active rollback segments to complete the import. One segment must be large enough to

contain the entire contents of the largest table for the import to succeed without incremental commits.

· The same tablespaces as used by the PeopleSoft OwnerID in the existing PeopleSoft database.

· The PS User ID and the PSDBOWNER table. To create these, run the DBOWNER.SQL script

provided by PeopleSoft through SQL*Plus. You must manually populate this table with a row for the

PeopleSoft database you are moving to the new instance. Check the contents of the existing

PS.PSDBOWNER table for a guide.

· The PeopleSoft OwnerID and ConnectID with the same name as in the existing PeopleSoft database.

You can create this ID by running the PSROLES.SQL, PSADMIN.SQL and CONNECT.SQL scripts

through SQL*Plus. See the PeopleSoft Installation Guide for details on running these scripts at

installation time.

· Set up appropriate LISTENER.ORA and TNSNAMES.ORA entries for the new instance.

Pre-Creating Unicode Database Table Structures Using PeopleSoft

Data Mover

Sign on to the newly created Oracle database using Data Mover. Login in bootstrap mode using the

OwnerID and password.

Once logged in, execute the following Data Mover script, substituting the name of the structural export file

you created in with Data Mover in a previous step. Be sure to include the “SET NO STATISTICS;”

command only if you are running PeopleTools 8.40 or higher. Without this command on PeopleTools

8.40, the Data Mover import process will fail once the PSDDLMODEL table is created:

SET NO TRACE;

SET NO DATA;

SET INPUT ;

SET UNICODE ON;

SET STATISTICS OFF; /* Only include this statement if you are running

PeopleTools 8.40 or higher */

SET NO INDEX;

IMPORT *;

This script creates all the tables in the original PeopleSoft database in the new database with no rows of

data, and without indexes. Indexes are omitted in this step, as it much more efficient to create indexes

after loading table data as compared to loading data into pre-indexed tables.

Importing Database Contents Using the Oracle Import Utility

This section discusses how to:

· Clear the NLS_LANG variable.

· Create import parameter files.

· Run the Oracle Import utility.

Understanding Importing Database Contents Using the Oracle Import Utility

Once the tables have been pre-created by Data Mover, you are ready to import the database export you

created. You perform this import using Oracle’s import utility (imp).

10

Clearing the NLS_LANG Variable

Before running the Oracle import process, clear the NLS_LANG environment variable (UNIX) or remove

the NLS_LANG registry setting (Windows). Doing ensures that Oracle reads the character set

information from the export file and performs the appropriate conversion to the database’s UTF -8

character set.

See Also

Setting the NLS_LANG Variable

Creating Import Parameter Files

Create an import parameter file, similar to the export parameter file you created during the export step.

This file should have the following commands:

userid=/

buffer=100000

file=.dmp

rows=Y

indexes=Y

log=.txt

ignore=Y

full=Y

Running the Oracle Import Utility

Run the Oracle Import utility from the command line using the following command, substituting in the

name of the file containing the above parameters.

imp parfile=

Ensure that the import completes successfully with no errors before continuing.

Specifying Unicode Databases in PeopleSoft Systems

When the import has completed successfully you must specify in PeopleSoft that the database is now a

Unicode database. To do this, login to SQL*Plus as the database owner ID, and run the following SQL

statement:

UPDATE PSSTATUS SET UNICODE_ENABLED=1;

See Also

Rerunning Oracle Database Imports

Creating Views

You are now ready to sign into the new PeopleSoft Unicode database. Log into SQL*Plus, and edit and

run GRANT.SQL (provided in your PeopleTools installation in the SCRIPTS directory), to setup the

appropriate grants to the ConnectID you created previously. Remember to edit the script first to include

the correct Connect ID.

11

You should now be able to log into your new PeopleSoft Unicode database using Application Designer.

Follow the steps in your PeopleSoft 8 Installation and Administration guide entitled “Create PeopleSoft

Views” in order to create all the views required by your database.

Rerunning Oracle Database Imports

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

Before re-running any Data Mover imports, or any time PSSTATUS is re-imported for a non-Unicode

export file, set the UNICODE_ENABLED flag to 1.

See Also

Specifying Unicode Databases in PeopleSoft Systems

Advertisements

From → PeopleSoft

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: