On The Peoplesoft Road

Peoplesoft VM : about the database image

2009-11-07T22:11:00.010+01:00

I created an virtual machine from the database server template downloaded from edelivery (OVM_EL5U2_X86_64_HCM91_PVM), here are my first screenshots and comments.

1. In Oracle VM Manager, list of the templates downloaded from edelivery :

2. In Oracle VM Manager, list of the virtual machines, created from the Peoplesoft templates :
3. First start of the database VM (OVM_EL5U2_X86_64_HCM91_PVM) :4. Once the VM is in running status, click on "console" button :

5. Enter the VM password :
6. Nice, we arrive on the database server, bare is progressing :
7. Don't configure the DHCP :
8. Give the IP address, netmask, DNS and server name :
9. I want the database start on server boot, and set my own database password :

10. My database name will be H91TMPLT :
11. Finally, first login onto the database server (default root password is ovsroot):

It quite a fast process, if we except the large amount Gb to download and to be created as virtual machine.

So far, only few remarks/bugs :
1. On the first boot of the machine I configured the database to be started on boot (step 9 above), , unfortunately it looks like the database and listener do not start automatically on the virtual machine start.
2. The /etc/oratab does not contain the database name I gave during the first VM setting, but only the following (it could explain the point 1) :

orcl:/u01/app/oracle/product/11.1.0/db_1:N

3. The database password given on the first VM boot are not taken in account, e.g. SYSTEM's password is manager, not the one I gave during the initialization (step 9 described above)
4. An other point, the image is about 60Gb, but less than 30Gb are used onto the VM, that's a pitty, so much wasting space onto the host for nothing :

[root@psovmdb ~]# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/xvda2             3177204   1039168   2138036  33% /
/dev/xvda1               31077      8277     21196  29% /boot
tmpfs                  2097152         0   2097152   0% /dev/shm
/dev/xvdb1             9851308   4478364   4872524  48% /u01
/dev/xvdc1            25806268  19211972   5283396  79% /opt/oracle/psft/ptdb
[root@psovmdb ~]#

Speaking about size, note the database is only 16.3Gb datafiles :

SQL> select sum(bytes)/1024/1024 from dba_data_files
 2  union all
 3  select sum(bytes)/1024/1024 from dba_temp_files;

SUM(BYTES)/1024/1024
--------------------
              16007
                320
SQL>

5. Be aware, the SYSADM's password is in capital letter :

SQL> conn sysadm/sysadm
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn sysadm/SYSADM
Connected.
SQL>

6. Only English language is installed :

SQL> select language_cd from pslanguages where installed=1;

LANGUAGE_CD
------------
ENG

Well, enjoy it !

Nicolas.

OracleVM Template for Peoplesoft

2009-10-28T09:12:00.004+01:00

October'09, the 26th, Oracle provided new OracleVM templates. These ones are special for Peoplesoft community, there are for Peoplesoft HRMS9.1 running on Peopletools8.50.02.
Please, read the announcement here.
We can download from edelivery website the three following templates (all based on OEL5.2 64-bit) :
1. One template for database (Oracle 11.1.0.6).
2. One template for application and process scheduler server.
3. One template for webserver.

Please read carefully this, there is also a wiki page dedicated to these templates.
Once more, a very nice job !

Right now, OracleVM need to be installed on a spare server to be tested...

Nicolas.

Peopletools 8.50 got certification on Oracle 11.2

2009-10-26T14:48:00.004+01:00

As of last week, Peopletools 8.50 are becoming supported on Oracle 11.2 (64-bit only).
So far, it is only for RHEL/OEL 5.x, a very good job done by the Peoplesoft team ! I think it was rather quick to get it. A side note, it is not yet supported within 11.2 64-bit RAC.
SLES-11 is also on the way, awaiting the availability of Oracle 11.2 on other plateforms...

Enjoy,

Nicolas.

CentOS 5.4

2009-10-22T21:06:00.003+02:00

...is available : http://mirror.centos.org/centos/5/isos/
After a "yum update" from a 5.3 CentOS, and a little bit more than 200Mb of downloaded packages, CentOS 5.4 is installed :

[root@hercules /root]# rpm -q centos-release
centos-release-5-4.el5.centos.1
[root@hercules /root]#

Enjoy,

Nicolas.

Peopletools patch 8.50.02 zipped

2009-10-10T09:54:00.003+02:00

Around three weeks after the GA of Peopetools 8.50, we got now the patch 8.50.02.
I won't follow every single patch, usually one every three or four weeks at the beginning of a release life, but we have to note the format of the package. This is a zip, not a exe file anymore, which we are able to unzip on Unix/Linux and still password protected (you need an access to My Oracle Support), here OEL5.3 :

[root@orion2 PeopleTools8.50.02]# unzip 85002.zip
Archive:  85002.zip
   creating: cd85002/Disk1/
   creating: cd85002/Disk1/InstData/
[85002.zip] cd85002/Disk1/InstData/emocmutl.jar password:

Few weeks ago, I suggested this change on Oracle Mix here.
Thanks to the Peoplesoft team, that will greatly simplify the copy onto the Unix/Linux server...

Nicolas.

Peoplesoft and non-certified OS

2009-10-02T11:26:00.006+02:00

I recently give answers on OTN forums about Peoplesoft installation on non-certified plateforms. Like usual, non-certified does not mean that won't work, that just means its on your own risk and responsibility.

The first one was Peopletools 8.49 on CentOS 5.3. Since CentOS is a recut of RHEL, and Peoplesoft is certified on RHEL, that should work.
There is some tricky stuff to get it work, but it works. Find out my post here.

The second one was Peopletools 8.50 on Windows XP (32-bits).
Peoplesoft has never been certified as a server (application server, process scheduler and webserver) on Windows XP, however, for learning purpose I can understand the need to install it there. Furthermore, as long as Peoplesoft won't provide the Application Designer tool for an other plateform, Windows is still required to do some tasks (from client side perspective).
From Peopletools 8.50, only 64-bits OS are supported. So, the main question was if that works on a 32-bits XP machine.
Finally, I got it work on Windows XP Pro 32-bits, everything was installed and running fine without any tricky stuff. Find my post here.

Interestingly, the second question makes a remark. All the PT8.50 components provided on edelivery for Windows are 32-bits (you can even read on the certification matrix the need of the 32-bits database libraries...). Knowning Peoplesoft is certified only on 64-bits OS server (W2k3/W2k8), that makes a full 32-bits application certified only on a 64-bits Windows OS. Why such restriction ?

Again, it is not certified. So, keep it for learning purpose only.

Enjoy,

Nicolas.

HRMS9.1 available

2009-09-26T10:56:00.002+02:00

September 2009... the list of new product versions delivered by Oracle this month becomes very inpressive.
In the already long list, a new comer, on the Peoplesoft side, one week after the Peopletools 8.50, Peoplesoft HRMS9.1 is now available !

Read the announcement :
http://blogs.oracle.com/peopletools/2009/09/peoplesoft_hrms_91_is_now_gene.html

Enjoy !

Nicolas.

Peopletools 8.50 : a first look

