Sunday, January 27, 2008

On The Peoplesoft Road : Peoplesoft Database creation - Part II

After loading the Peoplesoft Application (including PeopleTools objects) in the database creation part I, we should upgrade the database to the latest release we curretly use. This step is not required if your application has been built (I mean the Appliatin database version) on the same PeopleTools which you use.
Since we currently install HRMS9.0 on PT8.49, and like for all the version 9.0 it has been built on PT8.48, we should upgrade the Applicatin database version. It consists on several scripts (sql, dms) and application of projects. Some of the following steps below are also for multilingual application, don't apply these step if your only in english.
The scripts to be apply are under $PS_HOME/scripts, the projects under $PS_HOME/projects

1. Run the sql script update of maintenance table (/apps/psoft/hrms9/scripts) :
[hrms9@orion:/apps/psoft/hrms9/scripts]$ sqlplus sysadm/sysadm@DMOHRMS9

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 6 20:58:53 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @rel849un.sql
SQL> UPDATE PSSTATUS SET TOOLSREL='8.49',
2 LASTREFRESHDTTM = SYSDATE
3 ;

1 row updated.

SQL>
SQL> CREATE UNIQUE INDEX PSAPSROLECLASS ON PSROLECLASS (CLASSID,
2 ROLENAME) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
3 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
4 ;

Index created.

SQL> ALTER INDEX PSAPSROLECLASS NOPARALLEL LOGGING
2 ;

Index altered.

SQL>
SQL> CREATE INDEX PSCPSTREELEAF ON PSTREELEAF
2 (SETID,
3 TREE_NAME,
4 EFFDT DESC,
5 TREE_NODE_NUM,
6 RANGE_FROM,
7 RANGE_TO) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
8 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
9 ;

Index created.

SQL> ALTER INDEX PSCPSTREELEAF NOPARALLEL LOGGING
2 ;

Index altered.

SQL>
SQL> COMMIT
2 ;

Commit complete.

SQL> SPOOL OFF
SQL>

2. Run the grant script to allow the PEOPLE user to read the tables as well (PSSTATUS, PSOPRDEFN, PSACCESSPRFL) :
[hrms9@orion:/apps/psoft/hrms9/scripts]$ sqlplus sysadm/sysadm@DMOHRMS9 @grant.sql

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 6 21:03:03 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


Grant succeeded.


Grant succeeded.


Grant succeeded.


Commit complete.

SQL>


3. As user PS, open the DataMover, modify and run storeddl.dms (comment out the database you are not running on) :


4. In bootstrap mode (user which is the owner of objests), SYSADM in the current case, open DataMover, modify (only the log output file) and run msgtlsupg.dms

5. In Application Designer, as PS user (you cannot connect in bootstrap mode - SYSADM - within the AppDesigner), copy the project PPLTLS84CUR
=> Don't build it now !
Take care, the project is very big and take age to copy (network avaibility !)
Some messages can be ignore. Don't build the project now !

6. In Application Designer, as PS user, copy the project PPLTLS84CURML or if you installed French, copy the project PPLTLSML
=> Don't build it !
Don't build the project !

7. In Application Designer, as PS user, copy the project PPLTLS84CURDEL
=> Don't build it !
Don't build the project !

8. In Application Designer, as PS user, open and build the project PPLTLS84CUR
Open the settings to set all the parameters as well :Name a sql script to be run after project building :Take care to all the alter and create tabs options :Some warning are raised, you can ignore them.

9. Run the SQL script created by the previous project's build in SQL*Plus
At this step, you shouldn't have any error, check the spool file as well.

10. In Application Designer, as PS user, copy and build the project PT84TBLSPC
In the copy option, take care to check the DDL from source option :Build the project :Take care of the build settings, they are a different than previously :
No warnings, no errors.

11. Run the SQL script created by the previous project's build in SQL*Plus

12. Update the Peopletools system data, run the following DMS scripts in bootstrap mode (from $PS_HOME/scripts) : pt849tls.dms, pslanguages.dms, tlsupgnoncomp.dms

13. For ML application, run the following statement in DMS (take from the install doc the three letter code of your language, FRA is for french) :
UPDATE PSLANGUAGES SET INSTALLED = 1 WHERE LANGUAGE_CD='FRA';

14. In DMS, as PS user, run the following DMS scripts (from $PS_HOME/scripts):
pt849tlsfra.dms (if you installed French db), msgtleng.dms, ptstreng.dms, $PS_HOME/src/cbl/base/storept.dms, ptdefnsec.dms
and createvw.dms, this last one will (re)create around 18000 views, so take care to the time to apply.

