Creating a New Oracle Database Instance

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.

Understanding the New Oracle Database Instance

First, you must create a new Oracle instance with a Unicode encoding. This database 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 owner ID 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.

Important:

The AL32UTF8 character set is the preferred Unicode character set for the Oracle database.

When you create the new Oracle instance, be sure to specify CHARACTER SET AL32UTF8 (or CHARACTER SET UTF8) at the end of the CREATE DATABASE command.

For More Information on CREATE DATABASE sql command:

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

Pre-Creating Objects in the New Oracle Database Instance

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 owner ID in the existing PeopleSoft database.

  • The PS user ID and the PSDBOWNER table. To create these, run the DBOWNER.SQL script provided with the PeopleSoft software. 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 owner ID and connect ID with the same names as in the existing PeopleSoft database. You can create these IDs by running the PSROLES.SQL, PSADMIN.SQL and CONNECT.SQL scripts. See PeopleSoft 9.2 Application Installation for details on running these scripts at installation time.

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

You must set the NLS_LENGTH_SEMANTICS=CHAR variable in your init.ora file after running connect.sql, and restart the database. The installation guide provides information on the use of these scripts and on setting this and other variables.

See PeopleSoft 9.2 Application Installation for Oracle.

Adding NLS_LENGTH_SEMANTICS=CHAR

For Apps 8.9 or later, make sure that NLS_LENGTH_SEMANTICS=CHAR is added to the init.ora file after running CONNECT.SQL and before running Data Mover.

Add to init.ora file if not there already: NLS_LENGTH_SEMANTICS=CHAR, and then restart the database. Check with this SQL: 

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

Assuming Apps 8.9 or later, the character set should be UTF8 or AL32UTF8, and the length semantics should be CHAR.

To create a new oracle database instance:

  • Create a directory in the filesystem in for the new database

  • Create init<SID>.ora file with appropriate paths and control file details

    Note:

    After database creation and before running Data Mover, you will add the entry NLS_LENGTH_SEMANTICS=CHAR to init.ora

  • Create directories bdump, cdump, and udump as specified in the init.ora file.

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

Open Command Prompt and set the ORACLE_SID

set ORACLE_SID=HR

If on Windows, create the new service using oradim, for instance for a database named HR, and a system password of manager:

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.

Open each sql file from PS_HOME\scripts\nt folder and edit the file to add necessary parameters and execute the sql in sqlplus.

The scripts are located in the PS_HOME\scripts\nt directory.

Script Location

CREATEDB10.SQL

PS_HOME\scripts\nt\createdb10.sql;

UTLSPACE.SQL

PS_HOME\scripts\nt\utlspace.sql;

DBOWNER.SQL

PS_HOME\scripts\nt\dbowner.sql;

xxDDL.SQL

Tablespace size defaults may need to be increased in the script.

PS_HOME\scripts\nt\ptddl.sql;

xxDDL.SQL (for app example HCDDL.SQL) – Tablespace size defaults may need to be increased in the script.

PS_HOME\scripts\nt\hcddl.sql;

PSROLES.SQL

PS_HOME\scripts\nt\psroles.sql;

PSADMIN.SQL

PS_HOME\scripts\nt\psadmin.sql;

Examples at prompts: SYSADM, SYSADM, PSDEFAULT

CONNECT.SQL

PS_HOME\scripts\nt\connect.sql;

After running connect.sql, edit your init.ora file and add the parameter NLS_LENGTH_SEMANTICS=CHAR and restart the database.

For more information on scripts:

See the product documentation for PeopleSoft 9.2 Application Installation for Oracle.