2009-09-20T19:56:00.008+02:00

Last Friday, September 2009, the 18th, Peopletools 8.50 has been released. The announcement is here.

As usual, it is available on http://edelivery.oracle.com

The Peopletools are now in 3 CDs (instead of 7 for PT8.49 !) :
V17557-01 => 1.2Gb
V17558-01 => 1.9Gb
V17559-01 => 308Mb
I'm still wondering why not doing on DVD format, or why doing a third CD so small ?

Tuxedo is coming with version 10gR3, reference and size are depending of your plateform, but here for Linux (only 64bits is available) :
V15055-01 => 71Mb

Finally, Weblogic , coming in version 11gR1 (it is rather confusing, actually the version is 10.3.1), reference and size are depending of your plateform, but here for Linux :
V16997-01 => 587Mb

The documentation is also available on usual website, http://www.oracle.com/technology/documentation/psftent.html and especially the installation guide, depending of the database type you are installing Peopletools onto :
http://download.oracle.com/docs/cd/E15742_01/psft/html/docset.html
Note, the licence code are provided in a pdf document format.
I'm still wondering why the need of this licence code, why not just ask for the database type ?
I suggested it on http://mix.oracle.com, feel free to add your voice :
https://mix.oracle.com/ideas/104482-peopletools-install-remove-the-licence-code

Time to have a first look into it.
I'll write tutorial in a few weeks time, maybe once Peoplesoft 9.1 will come out (there will come based on Peopletools 8.50).
Ok, now the installation. I'm doing this installation on my lab, OEL5.3 64-bits and Oracle database 11gR2 64-bits (I know it is not yet certified, but on the way).

1. Tuxedo :
By running the installer, we are feeling the Oracle touch, you are prompted to create an Oracle home directory to install Tuxedo :

===============================================================================
Choose Oracle Home
------------------


    1- Create new Oracle Home

Enter a number: 1
Specify a new Oracle Home directory: /apps/bea/tuxedo/10gR3

Well, nothing much to say, standard install, and finally checking :

[tuxedo@orion2:/nfs/software/PeopleSoftCD/BEA/Tuxedo/Oracle_Tuxedo_10gR3_Linux_64]$ cd /apps/bea/tuxedo/10gR3
[tuxedo@orion2:/apps/bea/tuxedo/10gR3]$ export TUXDIR=/apps/bea/tuxedo/10gR3
[tuxedo@orion2:/apps/bea/tuxedo/10gR3]$ . $TUXDIR/tux.env
[tuxedo@orion2:/apps/bea/tuxedo/10gR3]$ $TUXDIR/bin/tmadmin -v
INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level (none)
[tuxedo@orion2:/apps/bea/tuxedo/10gR3]$

2. Weblogic :
Again, the installer has been greatly modified to make it "more Oracle" like.

<-- Oracle Installer - WebLogic 10.3.1.0 ------->

Choose Middleware Home Directory:
---------------------------------

    "Middleware Home" = [Enter new value or use default "/home/weblogic/Oracle/Middleware"]

Enter new Middleware Home OR [Exit][Previous][Next]> 

Here you're also promted to keep update with the lastest version of Oracle :
<----- Oracle Installer - WebLogic 10.3.1.0 ----------->

Register for Security Updates:
------------------------------

Provide your email address to be informed of security issues, 
install the product, and initiate configuration manager. 
http://www.oracle.com/support/policies.html

   1|Email:[]
   2|My Oracle Support Password:[]
   3|Receive Security Update:[Yes]

Enter index number to select OR [Exit][Previous][Next]> 3

<----- Oracle Installer - WebLogic 10.3.1.0 ----------->

Register for Security Updates:
------------------------------

Provide your email address to be informed of security issues, 
install the product, and initiate configuration manager. 
http://www.oracle.com/support/policies.html

    "Receive Security Update:" = [Enter new value or use default "Yes"]

Enter [Yes][No]? No

<----- Oracle Installer - WebLogic 10.3.1.0 ----------->

Register for Security Updates:
------------------------------

Provide your email address to be informed of security issues, 
install the product, and initiate configuration manager. 
http://www.oracle.com/support/policies.html

    "Receive Security Update:" = [Enter new value or use default "Yes"]

    ** Do you wish to bypass initiation of the configuration manager and
    **  remain uninformed of critical security issues in your configuration?

Enter [Yes][No]? Yes

I find it rather confusing, "No" I don't want to receive security update and "Yes", I want to keep "uninformed"...

The documentation about Weblogic installation strongly recommand to get the latest patch of the JDK/JRockit.
Why not having it by default ?

3. Peopletools on Linux (64bits) - console :
Only three CDs have to be unzipped (disk1, disk2 and disk3 folders in one directory).
Here changes are doing life simpler, firstly the command line is simply "setup.sh" and the licence code is only one field to fill up.
But again, you're prompt to keep (un)informed of security update... maybe it is me, but here we're talking about a server installation, not a workstation...
And, a good one, we are not prompt for the next CD anymore like it was in the previous release.

4. Peopletools on Windows (client) - GUI :
The main difference with a console mode, the licence code are still divided in 6 fields...
This is a Java, and you could have to allow the program to be executed onto Windows :

5. Database :
I did not create database from scratch, need more time and disk space, but I reused a FSCM9 database (working on Peopletools 8.49).
So, I just managed an upgrade to the latest Peopletools release, the steps very similar of previous release, with some slight differences, including an activation of TIMESTAMP (script upgdboptions_enabletimestamps.sql), and some additional Application Engine (UPGPTSERVOPR, UPGPT850PTFP).
Again, patience and time are required, the project are very big and the project load and build take quite a while.

6. Application server :
Here two warning with big differences compared with Peopletools 8.4x, some environment variables are not set by default into psconfig.sh.
TUXDIR must be set to the Tuxedo home directory, otherwise you cannot configure and start a domain.
PS_CFG_HOME must also be set to the directory you want your domain configuration files, otherwise those files are going into /home//psft/appserv...
You can set those variables in the .profile (or .bash_profile, depending of your plateform), i.e. :

[ptools@orion2:/home/ptools]$ more .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME
export ORACLE_HOME=/apps/oracle/product/11.2.0

export TUXDIR=/apps/bea/tuxedo/10gR3
export LD_LIBRARY_PATH=$TUXDIR/lib:$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$TUXDIR/bin:$PATH:$ORACLE_HOME/bin

export PS_HOME=/apps/psoft/ptools850
export PS_CFG_HOME=$PS_HOME
. $PS_HOME/psconfig.sh

[ptools@orion2:/home/ptools]$

I'm not sure to understand why it has be remove from standard configuration settings, but I'm sure that'll cause some headache if they are not set to find the config files.

7. PIA :
Again, the installer command line has been greatly simplify, just run setup.sh. Oracle Applicatoin Server has been removed from the choice, we've now only Weblogic and WebSphere, quite understandable.
A slight difference is about the password of the system user, you should take care about if you're upgrading an existing database (and used by report node) :

Please enter the administrator login and password for WebLogic domain.

   Login ID [system]:

   Password [Passw0rd]:

   Re-type Password [Passw0rd]:

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :

