Move Peoplesoft install to a new server

Since I move from OEL4.6 to OEL5.3, same word size (32-bits), same OS, no need to reinstall everything.
The fastest and simplest way, is to copy PS_HOME, TUX_HOME and WEBLOGIC_HOME to the new server.

Recreate the user with same .bash_profile and same rights as of the source.

If you copy to a different path directory, don't worry :
1. For PS_HOME, just define it as well in the .bash_profile of the PS_HOME owner
2. For Weblogic, when you recreate the webserver domain, give the new path
3. For Tuxedo, modify the script $PS_HOME/setup/psdb.sh, set the variable TUXDIR according to the new path :
...
TUXDIR=/apps/bea/tuxedo/9.1;export TUXDIR
...

Before running any application server, you should be sure about one environment variable value (due to OEL 5.x) . Open the file $PS_HOME/psconfig.sh, and check the variable PS_HOSTTYPE, it should be :
PS_HOSTTYPE=redhat-4-ia32;export PS_HOSTTYPE
Then disconnect and reconnect as the PS_HOME owner, that'll recall the psconfig.sh.
Because of some changes on OEL5.x, this file is not automatically updated by the installer, if you do not set correctly PS_HOSTTYPE, you could hit the following error when running psconfig.sh :
-bash - Error : Unsupported Host Type unknown...
ERROR: PS_HOSTTYPE is not set to a valid value.


Finally, you'll need :
1. Recreate the web server domain :
[weblogic@orion2 /apps/psoft/hrms9/setup/PsMpPIAInstall]$ export DISPLAY=0.0
[weblogic@orion2 /apps/psoft/hrms9/setup/PsMpPIAInstall]$ ./setup.linux -console
...
2. Reconfigure the application server domain with the psadmin menu

But it is very easy, only few minutes are required.
Assuming the tnsnames.ora file is updating to the new database location, everything should start on this new server.

Nicolas.

Oracle upgrade 10.2.0.4 to 11.1.0.7

Before running the upgrade, I'll do a manual upgrade, a script must be run on the source database :
$ORACLE_HOME/rdbms/admin/utlu111i.sql
Don't forget it. Apply the recommandations returned by the script.

Then, stop the database.
Since I move from a OEL4.6 server to a OEL5.3 server, I copied all the datafiles over the network.
Here an example of one datafiles :
ssh root@192.168.1.21 "gzip -c < /oradata2/DMOHRMS9/datafiles/ccapp.dbf -"|gunzip -c > /oradata/DMOHRMS9/datafiles/ccapp.dbf

Upgrade the database itself by running the upgrade script (database started in UPGRADE mode) :
$ORACLE_HOME/rdbms/admin/catupgrd.sql
The script bring your database down.
Finally, start your db in normal mode (OPEN) :
[oracle@orion2 /home/oracle]DMOHRMS9$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Feb 26 19:07:20 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 765833216 bytes
Fixed Size 1316120 bytes
Variable Size 188746472 bytes
Database Buffers 570425344 bytes
Redo Buffers 5345280 bytes
Database mounted.
Database opened.
SQL>

And run the additionals two scripts to finalize the upgrade :
$ORACLE_HOME/rdbms/admin/catuppst.sql
$ORACLE_HOME/rdbms/admin/utlrp.sql

We have now a 11gR1 database on the new server.

Next step is the PS_HOME, Weblogic and Tuxedo move to the new server.

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,