Manage Peoplesoft with OEM Grid Control (2/3)

After installing the OEM Grid Control 11gR1 – the OVM template, including the Peoplesoft plug-in for OMS as shown here, it’s time for the target system to be prepared. All the client servers have to have the OM Agent. By client, it means client for the OMS, say so the clients are the databases servers and Apps/Batch/PIA servers which I created based on PSOVM as shown in previous posts. But the Peoplesoft plug-in is crawling a PS_HOME, it has to be installed only on Apps/Batch/PIA servers.

1. The OEM Agent
Whether there’s space enough in $ORACLE_HOME (/u01/app/oracle/product) on the PSOVM database servers (user oracle), there’s no space left next to the Oracle client (/opt/oracle/psft/pt) on the PSOVM App/Batch/PIA servers (user psadm1).
Before installing the Agent on App/Batch/PIA, let’s make a dedicated folder where’s space allows us to install it, as following :
[root@psovmhcm /]# cd /
[root@psovmhcm /]# df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/xvda2             3876120    724696   3111424  19% /
[root@psovmhcm /]# mkdir oracle_agent
[root@psovmhcm /]# chown -R psadm1:oracle oracle_agent
[root@psovmhcm /]# cd /opt/oracle/psft/pt
[root@psovmhcm pt]# ln -s /oracle_agent .
[root@psovmhcm pt]# ls -lrt
total 56
drwx------  2 root   root   16384 Dec 20 19:44 lost+found
drwxr-xr-x  9 root   root    4096 Dec 20 20:08 bea
drwxr-xr-x  7 root   root    4096 Dec 20 20:12 oracle-client
drwxr-xr-x  5 root   root    4096 Jan 23 09:37 cobol
drwxr-xr-x 28 psadm1 oracle  4096 Jan 23 09:43 tools
lrwxrwxrwx  1 root   root      13 Mar 30 11:16 oracle_agent -> /oracle_agent
[root@psovmhcm pt]#

Remember, the PSOVMs are 64bit word sized (furthermore Peopletools 8.5x is only supported on 64bit OS), the Agent to be downloaded is from Linux x86-64, 11.1.0.1.0 :
http://www.oracle.com/technetwork/oem/grid-control/downloads/agentsoft-090381.html
Once downloaded, upload the file to the PSOVM OEM Grid Control previously installed, into the folder /u01/OracleHomes/Middleware/oms11g/sysman/agent_download/11.1.0.1.0 and unzip it.

Then, everything can be done through the OEM GUI. Much easier than ever, especially when deploying Agent through multiple target. However, screenshots below are coming from a deployment on one target only :

Choose the platform Linux-x86-64, that’s the one we’ve just upload to the OMS server :

Here you mention the owner of the Agent home (OMA_HOME),
=> for the PSOVM database servers it will be oracle,
=> for the PSOVM App/Batch/PIA servers it will be psadm1
Then, here you also mention the parent folder hosting the Agent home (OMA_HOME),
=> for the PSOVM database servers it will be /u01/app/oracle/product,
=> for the PSOVM App/Batch/PIA servers it will be /opt/oracle/psft/pt/oracle_agent 
Uncheck the run of root.sh, it has to be done manually later on (somehow it failed in my case when it was checked).
Give the sysman password :


Be patient during these phases, depending of the number of targets you are deploying to and of the network bandwidth, it can take a quite while :


Now, the new host has been added in the host’s list :

Do not forget to run root.sh (in $OMA_HOME). We can eventually check the agent status from the target host :

And the database as well in the database’s list :

Just to show it, I also deployed the Agent through multiple targets at once :

In the end, we got all the hosts targets

         
Warning :
1. Do not forget to run root.sh from the target Agent home, otherwise you may encountered the following error (RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)) later on when managing the environment :

2. Do not forget to maintain the /etc/hosts according to the all environment. The /etc/hosts from OMS server should contains its own IP address, hostname and domain name, but also all the target servers’ references (IP, hostname and domain). Here’s the one I’m using on the OMS host server :
[oracle@ovmoem111 ~]$ more /etc/hosts
127.0.0.1       localhost.localdomain   localhost
192.168.1.160 ovmoem111.phoenix.nga ovmoem111
192.168.1.133   psovmdb.phoenix.nga psovmdb
192.168.1.143   psovmcrm.phoenix.nga psovmcrm
192.168.1.142   psovmcrmdb.phoenix.nga psovmcrmdb
192.168.1.145   psovmfscm.phoenix.nga psovmfscm
192.168.1.144   psovmfscmdb.phoenix.nga psovmfscmdb
192.168.1.147   psovmhcm.phoenix.nga psovmhcm
192.168.1.146   psovmhcmdb.phoenix.nga psovmhcmdb
192.168.1.148   psovmhcmdb2.phoenix.nga psovmhcmdb2
[oracle@ovmoem111 ~]$

The /etc/hosts file from target hosts must contains their own IP address, hostname and domain name as well as the ones of the OMS server. Here’s an example on one of the target host :
[root@psovmcrm ~]# more /etc/hosts
127.0.0.1       localhost.localdomain   localhost
::1     localhost6.localdomain6 localhost6
192.168.1.143   psovmcrm.phoenix.nga psovmcrm
192.168.1.160 ovmoem111.phoenix.nga ovmoem111
[root@psovmcrm ~]#

If not doing so, you can have the following error (not possible to configure target database for instance) :

