Oracle 11.1.0.7 install on OEL5.3

After installing OEL5.3, time to install Oracle. Peoplesoft 8.48 and 8.49 are fully supported on the last version, 11.1.0.7.

So, let's go.

Firstly, be sure you have all the required packages installed :
binutils-2.17.50.0.6-9.el5.i386
compat-db-4.2.52-5.1.i386
compat-gcc-34-3.4.6-4.i386
compat-gcc-34-c++-3.4.6-4.i386
compat-glibc-headers-2.3.4-2.26.i386
compat-libgcc-296-2.96-138.i386
compat-libstdc++-296-2.96-138.i386
compat-libstdc++-33-3.2.3-61.i386
cpp-4.1.2-44.el5.i386
elfutils-libelf-0.137-3.el5.i386
elfutils-libelf-devel-0.137-3.el5.i386
elfutils-libelf-devel-static-0.137-3.el5.i386
gcc-4.1.2-44.el5.i386
gcc-c++-4.1.2-44.el5.i386
glibc-2.5-34.i686
glibc-common-2.5-34.i386
glibc-devel-2.5-34.i386
glibc-headers-2.5-34.i386
java-1.4.2-gcj-compat-1.4.2.0-40jpp.115.i386
ksh-20080202-2.el5.i386
libaio-0.3.106-3.2.i386
libaio-devel-0.3.106-3.2.i386
libgcc-4.1.2-44.el5.i386
libstdc++-4.1.2-44.el5.i386
libstdc++43-devel-4.3.2-7.el5.i386
libstdc++-devel-4.1.2-44.el5.i386
make-3.81-3.el5.i386
sysstat-7.0.2-3.el5.i386
unixODBC-2.2.11-7.1.i386
unixODBC-devel-2.2.11-7.1.i386

If some are missing, add it, and no more the question on which cd is it, OEL5.3 is delidered as a DVD right now.

And also the kernel settings :
1. /etc/sysctl.conf
Change the line
net.core.rmem_default = 262144
by
net.core.rmem_default = 4194304
After modification, run "sysctl -p" to take the changes in account without reboot the server.

2. /etc/pam.d/login
Add the line :
session required pam_limits.so


Then in a XWindows client, run the following command as oracle user and everything should be fine.
./runInstaller :






Finally, the patchset 1:




Enjoy,

Oracle Entreprise Linux 5.3

