Oracle 11.2.0.1 install on OEL5.3 64 bits

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

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

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

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

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

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

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

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

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"

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

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

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,

Modify NLS_LENGTH_SEMANTICS online

From Peopletools 8.48, when working on a UNICODE Oracle database, it is recommanded, do not say mandatory, to set the NLS_LENGTH_SEMANTICS to CHAR to be able to enter multi-bytes characters.

By default, NLS_LENGTH_SEMANTICS is set to BYTE.
This is a problem if you didn't modify this parameter when started the database, you cannot change it afterwards online without bouncing the database.
If you can change the value for your own session (alter session) and everything works fine and you can play with the new value, if you do it on system level (alter system), the new value is not taken in account (however, the alter system command does not return any error !):
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';

NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics BYTE TRUE


SQL> alter system set nls_length_semantics=char;

System altered.

SQL> create table sysadm.nicolas(column1 varchar2(10));

Table created.

SQL> select column_name,data_type,data_length,char_length,char_used
from dba_tab_columns where table_name='NICOLAS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 10 10 B
As you can see, the character used is BYTE, not CHAR as it is expected after parameter value change.

The only workaround to this, is to change the spfile and bounce the database :
SQL> alter system set nls_length_semantics=char scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2074664 bytes
Variable Size 746588120 bytes
Database Buffers 318767104 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> drop table sysadm.nicolas;

Table dropped.

SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';

NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics CHAR FALSE


SQL> create table sysadm.nicolas(column1 varchar2(10));

Table created.

SQL> select column_name,data_type,data_length,char_length,char_used from dba_tab_columns where table_name='NICOLAS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 30 10 C

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Now it is becoming CHAR as expected from the beginning.

I found it disapointed.

Addendum : it is an known bug #1488174 (internal bug, non-public), opened several years ago on database version 8.x, not solved yet. Find out more in the metalink note #144808.1.

Create index with compute statistics

When you are (re)building an index on Peoplesoft with in Application designer, Peoplesoft is writing a sql script to (re)create the index on the database level.
The statement of index creation contains "compute statistics" option.
If in most of cases there is no issue in that, sometimes you could have some strange behaviour behind this simple statement.

Some times ago, on one of our 9.2 database, we had a query which was not fast as expected. Just to try, we decided to create a new index. It was still not fast enough, then drop this new index.
Strangely, this index creation makes Oracle optimizer change his behaviour comparing before the index (creation and drop) and start to use an other explain plan.
How comes ? We created and drop a new index (through Application Designer) and Oracle "decided" to change the execution plan, hmmm, strange.
After looking deeper, it appears the table statistics had been updated by the index creation containing "compute statistics".

Example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------


SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> drop index idx;

Index dropped.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------
14 20/03/09

But, in some cases, I wouldn't want the table statistics to be updated by my index creation. That's bad, that does not happen without this option "compute statistics" :
SQL> create index idx on emp(ename);

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Finally, found an event to work around and avoid that table statistics update :
SQL> alter session set events '8130 trace name context forever';

Session altered.

SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Ah, now that's better, now if I drop this index, my explain plan won't change as before the index.

This case has been tested on 9.2.0.6 and 9.2.0.8. Seems not to be a problem anymore in the higher version.

Enjoy,

Peoplesoft 8.49 on OEL 64-bits

As I wrote few days ago, Peoplesoft on Linux 64-bits is now supported on OEL5.2 min (and also RHEL) from Peopletools 8.49.14 and higher.
The Peoplesoft binaries are the same for 64-bits and 32-bits OS.
So, you could either install the same Peoplesoft (application binaries and Peopletools binaries) you already have for your 32-bits Linux, or much more simple, just copy the entire PS_HOME over the network.
There is no issue about that.