2. The Peoplesoft plug-in for the Agent 
To be done only on App/Batch/PIA PSOVM servers.
To be able to manage the Peoplesoft environment from OEM Grid Control, the Agent must have the Peoplesoft plug-in. This is the same package used for the plug-in previously installed on OMS.
To be downloaded from https://edelivery.oracle.com (free registration), Product “Peoplesoft Enterprise”, Platform “Linux x86-64” (but the results do not differ, it does not matter for Peoplesoft packages), then choose the pack “Oracle Application Management Pack 1.0.0.2.0 for PeopleSoft Enterprise Media Pack v2” part number B60821-02.
Download the part number V22794-01 named “PeopleSoft Enterprise Environment Management Plug-in for Oracle Enterprise Manager (8.51.0.1.0)”. Despite the name, it is compatible on Peopletools 8.48 and above, moreover it is also compatible with OEM Grid Control 11gR1.

The Peoplesoft plug-in installation on Agent will be the easiest part, using the silent installation option as following.
Warning : the Peoplesoft plug-in for OEM Agent must be done whilst the psconfig.sh has not been previously invoked in the session (the PS environment must not be set), make it not happening.

Here I rename the psconfig.sh to avoid all confusion.
login as: root
root@192.168.1.143's password:
Last login: Wed Mar 30 11:19:04 2011 from 192.168.1.202
[root@psovmcrm ~]# su - psadm1
[psadm1@psovmcrm ~]$ cd $PS_HOME
[psadm1@psovmcrm tools]$ pwd
/opt/oracle/psft/pt/tools
[psadm1@psovmcrm tools]$ mv psconfig.sh _psconfig.sh
[psadm1@psovmcrm tools]$ exit
logout
[root@psovmcrm ~]# su - psadm1
-bash: /opt/oracle/psft/pt/tools/psconfig.sh: No such file or directory

From the Peoplesoft plug-in, unzip the file 5016553.zip (present under the downloaded Peoplesoft plug-in package) and copy it on your local server for installation (the target OMA host).
[psadm1@psovmcrm ~]$ cd /tmp
[psadm1@psovmcrm tmp]$ scp -r root@ovmoem111:/nfs/software/OracleCD/OEM/PeoplesoftPlugin/8.51/Disk1/5016553 .
root@ovmoem111's password:
<…snipped…>
[psadm1@psovmcrm tmp]$

You must set the ORACLE_HOME variable to the OMA_HOME and stop the Agent.
[psadm1@psovmcrm tmp]$ export ORACLE_HOME=/opt/oracle/psft/pt/oracle_agent/agent11g
[psadm1@psovmcrm tmp]$ cd $ORACLE_HOME/oui/bin
[psadm1@psovmcrm bin]$ cd ../../bin
[psadm1@psovmcrm bin]$ ./emctl stop agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Agent is Not Running
[psadm1@psovmcrm bin]$

Then, from the OUI Installer of Agent, run the silent installation.
[psadm1@psovmcrm bin]$ cd -
/opt/oracle/psft/pt/oracle_agent/agent11g/oui/bin
[psadm1@psovmcrm bin]$ ./runInstaller.sh -silent -ignoreSysPrereqs -waitForCompletion -responseFile /tmp/5016553/Disk1/response/psemagent.rsp -sourceLoc /tmp/5016553/Disk1/stage/products.xml -invPtrLoc $ORACLE_HOME/oraInst.loc ORACLE_HOME=$ORACLE_HOME
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 2055 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-04-01_07-56-41AM. Please wait ...Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

You can find the log of this install session at:
/home/psadm1/oraInventory/logs/installActions2011-04-01_07-56-41AM.log
.................................................................................................... 100% Done.

Loading Product Information
............................................................... 100% Done.

Analyzing dependencies
............................................................... 100% Done.

-----------------------------------------------------------------------------
Summary
Global Settings
    Source: /tmp/5016553/Disk1/stage/products.xml
    Oracle Home: /opt/oracle/psft/pt/oracle_agent/agent11g (agent11g1)
    Installation Type: Typical
Product Languages
   English
Space Requirements
   / Required 23MB (includes 7MB temporary) : Available 1.82GB
New Installations (1 products)
   PeopleSoft Environment Management Plug-in (Agent) 8.51.0.1.0
-----------------------------------------------------------------------------

Installation in progress (Friday, April 1, 2011 7:58:52 AM EDT)
...............................................................................................                                 95% Done.
Install successful

Linking in progress (Friday, April 1, 2011 7:59:03 AM EDT)
Link successful

Setup in progress (Friday, April 1, 2011 7:59:03 AM EDT)
Setup successful

End of install phases.(Friday, April 1, 2011 7:59:11 AM EDT)
Starting to execute configuration assistants
Configuration assistant "PeopleSoft Environment Management Agent Plug-in" succeeded
The installation of PeopleSoft Environment Management Plug-in (Agent) was successful.
Please check '/home/psadm1/oraInventory/logs/silentInstall2011-04-01_07-56-41AM.log' for more details.
[psadm1@psovmcrm bin]$

The Agent is restarted automatically, we can check it.
[psadm1@psovmcrm bin]$ cd -
/opt/oracle/psft/pt/oracle_agent/agent11g/bin
[psadm1@psovmcrm bin]$ ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /opt/oracle/psft/pt/oracle_agent/agent11g
Agent binaries    : /opt/oracle/psft/pt/oracle_agent/agent11g
Agent Process ID  : 1731
Parent Process ID : 1708
Agent URL         : https://psovmcrm.phoenix.nga:3872/emd/main/
Repository URL    : https://ovmoem111.phoenix.nga:4900/em/upload
Started at        : 2011-04-01 07:59:47
Started by user   : psadm1
Last Reload       : 2011-04-01 07:59:47
Last successful upload                       : 2011-04-01 08:01:25
Total Megabytes of XML files uploaded so far :     0.43
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    52.60%
Last successful heartbeat to OMS             : 2011-04-01 08:05:27
---------------------------------------------------------------
Agent is Running and Ready
[psadm1@psovmcrm bin]$