The installation of OEL5.3 is pretty similar of the previous version.
Two main differences however :
1. We have now a DVD iso image available for download (http://edelivery.oracle.com)


2. We have an Oracle specific package (oracle-validated-1) to check some settings and create the oracle user and dba/oinstall groups :


All the other steps are same as the previous version, I won't paste the screenshots again over here.
You can find all the screenshots of the OEL4.6 install in one of my previous posts :
http://gasparotto.blogspot.com/2007/12/on-peoplesoft-road-oel-46.html

Enjoy,

Speed up a copy over the network

I have to copy all datafiles of a database over the network between two of my VMs.
The datafiles are big, all together, it is around 150Gb. My network is slow and busy (an average of 5 or 6Mb/s).
The standard scp command line copy one 1gb datafile in 6 minutes (5'52'') :

Sun Feb 22 15:39:57 CET 2009
scp root@192.168.1.21:/oradata/DMOFSCM9/datafiles/pclarge.dbf .
Sun Feb 22 15:45:49 CET 2009
ls -lrt
-rw-r----- 1 root root 1040195584 Feb 22 15:45 ./pclarge.dbf

How to speed up the copy ?
I found a way to reduce that time to 34 seconds for this 1Gb datafile. Since the servers are mine, everything is allowed .
But only one command line :
Sun Feb 22 15:45:49 CET 2009
********
Sun Feb 22 15:46:23 CET 2009
ls -lrt
-rw-r--r-- 1 root root 1040195584 Feb 22 15:46 ./pclarge.dbf

Any idea ?

update : thank you for the suggestions, I have to test them.
Currently, I used the following command, I don't say it is the best, but at least, was not bad according to the down time :
ssh root@192.168.1.21 "gzip -c < /oradata/DMOFSCM9/datafiles/pclarge.dbf -"|gunzip -c > /oradata/DMOFSCM9/datafiles/pclarge.dbf

OEL5.3, Oracle 11.1.0.7 and PTools patches

My lab is becoming out of date, built more than one year ago.
My OS is Oracle Entreprise Linux 4.6, and last week the 5.3 has been released, time to get a newer OS.
My databases are Oracle 10.2.0.4, and now Peopletools 8.49 is supported onto 11.1.0.x for a while, time for change.
Lastly, my Peopletools are 8.49.11, the latest patch is 8.49.18, also time to go further.

It is a lot of work, I'm planning to create a new VM, install all the required binaries (Oracle, Peopletools, Application HRMS, CRM and FSCM) then move the databases to that new VM and upgrade them.

I'll post soon the steps of that migration.

Enjoy,

DEMO HRMS 9.0 : ORA-00600

If you are installing, working, with Peoplesoft HRMS9.0, Peopletools 8.49 on Oracle database 10gR2 (currently on 10.2.0.4), you could easily hit an ORA-600 error.

Go to the front end application, menu :

"Manager Self Service / Job and Personal Information / Request Job Change"

You'll receive the following pop-up error message :

And the alert.log of the database :

[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ tail -f alert_DMOHRMS9.log

Starting background process QMNC

QMNC started with pid=13, OS id=6767

Sun Feb 8 17:38:24 2009

Completed: ALTER DATABASE OPEN

Sun Feb 8 17:38:32 2009

Starting background process CJQ0

CJQ0 started with pid=19, OS id=6830

Sun Feb 8 17:45:08 2009

Errors in file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc:ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

The content of the file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc :
[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ more /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
/apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/10.2.0
System name: Linux
Node name: orion.phoenix-nga
Release: 2.6.9-67.0.0.0.1.ELsmp
Version: #1 SMP Sun Nov 18 00:23:42 EST 2007
Machine: i686
Instance name: DMOHRMS9
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 6865, image: oracle@orion.phoenix-nga
*** ACTION NAME:() 2009-02-08 17:45:08.957
*** MODULE NAME:(PSAPPSRV@orion.phoenix-nga (TNS V1-V3)) 2009-02-08 17:45:08.957 *** SERVICE NAME:(DMOHRMS9) 2009-02-08 17:45:08.957
*** SESSION ID:(145.39) 2009-02-08 17:45:08.957
*** 2009-02-08 17:45:08.956
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT ' ' ,C.MANAGER_ID ,COUNT(*) FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2
.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_R CD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTI D AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX (A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 W HERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID

----- Call Stack Trace -----

[...]

If we are running the same query onto the database within SQL*Plus, same error, even to get the explain plan :
SQL> explain plan for
2 SELECT ' ' ,C.MANAGER_ID ,COUNT(*)
3 FROM PS_JOB A , PS_DEPT_TBL C
4 WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W')
5 AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) 6 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) 7 AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) 8 9 10 11 12 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID 13 14 15 16 17 18 ;

FROM PS_JOB A , PS_DEPT_TBL C

* ERROR at line 3: ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

Some of the indexes of the two tables involved in this query are created in descending order.

The descending indexes have been introduced with the Peopletools 8.48 onwards for some (for me, mysterious) reasons, let's say surely to improve the performances.

But, I remember some months ago a benchmark I did on CRM9 (Peopletools 8.48 - Oracle database 10.2.0.3), the indexes in descending order cause a lot of performances issues. I had to use an hidden parameter to deactivate the descending indexes to have better performance.

/* For reminder, it is not only more than a huge work to recreate all the indexes without that DESCending clause, but that make the Peoplesoft application customized, which is not so good for the support, that's why the hidden parameter was a good workaround to keep the Peoplesoft objects closer to the standard. */

Here again, the descending indexes causes the trouble.

As you could have seen above, it is not performance issue, but transactional error and a database ORA-600.

It seems to hit an "old" Oracle bug (from 8.1.7.x to 9.2.x, bug ref #869177) for which the workaround was the setting of "_IGNORE_DESC_IN_INDEX" to TRUE (FALSE by default).

So, it seems we are still under the same effect, my database is 10.2.0.4. So, let's change the setting :

SQL> conn / as sysdba
Connected.
SQL> alter system set "_ignore_desc_in_index" = true scope=both;

System altered.

SQL>

Now, it is not enough, the indexes have to be rebuilt. Rebuild in the Peoplesoft meaning, DROP + CREATE.

So, open the Application Designer, connect within PS user.

Insert into the project the two records JOB and DEPT_TBL.

Then build the project, check only "Create Index", go to the settings, and be sure you are checking the "Recreate index if it already exists", then build the project, as show below :

Then, run the generated SQL script, that won't modify the record (the tables), just drop and recreate the indexes. Oracle will ignore the DESC option embedded in the commands lines of the indexes creation because of the setting of that hidden parameter "_IGNORE_DESC_IN_INDEX".

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

SQL> @rebuild_indexes

Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index created.


Index altered.
[...]

Finally, go back to the front end application, and retry the previous failing transaction, from now that'll work fine :

Problem solved.

However, here above was only an example on two records (tables), but you could have to rebuild all the indexes for all the records. A lot of time is required for that.

Conclusion : before creating, especially before loading the Peoplesoft objects into the database (before the first datamover load of the database setup), it could be a good idea to set this hidden parameter "_IGNORE_DESC_IN_INDEX" to TRUE.

Caution : from now, you have a difference between the Peoplesoft datamodel (with the DESCending order in the indexes description) and the Oracle dictionary (without the DESCending order), that means every time you'll rebuild the records, the Application Designer will try to rebuild the indexes, that is not the case when objects are same on both sides. This is one of the biggest problem of this solution.

Caution 2 : playing around hidden parameter is supposed to be validated by your Oracle support.

Going further : after raising a SR to the Oracle support, it seems this bug is solved on Oracle 11.1, and a fix will be included in the coming 10.2.0.5 patchset.

Wait and see, or manage an upgrade to 11.1 (after testing, I confirm, it is working fine on 11.1.0.7).

Enjoy,

Full PeopleBooks on OTN

As announced on the PeopleSoft Technology Blog the Peoplebooks are now available with a workable search feature.
The Peoplebooks are still available in PDF format (less usable) :
http://www.oracle.com/technology/documentation/psftent.html

But from now, this is a fully working Peoplebooks which are avalaible, a very great new :
http://www.oracle.com/pls/psft/homepage

Enjoy it !

2009

First of all, I wish an happy New Year to all the Oracle community ! Best wishes, and may the economic crisis don't impact you, or at a minimum level.

What are waiting us for the new year 2009 ?

Well, several new versions are waiting, let's make some bets :
1. Peopletools 8.50, a Web 2.0 ready, and a big difference compared to the Peopletools 8.4x. Maybe for the first quarter.
2. Peoplesoft 9.1, few Peoplesoft applications are waiting in that new version, started by FSCM. Maybe for the second quarter.
3. Oracle 10.2.0.5, with a lot of bug fixes I'm looking for, let's bet for the second quarter, or maybe the third.
4. Oracle 11.2, let's bet for the third quarter of the year - during the OOW2009 ?
5. Oracle Entreprise Linux 5.3, sometime between the first and the second quarter.

And also a wish regarding the OTN forums, make it more usable and pointless and workable...
Metalink2 could be less funny and more usable...
Metalink3 could be improved, currently many links are still pointing to the old and unavailable GSC...

Enjoy this year !

impdp with in Peoplesoft database

I was very enthusiat by the new features offered by the Oracle import dump, the new import utility in 10g.
Especially with the option NETWORK_LINK which allow us to do an import in a database through a database link directly from the database source, without having the need of the creation of any dump file.
It is very useful regarding the size of Peoplesoft database dump, several dozen of Gb.
Until today, to save disk space, it was common to use an old export utility into a dump file and compress on the fly. Same for the import, uncompress and import into database on fly.
Since it is not possible to do with expdp, the NETWORK_LINK should offer a good alternative.

Unfortunately, two mains points make it impossible to use :

1. NETWORK_LINK does not work with tables containing LONG datatype.
It is not a joke, but Peoplesoft database (before the Peoplesoft version 9.0 and Peopletools 8.49) still contains a lot of tables like that. I was very disapointed by this limitation, and I was surprised because nothing in the documentation say anything about that limitation. You could read the Metalink note 6630677.8 (IMPDP skips table exported just before table with LONG column using NETWORK_LINK - seems to be fixed in 11.1.0.6).
Ok, we could find a workaround and work through a file for these tables (with LONG datatype column in it), even if it's a pity. Or work with the very old, but much more robust SQL*Plus command COPY.

2. There is a bug in the number of imported rows reported in the log file (known bug #7146127, not solved yet). In some cases, for tables containing a lot of columns (I don't know what's the number to hit this error), it is reported 1 imported row in the log file, even if it imported several of thousands of rows. So, now how to prove how many have been imported ? Run a count against every single imported tables ? Not acceptable.

Here is the output of number of columns by tables (Peoplesoft HRMS9, Peopletools 8.49) :
Nb tables with less than 100 columns.................. : 42655
Nb tables with more than 100 and less than 200 columns : 171
Nb tables with more than 200 columns.................. : 21

What else ? Still stuck with exp/imp utility despite they are old.

Enjoy,

Addendum (04-FEB-2009) : regarding the wrong number of rows reported in the logfile, there is one off-patch for 10.2.0.4, it is fixed in the coming 11.2, please have a look into the following Metalink note :
Impdp Logfile Reports Wrong Number of Row Imported For a Table If Using NETWORK_LINK
Doc ID: 752476.1

Oracle 11.1.0.6_1013

Last month, October 2008, Oracle has released a new version of the initial patchset level of 11gR1 - 11.1.0.6 - downloadable on http://download.oracle.com
If you start a new project with in 11gR1 database, take care to have this version, the file is suffixed by _1013.
This new recut of the 11gR1 include a bug fix - see the Metalink note 738115.1 for more information.

Enjoy,

Metalink3, the end of Peoplesoft GSC, future of database support

Yesterday, Firday 7th, November 2008, the website Metalink3 has take in place of the Peoplesoft Global Support Center (http://www.peoplesoft.com).
That was expected since Oracle bought Peoplesoft. Finally, it is happening.
It is a very big change for Peoplesoft cutomers.
For who work on Peoplesoft for several years, that means a lot of habits have to be change (started with the severity scale and basics words as "case" becoming "SR").

Awaiting the effectivness of the replacement (from the coming Monday, after the outage during this week-end), the Metalink3 was available in "reduce" mode without all the features. That is more likely the Oracle classic Metalink site we know for the database support.

Moreover, it has been already announced, the Metalink3 website will become also the support website for the Oracle Database, eBusiness... the date is not defined yet.

Well, hopefully, that'll help,

Enjoy,

Nicolas.

Oracle 11g patch set 1 (11.1.0.7)

Few days ago the Oracle 11g patch set 1 - 11.1.0.7 - has been out for Windows plateform (it was already available for Lunix few weeks ago).
Time to upgrade my Personal Edition database :
SQL> select * from v$version;

BANNER
------------------------------------------------------------------
Personal Oracle Database 11g Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL>

OTN forums : 28 days later, what else ?

In reference to the horror movie 28 days later.

Tomorrow, already 4 weeks later. Or 28 days later... (to continue the movie reference, read this article : 500)
28 days after the OTN forum's software upgrade. And ?

Well, a lot of posts have been written about new features around the blogoshpere- like the most (in)famous point system.

So, 28 days later, where are we ? Instability is still the main problem, at least from some part of the world.We have to be patient, or learn patience... down... up... down... up... and network issue...

Instability.

The top user list has been dropped down. Since OTN forums is cumulated points and no more the number of posts for the top10 users, everyone have seen his point counter dropped to 0.
Ok, if this is not a problem in the two major forum (Database - General and SQL & PL/SQL) where there is enough activity to show this activity, I think this is a real problem on some less popular forum like one of my favorite - Peoplesoft.
Between instability, no more people in the top users list, and a point system not very well explained, people are went away. There was some sort of activities in such forum, but since... there is very, very, very few.

Less acitvity.

Then, some new behaviours have been seen. Ok, there is an report abuse button, very good. But...
Sometimes, there are a bunch of answers without more questions about the requirements where it would be required. Pity.
Some people copy&paste what they found over Internet/Metalink without given the link reference, and they got points, because the OP doesn't realize the plagiarism (it seems to me it happens much more than before). Pity.
Sometimes answers are marked as correct when obviously it is not. Pity.
Sometimes answers are marked as correct for giving a link to http://tahiti.oracle.com/. Does that make sense ?
Sometimes people claim their points. Pity. Did they claimed a thanks before ?
It is interesting to see how point system has motivated some people to try their chance, and also to "reactivate" some old account. Indeed, I haven't seen so much active "old" account since a while.
I have no statistics, but seems to be a lot of new users have been created after the upgrade...
In the mean time, a lot of other people disappeard.
Coincidence ?

New people. New behaviour.

Well, finally, it seems we got a new "OTN forums world", like a new world 28 days after pandemic with only few survivors.

I wish a long and a workable life to the OTN forums.

Have fun,

Oracle Open World 2008

I'm registered for the Oracle Open World 2008 in San Fancisco, California, 21-25 September.
First time I'll go there, it seems very interesting.
I'll post later here the conferences I'll try to follow (some about Oracle database, some about Peoplesoft/Peopletools).

Enjoy,

A bare-metal virtualization for free

Yesterday, VMWare announced one of their products, VMWare ESXi, will be provide for free later next week.
Very interesting for whom want test, learn and work on a bare-metal virtualization software, hypervisor, for free.
This is a little bit different than VMWare Server, but also make a more robust and perfomant system. The main difference is about host OS, VMWare Server need one whereas VMWare ESXi don't need it - it's bootable -, however it required a dedicated machine.
A test should come soon here.

Enjoy,

Firefox 3

Just for the fun...

Peoplesoft (server) on Windows XP

Peoplesoft, the server part, has never been supported on Windows XP, and maybe will never supported.

So, why such article ?
On the Peoplesoft forum, there are a lot of questions about that configuration. And I receive a lot of questions. On one part I can understand for training and learning purpose.

On an other hand, I showed by the past through articles on this blog that was not so complicated, and much more interesting, to build a solution based on Linux with in a virtual machine.

Anyway, why not try it. I have to say once again, Peoplesoft (appserver, process scheduler and web server) is not supported on Windows XP.

1. Tuxedo 9.1 (downloaded from http://edelivery.oracle.com, Peopletools 8.49 folder, build B42600-01) Run the pstuxinstall.exe from the unzipped package :


On the following screen give the BEA HOME path directory you previously created. A tuxedo folder will be created inside. This version of Tuxedo include the rolling patch 036, you can check it into the file patchlev under udataobj :
2. BEA Weblogic 9.2 (downloaded from http://edelivery.oracle.com, Peopletools 8.49 folder, build B42602-01)
Run the server920-win32.exe from the unzipped package :

Choose the BEA HOME you previously created (the sane as the one for Tuxedo) :Don't need to choose a complete installation, here we don't need the developper part of BEA product, just the web server, check the custom install :Check only the server, the only one required componenet for our installation (that'll save disk space):Once more, we need only the minimal installation, uncheck the additional tool :
Choose the path directory where you want to install weblogic :


3. BEA Weblogic 9.2 maintenance pack 1 (downloaded from ftp://ftp.peoplesoft.com/outgoing/ptools/weblogic/9.20/mp1)
Run the server921_upgrade_win32.exe :

Choose your BEA HOME path directory :
It should recognize your previous installation :
4. BEA Weblogic 9.2 maintenance pack 1 patches 1 and 2 (downloaded from ftp://ftp.peoplesoft.com/outgoing/ptools/weblogic/9.20/mp1/patches)
Since my try, the patch 4 has been raised, it is bigger, but process is same, you could try it by yourself.
Copy the files installwlspatch.cmd, wls92mp1-p1.zip and wls92mp1-p2.zip into the BEA_HOME directory you defined earlier. Then run the installation command from a DOS window :
installwlspatch.cmd wls92mp1-p1.zip
That'll install at what required itself. Same for the second patch, and in case same for the latest one.


Check the existence of txt files with the same name as the patch.

5. PeopleTools 8.49 (downloaded from http://edelivery.oracle.com, Peopletools 8.49 folder, build B42585-01,B42586-01, B42587-01, B42588-01, B42589-01, B42590-01 and B42591-01) I won't become on that point, it's exactly the same process as the one for Linux, GUI excepted. Here below just few key screenshots :


6. PeopleTools 8.49 patch 11 - only if you are licenced - (downloaded from ftp://ftp.peoplesoft.com/outgoing/ptools/84911) From the extracted package, under disk1, run setup.exe, and follow the exact process as the previous one.

7. Create your Application Server.
Don't forget to add a line with an IP address and your computer name into your Windows/system32/driver/etc/hosts file.
I'll use an existing remote Oracle database :
C:\app\psoft\hrms9\appserv>psadmin

PSADMIN -- Tools Release: 8.49.11
Copyright (c) 1988, 2003, Oracle. All rights reserved.
[...]
----------------------------------------------
Quick-configure menu -- domain: DMOHRMS9
----------------------------------------------
Features Settings
========== ==========
1) Pub/Sub Servers : No 15) DBNAME :[DMOHRMS9]
2) Quick Server : No 16) DBTYPE :[ORACLE]
3) Query Servers : No 17) UserId :[PS]
4) Jolt : Yes 18) UserPswd :[PS]
5) Jolt Relay : No 19) DomainID :[DMOHRMS9]
6) WSL : Yes 20) AddToPATH :[c:\app\oracle\11.1.0\BIN]
7) PC Debugger : No 21) ConnectID :[people]
8) Event Notification: No 22) ConnectPswd:[peop1e]
9) MCF Servers : No 23) ServerName :[]
10) Perf Collator : No 24) WSL Port :[7000]
11) Analytic Servers : No 25) JSL Port :[9000]
12) Domains Gateway : No 26) JRAD Port :[9100]

