Sunday, February 14, 2010

Peoplesoft database creation - Part II

After creating the database in the database creation part I, it is time to load the Peoplesoft obects.
The scripts to be apply are under $PS_HOME/scripts, the projects under $PS_HOME/projects
This part is entirely dedicated to the manual database load - this is not required if you are using the DBWizard as I explained earlier in an other article.

Before hands, add the tns antry for the remote database, and test the connectivity from the client workstastion.

1. Configure the connectid within Configuration Manager

2. Run the DataMover and connect in boot strap mode (Peoplesoft's owner, generally SYSADM)

3. File > Database Setup
4. If you're working on a Unicode database, check Unicode and choose a proper characterset.
5. Check DEMO database to build a database with some data sample (typically for training purpose or developement) and SYS to build a database without data sample (UAT and production database). For more details about DMO and SYS difference, read this article. In addition, add the languages you want to see in your application
note : in the screen below, the access password is specified by default in upper case (it could be an issue on 11g with password case sensitivity feature activated if the sysadm's password has been defined in lower case)
The base language is most often "English", to make your life easier leave it in English.
Finally run the script, be aware, over the network, it could take several hours to complete.


NOTE : it is important to know, since it is a HCM9.1 installation, you don't have to upgrade the Peopletools to the latest release. All Peoplesoft applications 9.1 have been built on Peopletools 8.50 and consequently they are already on the latest Peopletools level.

6. Try to connect onto the AppDesigner with the HCM default user PS/PS
If the connection fails as showed above, run the %PS_HOME%/scripts/grant.sql to give the grant required to people user (connectid).

If it fails again, verify the PSACCESSPRFL table, ACCESSID and ACCESSPWD, if they are equal, you may have to change the Oracle SYSADM user's password in upper case as below :

The connection to AppDesigner should succeed.

7. The following step is dedicated to the Multilingual installation. If you don't install a ML database, skip this step and go directly to the part III of the database creation.
7.1 Copy the project PPLTLSML from file under %PS_HOME%/projects


Choose only the language(s) you want to see in your application

7.2 Go to the DataMover with PS user
7.3 open and run the script(s) %PS_HOME%/scripts/pt850tlsxxx.dms (xxx is being your code language you are installing).




11 comments:

Ayyanar said...

Hi,
i am not able to connect the database with following

sqlplus SYSADM/SYSADM@HRMDEMO,

but i can able to connect like

sqlplus then i given the user name and password i am able to connect,
also i am not able to logon to datamover with SYSADM and it is giving ORA-12514 error,
please advice

Note: I am installed oracle 11g R2 32 bit.

Ayyanar said...

Hi Nicolas,

I am able to connect now,problem is host is mapped in ip address in both tns and listener. Changed to localhost, it solved the problem. i am able to connect and logon to datamover.

Thanks
Ayyanar

David PS said...

Hi,
I am installing Portal Solutions 9.1 on Tools 8.50 on Oracle 11g and Windows 2003 platform manually. I got the the point where I run the DMS for Database Setup in bootstrap mode, (connected using SYSADM). All the other scripts are ran manually and successfully.

The script was running fine until I got the error

- SQL Error. Error Position: 175 Return: 959 - ORA-00959: tablespace 'FSAPP' does not exist

CREATE TABLE PS_BUS_UNIT_LANG (BUSINESS_UNIT VARCHAR2(5) NOT NULL, LANGUAGE_CD VARCHAR2(3) NOT NULL, DESCR VARCHAR2(30) NOT NULL, DESCRSHORT VARCHAR2(10) NOT NULL) TABLESPACE FSAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
Error: Unable to process create statement for BUS_UNIT_LANG
Ended: Sun Aug 01 12:48:27 2010
Unsuccessful completion

When I ran the script to create the table space and executed sqlplus in nolog mode so I dont now how many more table spaces were not created.
Please how I get around this issue and finish my installation?

Thanks in advance for you help.
David

Anonymous said...

Hi, I am installing HCM 8.9 with PT 8.48.
I only have one doubt...pls let me know with which user(SYSADM, SYSDBA or SYSTEM), should I run the rel scripts to update the peopletools release?

cstjohn17 said...

Gasparotto, your blog is terrific. I am very new to Peoplesoft but enjoy reading your descriptions of the processes.

I am on a team that is upgrading from 8.9/8.49 HRCMS to 9.1/8.5. The project also calls for a conversion from MS SQL 2005(non unicode) to Oracle 11g (Unicode).

Our approach is to first convert the database from SQL to Oracle on version 8.9/8.49. After this is complete a more traditional upgrade will occr taking the app from 8.9 to 9.1.

We have used an export *; command with DataMover. We have fought with connectivity and security issues but now have the app server and PIA running. We can connect and login but have seen some issues related to improper view creation. Currently we plan to re-work the process on a seperate SID using the MSVPEXP DataMover script.

Any other ideas of suggestions would be appreciated. Thank you,
- Chris

Parvathy said...

Awsome! thanks a ton. Step 6 resolved my issue

Anonymous said...

Hi Nicolas,

Where are you running PPLTLS84CUR and PPLTLS84CURDEL can you please let me know whether it is neccessary to copy those projects i am installing PT851 with HRMS9.1..

Thankyou...

Anonymous said...

Hello Gasparotto,

Nice blog. while running the DB Script using Datamover, getting below error. could you please advise

Installing HCM 9.2 tools 8.55


Input file: HC9.2\Data\hcengs.db


Importing ACCDT_INS_TBL File: Data MoverSQL error. Stmt #: 0 Error Position: 393 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_ACCDT_INS_TBL"."DESCRSHORT" (actual: 11, maximum: 10) Failed SQL stmt: INSERT INTO PS_ACCDT_INS_TBL (ACCIDENT_INS, EFFDT, EFF_STATUS, HRDE_AI_PROV_NBR, DESCR100, DESCRSHORT, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3, COUNTY, STATE, POSTAL, GEO_CODE, IN_CITY_LIMIT, HRDE_AI_LIMIT_EAST, HRDE_AI_LIMIT_WEST, GPDE_SI_MX_ACC_INC, CURRENCY_CD) VALUES (:1, TO_DATE(:2,'YYYY-MM-DD'), :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27) Error: Unable to insert row 8 Error: SQL execute error for ACCDT_INS_TBL Ended: Fri Jun 23 11:38:14 2017 Unsuccessful completion


could you please advise

Anonymous said...

Hi,

I get a similiar error
Importing ACCDT_INS_TBL File: Data MoverSQL error. Stmt #: 0 Error Position: 393 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_ACCDT_INS_TBL"."DESCRSHORT
Did you get a fix for it?
Please share.

Thanks

Anonymous said...

I see a similiar error
Importing ACCDT_INS_TBL
File: Data MoverSQL error. Stmt #: 0 Error Position: 393 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_ACCDT_INS_TBL"."DESCRSHORT" (actual: 11, maximum: 10)
Failed SQL stmt: INSERT INTO PS_ACCDT_INS_TBL (ACCIDENT_INS, EFFDT, EFF_STATUS, HRDE_AI_PROV_NBR, DESCR100, DESCRSHORT, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3, COUNTY, STATE, POSTAL, GEO_CODE, IN_CITY_LIMIT, HRDE_AI_LIMIT_EAST, HRDE_AI_LIMIT_WEST, GPDE_SI_MX_ACC_INC, CURRENCY_CD) VALUES (:1, TO_DATE(:2,'YYYY-MM-DD'), :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27)
Error: Unable to insert row 8
Error: SQL execute error for ACCDT_INS_TBL
Ended: Tue Aug 08 16:03:49 2017


Anybody who got a resolution,please share.

Nicolas Gasparotto said...

With such error, you all missed an Oracle init parameter when creating the database (see part 1) : NLS_LENGTH_SEMANTICS=CHAR
Solution is to drop and revreate your database with correct setting.
Regards,