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
==============================
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
No comments:
Post a Comment