Rename back to the original name the psconfig.sh for further connection.
[psadm1@psovmcrm bin]$ cd /opt/oracle/psft/pt/tools
[psadm1@psovmcrm tools]$ mv _psconfig.sh psconfig.sh
[psadm1@psovmcrm tools]$

From now, we should be able to add this Peoplesoft environment in the OEM Grid Control.
So far we have this :

Nest step is then to add Peoplesoft target. To be continued here.

Manage Peoplesoft with OEM Grid Control (1/3)

Few weeks ago, I showed how to expand a Peoplesoft OVM use within a Physical Standby Database, how to enable the Active Data Guard, how a Batch server can run on Data Guard and how a App server can run on Data Guard.

Beside that, still through the given Peoplesoft OVM we can easily expand the Peoplesoft management and test how to manage a entire Peoplesoft environment within Oracle Enterprise Manager Grid Control. Right, that would required a bit more than just the Peoplesoft OVM. But all easy to get and easy to install.
Oracle is not only delivering templates for Applications layer, such as Peoplesoft, but also for OEM Grid Control (11gR1). The tests will also need the Peoplesoft plug-in for the OMS, the agent as well as the Peoplesoft plug-in for the agent.

Three steps to reach the expected results :
1. Install the OEM Grid Control 11gR1 (that will be done within the delivered template deployment), and the Peoplesoft plugin installation on top of the OMS. – this current post
2. Install the Agent for OEM on client server host, and the Peoplesoft plugin installation on top of the agent. – see here
3. Configuring the Peoplesoft in OEM Grid Control. – see here

1. OEM Grid Control 11gR1
So, first of all, let’s start with OEM Grid Control 11gR1. The template is downloadable on https://edelivery.oracle.com/linux (free registration), Product “Oracle VM templates”, Platform “X86 32 bit”, pack “Oracle VM Template for Enterprise Manager Grid Control 11.1 Media Pack v1 for x86 (32 bit)”, part number B60727-01. There are 4 disks to download, all together approx. 15Gb. Be aware, it is required 5Gb RAM available on the Oracle VM Server…

Then a standard VM template deployment, straightforward even though it took quite a while in my environment.
 

In the end, the OEM Grid Control is reachable within the URL : https://<ip_address>:7799/em/console/logon/logon

2. The Peoplesoft plugin for OMS 
To be downloaded from https://edelivery.oracle.com (free registration), Product “Peoplesoft Enterprise”, Platform “Linux x86-64” (but the results do not differ, it does not matter for Peoplesoft packages), then choose the pack “Oracle Application Management Pack 1.0.0.2.0 for PeopleSoft Enterprise Media Pack v2” part number B60821-02.
Download the part number V22794-01 named “PeopleSoft Enterprise Environment Management Plug-in for Oracle Enterprise Manager (8.51.0.1.0)”. Despite the name, it is compatible on Peopletools 8.48 and above, moreover it is also compatible with OEM Grid Control 11gR1.
Once you have it, open a X-Window client to start the installation within a GUI. Note the plug-in has no installer embedded, it should be installed from within the existing OUI of the installed OMS.
On PSOVM OEM Grid Control, run the Installer from  /u01/OracleHomes/MiddleWare/oms11g/oui/bin :
 
Then the installation is quite straightforward :






Once installed, in OEM Grid Control, under the tab “Target”, we’ll see one more sub-tab available, “Peoplesoft”:

Next step, the Agent on the target hosts. To be continued here.

COBOL Peopletools 8.51 and ulimit

Whilst testing the Cobol on HCM9.1 after an upgrade from Peopletools 8.50 to Peopletools 8.51, the Cobol were just crashed. It did work fine in PT8.50 in the same environment, same platform, same OS settings, same PS_HOME (OS is AIX5.3ML9).
The Peopletools 8.51 have been installed on top of the existing PS_HOME 8.50.
But here, the Cobol just crashed, the log file being the following :
Oracle Enterprise Global Payroll and Absence Management – Version 9.0
                                         
Copyright (C) 2006
Oracle
                                                                                     
All Rights Reserved  

<...snipped...>

kgefec: fatal error 0
ORA-21503: program terminated by fatal error
ORA-04030: out of process memory when trying to allocate 2097184 bytes (Alloc statemen,intermBuf_kpdStm alloc)
Errors in file :
ORA-21503: program terminated by fatal error
ORA-04030: out of process memory when trying to allocate 2097184 bytes (Alloc statemen,intermBuf_kpdStm alloc)

----- Call Stack Trace -----
calling              call     entry                argument values in hex     
location             type     point                (? means dubious value)   
-------------------- -------- -------------------- ----------------------------
skgudmp+0044         bl       kgdsdst              FFFF3F50 ? 100A4848 ?
kgefec+00cc          bl       _ptrgl             
kghnospc+089c        bl       kgefec               100A4848 ? 0 ? FBE ? 0 ?
kghalo+0d08          bl       kghnospc             BDE9694 ? 0 ? 16672750 ?
                                                   1666E878 ? 3EA0 ?
kpuhhalo+03cc        bl       kghalo               BDF0494 ? 0 ? 0 ? 0 ? 0 ? 0 ?
                                                   0 ? 0 ?