Actions
=========
13) Load config as shown
14) Custom configuration
h) Help for this menu
q) Return to previous menu

Enter selection (1-26, h, or q): 13
Performing load prechecks ...
Loading validation table...
setting DBName=DMOHRMS9
setting DBType=ORACLE
setting UserId=PS
setting UserPswd=PS
setting ConnectId=people
setting ConnectPswd=peop1e
setting ServerName=
setting Port=7000
setting Port=9000
setting Listener Port=9100
setting Domain ID=DMOHRMS9
setting Add to PATH=c:\app\oracle\11.1.0\BIN
New CFG file written with modified Startup parameters

Log Directory entry not found in configuration file.
Setting Log Directory to the default... [PS_SERVDIR\LOGS]
Spawning disabled for server PSAPPSRV.
Configuration file successfully created.
CFG setting changes completed, loading configuration...
Domain configuration complete.
[...]
-------------------------------
PeopleSoft Domain Boot Menu
-------------------------------
Domain Name: DMOHRMS9

1) Boot (Serial Boot)
2) Parallel Boot
q) Quit

Command to execute (1-2, q) [q]: 1
Attempting to boot bulletin board...
tmadmin - Copyright (c) 1996-1999 BEA Systems, Inc.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by BEA Systems, Inc.
Tuxedo is a registered trademark.
No bulletin board exists. Entering boot mode.
INFO: BEA Tuxedo, Version 9.1, 32-bit, Patch Level 036
INFO: Serial #: 650522264137-2065448083901, Expiration NONE, Maxusers 1000000
INFO: Licensed to: Oracle-Peoplesoft-ISV