8. Connection :
Few very first impression, the "Processing" on the top right has been replace by a kind of wheel, and some dialog box are coming up in front, looks nice and more "modern" style.

9. (update 21-SEPT-2009) Following the advice from Bauke Gehem (see comment below), I changed the settings to get a very nice look :
Thanks so much Bauke !

Enoy this version.

Nicolas.

Peopletools 8.50

2009-09-18T10:55:00.003+02:00

Again, we can say September' 2009 is rich in events for Oracle.
After Oracle database 11gR2, OEL5.4, one more proof, Peopletools 8.50, waited for a while is released !

Go through http://edelivery.oracle.com/ to do download
Go through http://download.oracle.com/docs/cd/E15742_01/psft/html/docset.html for the installation doc !

Enjoy !

Nicolas.

Update 19-SEPT-2009 :
The patch 1 is now available on the usual ftp website with the password protection getting from My Oracle Support.
PT8.50 has also been announced on the Peoplesoft Technology blog.

Nicolas.

OEL5.4 available

2009-09-16T19:00:00.002+02:00

September' 2009 is beeing a very rich month regarding new Oracle software version.

After the Oracle database 11gR2, this is now for Oracle Entreprise Linux 5.4 to be available only few weeks after the linux based version RHEL5.4.
We'll see later if there is added value by Oracle in this release compared to RHEL.

Now, I'm awaiting the CentOS 5.4 which is the OS of my lab.
But since CentOS is a free OS developped on free time, it is always coming much later, one or two months...

Enjoy,

Nicolas.

Get rid off segment of empty table (11gR2)

2009-09-08T22:31:00.004+02:00

I'm trying to get rid off segment of empty table, according to the documentation it should be possible to alter the table with the clause "SEGMENT CREATION DEFERRED", but it does not look like so obvious :

SQL> create table mytable1(id number);

Table created.

SQL> create table mytable2(id number);

Table created.

SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';

no rows selected

SQL> insert into mytable1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';

SEGMENT_NAME   BYTES
----------     ------
MYTABLE1       65536

SQL> truncate table mytable1;

Table truncated.

SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';

SEGMENT_NAME   BYTES
----------     ------
MYTABLE1       65536

SQL> alter table mytable1 move;

Table altered.

SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';

SEGMENT_NAME   BYTES
----------     ------
MYTABLE1       65536

SQL> alter table mytable1 move segment creation deferred;
alter table mytable1 move segment creation deferred
                          *
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations


SQL> alter table mytable1 segment creation deferred;
alter table mytable1 segment creation deferred
                     *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Ok, then let's try to export and import tables to see if it is easy :

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ expdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2

Export: Release 11.2.0.1.0 - Production on Tue Sep 8 21:57:36 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSADM"."SYS_EXPORT_TABLE_01":  sysadm/******** dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSADM"."MYTABLE1"                             0 KB       0 rows
. . exported "SYSADM"."MYTABLE2"                             0 KB       0 rows
Master table "SYSADM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
  /oradata/DMOFSCM9/dpdump/mytable.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 21:57:46