kpuertb_reallo+0110  bl       kpuhhalo             166747E8 ? 200000 ? CC1590C ?
kpuex_reallocT+00c0  bl       _ptrgl             
kpuspi+05c0          bl       kpuex_reallocT       FFFF4AA0 ? 0 ? 0 ? 2 ?
OCIStmtSetPiec+0014  bl       kpuspi               1 ? FFFF4C8A ? 1 ? 2 ? 0 ?
                                                   0 ? 7 ? 17F3D278 ?
PSORA_FetchNex+02ec  bl       0B876680           
PS_sqlfet+002c       bl       0B8770F8           
C_GETSTMT+0430       bl       0D415C10         
  
<...snipped...>

----- End of Call Stack Trace -----

1110200: 1302083115: PSPAL::Abort: Unexpected signal received
1110200: 1302083115: PSPAL::Abort: Location: /vob/peopletools/src/pspal/exception_sigaction.cpp:494: PSPAL::SigactionSignalHandler::RecoverableSignalHandler(int, siginfo_t *, void *)
1110200: 1302083115: PSPAL::Abort: Generating process state report to ./process_state.txt
1110200: 1302083115: PSPAL::Abort: Terminating process now.

Execution error : file '/appl/psoft/hr91/cblbin/PTPSQLRT.gnt'
error code: 115, pc=0, call=1, seg=0
115     Unexpected signal (Signal 6)

It appears the ulimit for “data” was not set to unlimited, but to a low value. After setting it to unlimited for the Peoplesoft OS user, and re-linking the Cobol, it worked again.
That’s a small change within PT8.51, it was working fine without that unlimited data limit within 8.50.

Find out more, other workarounds about this error : E-COB: PTPSQLRT-114 Attempt to access item beyond bounds of memory (Signal 11) [ID 638612.1]

Nicolas.

Peopletools patches 8.50.14 and 8.50.15 removed

Time to time I’m checking the Peopletools patch availability on ftp://ftp.peoplesoft.com/outgoing/ptools/.
I was surprised to see the date of patches 8.50.14 and 8.50.15 more recent than the date of patch 8.50.16, so I was going into there and saw nothing but a readme.txt with such explanation :
”PEOPLETOOLS PATCH 8.50.14 HAS BEEN REMOVED. PLEASE UPGRADE TO LATEST PEOPLETOOLS PATCH.”
and
”PEOPLETOOLS PATCH 8.50.15 HAS BEEN REMOVED. PLEASE UPGRADE TO LATEST PEOPLETOOLS PATCH on 8.50.”
It should be something seriously wrong with those patches, checking My Oracle Support does not give much information except the following notes where you can read the advice to upgrade to the latest Peopletools 8.50 patch (at least 8.50.16) :
E-PIA: PT8.50.14 / PT8.50.15 End Users Kicked Out of PeopleSoft [ID 1306952.1]
E-AS: Users Kicked Out Upon Applying For A Job In PeopleTools 8.50.14 [ID 1298866.1]

Searching over Internet does not give that much information neither, we can found that thread talking about a security flaw.

Well, if you are on one of these patches, you better should 1) raise a SR to the support 2) begin a patch upgrade.

Nicolas.

Peopletools 8.51 : psadmin is a symbolic link

Up to Peopletools 8.50, you see that psadmin under $PS_HOME/appserv is an executable file :
[psadm2@psovmcrm ~]$ cd $PS_HOME/appserv
[psadm2@psovmcrm appserv]$ ls -lrt psadmin
-rwxr-xr-x 1 psadm1 oracle 281383 Apr 29  2010 psadmin
[psadm2@psovmcrm appserv]$ ./psadmin -v
Version 8.50.09
[psadm2@psovmcrm appserv]$

From Peopletools 8.51, psadmin under $PS_HOME/appserv became a symbolic link to $PS_HOME/bin/psadmin :
[psadm2@psovmhcm ~]$ cd $PS_HOME/appserv
[psadm2@psovmhcm appserv]$ ls -lrt psadmin
lrwxrwxrwx 1 psadm1 oracle 37 Dec 20 20:03 psadmin -> /opt/oracle/psft/pt/tools/bin/psadmin
[psadm2@psovmhcm appserv]$ ./psadmin -v
Version 8.51.02
[psadm2@psovmhcm appserv]$

It’s a kind of consolidation, all the binaries go into the same directory. Looks good, that’s fine by me.
But it could raise a problem.

When you are upgrading your Peopletools version, it has always been recommanded to install the new version on top of the existing PS_HOME, and that’s still the case within Peopletools 8.51 : see Task 3-1-4: Installing the PeopleTools Software, page 21 of the Enterprise PeopleTools 8.51 Upgrade :
”Run the PeopleSoft Installer and install the software to the same PS_HOME directory where you installed the previous PeopleSoft PeopleTools software.”

Doing so, installing the new Peopletools release on top of the existing PS_HOME works fine, except that the psadmin under $PS_HOME/appserv won’t be replaced by the symbolic link.
I’m not sure how the Peopletools installer is precisely working, but indeed, when you are trying to create a symbolic link whenever a file within the same name exist, an error is raised :
[psadm1@psovmcrm appserv]$ ln -s $PS_HOME/bin/psadmin .
ln: creating symbolic link `./psadmin' to `/opt/oracle/psft/pt/tools/bin/psadmin': File exists
[psadm1@psovmcrm appserv]$