Booting admin processes ...

exec BBL -A :
process id=560 ... Started.
1 process started.
Attempting to boot ...
INFO: BEA Tuxedo, Version 9.1, 32-bit, Patch Level 036
INFO: Serial #: 650522264137-2065448083901, Expiration NONE, Maxusers 1000000
INFO: Licensed to: Oracle-Peoplesoft-ISV

Booting server processes ...

exec PSWATCHSRV -A -- -ID 64419 -C psappsrv.cfg -D DMOHRMS9 -S PSWATCHSRV :
process id=2652 ... Started.
exec PSAPPSRV -s@..\psappsrv.lst -s@..\psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PS
APPSRV :
process id=2664 ... Started.
exec PSAPPSRV -s@..\psappsrv.lst -s@..\psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PS
APPSRV :
process id=2136 ... Started.
exec PSAPPSRV -s@..\psappsrv.lst -s@..\psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PS
APPSRV :
process id=2716 ... Started.
exec PSSAMSRV -A -- -C psappsrv.cfg -D DMOHRMS9 -S PSSAMSRV :
process id=2684 ... Started.
exec PSMONITORSRV -A -- -ID 64419 -C psappsrv.cfg -D DMOHRMS9 -S PSMONITORSRV :
process id=2728 ... Started.
exec WSL -A -- -n //INDUS:7000 -z 0 -Z 0 -I 5 -T 60 -m 1 -M 3 -x 40 -c 5000 -p 7001 -P 7003 :
process id=2772 ... Started.
exec JSL -A -- -n //INDUS:9000 -m 5 -M 7 -I 5 -j ANY -x 40 -S 10 -c 1000000 -w JSH :
process id=1728 ... Started.
exec JREPSVR -A -- -W -P C:\app\psoft\hrms9\appserv\DMOHRMS9\jrepository :
process id=512 ... Started.
9 processes started.