15. Run application engine from your client, go to $PS_HOME/bin/client/winx86:
This query define the user who can run the AEs
SQL> select roleuser, rolename from psroleuser where rolename in ('PeopleSoft Administrator', 'Portal Administrator');

ROLEUSER ROLENAME
------------------------------ ------------------------------
PSEM PeopleSoft Administrator
PSHC PeopleSoft Administrator
PS Portal Administrator
PSEM Portal Administrator

Better to use PS, then in a DOS Window :
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPG844PORTAL
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGQRYDUPHED
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPTSMDAT
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT846PP
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT848PP
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT848IBUG (addendum : you should NOT run this AE if the Application you are installing has been built on Peopletools 8.48 or later)

***note CD => dbname, CT => dbtype, CO => user, CP => pwd, R => run control, AI => AppEngine name***

16. In Application Designer, as PS user, open the project PTUPGIBCLONE and copy to a file

(addendum : if the Application you are installing has been built on Peopletools 8.48 or later go directly to step 20)

17. In DMS, as PS user, run $PS_HOME/scripts/ptupg_trx_export.dms

18. In Application Designer, as PS user, open the project PTUPGIBDELETE and copy to a file.

19. In Application Designer, as PS user, copy the project PTUPGIBDELETE from the file previously created, since the project already exists in database, you should be sure to check "From File"
20. Within SQL*Plus, run the grant.sql script.

The PeopleTools upgrade is finished.

11 comments:

Anonymous said...

Excelent Document, Better than Oracle's Installation Guide. I would like if oracle too can come up with multiple Installation guides that addresses each combination of database and OS seperately .... makes life much easier

Anonymous said...

Hi Nic,

What could be wrong that I can't find this PTUPGIBCLONE in step 16

Need your help.


Thanks

Gasparotto Nicolas said...

I assume you are the one who posted few minutes ago the same question onto the Peoplesoft forum :
http://forums.oracle.com/forums/thread.jspa?threadID=856006&tstart=0

And for reminder, here below my answer :
That is because you did not run the AE UPGPT848IBUG which create the project you are looking for.
You don't have to apply that step if your delivered application run on PT8.48 or above, from the doc you could read on that step (Converting Integration Broker) :
"If your database is delivered with PeopleTools 8.48 or higher, do not run this task since the database is
already delivered with the new Integration Broker objects as of PeopleTools 8.48. Instead, proceed to
Changing the User Interface.
"
So, most probably you don't need to run this step at all.

I realized I didn't wrote to avoid that step in that case,, thank for your remark, I added an addendum at the step 16 which cause your troubles.

Enjoy,

Nicolas.

Anonymous said...

Thanks alot. It was me.


Now I want to do HRCS 9.0 MP5 upgrade. Could you please give me some ideas on how to quickly get it done?


Thanks


Texas!

Anonymous said...

Actually, its HRCS 9.0 MP5 upgrade with ZAF localisations.

Jim Marion said...

Tips:

* When running createvw.dms, be sure to use Configuration Manager to turn off PeopleTools tracing. I forgot and had a 300 MB trace file after the first few thousand views.

* If you can't log into AppDesigner after running the PPLTLS84CUR SQL script, then run grants.sql.

Anonymous said...

Can somebody help, please? I'm on the database configuration II, I got this message when I attmpted to login to the data mover with PS and PS.
File: e:\pt849-904-R1-retail\peopletools\SRC\psmgr\mgrgensql.cppSQL error. Stmt #: 1926 Error Position: 21 Return: 942 - ORA-00942: table or view does not exist
Failed SQL stmt:SELECT COUNT(*) FROM PSAUTHQUEUEMON WHERE CLASSID = :1

Yinka said...

Can anyone
help, I'm having difficulties in running this files in data mover as PS/PS, msgtleng.dms, ptstreng.dms . I got insufficient privilege message.

kunal said...

Hi Nic!
Thank you for this awesome document on the installation..I ran everything till step 9 and it all went fine. Then I had to take a break and started the installation again next day and I rebooted my computer. Now when I try to log into App designer with VP1/VP1 it does not work..it says invalid id/pwd I even tried SYSADM/SYSADM and same error! I even checked the spool file and there was no error. please help!

ajith said...

Hi Nicolas,

In step3 of db creation part II for finance user should we login as VP1??

Where ae we specifying VP1 for finance..do we need to change all PS users to VP1 in dbowner.sql script?/

Please advise..

Anonymous said...

ajith the VP1 for Finance and PS for HRMS will be imported after by excuting the install from DMS.

HELLO-IT