Unfortunately, no error is returned by the installer, and nothing in the log file. So, it will “silently” continues to work on previous installed version. When you are upgrading an existing PS_HOME, always verify the version of the psadmin using the following :
./psadmin –v
It should obviously correspond to the version you installed. And be sure that from Peopletools 8.51, it is a symbolic link, otherwise, create it manually :
[psadm1@psovmcrm ~]$ cd $PS_HOME/appserv
[psadm1@psovmcrm appserv]$ rm -f psadmin
[psadm1@psovmcrm appserv]$ ln -s $PS_HOME/bin/psadmin .
[psadm1@psovmcrm appserv]$ ./psadmin –v

Enjoy it !

Find out more : My Oracle Support note E-UPG PT 8.51 New location of Psadmin.exe [ID 1251444.1] and E-UPG Master Note Upgrade Issues with PT 8.51 [ID 1262396.1]

Peopletools 8.51 : on TABLE’s build (bis)

This blog entry continues the previous one I wrote about table’s build, especially about the partitioned tables. As it has been showed in the previous article, Peopletools 8.51 takes advantages of the DBMS_METADATA.GET_DDL package from the Oracle database, very useful if you are working within partitioned tables. Those are saved and survive a table rebuild through AppDesigner which has never been the case before.
Is it really right ? Does it really survive a table’s rebuild ? Well, I showed in the previous article that it does, but I came across a case which does not ! Example.

If you have to run the SETSPACE.SQR to update the tablespaces’ list in Peoplesoft, you’ll come in troubles within the partitioned tables.

Still working with my partitioned table PS_MYTABLE : 

Now, I run SETSPACE.SQR : 

Surprisingly, it updated the row of my table PS_MYTABLE to PTTBL (which has actually nothing to do with my table!).
Now from AppDesigner, the tablespace of this record has been changed : 

And if I rebuild the table, the partitions are simply lost, the table going to PTTBL ! 

To retrieve the partitions, put back the table in the original tablespace CUAUDIT, and rebuild : 
  

Pfew ! So, if the partitions are much better managed from AppDesigner table’s build than before, there’s still few surprise and thing to know !

So, working with partitions ? Take care, Peopletools 8.51 does not solve all, partitioned tables are still a very sensitive domain in Peoplesoft world !

Peopletools 8.51 : on TABLE’s build

Peopletools 8.51 is coming with a very interesting improvement. The table definition (DDL) is taking from the backend before rebuilding the table. It means that if you have a partitioned table, it will survive a table rebuild, unlike the previous versions which just destroyed them recreating the table as non-partitioned. All the DBA working on Peoplesoft and Oracle have already encountered such situation. Sometimes, inadvertently a very big partitioned table to be rebuild become non-partitioned, raising a lot of subsequent issues.
On an other side, the indexes defined only in database are not (always) taking in account on table’s build. Note that I wrote “always”, it means that under some circumstances it does, have a look.

1. Table’s DDL from database
Firstly, let’s have a try to see how Peoplesoft is taking advantages of the DBMS_METADATA.GET_DDL package available in Oracle database.
Partitioning a table is still not available from AppDesigner, it must be done within the backend. 
First, change a regular table to partition (let’s keep example simplest as possible) :
 
The table PS_MYTABLE is known from AppDesigner, but even being partitioned, we don’t see it from AppDesigner :


 
The DDL here is not coming from the database, but from the AppDesigner definition.
Let’s rebuild the table and see the generated script :

Now the script looks much better than ever. For the first time Peoplesoft is able to generated the script with a partitioned table definition as is from the backend :

That’ll save a lot of headaches. But overall, do not forget whenever sqlerror exit !

Addendum (28-MAR-2011) : coming across a case where Peopletools destroys the partitions definition, have a look here.

2. Index (shortname) on database only are not taken in account
The table’s DDL is taken from the backend, but if you have DBA in hurry creating indexes only from the backend without using AppDesigner for some performance reason, you’re still facing a known issue when rebuilding table. The database only index will be lost. Let’s create one with a Peoplesoft standard name :
 
Then rebuild the table (even forcing the index recreation) :

The script is as follow :
-- Start the Transaction

-- Data Conversion

-- Create temporary table