8. Create your WebServer.
C:\app\psoft\hrms9\setup\PsMpPIAInstall>setup.exe -console -is:javahome C:\app\bea\jdk150_06
You have to give the java home here jdk150_06, coming from the Weblogic patches. A new DOS window will appear to the screen.
Welcome to the InstallShield Wizard for PeopleSoft Internet Architecture.

Using the InstallShield Wizard you will install PeopleSoft Internet
Architecture on your computer.

Version: 8.49.11

Note: If installing onto a BEA WebLogic Server, make sure to shutdown any
running web servers to avoid web server corruption.

Select Next to continue or Cancel to exit.

Cliquez sur 1 pour Suivant, 3 pour annuler ou 5 pour afficher Ó nouveau [1]


Choose the directory where you installed PeopleSoft, commonly known as
"PS_HOME":

Indiquez un nom de rÚpertoire ou cliquez sur EntrÚe.
[C:\app\psoft\hrms9]

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]

SÚlectionnez le type d'installation le mieux adaptÚ Ó vos besoins.

[X] 1 - Oracle Application Server

[ ] 2 - BEA WebLogic Server

[ ] 3 - IBM WebSphere Server

Pour sÚlectionner une option, indiquez le numÚro correspondant ou 0, lorsque
vous avez terminÚ : [0]2

