Monday, March 28, 2011

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 : 
TableDDL_013
Now, I run SETSPACE.SQR : 
TableDDL_012
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 : 
TableDDL_014
And if I rebuild the table, the partitions are simply lost, the table going to PTTBL ! 
TableDDL_015
To retrieve the partitions, put back the table in the original tablespace CUAUDIT, and rebuild : 
TableDDL_016  
TableDDL_017
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 !

Sunday, March 27, 2011

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) :
TableDDL_001 
The table PS_MYTABLE is known from AppDesigner, but even being partitioned, we don’t see it from AppDesigner :
TableDDL_002
TableDDL_003
 TableDDL_004
The DDL here is not coming from the database, but from the AppDesigner definition.
Let’s rebuild the table and see the generated script :
TableDDL_005
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 :
TableDDL_006
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 :
TableDDL_007 
Then rebuild the table (even forcing the index recreation) :
TableDDL_008
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 (!) :
TableDDL_009 
And now, rebuild the underlying table PS_MYTABLE:
TableDDL_010
=> 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 :
 WheneverSqlErrorExit_023
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 :
 WheneverSqlErrorExit_020
The generated script is very simple, as follow :
WheneverSqlErrorExit_021 
Then, running the script in a SQL*Plus session :
WheneverSqlErrorExit_022 
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 :
WheneverSqlErrorExit_006 
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) :
WheneverSqlErrorExit_007 
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) :
WheneverSqlErrorExit_024
WheneverSqlErrorExit_025
WheneverSqlErrorExit_027
And the script is now :
WheneverSqlErrorExit_028 
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 :
WheneverSqlErrorExit_029
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) :
WheneverSqlErrorExit_016
And ultimately, you can recover the table from the recyclebin with flashback :
 WheneverSqlErrorExit_017
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 :
WheneverSqlErrorExit_030
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 !

Tuesday, March 22, 2011

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):
OMSA65_001 
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 !

Monday, March 14, 2011

PSOVM and Active Data Guard : Appserver

After setting a Physical Standby Database and enable Active Data Guard, this part assume you’ve done all the requirements to run the batch process onto the read-only database, as explained in earlier post : secondary ACCESSID, secondary SYMBOLICID, database link, local synonyms and remote synonyms must have been configured.
This part will be focused on the Application Server which also can be configured to run onto the read-only database for few elements. Working on PSOVM make the configuration even easier for testing and learning purpose, it is coming with the latest Peopletools version, 8.51, which come with that configuration option in psappsrv.cfg :
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=
DBType=
UserId=
UserPswd=
ConnectId=
ConnectPswd=
ServerName=
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=

That option will work only for an enable Oracle Active Data Guard.
At the end of this stage, the global architecture of my PSOVM environment will be as follow (in green the changes compared to the previous post) :
PS_ACTIVEDG_051 1. Required scripts
Please follow instruction given for the Batch server configuration on Active Data Guard, step 1.

2. Create a secondary ACCESSID/SYMBOLICID on primary db – psadmin.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 2.

3. Create a dedicated application user attached to the secondary SYMBOLICID
Please follow instruction given for the Batch server configuration on Active Data Guard, step 3.

4. Create database link on primary db to the primary db – createdblinktoprimarydb.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 4.

5. Create local synonyms – createlocalsynonyms.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 5.

6. Create remote synonyms – createremotesynonyms.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 6.

7. Application server configuration
7.1
Be sure the tnsnames.ora is properly configured, please follow instruction given for the Batch server configuration on Active Data Guard, step 8.1.

7.2 Modify psappsrv.cfg
Set the standby database and standby user :
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=H91TMPLT
DBType=ORACLE
UserId=PS
UserPswd=PS
ConnectId=people
ConnectPswd=peop1e
ServerName=____
StandbyDBName=H91STBY
StandbyDBType=ORACLE
StandbyUserId=PSBKP
StandbyUserPswd=PSBKP

7.3 Reconfigure the application server APPDOM (psadmin tool)
[psadm2@psovmhcm PRCSDOM]$ cd $PS_HOME/appserv
[psadm2@psovmhcm appserv]$ ./psadmin

PSADMIN -- Tools Release: 8.51.02
Copyright (c) 1996, 2010, Oracle. All rights reserved.