CREATE TABLE PSYMYTABLE (EMPLID VARCHAR2(11) NOT NULL,
   MYSTRING VARCHAR2(30) NOT NULL,
   MYNUM DECIMAL(8, 3) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "CUAUDIT"
  PARTITION BY RANGE ("EMPLID")
(PARTITION "PS_MYTABLE_P1"  VALUES LESS THAN ('51')
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CUAUDIT" NOCOMPRESS ,
PARTITION "PS_MYTABLE_P2"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CUAUDIT" NOCOMPRESS )
/

-- Copy from source to temp table

INSERT INTO PSYMYTABLE (
        EMPLID,
    MYSTRING,
    MYNUM)
  SELECT
        EMPLID,
    MYSTRING,
    MYNUM
  FROM PS_MYTABLE
/

-- CAUTION: Drop Original Table

DROP TABLE PS_MYTABLE
/

-- Rename Table

RENAME PSYMYTABLE TO PS_MYTABLE
/

-- Done
=> As you can see, no index (re)creation !
It is a known case for ages on Peoplesoft. You have to define the same indexes manually onto AppDesigner as it is on database level. However in some cases there’re some bad surprise.

3. Index (longname) on database only are taken in account
Whether a database index is generally not visible from AppDesigner table’s rebuild, it can raise some issue in a specific case. Few weeks ago, I been faced to that case.
Let’s create an index on the the database only, specify a long name (!) :
 
And now, rebuild the underlying table PS_MYTABLE:

=> it will crash, closing the AppDesigner without any message box !
Here below, we can see the log file generated :
SQL Build process began on 27/03/2011 at 18:55:24 for database H91TMPLT.
File: E:\pt851-903-R1-retail\peopletools\src\psbld\bldcreat.cppSQL error. Stmt #: 2180  Error Position: 0  Return: 31603 - ORA-31603: object "PSBMYTABLE_LONG_IN" of type INDEX not found in schema "SYSADM" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 3241 ORA-06512: at "SYS.DBMS_METADATA", line 4812 ORA-06512: at line 1
Failed SQL stmt:SELECT DBMS_METADATA.GET_DDL('INDEX',:1) from dual
There’s no way but rename the index to a shorter name !

Consequently, if a database short named index is not taking in account on table’s build, a long named index is !

Enjoy,

Peopletools 8.51 : “whenever sqlerror exit” is still missing

Whether a lot of new features are coming within the latest Peopletools version 8.51 working closer from Oracle database, one is still missing. That one I’m awaiting for the last 10 years I’m working on Peoplesoft.
When you build a table, there no clause “whenever sqlerror exit” automatically added on top of the generated script, and that could save us from data loss. Here’s a simple example.
First of all, for the need of the test, I created two fields – MYSTRING VARCHAR2(30) and MYNUM NUMBER(8,3).
Then I create a record as following :
 
I put that record in a very small tablespace CUAUDIT which has been set in noextend mode :
SQL> alter database datafile '/u01/app/oracle/oradata/H91TMPLT/cuaudit.dbf' autoextend off;

Database altered.
Then I build that record, as usual :
 
The generated script is very simple, as follow :
 
Then, running the script in a SQL*Plus session :
 
So far, so good.
Now, populate the table with whatever you like (here with random values, that does not matter), but it fulfill the tablespace :
 
Now, I’d like to reduce the size of the column MYNUM from 8,3 to 7,3. As we all know, that’s not possible to achieve this by an ALTER TABLE when the column is not empty (ORA-01440 : column to be modified must be empty to decrease precision or scale) :
 
So, let’s go in AppDesigner, modifying the column length and rebuild the table, not through ALTER TABLES (that won’t work) but through CREATE TABLES (with a staging table) :



And the script is now :
 
As far as we can see, some efforts have been made in the script on the comments to warn people reading the script about the table drop command, but from my point of view this is far not enough.
Return in SQL*Plus session, and run it :

There’s obviously not enough space in my tablespace for the staging table, but the script does not care error, it continues and drop my source table. It is just gone !!
If you are lucky enough, or rather a “data safe guardian”, you have RECYCLEBIN feature turned on on your database, otherwise, a restore will be needed (sigh) :

And ultimately, you can recover the table from the recyclebin with flashback :
 
So, you have to set the famous clause “whenever sqlerror exit” in your SQL*Plus session and you’ll be able to run safely the script :

Now the source table still exists, and you are able to fix the issue before running it again. But before the next run, don’t forget to drop the staging table, otherwise it will fail again !
On a simple script, we can see easily the errors, if needed, what to recover, and how to fix it. But most of the time, during project migration, there’s much more to build (e.g. Peopletools upgrade), and very bad surprise are not rare.

As I said at the beginning of this blog entry, this case is not new, it has always been like that (at least from Peopletools 8.18 I started on), but seeing Peopletools working closer and closer from the Oracle Database, I sincerely hope that will be included in the next Peopletools release.
Awaiting that fix, take care !

OT: Dell OpenManage (Server Administration) - bis

Last month I updated all the firmware of my server PowerEdge 2900 III implying the upgrade of the utility Dell OpenManage Server Administration (OMSA) as I explained here.
Since I was late of a couple of months on the versions, it raised a couple of errors that I was thinking to avoid by maintaining up-to-date the OMSA version. Today a new version as been released, the 6.5, let’s have it installed.
First, download from the Dell Drivers & Download Center. Then it should be a simple run of setup.sh script, since according to the documentation coming within the package, it is able to upgrade from a n-1 version (my current version is OMSA6.4).

A) Once gunziped and untared the downloaded package, run the setup.sh, unfortunately, it failed in my case :
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# ./setup.sh

##############################################

  Server Administrator Custom Install Utility

##############################################

  Server Administrator version 6.4.0 is currently installed.
  Installed components are:
   - srvadmin-argtable2-6.4.0-2.2.el5
   - srvadmin-omacore-6.4.0-1.195.2.el5
   - srvadmin-storelib-6.4.0-1.413.1.el5
   - srvadmin-isvc-6.4.0-1.98.1.el5
   - srvadmin-racdrsc-6.4.0-1.249.2.el5
   - srvadmin-racadm4-6.4.0-1.219.1.el5
   - srvadmin-omilcore-6.4.0-1.537.1.el5
   - srvadmin-xmlsup-6.4.0-1.231.1.el5
   - srvadmin-sysfsutils-6.4.0-1.3.el5
   - srvadmin-smweb-6.4.0-1.312.6.el5
   - srvadmin-storelib-sysfs-6.4.0-1.4.2.el5
   - srvadmin-racadm5-6.4.0-1.217.1.el5
   - srvadmin-omcommon-6.4.0-1.233.1.el5
   - srvadmin-smcommon-6.4.0-1.312.6.el5
   - srvadmin-jre-6.4.0-1.232.1.el5
   - srvadmin-storage-6.4.0-1.312.6.el5
   - srvadmin-hapi-6.4.0-1.52.1.el5
   - srvadmin-deng-6.4.0-1.59.1.el5
   - srvadmin-rac-components-6.4.0-1.249.2.el5

  Do you want to upgrade Server Administrator to 6.5.0?
  Press ('y' for yes | 'Enter' to exit): y
error: Failed dependencies:
        srvadmin-racadm5 = 6.4.0 is needed by (installed) dell_ie_rac_5-6.4.0-1.23.1.el5.x86_64
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]#