Entrez 0 pour poursuivre ou 1 pour faire une autre sÚlection : [0]

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Select the web server root directory:

Indiquez un nom de rÚpertoire ou cliquez sur EntrÚe. [C:\Bea] C:\app\bea

Detected web server version: WebLogic 9.2.1

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Please enter the administrator login and password for WebLogic domain.

Login ID:

[system]
Password:[password]

:

Re-type Password:[password]

:

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Select domain type:


[X] 1 - Create New WebLogic Domain

Pour sÚlectionner une option, indiquez le numÚro correspondant ou 0,
lorsque vous avez terminÚ [0]:

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Enter domain name or click Next to select default:

[peoplesoft]

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]

Please select the configuration to install.

[X] 1 - Single Server Domain
[ ] 2 - Multi Server Domain
[ ] 3 - Distributed Managed Server

Pour sÚlectionner une option, indiquez le numÚro correspondant ou 0, lorsque
vous avez terminÚ : [0]


Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Please specify a name for the PeopleSoft web site:

Website name:

[ps] DMOHRMS9

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Enter port numbers and summaries.

AppServer name:

[indus]

JSL Port:

[9000]

HTTP Port:

[80]

HTTPS Port:

[443]

Authentication Token Domain:(optional)

[]

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Please enter the Name of the Web Profile used to configure the webserver. The
user id and password will be used to retrieve the web profile from the
database. (NOTE: Other available preset web profile names are "TEST", "PROD",
and "KIOSK".)