--------------------------------
PeopleSoft Server Administration
--------------------------------

  1) Application Server
  2) Process Scheduler
  3) Search Server
  4) Replicate Config Home
  q) Quit

Command to execute (1-4, q): 1

--------------------------------------------
PeopleSoft Application Server Administration
--------------------------------------------

  1) Administer a domain
  2) Create a domain
  3) Delete a domain
  4) Import domain configuration
  q) Quit

Command to execute (1-4, q) : 1

Domain list:

   1) APPDOM

Select domain: 1

--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: APPDOM

  1) Boot this domain
  2) Domain shutdown menu
  3) Domain status menu
  4) Configure this domain
  5) TUXEDO command line (tmadmin)
  6) Edit configuration/log files menu
  7) Messaging Server Administration menu
  8) Purge Cache
  9) Preload Cache
10) Clean IPC resources of this domain
  q) Quit

Command to execute (1-10, q) : 4

This option will shutdown the domain.
Do you want to continue? (y/n) [n] :y

tmadmin - Copyright (c) 2007-2008 Oracle.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by Oracle.
Tuxedo is a registered trademark.

> INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level 031

Booting admin processes ...

exec BBL -A :
        CMDTUX_CAT:821: INFO: Duplicate server.
0 processes started.

> Shutting down all admin and server processes in /home/psadm2/psft/pt/8.51/appserv/APPDOM/PSTUXCFG

Shutting down server processes ...
<…snipped…>
Shutting down admin processes ...

        Server Id = 0 Group Id = psovmhcm.phoenix.nga Machine = psovmhcm.phoenix.nga:   shutdown succeeded
18 processes stopped.

All domain processes have stopped.

----------------------------------------------
Quick-configure menu -- domain: APPDOM
----------------------------------------------
      Features                      Settings
     ==========                    ==========
  1) Pub/Sub Servers   : Yes   15) DBNAME     :[H91TMPLT]
  2) Quick Server      : No    16) DBTYPE     :[ORACLE]
  3) Query Servers     : Yes   17) UserId     :[PS]
  4) Jolt              : Yes   18) UserPswd   :[PS]
  5) Jolt Relay        : No    19) DomainID   :[APPDOM]
  6) WSL               : No    20) AddToPATH  :[.]
  7) PC Debugger       : No    21) ConnectID  :[people]
  8) Event Notification: No    22) ConnectPswd:[peop1e]
  9) MCF Servers       : No    23) ServerName :[____]
10) Perf Collator     : No    24) WSL Port   :[7000]
11) Analytic Servers  : Yes   25) JSL Port   :[9000]
12) Domains Gateway   : No    26) JRAD Port  :[9100]

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

HINT: Enter 15 to edit DBNAME, then 13 to load

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

Log Directory entry not found in configuration file.
Setting Log Directory to the default... [PS_SERVDIR/LOGS]
Spawning disabled for server PSAPPSRV.
Spawning disabled for server PSQRYSRV.
Spawning disabled for server PSBRKHND_dflt.
Spawning disabled for server PSPUBHND_dflt.
Spawning disabled for server PSSUBHND_dflt.
Configuration file successfully created.
CFG setting changes completed
Loading configuration...
Domain configuration complete.

7.4 Restart the application server
--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: APPDOM

  1) Boot this domain
  2) Domain shutdown menu
  3) Domain status menu
  4) Configure this domain
  5) TUXEDO command line (tmadmin)
  6) Edit configuration/log files menu
  7) Messaging Server Administration menu
  8) Purge Cache
  9) Preload Cache
10) Clean IPC resources of this domain
  q) Quit

Command to execute (1-10, q) :  1

-------------------------------
PeopleSoft Domain Boot Menu
-------------------------------
     Domain Name: APPDOM

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

Command to execute (1-2, q) [q]: 1

Archived a copy of the domain configuration to /home/psadm2/psft/pt/8.51/appserv/APPDOM/Archive/psappsrv.cfg
Attempting to boot bulletin board...
tmadmin - Copyright (c) 2007-2008 Oracle.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by Oracle.
Tuxedo is a registered trademark.
No bulletin board exists. Entering boot mode.

> INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level 031

Booting admin processes ...

exec BBL -A :
        process id=1905 ... Started.
1 process started.
Attaching to active bulletin board.

> Attempting to boot ...
INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level 031