B) Trying to remove that RPM, it also failed :
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# rpm -e srvadmin-racadm5
error: Failed dependencies:
        srvadmin-racadm5 = 6.4.0 is needed by (installed) dell_ie_rac_5-6.4.0-1.23.1.el5.x86_64

C) Trying to remove that last one also failed :
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# rpm -e dell_ie_rac_5-6.4.0-1.23.1.el5.x86_64
error: Failed dependencies:
        dell_ie_rac_5 is needed by (installed) dell_ft_install-1.1-1.noarch

D) Finally, that the root one, this package is the installer utility for the firmware update, it can be re-installed later on, let’s remove all these RPMs :
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# rpm -e dell_ft_install-1.1-1.noarch
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# rpm -e dell_ie_rac_5-6.4.0-1.23.1.el5.x86_64
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# rpm -e srvadmin-racadm5

E) Now the setup. sh must be successful :
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# ./setup.sh

##############################################

  Server Administrator Custom Install Utility

##############################################

  Server Administrator version 6.4.0 is currently installed.
  Installed components are:
   - srvadmin-argtable2-6.4.0-2.2.el5
   - srvadmin-omacore-6.4.0-1.195.2.el5
   - srvadmin-storelib-6.4.0-1.413.1.el5
   - srvadmin-isvc-6.4.0-1.98.1.el5
   - srvadmin-racdrsc-6.4.0-1.249.2.el5
   - srvadmin-racadm4-6.4.0-1.219.1.el5
   - srvadmin-omilcore-6.4.0-1.537.1.el5
   - srvadmin-xmlsup-6.4.0-1.231.1.el5
   - srvadmin-sysfsutils-6.4.0-1.3.el5
   - srvadmin-smweb-6.4.0-1.312.6.el5
   - srvadmin-storelib-sysfs-6.4.0-1.4.2.el5
   - srvadmin-omcommon-6.4.0-1.233.1.el5
   - srvadmin-smcommon-6.4.0-1.312.6.el5
   - srvadmin-jre-6.4.0-1.232.1.el5
   - srvadmin-storage-6.4.0-1.312.6.el5
   - srvadmin-hapi-6.4.0-1.52.1.el5
   - srvadmin-deng-6.4.0-1.59.1.el5
   - srvadmin-rac-components-6.4.0-1.249.2.el5

  Do you want to upgrade Server Administrator to 6.5.0?
  Press ('y' for yes | 'Enter' to exit): y
Preparing...                ########################################### [100%]
   1:srvadmin-xmlsup        ########################################### [  6%]
   2:srvadmin-sysfsutils    ########################################### [ 11%]
   3:srvadmin-storelib-sysfs########################################### [ 17%]
   4:srvadmin-storelib      ########################################### [ 22%]
   5:srvadmin-hapi          ########################################### [ 28%]
   6:srvadmin-racadm4       ########################################### [ 33%]
   7:srvadmin-argtable2     ########################################### [ 39%]
   8:srvadmin-omilcore      ########################################### [ 44%]
     **********************************************************
     After the install process completes, you may need
     to log out and then log in again to reset the PATH
     variable to access the Dell OpenManage CLI utilities

     **********************************************************
   9:srvadmin-deng          ########################################### [ 50%]
  10:srvadmin-omcommon      ########################################### [ 56%]
  11:srvadmin-isvc          ########################################### [ 61%]
  12:srvadmin-smcommon      ########################################### [ 67%]
  13:srvadmin-omacore       ########################################### [ 72%]
  14:srvadmin-rac-components########################################### [ 78%]
  15:srvadmin-storage       ########################################### [ 83%]
  16:srvadmin-jre           ########################################### [ 89%]
  17:srvadmin-racdrsc       ########################################### [ 94%]
  18:srvadmin-smweb         ########################################### [100%]

   Do you want the Server Administrator services started?

   Press ('y' for yes | 'Enter' to exit): y
Starting Systems Management Device Drivers:
Starting dell_rbu:                                         [  OK  ]
Starting ipmi driver: Already started                      [  OK  ]
Starting Systems Management Data Engine:
Starting dsm_sa_datamgrd:                                  [  OK  ]
Starting dsm_sa_eventmgrd:                                 [  OK  ]
Starting dsm_sa_snmpd:                                     [  OK  ]
Starting DSM SA Shared Services:                           [  OK  ]

[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]#

F) I noticed that for some reasons the Web Server component is not getting installed, re-run the setup.sh and add that one :
[root@hercules /nfs/software/Utils/dell/OpenManage/6.5_RHEL5]# ./setup.sh

##############################################

  Server Administrator Custom Install Utility

##############################################

  Components for Server Administrator Managed Node Software:

  Installed Components:
    [x] Server Instrumentation
    [x] Storage Management

  Add Components
    [ ] 1. Server Administrator Web Server
    [ ] 2. Remote Enablement
    [ ] 3. Remote Access Core Components
    [ ] 4. Remote Access SA Plugin Components
    [ ] 5. All

  Enter the number to select a component from the above list.
  Enter q to quit.

  Enter : 1

##############################################

  Server Administrator Custom Install Utility

##############################################

  Selected Options
   - Server Administrator Web Server

  Dependencies

  Components for Server Administrator Managed Node Software:

  Installed Components:
    [x] Server Instrumentation
    [x] Storage Management

  Add Components
    [x] 1. Server Administrator Web Server
    [ ] 2. Remote Enablement
    [ ] 3. Remote Access Core Components
    [ ] 4. Remote Access SA Plugin Components
    [ ] 5. All

  Enter the number to select a component from the above list.
  Enter c to copy selected components to destination folder.
  Enter i to install the selected components.
  Enter r to reset selection and start over.
  Enter q to quit.

  Enter : i