Then standard import (without particular options) :

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql

Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:04 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01":  sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:06

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
   (    "ID" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSDEFAULT" ;

CREATE TABLE "SYSADM"."MYTABLE2"
   (    "ID" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "PSDEFAULT" ;

The SEGMENT CREATION IMMEDIATE is there for my table mytable1 (eventhough it is empty).
Let's try to remove storage of table during import :

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql

Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:17 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01":  sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:19

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
   (    "ID" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "PSDEFAULT" ;

CREATE TABLE "SYSADM"."MYTABLE2"
   (    "ID" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "PSDEFAULT" ;

No more luck.

Finally, let's remove the segment attribute :

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql

Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:34 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01":  sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:36

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
   (    "ID" NUMBER
   ) ;

CREATE TABLE "SYSADM"."MYTABLE2"
   (    "ID" NUMBER
   ) ;

[oracle@orion2:/oradata/DMOFSCM9/dpdump]$

Unfortunately, no more tablespace... that means every single imported table will be (re)created into the user's default tablespace, which is not good enough.

Looks like as soon as we have a segment for a table, it is rather difficult to "remove" it.

To be continued,

Nicolas.

Peoplesoft on 11gR2 64-bits

2009-09-07T20:35:00.003+02:00

I should start to say, Peoplesoft (any version) is not supported yet on 11gR2 (64/32 bits).
But I would like to test it how it is working.

1. Install the 11gR2 64-bits, as I explained here

2. Upgrade your database, here below a FSCM9 database (or create from scratch - I did not tested it yet)
The upgrade is quite standard, start the db with old version and run the 11.2 check utility :

@/apps/oracle/product/11.2.0/rdbms/admin/utlu112i.sql

Then start your db in upgrade mode, and run the upgrade script (with 11.2 binaries) :

@$ORACLE_HOME/rdbms/admin/catupgrd.sql

After restarting the db in normal mode, check it afterwards :

@$ORACLE_HOME/rdbms/admin/utlu112s.sql

There is an additional script to run :

@$ORACLE_HOME/rdbms/admin/catuppst.sql

And finally, recompile the objects :

@$ORACLE_HOME/rdbms/admin/utlrp.sql

3. Because 11gR2 64-bits is not coming with 32-bits libraries, as I wrote here, you'll need to install a 32-client into a new Oracle home for the application and batch server clients.
That is fine, nothing much to say about the client install, check the runtime option :


4. Create the required symbolic link $ORACLE_HOME/lib/libclntsh.so.9.0 pointing to $ORACLE_HOME/lib/libclntsh.so.11.1 (assuming ORACLE_HOME is
your Oracle client home path directory). Do not forget to configure the tnsnames.ora as well.

5. Change the ORACLE_HOME env variable to the Oracle client home pathdirectory of the Peoplesoft Unix account, and reconfigure the
application and batch domains.

Finally every should run fine.

One very interesting  feature of 11gR2 for Peoplesoft is the possibility to deferred the segment creation, it will be very nice to
avoid segment creation for thousands of unused and empty tables., I should also have a try to rebuild the tables without segments...

Enjoy,

Nicolas.

11gR2 64-bits : where is lib32 ?

2009-09-06T10:52:00.003+02:00

From the Peopletools 8.49.14, Peoplesoft is fully certified on a 64-bits OS and 11gR1 64-bits (application server and batch server), I already post an article few months ago.
Peoplesoft was still a 32-bits application, but in 11gR1 64-bits, Oracle provided a lib32 directory containing all the required 32-bits libraries, and it worked fine by doing a symbolic link from $ORACLE_HOME/lib/libclntsh.so.9.0 to $ORACLE_HOME/lib32/libclntsh.so.11.1.

Here we go, 11gR2 does not provide anymore the 32-bits libraries, no more $ORACLE_HOME/lib32 directory.
Consequently, Peopletools 8.49 (application server and batch server) cannot be used within 11gR2 64-bits.

What’s up if you are already on a 64-bits OS ? The thing is, you cannot install 11gR2 32-bits ~~client~~ server on 64-bits Linux (blocked by the Installer).
So, what ?
Well, Peoplesoft does not say anything yet about 11gR2, but most likely, Peoplesoft 8.49.x won't be certified soon on 11gR2 64-bits... you must stay on 11gR1 64-bits, or move your application/batch server to 11gR2 32-bits client (sigh)…

I don't hink that'll easy for Peoplesoft to get this certification (11gR2 64-bits client) for existing Peopletools (8.49). Hopefully, that won’t be an issue for the coming Peopletools 8.50 (note, it won't be certified anymore on Linux 32-bits).
This is an issue not only for Peoplesoft application, but for all the 32-bits application running out an Oracle 64-bits client so far.

Update 1 (07-Sept-09): well it seems to be possible by installing the 11gR2 32-bit client onto a new Oracle home on Linux 64-bits, thanks to Fredrik Adolfsson for pointing me out the right documentation :
http://download.oracle.com/docs/cd/E11882_01/install.112/e10841/install_overview.htm#BABFICCG

Oracle 11.2.0.1 install on OEL5.3 64 bits

2009-09-05T22:05:00.008+02:00

It is a quite interesting release, a lot of changes in the installation screenshots, especially for those who followed the Oracle install for a long time.
Here, we go.

Firstly, be sure the following RPMs are installed on your OS :

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2 (32 bit)
libstdc++-devel 4.1.2
make-3.81
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-2.2.11 (32 bit)
unixODBC-devel-2.2.11
unixODBC-devel-2.2.11 (32 bit)

Note the 32 bits packages requirements, it was a surprise for me.
Some of them was not installed on my server, so, need to be installed :

[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd2]# ls unix*
unixODBC-2.2.11-7.1.i386.rpm
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd2]# rpm -Uvh unixODBC-2.2.11-7.1.i386.rpm
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:unixODBC               ########################################### [100%]
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd2]# cd ../cd3
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# ls unix*
unixODBC-devel-2.2.11-7.1.i386.rpm  unixODBC-kde-2.2.11-7.1.i386.rpm
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# rpm -Uvh unixODBC-devel-2.2.11-7.1.i386.rpm
warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:unixODBC-devel         ########################################### [100%]
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# ls libaio*
libaio-devel-0.3.106-3.2.i386.rpm
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# rpm -Uvh libaio-devel-0.3.106-3.2.i386.rpm
warning: libaio-devel-0.3.106-3.2.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libaio-devel           ########################################### [100%]
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]#

Moreover, modifications are required into /etc/sysctl.conf :

#fs.file-max = 6553600
#change for 11gR2
fs.file-max = 6815744
...
#net.core.wmem_max = 262144
#change for 11gR2
net.core.wmem_max = 1048586
...
#net.ipv4.ip_local_port_range = 1024 65000
#change for 11gR2
net.ipv4.ip_local_port_range = 9000 65500

The run

/sbin/sysctl -p

or reboot your system.

Finally, within your prefered XWindow client :

Enjoy the new version,

Nicolas.

11gR2

2009-09-01T22:38:00.003+02:00

Well, it is not a scoop anymore, but the release 2 of Oracle 11g has been out today (only for Linux 32/64 bits).
The size is amazing (or is it already the inflation?) : the 10gR2 was about 800Mb, right now, 11gR2 is splitted on two files of 1Gb each...

Anyway, it needs to be tested. Especially the LISTAGG function as Laurent mentioned here.

Download : http://www.oracle.com/technology/software/products/database/index.html
Doc : http://www.oracle.com/pls/db112/homepage

Nicolas.

Peoplesoft plugin for OEM - part 3

2009-09-01T22:18:00.006+02:00

Following the OEM 10.2.0.5 upgrade, I'm going to install the Peoplesoft plugin for OMS.
This is downloadable from hhtp://edelivery.oracle.com

Here we go.

1. Stop the OMS ($ORACLE_HOME/opmn/opmnctl stopall)
2. Go into $ORACLE_HOME/oui/bin (from OMS path directory) and run the installer.
3. Change the source location to Disk1/stage from the download file, as below :

4. Select Peoplesoft product for OMS
5. It will prompt for the SYS's password, and click on Install.
Unfortunately, in my case, I got an error :

[oracle@opcenter bin]$ more /appl/oracle/oms10g/cfgtoollogs/psemca-2009-8-30_17-321.out
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Environment :
ORACLE HOME          = /appl/oracle/oms10g
REPOSITORY HOME      = /appl/oracle/oms10g
SQLPLUS              = /appl/oracle/oms10g/bin/sqlplus
SQL SCRIPT ROOT      = /appl/oracle/oms10g/sysman/admin/emdrep/sql
EXPORT               = /appl/oracle/oms10g/bin/exp
IMPORT               = /appl/oracle/oms10g/bin/imp
LOADJAVA             = /appl/oracle/oms10g/bin/loadjava
JAR FILE ROOT        = /appl/oracle/oms10g/sysman/admin/emdrep/lib
JOB TYPES ROOT       = /appl/oracle/oms10g/sysman/admin/emdrep/bin
Arguments :
...

Failed to register/update the following template(s):
     Get New Alerts          XML Outbound
     Get Updated Alerts              XML Outbound
     Get Response            Inbound
Exception has been caught. Please check log file for more information.
Restore the tweaked target type driver scripts ..
Total time taken for parsing and reconstructing target types SQL files: 25 ms.
Optimization done.
Done.
Running setSchemaStatus: BEGIN EMD_MAINTENANCE.SET_VERSION('_UPGRADE_','0','0','SYSTEM',EMD_MAINTENANCE.G_STATUS_UPGRADED);END;

Repository Upgrade has errors. Please check file /appl/oracle/oms10g/sysman/log/emrepmgr.log.18350 for detailed errors.



....
SQL> SET ECHO OFF
 MGMT_JOB_ENGINE.validate_job_type(l_job_type_name, l_commands, l_nested_jobtypes );
                 *
ERROR at line 42:
ORA-06550: line 42, column 21:
PLS-00302: component 'VALIDATE_JOB_TYPE' must be declared
ORA-06550: line 42, column 5:
PL/SQL: Statement ignored
ORA-06550: line 66, column 17:
PLS-00302: component 'INSERT_CLUSTER_TARGET_TYPES' must be declared
ORA-06550: line 66, column 1:
PL/SQL: Statement ignored
ORA-06550: line 130, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 130, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 205, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 205, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 262, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 262, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 337, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 337, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 394, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 394, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 45

....

My OMS is 10.2.0.5, and it is clearly specify the OMS should be 10.2.0.2 minimum, so ?
So, when installing OMS with all the defaults, it is installing a 10.1.0.4 repository database, it needs to be upgraded before going further.

6. Finally, after upgrading my repository database to 10.2.0.4 (today, the latest 10gR2 release), it ran smoothly.

7. It is restarting OMS automatically, and once you're connected onto OEM, you got one more tab named Peoplesoft :
(sorry for the French screenshot, my explorer is installed in French, but I think it is understandable, at least for what I wan to show).

Next step is the Peoplesoft plugin for Agent.

Enjoy,

Nicolas.

Peoplesoft plugin for OEM - part 2

2009-08-30T15:33:00.003+02:00

The upgrade of OEM 10.2.0.3 to 10.2.0.5 on Linux x64 has not been difficult, no issue, nothing special to set.

To be upgraded, the OMS needs to be down more than 4 minutes before running the Installer, otherwise, you get the following error :

Enjoy,

Nicolas.

Metalink3 becomes My Oracle Support

2009-08-30T10:38:00.002+02:00

Last year, the GSC - Global Support Customer - moved to Metalink3.

That was only a temporary situation to move all Peoplesoft's customers (and some others products) into an Oracle support website. This week-end, it is going more and more Oracle, the Metalnk3 will move to the My Oracle Support.

From the email we all received :
Oracle Global Customer Support is pleased to announce the launch of Oracle’s new online support portal, My Oracle Support. This coming weekend, August 28-30, 2009, Oracle will upgrade Oracle MetaLink 3 and officially change the name to "My Oracle Support." This transition will migrate existing OracleMetaLink 3 customers and partners (those using PeopleSoft, JD Edwards, Siebel, and Hyperion products) to My Oracle Support at https://support.oracle.com.

I'm still not convinced by this change, expecially the requirement to use MOS, FlashPlayer need to be installed... and some other inconvenients of the MOS usage itself (colors - too many -, pagelets - not needed -, search features - not working -...). All we want is a working and simple support website, not a "geek" or "movie" support website with all the latest Internet technologies, just a simple one to use, I'm not saying Metalink(1|2|3) was perfect, but at least it was simple. After all, we are looking for support (the two mains activities from there are Metalink note reading and SR creation), not for bying or to be convinced to use Internet technologies.

There is a thread into OTN forums, Metalink Classic - To Be Continued but it looks there is no way to avoid the change.

I'm sure, it will be discussed again and again over the Oracle community.

Enjoy,

Peoplesoft plugin for OEM - part 1

2009-08-25T18:12:00.005+02:00

In order to install OEM 10.2.0.5, we need to install OEM 10.2.0.3 as explain in the readme

So, let's install first OEM 10.2.0.3.

My OS is OEL 5.3 64-bits.

[root@opcenter cd1]# uname -a
Linux opcenter.phoenix-nga 2.6.18-128.el5 #1 SMP Wed Jan 21 08:45:05 EST 2009 x86_64 x86_64 x86_64 GNU/Linux
[root@opcenter cd1]#

However, you could hit the following error during the Enterprise Management installation, when starting the Apache server :

oracle@opcenter sysman]$ more /appl/oracle/oms10g/opmn/logs/HTTP_Server~1

09/08/23 18:37:44 Start process
--------
/appl/oracle/oms10g/Apache/Apache/bin/apachectl start: execing httpd
/appl/oracle/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

That's because the installed application is a 32-bits application, it needs the 32-bits libraries which does not exists natively on a 64-bits plateform.
It requires to install manually one more package, gdbm-1.8.0-26.2.1.i386.rpm (which add the library /usr/lib/libgdbm.so.2), and create a symbolic link to this new library :

[root@opcenter cd1]# rpm -Uvh gdbm-1.8.0-26.2.1.i386.rpm
warning: gdbm-1.8.0-26.2.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:gdbm                   ########################################### [100%]
[root@opcenter cd1]# ln -s /usr/lib/libgdbm.so.2 /usr/lib/libdb.so.2

Apart from that, it is taking time, but no issue to install OEM 10.2.0.3 on OEL5.3 64-bits.
The next step is the upgrade to OEM 10.2.0.5, then the real target is the installation of the Peoplesoft plugin for OEM...

Enjoy,

Nicolas.

Oracle PSU 10.2.0.4.1

2009-07-14T21:25:00.002+02:00

Within the quartely CPU (Critical Patch Update) of July 2009 available from today, Oracle introduce a new concept, the PSU (or Patch Set Update).
It is a "bundle" of patches including the latest CPU, and the current one includes also the following patches :
*Generic Recommended Bundle #4 (Patch 8362683)
*RAC Recommended Bundle #3 (Patch 8344348)
*Data Guard Broker Recommended Bundle #1 (Patch 7936793)
*Data Guard Physical/Recovery Recommended Bundle #1 (Patch 7936993)
*Data Guard Logical Recommended Bundle #1 (Patch 7937113)
PSU is a cumulative bundle of patches, the concept exists for a while for Windows plateform, but right now, we got it for most of the Unix/Linux plateform.
It is too early to say if that's good or not, I think it is good idea, that could avoid to apply dozen of individual patches one by one. On an other hand, we still should be able to choose between cumulative and individual patches, that'd a pity to have to download and install several Mb file to get a small fix of few Kb.

Find out more in the Metalink notre Intro to Patch Set Updates (PSU) Doc Id #854428.1

Enjoy Oracle database,

Surveys

2009-05-24T18:02:00.003+02:00

Few weeks ago, I opened two surveys :
1. What OS your Peoplesoft appl is running on ?
2. What DB your Peoplesoft appl is running on ?

They are just by curiosity, to know a little bit more about the differents configuration of Peoplesoft customer around the world. Only 7 days remain...

Thanks in advance for your collaboration.

DEMO CRM 9.0 : ORA-00600

2009-05-24T17:13:00.002+02:00

Few weeks ago, I wrote about an ORA-600 on HRMS9.0 here
Today, it is a little bit different, it is also an ORA-600, but on a CRM database and not in a transactional mode.
The query is coming from development team, when doing customized code, create the following hierarchical query :

SELECT p.emplid
FROM   ps_rb_worker w , ps_rd_person p 
WHERE  p.person_id = w.person_id
START WITH p.emplid = '000001' 
CONNECT BY PRIOR w.person_id = w.supervisor_id ;

The database is 10.2.0.4, OS does not matter :

SQL> SELECT p.emplid
FROM   ps_rb_worker w , ps_rd_person p
WHERE  p.person_id = w.person_id
START WITH p.emplid = '000001'
CONNECT BY PRIOR w.person_id = w.supervisor_id ;

CONNECT BY PRIOR w.person_id = w.supervisor_id
                                              *
ERROR at line 5:
ORA-00600: internal error code, arguments: [qkacon:FJswrwo], [10], [], [], [],[], [], []

Well, after a quick look on Metalink, found a workaround with a HINT :

SQL> SELECT /*+ NO_CONNECT_BY_COST_BASED */ p.emplid
FROM   ps_rb_worker w , ps_rd_person p
WHERE  p.person_id = w.person_id
START WITH p.emplid = '000001'
CONNECT BY PRIOR w.person_id = w.supervisor_id ;


EMPLID
-----------
000001

That's fine, a result is returned, but what about the explain plan ?

SQL> explain plan for
SELECT /*+ NO_CONNECT_BY_COST_BASED */ p.emplid
FROM   ps_rb_worker w , ps_rd_person p
WHERE  p.person_id = w.person_id
START WITH p.emplid = '000001'
CONNECT BY PRIOR w.person_id = w.supervisor_id ;

Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 136118842

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |    66 |  4092 |    37   (6)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|                 |       |       |            |          |
|*  2 |   FILTER                  |                 |       |       |            |          |
|   3 |    COUNT                  |                 |       |       |            |          |
|*  4 |     HASH JOIN             |                 |    66 |  4092 |    37   (6)| 00:00:01 |
|   5 |      NESTED LOOPS         |                 |    66 |  3234 |    31   (4)| 00:00:01 |
|*  6 |       HASH JOIN           |                 |    66 |  2772 |    31   (4)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL  | PS_RD_WRKR_ASGN |   329 |  5922 |    15   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL  | PS_RD_WRKR_JOB  |  1193 | 28632 |    15   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN   | PS_RD_PERSON    |     1 |     7 |     0   (0)| 00:00:01 |
|  10 |      INDEX FAST FULL SCAN | PSBRD_PERSON    |  1807 | 23491 |     5   (0)| 00:00:01 |
|* 11 |   HASH JOIN               |                 |       |       |            |          |
|  12 |    CONNECT BY PUMP        |                 |       |       |            |          |
|  13 |    COUNT                  |                 |       |       |            |          |
|* 14 |     HASH JOIN             |                 |    66 |  4092 |    37   (6)| 00:00:01 |
|  15 |      NESTED LOOPS         |                 |    66 |  3234 |    31   (4)| 00:00:01 |
|* 16 |       HASH JOIN           |                 |    66 |  2772 |    31   (4)| 00:00:01 |
|* 17 |        TABLE ACCESS FULL  | PS_RD_WRKR_ASGN |   329 |  5922 |    15   (0)| 00:00:01 |
|* 18 |        TABLE ACCESS FULL  | PS_RD_WRKR_JOB  |  1193 | 28632 |    15   (0)| 00:00:01 |
|* 19 |       INDEX UNIQUE SCAN   | PS_RD_PERSON    |     1 |     7 |     0   (0)| 00:00:01 |
|  20 |      INDEX FAST FULL SCAN | PSBRD_PERSON    |  1807 | 23491 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

If we got a resultm, performance are really bad. The explain plan looks not good at all, especially the index fast full scan on PSBRD_PERSON (table PS_RD_PERSON) , and it is doing this twice !

I started the current article with a link to an other ORA-600 reported on HRMS9.0 within Peopletools 8.49. My CRM9.0 is also on Peopletools 8.49, and one common point is the (in)famous DESCending indexes from Peopletools 8.48....

So, let's have a look on that side, is there DESC indexes on the involved tables ?

SQL> select distinct index_name,table_name
  2  from   user_ind_columns
  3  where table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON')
  4* and   descend = 'DESC'

PS_RD_WRKR_JOB                 PS_RD_WRKR_JOB
PS1RD_WRKR_JOB                 PS_RD_WRKR_JOB
PS3RD_WRKR_JOB                 PS_RD_WRKR_JOB
PS2RD_WRKR_JOB                 PS_RD_WRKR_JOB
PS0RD_WRKR_JOB                 PS_RD_WRKR_JOB
PS4RD_WRKR_JOB                 PS_RD_WRKR_JOB

Let's rebuild them by ignoring them :

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

System altered.

SQL> conn sysadm/sysadm
Connected.
SQL> declare
       v_stmt long;
     begin
    for i in (select distinct index_name,table_name
              from   user_ind_columns
              where table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON')
              and   descend = 'DESC') loop
        select dbms_metadata.get_ddl('INDEX',i.index_name) into v_stmt from dual;
        execute immediate 'drop index '||i.index_name;
        execute immediate v_stmt;
    end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select distinct index_name,table_name
from   user_ind_columns
where table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON')
and   descend = 'DESC';

no rows selected

And now, the explain plan :

SQL> explain plan for
SELECT p.emplid
FROM   ps_rb_worker w , ps_rd_person p
WHERE  p.person_id = w.person_id
START WITH p.emplid = '000001'
CONNECT BY PRIOR w.person_id = w.supervisor_id ;

Explained.

SQL> select * from table(dbms_xplan.display);
 
Plan hash value: 2569705422

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    62 |    32   (4)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING     |                 |       |       |            |          |
|   2 |   NESTED LOOPS                 |                 |     1 |    92 |    20   (5)| 00:00:01 |
|   3 |    NESTED LOOPS                |                 |     1 |    85 |    20   (5)| 00:00:01 |
|*  4 |     HASH JOIN                  |                 |     2 |   114 |    18   (6)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | PSBRD_PERSON    |     2 |    38 |     2   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL         | PS_RD_WRKR_JOB  |  1193 | 45334 |    15   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| PS_RD_WRKR_ASGN |     1 |    28 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | PS_RD_WRKR_ASGN |     1 |       |     0   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN           | PS_RD_PERSON    |     1 |     7 |     0   (0)| 00:00:01 |
|  10 |   NESTED LOOPS                 |                 |     1 |    62 |    32   (4)| 00:00:01 |
|  11 |    NESTED LOOPS                |                 |     1 |    49 |    31   (4)| 00:00:01 |
|* 12 |     HASH JOIN                  |                 |     1 |    42 |    31   (4)| 00:00:01 |
|* 13 |      HASH JOIN                 |                 |       |       |            |          |
|  14 |       CONNECT BY PUMP          |                 |       |       |            |          |
|* 15 |       TABLE ACCESS FULL        | PS_RD_WRKR_JOB  |    23 |   552 |    15   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS FULL         | PS_RD_WRKR_ASGN |   329 |  5922 |    15   (0)| 00:00:01 |
|* 17 |     INDEX UNIQUE SCAN          | PS_RD_PERSON    |     1 |     7 |     0   (0)| 00:00:01 |
|  18 |    TABLE ACCESS BY INDEX ROWID | PS_RD_PERSON    |     1 |    13 |     1   (0)| 00:00:01 |
|* 19 |     INDEX UNIQUE SCAN          | PS_RD_PERSON    |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Now, with unique scan index are doing against PS_RD_PERSON the query is running much much faster.

If a conclusion was needed, once more, on Peopletools 8.48 and above, rebuild the indexes with these DESC indexes is definately a good idea.

Enjoy,

About "_unnest_subquery"

2009-05-09T17:26:00.008+02:00

Peoplesoft give some recommandation regarding the Oracle init parameters. These recommandations include the hidden parameter "_unnest_subquery" to be set to false (true by default), even for the most recent version.
It is surprising me, because this recommandation exists for several years now, and on older database as well.

Ok, let's see what if we don't follow this specific recommandation.

On 10.2.0.4 database, we have the following query running for hours :


UPDATE PS_AB_PDI_CN_TAO
 SET country_2char = COALESCE(( SELECT q.country_2char
                                FROM ( SELECT o.oprid
                                            , j.emplid
                                            , j.empl_rcd
                                            , j.effdt
                                            , j.effseq
                                            , c.country_2char
                                      FROM ps_job j
                                         , psoprdefn o
                                         , ps_opr_def_tbl_Hr h
                                         , ps_location_Tbl l
                                         , ps_country_tbl c
                                      WHERE EXISTS ( SELECT 'x'
                                                     FROM ps_job j2
                                                     WHERE j.emplid = j2.emplid
                                                     AND j2.empl_Rcd = 1)
                                      AND j.emplid = o.emplid
                                      AND o.oprclass = h.oprclass
                                      AND j.effdt = ( SELECT MAX(j3.EFFDT)
                                                      FROM PS_JOB j3
                                                      WHERE j3.EMPLID = j.EMPLID
                                                      AND j3.EMPL_RCD = j.EMPL_RCD
                                                      AND j3.EFFDT <= sysdate)
                                     AND j.effseq = ( SELECT MAX(j4.EFFSEQ)
                                                      FROM PS_JOB j4
                                                      WHERE j4.EMPLID = j.EMPLID
                                                      AND j4.EMPL_RCD = j.EMPL_RCD
                                                      AND j4.EFFDT = j.effdt)
                                     AND l.setid = j.setid_location
                                     AND l.location = j.location
                                     AND h.country = l.country
                                     AND l.effdt = ( SELECT MAX(l2.effdt)
                                                     FROM ps_location_tbl l2
                                                     WHERE l.setid = l2.setid
                                                     AND l.location = l2.location
                                                     AND l2.effdt <= j.effdt)
                                     AND c.country = l.country ) q
                                 WHERE Q.EFFDT = ( SELECT MAX(EFFDT)
                                                   FROM PS_JOB Q2
                                                   WHERE Q.EMPLID = Q2.EMPLID
                                                   AND Q2.EFFDT <= SYSDATE
                                                   AND Q.EFFDT <= SYSDATE)
                                 AND Q.EFFSEQ = ( SELECT MAX(J3.EFFSEQ)
                                                  FROM PS_JOB J3
                                                  WHERE Q.EMPLID = J3.EMPLID
                                                  AND Q.EMPL_RCD = J3.EMPL_RCD
                                                  AND Q.EFFDT = J3.EFFDT)
                                 AND Q.OPRID = PS_AB_PDI_CN_TAO.OPRID)
                               ,' ')
  WHERE COUNTRY_2CHAR = ' ';

The explain plan is the following :


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                           |                   | 41882 |  3599K|    18   (0)| 00:00:01 |
|   1 |  UPDATE                                    | PS_AB_PDI_CN_TAO  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL                        | PS_AB_PDI_CN_TAO  | 41882 |  3599K|    18   (0)| 00:00:01 |
|   3 |   HASH UNIQUE                              |                   |     1 |   137 |    26  (12)| 00:00:01 |
|*  4 |    HASH JOIN                               |                   |     1 |   137 |    15  (14)| 00:00:01 |
|   5 |     NESTED LOOPS                           |                   |     1 |   117 |     8   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                          |                   |     1 |   110 |     7   (0)| 00:00:01 |
|   7 |       NESTED LOOPS SEMI                    |                   |     1 |    85 |     5   (0)| 00:00:01 |
|   8 |        NESTED LOOPS                        |                   |     1 |    74 |     4   (0)| 00:00:01 |
|   9 |         NESTED LOOPS                       |                   |     1 |    39 |     2   (0)| 00:00:01 |
|  10 |          TABLE ACCESS BY INDEX ROWID       | PSOPRDEFN         |     1 |    27 |     1   (0)| 00:00:01 |
|* 11 |           INDEX UNIQUE SCAN                | PS_PSOPRDEFN      |     1 |       |     0   (0)| 00:00:01 |
|  12 |          TABLE ACCESS BY INDEX ROWID       | PS_OPR_DEF_TBL_HR |    33 |   396 |     1   (0)| 00:00:01 |
|* 13 |           INDEX UNIQUE SCAN                | PS_OPR_DEF_TBL_HR |     1 |       |     0   (0)| 00:00:01 |
|  14 |         TABLE ACCESS BY INDEX ROWID        | PS_JOB            |     1 |    35 |     2   (0)| 00:00:01 |
|* 15 |          INDEX RANGE SCAN                  | PSAJOB            |     1 |       |     1   (0)| 00:00:01 |
|  16 |           SORT AGGREGATE                   |                   |     1 |    16 |            |          |
|* 17 |            FILTER                          |                   |       |       |            |          |
|* 18 |             INDEX RANGE SCAN               | PSAJOB            |     2 |    32 |     2   (0)| 00:00:01 |
|  19 |             SORT AGGREGATE                 |                   |     1 |    22 |            |          |
|  20 |              FIRST ROW                     |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 21 |               INDEX RANGE SCAN (MIN/MAX)   | PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|  22 |                SORT AGGREGATE              |                   |     1 |    22 |            |          |
|  23 |                 FIRST ROW                  |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 24 |                  INDEX RANGE SCAN (MIN/MAX)| PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|* 25 |        INDEX RANGE SCAN                    | PSAJOB            |     8 |    88 |     1   (0)| 00:00:01 |
|* 26 |       TABLE ACCESS BY INDEX ROWID          | PS_LOCATION_TBL   |     1 |    25 |     2   (0)| 00:00:01 |
|* 27 |        INDEX RANGE SCAN                    | PS_LOCATION_TBL   |     1 |       |     1   (0)| 00:00:01 |
|  28 |         SORT AGGREGATE                     |                   |     1 |    21 |            |          |
|* 29 |          INDEX RANGE SCAN                  | PS_LOCATION_TBL   |     1 |    21 |     2   (0)| 00:00:01 |
|  30 |      TABLE ACCESS BY INDEX ROWID           | PS_COUNTRY_TBL    |     1 |     7 |     1   (0)| 00:00:01 |
|* 31 |       INDEX UNIQUE SCAN                    | PS_COUNTRY_TBL    |     1 |       |     0   (0)| 00:00:01 |
|  32 |     VIEW                                   | VW_SQ_1           |  3743 | 74860 |     6  (17)| 00:00:01 |
|  33 |      SORT GROUP BY                         |                   |  3743 |   109K|     6  (17)| 00:00:01 |
|* 34 |       INDEX FAST FULL SCAN                 | PS_JOB            |  3743 |   109K|     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Well, the last three lines are not as I would expect.
Let's try this hidden parameter :
"_unnest_subquery"=false (true by default)
Finally the explain plan change a lot and looks much better.


------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                             |                   | 41882 |  3599K|    18   (0)| 00:00:01 |
|   1 |  UPDATE                                      | PS_AB_PDI_CN_TAO  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL                          | PS_AB_PDI_CN_TAO  | 41882 |  3599K|    18   (0)| 00:00:01 |
|   3 |   HASH UNIQUE                                |                   |     1 |   117 |    21   (5)| 00:00:01 |
|   4 |    NESTED LOOPS                              |                   |     1 |   117 |     8   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                             |                   |     1 |   110 |     7   (0)| 00:00:01 |
|   6 |      NESTED LOOPS SEMI                       |                   |     1 |    85 |     5   (0)| 00:00:01 |
|   7 |       NESTED LOOPS                           |                   |     1 |    74 |     4   (0)| 00:00:01 |
|   8 |        NESTED LOOPS                          |                   |     1 |    39 |     2   (0)| 00:00:01 |
|   9 |         TABLE ACCESS BY INDEX ROWID          | PSOPRDEFN         |     1 |    27 |     1   (0)| 00:00:01 |
|* 10 |          INDEX UNIQUE SCAN                   | PS_PSOPRDEFN      |     1 |       |     0   (0)| 00:00:01 |
|  11 |         TABLE ACCESS BY INDEX ROWID          | PS_OPR_DEF_TBL_HR |    33 |   396 |     1   (0)| 00:00:01 |
|* 12 |          INDEX UNIQUE SCAN                   | PS_OPR_DEF_TBL_HR |     1 |       |     0   (0)| 00:00:01 |
|  13 |        TABLE ACCESS BY INDEX ROWID           | PS_JOB            |     1 |    35 |     2   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN                     | PSAJOB            |     1 |       |     1   (0)| 00:00:01 |
|  15 |          SORT AGGREGATE                      |                   |     1 |    16 |            |          |
|* 16 |           FILTER                             |                   |       |       |            |          |
|* 17 |            INDEX RANGE SCAN                  | PSAJOB            |     2 |    32 |     2   (0)| 00:00:01 |
|  18 |            SORT AGGREGATE                    |                   |     1 |    22 |            |          |
|  19 |             FIRST ROW                        |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 20 |              INDEX RANGE SCAN (MIN/MAX)      | PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|  21 |               SORT AGGREGATE                 |                   |     1 |    19 |            |          |
|  22 |                FIRST ROW                     |                   |     1 |    19 |     2   (0)| 00:00:01 |
|* 23 |                 INDEX RANGE SCAN (MIN/MAX)   | PSAJOB            |     1 |    19 |     2   (0)| 00:00:01 |
|  24 |                  SORT AGGREGATE              |                   |     1 |    22 |            |          |
|  25 |                   FIRST ROW                  |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 26 |                    INDEX RANGE SCAN (MIN/MAX)| PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|* 27 |       INDEX RANGE SCAN                       | PSAJOB            |     8 |    88 |     1   (0)| 00:00:01 |
|* 28 |      TABLE ACCESS BY INDEX ROWID             | PS_LOCATION_TBL   |     1 |    25 |     2   (0)| 00:00:01 |
|* 29 |       INDEX RANGE SCAN                       | PS_LOCATION_TBL   |     1 |       |     1   (0)| 00:00:01 |
|  30 |        SORT AGGREGATE                        |                   |     1 |    21 |            |          |
|* 31 |         INDEX RANGE SCAN                     | PS_LOCATION_TBL   |     1 |    21 |     2   (0)| 00:00:01 |
|  32 |     TABLE ACCESS BY INDEX ROWID              | PS_COUNTRY_TBL    |     1 |     7 |     1   (0)| 00:00:01 |
|* 33 |      INDEX UNIQUE SCAN                       | PS_COUNTRY_TBL    |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Finally the query is running in few seconds only.

Ok, the Peoplesoft support continue to recommand this setting "_unnest_subquery"=false, and I understand now it helps.

However, looks interesting, cost is same, so what happens ? What makes Oracle decide to run the first intead of the second ?
The only comment about this parameter on Metalink is : "This parameter controls whether the optimizer attempts to unnest correlated subqueries or not." Fair enough, let's see within 10053 trace file the differences.

The part of the query causing the issue is the subquery with J3 alias and SYSDATE.
AND J.EFFDT = ( SELECT MAX(J3.EFFDT)
FROM PS_JOB J3
WHERE J3.EMPLID = J.EMPLID
AND J3.EMPL_RCD = J.EMPL_RCD
AND J3.EFFDT <= SYSDATE)
Surprisingly, for this subquery, the 10053 trace with "_unnest_subquery"=true shows that it even doesn't consider a RANGE SCAN (Min/Max) against PSAJOB where it is the lowest cost when "_unnest_subquery"=false.

Well, maybe time to raise a SR and start to follow the advices.

Enjoy !

The PS_HOME subfolders

2009-04-14T21:36:00.002+02:00

On PSST0101 blog, an interesting list of the folders existing under PS_HOME with some explanations for each.

Do not hesitate to contribute.

Enjoy,

CentOS 5.3 is out

2009-04-01T21:03:00.006+02:00

I know, it is a little bit out off topic here, and this is not because this is the 1st of April, but because I'm using it as the main host of all my virtual machines, and even if Oracle database software is officially not certified on CentOS, a lot of people are using it as a database server, as a free OS fully RHEL compatible.

Several week after RHEL 5.3, and two-three weeks later OEL 5.3, CentOS is finaly released its own version of 5.3.

If you are on CentOS 5.x (mine is CentOS5.2), it is very easy to upgrade. For some reasons I had to run the yum update a couple of times before taking in account the news packages. But then, everything run fine :

[root@hercules /root]# rpm -q centos-release
centos-release-5-2.el5.centos
[root@hercules /root]# yum list updates
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.fraunhofer.de
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: ftp.hosteurope.de
Updated Packages
NetworkManager.x86_64 1:0.7.0-3.el5 base
NetworkManager-glib.x86_64 1:0.7.0-3.el5 base
[...]the list of update pakages is around 300, I won't post here[...]
[root@hercules /root]# yum update
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.widexs.nl
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: centos.intergenia.de
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package glx-utils.x86_64 0:6.5.1-7.7.el5 set to be updated
---> Package xorg-x11-drv-ati.x86_64 0:6.6.3-3.22.el5 set to be updated
[...]

Transaction Summary
=============================================================================
Install 11 Package(s)
Update 272 Package(s)
Remove 2 Package(s)

Total download size: 363 M
Is this ok [y/N]: y
Downloading Packages:
(1/283): mkinitrd-5.1.19. 100% |=========================| 449 kB 00:01
(2/283): dbus-glib-0.73-8 100% |=========================| 162 kB 00:00
[...]
Complete!
[root@hercules /root]# rpm -q centos-release
centos-release-5-3.el5.centos.1

To check your Linux distribution, you can use :
[root@hercules /root]# more /etc/redhat-release
CentOS release 5.3 (Final)

Don't forget to bounce your server :
[root@hercules /root]# shutdown -r now

Lastly, if you are using VMWare like I do, don't forget to re-configure it :
[root@hercules /]# cd appl/vmware/bin
[root@hercules /appl/vmware/bin]# ./vmware-config.pl
Making sure services for VMware Server are stopped.

Stopping VMware services:
Virtual machine monitor [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Virtual ethernet [ OK ]

Configuring fallback GTK+ 2.4 libraries.
[...]
Starting VMware services:
Virtual machine monitor [ OK ]
Virtual ethernet [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Starting VMware virtual machines... [ OK ]

The configuration of VMware Server 1.0.8 build-126538 for Linux for this
running kernel completed successfully.

[root@hercules /appl/vmware/bin]#

All is running fine !

Thanks to the CentOS' team, even if this version is coming very late compared to the source.
To go further : http://www.centos.org

Enjoy,