Web Profile Name:

[DEV]

User ID:

[PTWEBSERVER]

Password:[PTWEBSERVER]

:

Re-type Password:[PTWEBSERVER]

:
:
Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Select the Report Repository location:

Indiquez un nom de rÚpertoire ou cliquez sur EntrÚe. [c:\psreports] e:\psreports

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]


Setup Type :

Web server root directory :

Web server version :

Web server domain :

Internet Architecture app name :

Integration Gateway app name :

PeopleSoft Business Interlink app name :

PeopleSoft Online Library Infrastructure :

Environment Management Hub :

Hello Portlet app name :

Portlet Container app name :

Testsuite app name :

Wsrptest app name :

Site name :

Authentication Token Domain :

Application server name :

JSL port :

HTTP port :

HTTPS port :

Report repository directory :

PIA webserver directory :

weblogic

C:\app\bea

9.2

peoplesoft

PORTAL

PSIGW

PSINTERLINKS

PSOL

PSEMHUB

helloportletapp

pspc

testsuite

wsrptest

DMOHRMS9


indus

9000

80

443

e:\psreports

C:\app\psoft\hrms9\webserv

Cliquez sur 1 pour Suivant, 2 pour PrÚcÚdent, 3 pour annuler ou 5 pour afficher
Ó nouveau [1]