Installing the selected packages.

Preparing...                ########################################### [100%]
   1:srvadmin-iws           ########################################### [100%]

   Do you want the Server Administrator services started?

   Press ('y' for yes | 'Enter' to exit): y
Starting Systems Management Device Drivers:
Starting dell_rbu:                                         [  OK  ]
Starting ipmi driver: Already started                      [  OK  ]
Starting Systems Management Data Engine:
Starting dsm_sa_datamgrd:                                  [  OK  ]
Starting dsm_sa_eventmgrd:                                 [  OK  ]
Starting dsm_sa_snmpd:                                     [  OK  ]
Starting DSM SA Shared Services:                           [  OK  ]

Starting DSM SA Connection Service:                        [  OK  ]

G) Now, from a web browser, check the OMS version within the default url port 1311 (https://hercules.phoenix-nga:1311/servlet/OMSALogin?msgStatus=null):
 
Done ! Version has been updated to 6.5.

H) Since I removed dell_ft_install-1.1-1.noarch, I reinstall it again, it will reinstall the all three RPMs uninstalled earlier to get OMSA upgraded :
[root@hercules /root]# yum install dell_ft_install
Loaded plugins: dellsysid, fastestmirror
Loading mirror speeds from cached hostfile
* addons: mirror.leaseweb.com
* base: mirror.leaseweb.com
* extras: mirror.leaseweb.com
* updates: mirror.leaseweb.com
Excluding Packages in global exclude list
Finished
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package dell_ft_install.noarch 0:1.1-1 set to be updated
--> Processing Dependency: dell_ie_rac_5 for package: dell_ft_install
--> Running transaction check
---> Package dell_ie_rac_5.x86_64 0:6.4.0-1.23.1.el5 set to be updated
--> Processing Dependency: srvadmin-racadm5 = 6.4.0 for package: dell_ie_rac_5
--> Running transaction check
---> Package srvadmin-racadm5.x86_64 0:6.4.0-1.217.1.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================== Package                                      Arch                               Version                                       Repository                                  
Size
==============================================================================================
Installing:
dell_ft_install                              noarch                             1.1-1                                         dell-omsa-indep                             2.7 k
Installing for dependencies:
dell_ie_rac_5                                x86_64                             6.4.0-1.23.1.el5                              dell-omsa-indep                             4.3 k
srvadmin-racadm5                             x86_64                             6.4.0-1.217.1.el5                             dell-omsa-indep                             961 k

Transaction Summary
==============================================================================================
Install       3 Package(s)
Upgrade       0 Package(s)

Total download size: 968 k
Is this ok [y/N]: y
Downloading Packages:
(1/3): dell_ft_install-1.1-1.noarch.rpm                                              | 2.7 kB
(2/3): dell_ie_rac_5-6.4.0-1.23.1.el5.x86_64.rpm                                     | 4.3 kB
(3/3): srvadmin-racadm5-6.4.0-1.217.1.el5.x86_64.rpm                                 | 961 kB 
----------------------------------------------------------------------------------------------Total                                                                                                                                            221 kB/s | 968 kB     00:04
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : srvadmin-racadm5                                         1/3
  Installing     : dell_ie_rac_5                                            2/3
  Installing     : dell_ft_install                                          3/3

Installed:
  dell_ft_install.noarch 0:1.1-1

Dependency Installed:
  dell_ie_rac_5.x86_64 0:6.4.0-1.23.1.el5                                              
srvadmin-racadm5.x86_64 0:6.4.0-1.217.1.el5

Complete!
[root@hercules /root]#

I) Now, a quick check of the firmware version :
[root@hercules /root]# update_firmware

Running system inventory...

Searching storage directory for available BIOS updates...
Checking SAS/SATA Backplane 0:0 Backplane Firmware - 1.05
        Available: dell_dup_componentid_11204 - 1.05
        Did not find a newer package to install that meets all installation checks.
Checking System BIOS for PowerEdge 2900 - 2.6.1
        Did not find a newer package to install that meets all installation checks.
Checking PERC 6/i Integrated Controller 0 Firmware - 6.3.0-0001
        Available: pci_firmware(ven_0x1000_dev_0x0060_subven_0x1028_subdev_0x1f0c) - 6.3.0-0001
        Did not find a newer package to install that meets all installation checks.
Checking BMC - 2.37
        Available: dell_dup_componentid_05814 - 2.37
        Did not find a newer package to install that meets all installation checks.
Checking NetXtreme II BCM5708 Gigabit Ethernet rev 12 (eth1) - 6.0.1
        Available: pci_firmware(ven_0x14e4_dev_0x164c) - 6.0.1
        Did not find a newer package to install that meets all installation checks.
Checking BIOS - 2.6.1
        Available: dell_dup_componentid_00159 - 2.6.1
        Did not find a newer package to install that meets all installation checks.
Checking NetXtreme II BCM5708 Gigabit Ethernet rev 12 (eth0) - 6.0.1
        Available: pci_firmware(ven_0x14e4_dev_0x164c) - 6.0.1
        Did not find a newer package to install that meets all installation checks.
Checking ST3500620SS Firmware - ms04
        Available: dell_dup_componentid_16861 - ms0c
        Did not find a newer package to install that meets all installation checks.

This system does not appear to have any updates available.
No action necessary.

[root@hercules /root]#

Enjoy !