The slight difference is for the Oracle libraries.
Do not forget, Peoplesoft is still working with in the 32-bits Oracle libraries, so you have to create a link to the 32-bits librairies inside the 64-bits folder.
If on a 32-bits OS you have to create the following link :
[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 libclntsh.so.9.0

On a 64-bits Oracle binaries, it must be the following :
[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s $ORACLE_HOME/lib32/libclntsh.so.11.1 libclntsh.so.9.0

Apart from that, everything is same and works fine.

This migration could be a good step before managing the coming Peopletools 8.50 upgrade, because it has been told the Peopletools 8.50 will be a real 64-bits application, moreover it seems it will be supported only on Linux 64-bits, the certification on a 32-bits Linux system will be retired.
Read pages 34 and 43 of PeopleTools Roadmap - OOW 2008

Enjoy,

A database move from 32-bits to 64-bits

Today, a move of a database from OEL5.3 32-bits to OEL5.3 64-bits. The database is running on Oracle 11.1.0.7 32-bits on source.

Firstly, be sure your target OS is a 64-bits OS :
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ uname -m
x86_64

And also an Oracle software 64-bits :
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ file oracle
oracle: setuid setgid ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

Shut the source database down, and copy to the new pletform all the files (including spfile or pfile).
Then on the 64-bits OS, start the database in upgrade mode and run utlirp.sql script, that'll invalidate all the objects :
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ export ORACLE_SID=DMOCRM9
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 15 11:16:14 2009

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

Connected to an idle instance.

SQL> startup upgrade pfile='/apps/oracle/admin/DMOCRM9/pfile/initDMOCRM9.ora'
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2163600 bytes
Variable Size 197135472 bytes
Database Buffers 557842432 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
[...]

Finally, restart your database in normal mode, and run utlrp.sql script to recompile all the invalidated objects :
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/apps/oracle/admin/DMOCRM9/pfile/initDMOCRM9.ora'
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2163600 bytes
Variable Size 197135472 bytes
Database Buffers 557842432 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem
[...]

Check the status of the objects onto the database :
SQL> select * from dba_registry
SQL> /

CATALOG
Oracle Database Catalog Views
11.1.0.7.0 VALID 15-MAR-2009 11:47:55
SERVER SYS
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG



CATPROC
Oracle Database Packages and Types
11.1.0.7.0 VALID 15-MAR-2009 11:47:55
SERVER SYS

SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC

DBSNMP,OUTLN,SYSTEM,TSMSYS


SQL>
SQL> select count(*) from dba_objects where status != 'VALID';

COUNT(*)
----------
0

Lastly, you can be happy with your 64-bits database :
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 15 12:01:13 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
[...]

Peoplesoft on Linux 64-bits

Good news for the Linux 64-bits customers. Peoplesoft announced the 3rd of March'09 they are now certified on Linux 64-bits (OEL/RHEL), please see the metalink3 note :
Oracle PeopleSoft extends certification of OEL and RHEL 5 x86-64

The requirements for 64-bits appliance are OEL5.2 or RHEL5.2 minimum, and Peopletools 8.49.14 min.

Now, need to be tested on my lab...

OTN forums : 28 weeks later

Following the previous post I wrote last September (28 days later), today 28 weeks later, in reference to an other horror movie, is a logical way to go right now.
So, 28 weeks later, are there any improvements in OTN Forums ?
No, not really. Even worse. As a "birthday" present, today a new issue appears (again) with the email address...

Less and less valuable people are particpating, people does not learn how to give points or "close" thread by marking thread as answered when it is not, forums slowness and often up-down effects, and of course, still these bloody markups we cannot avoid anymore, and still no getting back the red bullets to mark threads as (un)read.

It seems the upgrade of last summer was a downgrade, nothing more. And despite all the complaints of OTN members, nothing has been done to really improve the OTN forums.

Finally, why continue to use it ?

Move Peoplesoft install to a new server

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

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

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

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


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

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

Nicolas.

Oracle upgrade 10.2.0.4 to 11.1.0.7

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

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

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

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

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

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

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

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

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