[...]
BUILD SUCCESSFUL
Total time: 0 seconds


Deploy Completed. Click next to finish the install.

The InstallShield Wizard has successfully installed PeopleSoft Internet
Architecture. Choose Finish to exit the wizard.

Cliquez sur 3 pour terminer ou 5 pour afficher Ó nouveau [3]

9. Start you WebServer and enjoy with your front end application on XP
C:\app\psoft\hrms9\webserv\peoplesoft\bin>startPIA.cmd
[...]
Attempting to start WebLogic Server 'PIA'

starting weblogic with Java version:
java version "1.5.0_04"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_04-b05)
Java HotSpot(TM) Client VM (build 1.5.0_04-b05, mixed mode)
[...]
11 juin 2008 22 h 03 CEST Notice WebLogicServer BEA-000360 Server started in RUNNING mode
[...]

Lastly, in your favorite browser :

So, no issue on the installation of Application and Web servers. But once more, it is not supported.

Have fun,

Cube

Today, a question on the Oracle forum for a alternate query (here)

Coming from a bad design, find the sum of two columns, grouping by two others columns.
The first thing coming in mind is a subquery with a UNION ALL :
select dt, ccy, sum(amt) amt
from (select dt, ccy1 as ccy, amt1 as amt from a
union all
select dt, ccy2 as ccy, amt2 as amt from a)
group by dt,ccy;

Then, trying to avoid it :
select dt, decode(t,1,ccy1,2,ccy2) ccy, sum(decode(t,1,amt1,2,amt2)) amt
from a , (select 1 t from dual union select 2 from dual)
group by dt, decode(t,1,ccy1,2,ccy2);

As we can see in the thread, the UNION ALL (which implies 2 table scan) is more performant than 1 table scan (implies a cartesian product and DECODE usage).

Well, all was said, until the nice demo of CUBE usage by DimaCit, the query is now the following :
with X as (
select dt, ccy1, sum(amt1) amt1, ccy2, sum(amt2) amt2
from a
group by dt, cube(ccy1, ccy2)
)
select x1.dt, x1.ccy1, x1.amt1 + x2.amt2
from X x1, X x2
where x1.dt = x2.dt
and x1.ccy1 is not null and x1.ccy2 is null
and x2.ccy2 is not null and x2.ccy1 is null
and x1.ccy1 = x2.ccy2;

I have to say I never thought about CUBE() on those cases.
So, very nice query, a little bit tricky, and a very good point for the best performance !
A nice demo of CUBE usage. Thank to DimaCit

Addendum : the solution doesn't work if one of the currency is not in the other column, then the CUBE solution is no more a solution. Thanks to Dominic Brooksfor comment it out this point.