Booting server processes ...

exec PSWATCHSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -ID 152926 -D APPDOM -S PSWATCHSRV :
        process id=1909 ... Started.

<snipped>

exec JREPSVR -o ./LOGS/stdout -e ./LOGS/stderr -A -- -W -P /home/psadm2/psft/pt/8.51/appserv/APPDOM/jrepository :
        process id=1975 ... Started.
17 processes started.

7.5 On secondary database, check for the connections
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM       MACHINE                   PROGRAM                                          MODULE
----------- ------------------------- ------------------------------------------------ --------------------------------------
SYSBKP      psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSBKP      psovmhcm.phoenix.nga      PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSBKP      psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSBKP      psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSBKP      psovmhcm.phoenix.nga      PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSBKP      psovmhcm.phoenix.nga      PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
SYS         psovmhcmdb2               sqlplus@psovmhcmdb2 (TNS V1-V3)                  sqlplus@psovmhcmdb2 (TNS V1-V3)

26 rows selected.

We can see now all the processes coming from the batch server as well as all the processes coming from the application server.

8. Test
8.1 The Application server is able to run a read-only component onto the secondary database. Let’s choose a simple one.
PS_ACTIVEDG_038 
Onto the primary database, run the following :
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM       MACHINE                    PROGRAM                                          MODULE
----------- -------------------------- ------------------------------------------------ --------------------------------------
SYSADM      WORKGROUP\PHOENIX          pside.exe                                        pside.exe
SYSADM      psovmhcm.phoenix.nga       PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM      psovmhcm.phoenix.nga       PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM      psovmhcm.phoenix.nga       PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        HR_DR_SELECTION_UI
SYSADM      psovmhcm.phoenix.nga       PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PROCESSMONITOR
PUBLIC      psovmhcmdb2                oracle@psovmhcmdb2 (TNS V1-V3)                   oracle@psovmhcmdb2 (TNS V1-V3)
SYS         psovmhcmdb.phoenix.nga     sqlplus@psovmhcmdb.phoenix.nga (TNS V1-V3)       sqlplus@psovmhcmdb.phoenix.nga (TNS
On the output above, in bold, the component we’ll work on (HR_DR_SELECTION_UI), this component in that test will read data only without updated anything, so a good candidate for the test.

8.2 Change the component property
Open the Application Designer, open that component :
PS_ACTIVEDG_039
Go to the properties definitions
PS_ACTIVEDG_040 
Go to the tab “Use”
PS_ACTIVEDG_041
Check the read-only checkbox, click ok and save
 PS_ACTIVEDG_042 PS_ACTIVEDG_043
PS_ACTIVEDG_044

8.3 Go back to the front-end application, and run the transaction
 PS_ACTIVEDG_046PS_ACTIVEDG_047
PS_ACTIVEDG_048 

8.4 Whilst running the transaction, check the back-end
On the primary site, run the following :
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM      MACHINE                   PROGRAM                                          MODULE
---------- ------------------------- ------------------------------------------------ ----------------------------------------
SYSADM     WORKGROUP\PHOENIX         pside.exe                                        pside.exe

SYSADM     psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSADM     psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM     psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM     psovmhcm.phoenix.nga      PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        HR_DR_SELECTION_UI
PUBLIC     psovmhcmdb2               oracle@psovmhcmdb2 (TNS V1-V3)                   oracle@psovmhcmdb2 (TNS V1-V3)
SYS        psovmhcmdb.phoenix.nga    sqlplus@psovmhcmdb.phoenix.nga (TNS V1-V3)       sqlplus@psovmhcmdb.phoenix.nga (TNS V1-

We still can see that component running onto the primary database, what about the secondary database.

On the secondary site, run the following :
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM     MACHINE                 PROGRAM                                          MODULE
--------- ----------------------- ------------------------------------------------ -------------------------------------------
SYSBKP    psovmhcm.phoenix.nga    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        HR_DR_SELECTION_UI
SYSBKP    psovmhcm.phoenix.nga    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
SYS       psovmhcmdb2             sqlplus@psovmhcmdb2 (TNS V1-V3)                  sqlplus@psovmhcmdb2 (TNS V1-V3)

Ok, the HR_DR_SELECTION_UI we defined earlier as read-only component is running on the secondary database. However similarly to the batch server which run AE onto the secondary database, it runs few queries onto the primary database. Let’s have a look into a trace file.

8.5 Set the TraceSQL=7
After setting the TraceSQL, re-run the same transaction as above, and check for the generated file. Here below an extract of that file :
…<snipped>…
H91TMPLT RC=0 Dur=0.000007 Disconnect
H91TMPLT RC=0 Dur=0.000005 Commit
H91TMPLT RC=0 Dur=0.000007 Disconnect
notSamTran RC=0 Dur=0.000028 Open Cursor Handle=000000000FC0DB08
notSamTran RC=0 Dur=0.000009 Open Cursor Handle=000000000FC0DB08
H91TMPLT RC=0 Dur=0.000014 Disconnect
H91TMPLT RC=0 Dur=0.000004 Commit
H91TMPLT RC=0 Dur=0.000007 Disconnect
notSamTran RC=0 Dur=0.000011 Open Cursor Handle=000000000FC0DB08
notSamTran RC=0 Dur=0.000008 Open Cursor Handle=000000000FC0DB08
H91TMPLT RC=0 Dur=0.000008 Disconnect
H91TMPLT RC=0 Dur=0.000004 Commit
H91TMPLT RC=0 Dur=0.000006 Disconnect
notSamTran RC=0 Dur=0.000011 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000005 Commit
H91STBY RC=0 Dur=0.000008 Disconnect
notSamTran RC=0 Dur=0.000008 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000148 COM Stmt=SELECT A.TEXT_ID , TO_CHAR(A.EFFDT,'YYYY-MM-DD') , A.HR_SSTEXT_KEY1 , A.HR_SSTEXT_KEY2 ,
H91STBY RC=0 Dur=0.000005 Bind-1 type=2 length=3 value=HHR
H91STBY RC=0 Dur=0.000005 Bind-2 type=2 length=4 value=DRPT
H91STBY RC=0 Dur=0.000005 Bind-3 type=2 length=11 value=GROUP_INSTR
H91STBY RC=0 Dur=0.000004 Bind-4 type=26 length=10 value=2011-03-14
H91STBY RC=0 Dur=0.000004 Bind-5 type=2 length=13 value=HR_EE_INF_MGR
H91STBY RC=0 Dur=0.000004 Bind-6 type=2 length=1 value=
H91STBY RC=0 Dur=0.000004 Bind-7 type=2 length=1 value=
H91STBY RC=0 Dur=0.000005 Bind-8 type=2 length=1 value=
H91STBY RC=0 Dur=0.000005 Bind-9 type=2 length=1 value=
H91STBY RC=0 Dur=0.000004 Bind-10 type=2 length=1 value=
H91STBY RC=0 Dur=0.000006 Commit
H91STBY RC=0 Dur=0.000043 Disconnect
notSamTran RC=0 Dur=0.000013 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000006 Commit
H91STBY RC=0 Dur=0.000008 Disconnect
notSamTran RC=0 Dur=0.000009 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000126 COM Stmt=SELECT A.TEXT_ID , TO_CHAR(A.EFFDT,'YYYY-MM-DD') , A.HR_SSTEXT_KEY1 , A.HR_SSTEXT_KEY2 ,
H91STBY RC=0 Dur=0.000005 Bind-1 type=2 length=3 value=HHR
H91STBY RC=0 Dur=0.000005 Bind-2 type=2 length=4 value=DRPT
H91STBY RC=0 Dur=0.000006 Bind-3 type=2 length=15 value=GROUP_GROUP_HDR
H91STBY RC=0 Dur=0.000006 Bind-4 type=26 length=10 value=2011-03-14
H91STBY RC=0 Dur=0.000005 Bind-5 type=2 length=13 value=HR_EE_INF_MGR
H91STBY RC=0 Dur=0.000006 Bind-6 type=2 length=1 value=
…<snipped>…
Like for the batch server, the application server start running queries onto the primary database (H91TMPLT), then switch to the secondary database (H91STBY) for the main part of the component.

So, now we have batch server and application server able to run some queries onto the secondary database in read-only mode. But be aware, none of the Peoplesoft processes are purely read-only, that’s the reason it’s still continue to start/end transactions onto the primary database, and also the need for the remote synonyms.

Enjoy it !

Nicolas.