Prompt-free Appliance deployment : start the VM

This second last part of the “Prompt-free Appliance deployment” is about showing up the very first start of the VM after script modification.
As we saw in a previous thread, there was some special steps to mount properly the disk to be able to modify the scripts.
So, here it’s much more simple:

1. Unmount the disk
[root@omsa:/nfs/software/PeopleSoftCD/OVA]# umount /mnt/HCMDB-SES-85302d

2. Free up the loop devices which have been mounted:
[root@omsa:/nfs/software/PeopleSoftCD/OVA]# losetup -d /dev/loop1
[root@omsa:/nfs/software/PeopleSoftCD/OVA]# losetup -d /dev/loop0

3. Push back the vmdk disk to ESXi:
[root@omsa:/nfs/software/PeopleSoftCD/OVA/HCMDB-SES-85302d]# vifs --server 192.168.1.10:443 --username root --password pwd --put HCMDB-SES-85302d-esxi/HCMDB-SES-85302d-flat.vmdk '[vm] HCMDB-SES-85302d/HCMDB-SES-85302d-flat.vmdk'
Uploaded file HCMDB-SES-85302d-esxi/HCMDB-SES-85302d-flat.vmdk to HCMDB-SES-85302d/HCMDB-SES-85302d-flat.vmdk successfully.
[root@omsa:/nfs/software/PeopleSoftCD/OVA/HCMDB-SES-85302d]#

4. Start the VM:

With the ESXi client, start the VM Appliance:


You won’t be prompted for the license agreement, no root password question…


You won’t be prompted for the network settings neither:


The database name will be automatically filled in within the given name, and no question about SES installation anymore:


The VMWare ESXi tools will be installed automatically with all the default options:


And finally, you can check the VMWare are installed without manually tasks:


Eventually, you can connect to the front end application: 


Now, we have to find a way to script everything, it will be describe in the next and last part.

Nicolas.

Prompt-free Appliance deployment : scripts modification

In this third part of the “Prompt-free Appliance deployment” series, we will see what scripts have to be modified and how to suppress the prompt on the very first VM startup.

After mounting the disk as described in the previous step we can go further and one by one, take the prompt and the corresponding piece of script.

The main script is /opt/oracle/psft/vm/oraclevm-template.sh. Everything will start/end out there.

1. License agreement
This is the very first prompt you are facing to.
Most likely you want to respond yes, otherwise everything will stop right away.

Taken the file /opt/oracle/psft/vm/oraclevm-template.sh, we can easily see where this prompt comes from:
# display the banner if it exists and greater than 0 size
if [ -s $SCRIPT_PATH/.banner ]; then
    DISPLAY_BANNER=$(<$SCRIPT_PATH/.banner)
    echo ""
    echo "$DISPLAY_BANNER"
    echo ""
    echo -n "Do you want to proceed [y|n]: "
    GetYNResponse
    if [ $? == 0 ]; then
        # user decided not to proceed, exit the VM
        echo ""
        echo "You have decided not to proceed with the initialization, "
        echo "the VM will be shutdown now"
        echo ""
        ovm_press_anykey 5
        shutdown -hP now
    else
        echo ""
    fi
fi

And looking into the directory we can see that hidden file .banner:
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# ls -la|grep banner
-r--r--r-- 1 root root   567 Apr  1 07:50 .banner
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]#

The content of the file is actually the prompt:
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# more .banner
***SECURITY WARNING***

This virtual appliance has been constructed for ease of installation and
reduced setup time compared to a normal PeopleSoft installation using
downloaded media. It utilizes default user id and passwords throughout the
included tech stack in order to accomplish this.  If your installation
requires a more secure environment please utilize the information in the
Oracle Support Document 747524.1 (Securing Your PeopleSoft Application
Environment) which can be found at:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=747524.1

The easiest way to supress the license agreement prompt will probably to rename this .banner file:
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# mv .banner .banner.orig

2. The root password
If you read the script /opt/oracle/psft/vm/oraclevm-template.sh it is starting with calling a script /usr/lib/oraclevm-template/functions. This one actually contains a lot of functions which are called later on.
The one to set the password:
…
# Set password
# $1 - username
# $2 - password, if it's null, will prompt user to enter pasword
function ovm_set_password
{
    local username=$1
    local passwd=$2
    local encpasswd
    if [ -z "$username" ] || ! id "$username" >/dev/null 2>&1; then
        ovm_error "Username can't be null or does not exist."
        return 1
    fi
    if [ -n "$passwd" ]; then
        encpasswd=$(perl -e "print(crypt('$passwd','salt'),'\n')")
        usermod -p $encpasswd $username
    else
        while ! passwd $username; do
            :
        done
    fi
}
…
We need to call this function with username (root) and password. Please see part 5 below for the modification need.

3. Network settings
Similarly to the above with root’s password, the script /usr/lib/oraclevm-template/functions contains the function to configure the network:
…
# function ovm_configure_network
# Usage:
# 1. interactive mode
#  A. No argument specified. -  prompt user to select using DHCP or not.
#  B. $1 = "static" - force to configure static ip address.
#                     prompt user to enter ipaddress, netmask, gateway,
#                                          dns, hostname
# 2. silent mode
#  A. $1 = "dhcp", force to configure dhcp silently.
#  B. force to configure dhcp and set hostname silently.
#     $1 = "dhcp"
#     $2 = hostname
#  C. 5 arguments are required to configure static ip address silently.
#     $1 - ip address
#     $2 - netmask
#     $3 - gateway
#     $4 - dns server IP
#     $5 – hostname
…
Here, this function will need to be call with all the 5 parameters as described. Please see part 5 below for the modification need.

4. The database name
From the script oracle-template.sh, we can see the call to an other script, database dedicated:
#
# This function is called to setup a database on the local VM.
#
CheckCreateDBVirtualEnv() {

    RETURN_VALUE=0
    VBOX_SETUP=$1

    if [ -d $PS_DB_HOME ]; then
        . $SCRIPT_PATH/oraclevm-template-db.sh
        SourcePluginScript
        CreateVirtualEnvironment $VBOX_SETUP
        RETURN_VALUE=1
    else
        log_ovm_message "No PeopleTools Database environment to be created on this host ....skipping DB Setup"
    fi

    return $RETURN_VALUE
}

And, in this script, oraclevm-template-db.sh, the database name is defined as follow:
# get the current database name
DBNAME=`ls -l $PSFT_DB_HOME/oradata | grep '^d' | awk '{print $9}'`
DBSIDNAME=$DBNAME

if [ "$PROMPT_DBNAME" == "TRUE" ]; then
    # Prompt the user for database name
    GetUserInput
fi

The GetUserInput is actually a function:
GetUserInput() {

    echo ""
    echo -n "Enter the name of the database [$DBNAME]:"

    read VALUE
    if [ "$VALUE" != "" ]; then
        DBNAME=$VALUE
        DBSIDNAME=$DBNAME
    fi
}
To suppress the prompt, comment it out, and put the name you want:
GetUserInput() {

    echo ""
    #echo -n "Enter the name of the database [$DBNAME]:"

    #read VALUE
    VALUE="HR92DM00"
    if [ "$VALUE" != "" ]; then
        DBNAME=$VALUE
        DBSIDNAME=$DBNAME
    fi
}

5. Root password and network settings changes
Since the database is configured on this very virtual machine we are deploying, the root password and the network settings are both defined in the script oraclevm-template-db.sh.
It has already been modified for the database name (also see part 4 above when it is called).

Here, we can see the network function call:
# change the networking from static to variable
sed -i "s/ovm_configure_network \"static\"/ovm_configure_network/g" $ORACLE_DB_SCRIPT

It removes the “static” parameter to null… It should be change to put the 5 arguments needed to the network function as we’ve seen earlier. Also, whether there’s no the call to the password change function it must be change as well.
At the result we have the following:
# change the networking from static to variable
#sed -i "s/ovm_configure_network \"static\"/ovm_configure_network/g" $ORACLE_DB_SCRIPT
sed -i "s/ovm_configure_network.*/ovm_configure_network 192.168.1.20 255.255.255.0 192.168.1.254 192.168.2.254 hcm92000.phoenix.nga/" $ORACLE_DB_SCRIPT
sed -i "s/ovm_set_password.*/ovm_set_password root passw0rd/" $ORACLE_DB_SCRIPT

6. The SES installation
Finally, the last prompt your are seeing is the choice to install or not the Secure Enterprise Search (so called SES).
For this, we have to go back to the very first script, /opt/oracle/psft/vm/oraclevm-template.sh.
Here we go:
#
# This function is called to setup SES on the local VM.
#
CheckCreateSESVirtualEnv() {

    RETURN_VALUE=0
    VBOX_SETUP=$1

    if [ -d $PS_SES_HOME ]; then
        . $SCRIPT_PATH/oraclevm-template-search.sh

        USER_RESPONSE=1
        echo " "
        echo -n "Do you wish to setup Secure Search Enterprise (SES) on this VM [y|n]: "
        GetYNResponse
        USER_RESPONSE="$?"

        if [ "$USER_RESPONSE" == "1" ]; then
        CreateVirtualEnvironment $VBOX_SETUP

            # generate SES props
            PROP_FILE=$SCRIPT_PATH/ses.props
            echo DOMAIN_NAME=$APPSRVDOM >> $PROP_FILE
            echo DB_TYPE=$DBTYPE >> $PROP_FILE
            echo DB_NAME=$DBNAME >> $PROP_FILE
            echo DB_USER=$APPBATCH_PIA_USER >> $PROP_FILE
            echo DB_PWD=$APPBATCH_PIA_USER_PWD >> $PROP_FILE

            CheckConfigSES $PROP_FILE $IS_DEMO_SETUP $VBOX_SETUP
First you have to comment out the user input, and give the proper value to the parameter USER_RESPONSE depending you want to install it (1) or not (0). Here I don’t:
#
# This function is called to setup SES on the local VM.
#
CheckCreateSESVirtualEnv() {

    RETURN_VALUE=0
    VBOX_SETUP=$1

    if [ -d $PS_SES_HOME ]; then
        . $SCRIPT_PATH/oraclevm-template-search.sh

        USER_RESPONSE=1
        echo " "
        #echo -n "Do you wish to setup Secure Search Enterprise (SES) on this VM [y|n]: "
        #GetYNResponse
        #USER_RESPONSE="$?"
        USER_RESPONSE="0"

        if [ "$USER_RESPONSE" == "1" ]; then
        CreateVirtualEnvironment $VBOX_SETUP

            # generate SES props
            PROP_FILE=$SCRIPT_PATH/ses.props
            echo DOMAIN_NAME=$APPSRVDOM >> $PROP_FILE
            echo DB_TYPE=$DBTYPE >> $PROP_FILE
            echo DB_NAME=$DBNAME >> $PROP_FILE
            echo DB_USER=$APPBATCH_PIA_USER >> $PROP_FILE
            echo DB_PWD=$APPBATCH_PIA_USER_PWD >> $PROP_FILE

            CheckConfigSES $PROP_FILE $IS_DEMO_SETUP $VBOX_SETUP

7. VMWare tools
In the very end of the script, it could be very interesting to install automatically the VMWare tools without having to do it manually and without the need to mount a CD and so forth.

To achieve this, three steps:
7.1 Copy the iso file corresponding to your ESXi into /tmp
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# cp /nfs/software/Virtualization/VMWare/VMWare_vSphere/5.1u1/VMware-tools-linux-9.0.5-1065307.iso /mnt/HCMDB-SES-85302d/tmp
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]#
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# ls /mnt/HCMDB-SES-85302d/tmp
CVU_11.2.0.3.0_oracle  hsperfdata_oracle  hsperfdata_psadm3  logs  VMware-tools-linux-9.0.5-1065307.iso

7.2 Create a script to install the VMWare tools with all the default:
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# more vmware_tools.sh
mkdir -p /mnt/vmware-tools
mount -o loop /tmp/VMware-tools-linux-9.0.5-1065307.iso /mnt/vmware-tools
tar zxf /mnt/vmware-tools/VMwareTools-9.0.5-1065307.tar.gz -C /tmp
umount /mnt/vmware-tools
rmdir /mnt/vmware-tools
rm -f /tmp/VMware-tools-linux-9.0.5-1065307.iso
/tmp/vmware-tools-distrib/vmware-install.pl --default
rm -Rf /tmp/vmware-tools-distrib
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# chmod a+rx vmware_tools.sh

7.3 Modify /opt/oracle/psft/vm/oraclevm-template.sh and add the call to this new script
Within the main section, we can see the call to create the virtual environment:
main() {

    SetEnvVars

    SourcePluginScript

    if [ $# -eq 1 ] && [ "$1" == "--cleanup" ]; then
        CleanupVirtualEnv
    else
        if [ $# -eq 0 ]; then
            CreateVirtualEnv
        else
            UsageInstructions
        fi
    fi
}
Once it is all done, just run the VMWare tools script previously created:
main() {

    SetEnvVars

    SourcePluginScript

    if [ $# -eq 1 ] && [ "$1" == "--cleanup" ]; then
        CleanupVirtualEnv
    else
        if [ $# -eq 0 ]; then
            CreateVirtualEnv
            $SCRIPT_PATH/vmware_tools.sh
        else
            UsageInstructions
        fi
    fi
}

Here we saw all the modifications to make the VM starting without prompting for any user input, as it will be shown in the next and second last part of this series.
Whether the manual modification above are rather tedious, there will a script to do it for us, it will be the 5th and last part.

Nicolas.

Prompt-free Appliance deployment : mounting disk in r/w mode

As I expained in the previous blog entry, the intend of the “Prompt-free Applicance deployment” series is to avoid to enter manually all the VM details on the very first start. Whether there’re a lot of steps, in the end it all can be scripted.
It can be done by only one way: modification of the scripts started on VM startup. To do so, we must have access to the scripts which are hosted on one of the 5 given disks. As shown in that other blog entry, the first one is the one we want to go through.
Unfortunately, whether VMWare offers the utility vmware-mount, it cannot be in read/write mode, the vmdk being compressed.

There’s no straightaway and easy method. The only way I found is to push the VM to ESXi, get back the file corresponding to the first disk, mount it, modify the script, and push back the file.

Here we’ll see how to get back and mount the file corresponding to the first disk.

A) First of all, we should “push” the appliance to ESXi.

I’m using the tool OVFTool as below.
Create a local .ovftool file, it will be taken by the command line:
echo "lax" > .ovftool
echo "datastore=vm" >> .ovftool
echo "skipManifestCheck" >> .ovftool
echo "overwrite" >> .ovftool
echo "powerOffTarget" >> .ovftool
echo "net:HostOnly=VM Network 2" >> .ovftool
#echo "powerOn" >> .ovftool
echo "name="myVM_name >> .ovftool

Note that it should not be configured to start the VM automatically.
Then, run the command line:
ovftool inputVM.ovf vi://root:pwd@192.168.1.10:443


B) Second, get the file from ESXi.

Here we have first to check what is the file.
From ESXi server, we have something like:
/vmfs/volumes/506c2d23-01dbe48d-7940-001ec9deb63b/HCMDB-SES-85302d # ls -l
-rw-------    1 root     root     7543455744 Jun  5 19:12 HCMDB-SES-85302d-flat.vmdk
-rw-------    1 root     root           472 Jun  5 19:11 HCMDB-SES-85302d.vmdk
-rw-r--r--    1 root     root             0 Jun  5 19:11 HCMDB-SES-85302d.vmsd
-rwxr-xr-x    1 root     root          1766 Jun  5 19:11 HCMDB-SES-85302d.vmx
-rw-r--r--    1 root     root           271 Jun  5 19:11 HCMDB-SES-85302d.vmxf
-rw-------    1 root     root     5782896640 Jun  5 19:15 HCMDB-SES-85302d_1-flat.vmdk
-rw-------    1 root     root           479 Jun  5 19:12 HCMDB-SES-85302d_1.vmdk
-rw-------    1 root     root     36709597184 Jun  5 19:22 HCMDB-SES-85302d_2-flat.vmdk
-rw-------    1 root     root           479 Jun  5 19:15 HCMDB-SES-85302d_2.vmdk
-rw-------    1 root     root     13119782912 Jun  5 19:28 HCMDB-SES-85302d_3-flat.vmdk
-rw-------    1 root     root           479 Jun  5 19:22 HCMDB-SES-85302d_3.vmdk
-rw-------    1 root     root     18351128576 Jun  5 19:36 HCMDB-SES-85302d_4-flat.vmdk
-rw-------    1 root     root           479 Jun  5 19:28 HCMDB-SES-85302d_4.vmdk
/vmfs/volumes/506c2d23-01dbe48d-7940-001ec9deb63b/HCMDB-SES-85302d #

Disk one does not get any number, disk 2 is *_1, disk3 is *_2 and so on.

Within a simple command line, we can get it back to a local machine in a given directory:
vifs --server 192.168.1.10:443 --username root --password pwd --get '[vm] 'HCMDB-SES-85302d/HCMDB-SES-85302d-flat.vmdk' ./HCMDB-SES-85302d-esxi

Once done, on local machine:
[root@omsa:/nfs/software/PeopleSoftCD/OVA/HCMDB-SES-85302d/HCMDB-SES-85302d-esxi]# ls -l
total 7373864
-rw-r--r-- 1 root root 7543455744 Jun  5 21:49 HCMDB-SES-85302d-flat.vmdk
[root@omsa:/nfs/software/PeopleSoftCD/OVA/HCMDB-SES-85302d/HCMDB-SES-85302d-esxi]#


C) Now, mounting the file as a disk.

This is probably the trickiest past of that series. Well, unless you are a good Linux administrator.
We will use the Linux command losetup, from the man, here is a small explanation of what it is:
DESCRIPTION
       losetup  is  used  to associate loop devices with regular files or block devices, to detach loop devices and to query the status of a loop device.
Briefly speaking, mounting a flat file as a disk.

We should start by finding a free loop device:
[root@omsa:]# losetup --find
/dev/loop0
[root@omsa:]#

Then associating this loop device to the flat file:
[root@omsa:]# ls -l
total 7373864
-rw-r--r-- 1 root root 7543455744 Jun  5 21:49 HCMDB-SES-85302d-flat.vmdk
[root@omsa:]# losetup /dev/loop0 ./HCMDB-SES-85302d-flat.vmdk
[root@omsa:]#

We can now see the loop device as a result of a disk:
[root@omsa:]# fdisk -lu /dev/loop0

Disk /dev/loop0: 7543 MB, 7543455744 bytes
255 heads, 63 sectors/track, 917 cylinders, total 14733312 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00064cf9

      Device Boot      Start         End      Blocks   Id  System
/dev/loop0p1   *          63      208844      104391   83  Linux
/dev/loop0p2          208845    10522574     5156865   83  Linux
/dev/loop0p3        10522575    14731604     2104515   82  Linux swap / Solaris
[root@omsa:]#

We recognize here the swap space as I describe a the previous post in the third partition, the first partition is the grub partition (see previous post for the content).
From the output above, we can say that the second partition is our.
The unit is 512 bytes, the starting point of the second partition is 208845. The starting offset of our partition will be 512*208845=106928640

We will be able to create a new loop device from the first within the computed offset:
[root@omsa:]# losetup --find
/dev/loop1
[root@omsa:]# losetup /dev/loop1 /dev/loop0 -o 106928640

Now we can really mount the disk itself from within the last loop device:
[root@omsa:]# mkdir /mnt/HCMDB-SES-85302d
[root@omsa:]# mount /dev/loop1 /mnt/HCMDB-SES-85302d -o rw,user

As of now, we will be able to go on the mount point and modify whatever we want. Any change will remains onto the disk.
For instance:
[root@omsa:]# cd /mnt/HCMDB-SES-85302d
[root@omsa:/mnt/HCMDB-SES-85302d]# ls
bin  boot  dev  etc  home  lib  lib64  lost+found  media  misc  mnt  opt  proc  root  sbin  selinux  srv  sys  tmp  u01  usr  var
[root@omsa:/mnt/HCMDB-SES-85302d]# cd tmp
[root@omsa:/mnt/HCMDB-SES-85302d/tmp]# touch test
[root@omsa:/mnt/HCMDB-SES-85302d/tmp]# rm -f test
[root@omsa:/mnt/HCMDB-SES-85302d/tmp]# cd ../
[root@omsa:/mnt/HCMDB-SES-85302d]# ls
bin  boot  dev  etc  home  lib  lib64  lost+found  media  misc  mnt  opt  proc  root  sbin  selinux  srv  sys  tmp  u01  usr  var
[root@omsa:/mnt/HCMDB-SES-85302d]# cd ./opt/oracle/psft/vm/
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# ls
appbatch-start   expect-sftp     oraclevm-template-appbatch.sh  oraclevm-template-pia.sh     oraclevm-template-utils.sh  template-cleanup.sh
appliance.props  expect-ssh      oraclevm-template-db.sh        oraclevm-template-search.sh  ptem_variables.properties   tnsnames.ora
apply-hotfix.sh  installpia.sh   oraclevm-template-env.sh       oraclevm-template-ses.sh     README                      tnsnames.ora.exa
cmppropsfile.py  network-update  oraclevm-template-ext.sh       oraclevm-template.sh         sql                         updatepiahost.sh
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]# cp oraclevm-template.sh oraclevm-template.sh.orig
[root@omsa:/mnt/HCMDB-SES-85302d/opt/oracle/psft/vm]#

Ok, it will be serious time for script modification.
To be continued.

Nicolas.

Prompt-free Appliance deployment : disks description

What I mean by prompt-free here ?
Well, simply not being prompted during the very first Peoplesoft Appliance VM startup. I find it rather annoying, especially if we have to do it on regular basis. Yes, we do have to commit the agreement, choose a root password, DHCP or fixed IP address, db name, SES... please see part 8 of this previous posts.
The beauty here is that without prompts we could have a script, schedule it in the evening (download files from MOS, doing all the required tasks to move to ESXi as I explained in an other blog entry, and start the VM; all automatically), come back the day after and the VM is ready to be used without anything else to do manually.

You could be right to say it's useless (and probably painful) since you always can create a VM and do snapshots or backup.
But you may also think about creating multiple VMs from the same appliance, where you should each time enter manually all the prompts with different IP address and hostname... I much prefer something like a configuration file defined in advance.
Needless to say it's a nice challenge. Don't you like challenge ?

Even if you are not interested by avoiding prompts, it's always interesting to investigate what Peoplesoft is providing to us for the PUM - Peoplesoft Update Manager - environment. It is finally worth to go through this (long) journey.
So, we will go through a 5 posts series to explain as much details as possible what need to be done.

The very first step is to get a picture of what is delivered.

As I explained in part 4 of the other post, the delivered OVA file is nothing but an archive of VMDKs (disks).
There are 5 of them. Which one is what ? What is the content and description of each disk ? This is what we need to define before going further.

The header of each files gives a first answer:
[root@omsa HCM92000]# for i in `ls HCMDB-SES-85302d-disk?.vmdk`; do head -27 $i >> vmdk.txt; done
[root@omsa HCM92000]# more vmdk.txt
KDMV

version=1
CID=dd143577
parentCID=ffffffff
isNativeSnapshot="no"
createType="streamOptimized"

# Extent description
RDONLY 14733312 SPARSE "HCMDB-SES-85302d-disk1.vmdk"

# The Disk Data Base
#DDB

ddb.longContentID = "19c1a3f4136bd9ec953c84f1dd143577"
ddb.encoding = "UTF-8"
ddb.comment = "Converted image from System.img"
ddb.uuid.parentmodification = "00000000-0000-0000-0000-000000000000"
ddb.uuid.modification = "00000000-0000-0000-0000-000000000000"
ddb.uuid.parent = "00000000-0000-0000-0000-000000000000"
ddb.uuid.image = "022ac6dc-7b31-4d2b-ab4c-3c71e5c9f1b5"
ddb.geometry.sectors = "63"
ddb.geometry.heads = "16"
ddb.geometry.cylinders = "14616"
ddb.adapterType = "ide"
ddb.virtualHWVersion = "4"
<...>
KDMV

version=1
CID=b38c7d38
parentCID=ffffffff
createType="streamOptimized"

# Extent description
RDONLY 11294720 SPARSE "HCMDB-SES-85302d-disk2.vmdk"

# The disk Data Base
#DDB

ddb.virtualHWVersion = "4"
ddb.adapterType="ide"
ddb.geometry.cylinders="11205"
ddb.geometry.heads="16"
ddb.geometry.sectors="63"
ddb.uuid.image="2190202e-f3a8-413b-be1c-50409d983202"
ddb.uuid.parent="00000000-0000-0000-0000-000000000000"
ddb.uuid.modification="00000000-0000-0000-0000-000000000000"
ddb.uuid.parentmodification="00000000-0000-0000-0000-000000000000"
ddb.comment="Converted image from Oracle11gR2.img"

<...>
KDMV

version=1
CID=c0b779ea
parentCID=ffffffff
createType="streamOptimized"

# Extent description
RDONLY 71698432 SPARSE "HCMDB-SES-85302d-disk3.vmdk"

# The disk Data Base
#DDB

ddb.virtualHWVersion = "4"
ddb.adapterType="ide"
ddb.geometry.cylinders="16383"
ddb.geometry.heads="16"
ddb.geometry.sectors="63"
ddb.uuid.image="a91009aa-d8f8-424a-b61c-1cdc5fd56eb5"
ddb.uuid.parent="00000000-0000-0000-0000-000000000000"
ddb.uuid.modification="00000000-0000-0000-0000-000000000000"
ddb.uuid.parentmodification="00000000-0000-0000-0000-000000000000"
ddb.comment="Converted image from HCMDB.img"

<...>
KDMV

version=1
CID=1d37be00
parentCID=ffffffff
createType="streamOptimized"

# Extent description
RDONLY 25624576 SPARSE "HCMDB-SES-85302d-disk4.vmdk"

# The disk Data Base
#DDB

ddb.virtualHWVersion = "4"
ddb.adapterType="ide"
ddb.geometry.cylinders="16383"
ddb.geometry.heads="16"
ddb.geometry.sectors="63"
ddb.uuid.image="b68f6e0b-4349-4d76-8f7e-29ab8c475d6d"
ddb.uuid.parent="00000000-0000-0000-0000-000000000000"
ddb.uuid.modification="00000000-0000-0000-0000-000000000000"
ddb.uuid.parentmodification="00000000-0000-0000-0000-000000000000"
ddb.comment="Converted image from TOOLS.img"
<...>

KDMV

version=1
CID=8ea5f943
parentCID=ffffffff
createType="streamOptimized"

# Extent description
RDONLY 35842048 SPARSE "HCMDB-SES-85302d-disk5.vmdk"

# The disk Data Base
#DDB

ddb.virtualHWVersion = "4"
ddb.adapterType="ide"
ddb.geometry.cylinders="16383"
ddb.geometry.heads="16"
ddb.geometry.sectors="63"
ddb.uuid.image="a44bf647-058d-4909-b4f5-781bea534e93"
ddb.uuid.parent="00000000-0000-0000-0000-000000000000"
ddb.uuid.modification="00000000-0000-0000-0000-000000000000"
ddb.uuid.parentmodification="00000000-0000-0000-0000-000000000000"
ddb.comment="Converted image from SES.img"
<...>

It looks like all the disks have been initiated on Oracle VM (see the comment, “Converted” as well as the extension file img in comment line is typically an Oracle VM template). Rather interesting, Oracle developed everything on Oracle VM and moved all to VirtualBox.

Each disk has its own dedicated area :
1st one, “Converted image from System.img”, this disk contains the all system OS and the required scripts used for the deployment. This is the one we should go deeper later on for what we have to do. That one is defined as IDE disk.
2nd one, “Converted image from Oracle11gR2.img”, the database installation software.
3rd one, “Converted image from HCMDB.img”, the database.
4rth one, “Converted image from TOOLS.img”, the Peopletools installation software.
5th one, “Converted image from SES.img”, the SES software installation.

So, to go further in the automation, we should go and investigate further into the 1st disk, used to host the system and boot drive.
I’m using VMWARE ESXi, there’s a nice and small utility, vmware-mount (coming with Virtual Disk Development Kit) to work on disk.
Let’s use it and see the disk in details:
[root@omsa:HCMDB-SES-85302d]# vmware-mount -p HCMDB-SES-85302d-disk1.vmdk
Nr      Start       Size Type Id Sytem
-- ---------- ---------- ---- -- ------------------------
1         63     208782 BIOS 83 Linux
2     208845   10313730 BIOS 83 Linux
3   10522575    4209030 BIOS 82 Linux swap
[root@omsa:/nfs/software/PeopleSoftCD/OVA/HCMDB-SES-85302d]#
Let’s forget the 3rd mount point, it’s nothing but swap space.
We should check the first two to see which one is our for scripts hosting.

About the first mount point:
[root@omsa:HCMDB-SES-85302d]# mkdir /mnt/HCMDB-SES-85302d
[root@omsa:HCMDB-SES-85302d]# vmware-mount HCMDB-SES-85302d-disk1.vmdk 1 /mnt/HCMDB-SES-85302d
[root@omsa:HCMDB-SES-85302d]# ls –l /mnt/HCMDB-SES-85302d
total 18912
-rw-r--r--. 1 root root   67258 Jul 25  2011 config-2.6.18-274.0.0.0.1.el5xen
-rw-r--r--. 1 root root   98997 Jul 28  2011 config-2.6.32-200.13.1.el5uek
drwxr-xr-x. 2 root root    1024 Apr  1 07:50 grub
-rw-------. 1 root root 3322413 Sep  9  2011 initrd-2.6.18-274.0.0.0.1.el5xen.img
-rw-------. 1 root root 2963880 Sep  9  2011 initrd-2.6.32-200.13.1.el5uek.img
-rw-------. 1 root root 2976036 Sep  9  2011 initrd-2.6.32-200.13.1.el5uek.img.pvhvm
drwx------  2 root root   12288 Sep  9  2011 lost+found
-rw-r--r--. 1 root root  115821 Jul 25  2011 symvers-2.6.18-274.0.0.0.1.el5xen.gz
-rw-r--r--. 1 root root  161244 Jul 28  2011 symvers-2.6.32-200.13.1.el5uek.gz
-rw-r--r--. 1 root root 1230605 Jul 25  2011 System.map-2.6.18-274.0.0.0.1.el5xen
-rw-r--r--. 1 root root 2437866 Jul 28  2011 System.map-2.6.32-200.13.1.el5uek
-rw-r--r--. 1 root root 2195174 Jul 25  2011 vmlinuz-2.6.18-274.0.0.0.1.el5xen
-rwxr-xr-x. 1 root root 3677280 Jul 28  2011 vmlinuz-2.6.32-200.13.1.el5uek
[root@omsa:HCMDB-SES-85302d]# vmware-mount -d /mnt/HCMDB-SES-85302d
Hmmm, nothing much interesting for us here.
And about the second mount point:
[root@omsa:HCMDB-SES-85302d]# vmware-mount HCMDB-SES-85302d-disk1.vmdk 2 /mnt/HCMDB-SES-85302d
[root@omsa:HCMDB-SES-85302d]# ls –l /mnt/HCMDB-SES-85302d/opt/oracle/psft/vm
total 200
-rwxr-xr-x 1 root root  3600 Apr  1 04:54 appbatch-start
-r--r--r-- 1 root root    32 Apr  1 07:50 appliance.props
-rwxr-xr-x 1 root root   366 Apr  1 04:54 apply-hotfix.sh
-rwxr-xr-x 1 root root 10195 Apr  1 04:54 cmppropsfile.py
-rwxr-xr-x 1 root root   521 Apr  1 04:54 expect-sftp
-rwxr-xr-x 1 root root   459 Apr  1 04:54 expect-ssh
-rwxr-xr-x 1 root root  4064 Apr  1 04:54 installpia.sh
-rwxr-xr-x 1 root root  1514 Apr  1 04:54 network-update
-rwxr-xr-x 1 root root 23945 Apr  1 04:54 oraclevm-template-appbatch.sh
-rwxr-xr-x 1 root root  5695 Apr  1 04:54 oraclevm-template-db.sh
-rwxr-xr-x 1 root root 17248 Apr  1 04:54 oraclevm-template-env.sh
-rwxr-xr-x 1 root root  3816 Apr  1 04:54 oraclevm-template-ext.sh
-rwxr-xr-x 1 root root 13853 Apr  1 04:54 oraclevm-template-pia.sh
-rwxr-xr-x 1 root root  2230 Apr  1 04:54 oraclevm-template-search.sh
-rwxr-xr-x 1 root root     0 Apr  1 04:54 oraclevm-template-ses.sh
-rwxr-xr-x 1 root root 21569 Apr  1 04:54 oraclevm-template.sh
-rwxr-xr-x 1 root root 28642 Apr  1 04:54 oraclevm-template-utils.sh
-rwxr-xr-x 1 root root  2146 Apr  1 04:54 ptem_variables.properties
-rwxr-xr-x 1 root root  4649 Apr  1 04:54 README
drwxr-xr-x 2 root root  4096 Apr  1 05:36 sql
-rwxr-xr-x 1 root root  1586 Apr  1 04:54 template-cleanup.sh
-rwxr-xr-x 1 root root   140 Apr  1 04:54 tnsnames.ora
-rwxr-xr-x 1 root root   175 Apr  1 04:54 tnsnames.ora.exa
-rwxr-xr-x 1 root root  3495 Apr  1 04:54 updatepiahost.sh
[root@omsa:HCMDB-SES-85302d]# vmware-mount -d /mnt/HCMDB-SES-85302d
[root@omsa:HCMDB-SES-85302d]# rmdir /mnt/HCMDB-SES-85302d
Yeap, here we go. It contains all what is used during the Appliance deployment. This is the one we want to go to through if we want to go further in the vm scripts modifiation.
NB: we could do the same exercise of mounting other disk (2, 3, 4, and 5) to check their exact content, but it’s not really what we are looking for right now, we have enough information.

We could go straight away, but it’s not that simple. The given vmdk are “compressed”. The utility vmware-mount can only mount the disk in read only mode ! Not very useful if we want to modify the content.

In the next step, we will have to find a workaround which allow the files modification.

Nicolas.

Oracle 11gR2 DBFS and Peopletools 8.53 (bis)

Few days ago I wrote an article about Oracle 11gR2 DBFS and Peopletools 8.53.
As I said out there, there are 3 mains problems that I see :
* first, the database may grow dramatically
* second, the WebServer and FileSystem host must be Linux
* third, the fuse does not allow the automount even though you configure it in /etc/fstab. And automount would not be a solution anyway since on server boot the mount is happening before the autostart of the database which needs to be up to mount such a DBFS…

Despite those disadvantages, it is worth to go there, we get a more secure way to manage the reports.

We cannot do much on the second point, or wait for Oracle 12c (?) to unlock the OS limitation and offer this solution to all the shops regardless their OS.
On the first point, we can manage it, it’s all about how you deal with your space devices (db files, archives and backup).
And on the third point, it is just a matter of workaround…

The all difficulty in a Peoplesoft environment is that the filesystem used for the reporting must be reachable as soon as AppServer/Batch server and PIA start, otherwise some remaining batches may not post their reports.
So, whether we have to mount the DBFS after the start of the database, we also have to mount it before the start of the Peoplesoft processes. If we configured all your environment to start automatically, we cannot do that manually, it would be done once the system is available, too late for the AppServer and other Peoplesoft processes.

Here we’ll go through a solution how to implement a mount of the DBFS without manual intervention.

Note that all the tests below are done on Peoplesoft Appliance from April-2013, HCM92 Peopletools 8.53.02.
1. Go through the all configuration to be done as I explained in my other blog entry, Oracle 11gR2 DBFS and Peopletools 8.53.  This configuration is the bare minimum to make the manual mount working, but still required here. Following steps are in addition to make the “auto-mount”.

2. Set your libraries environment, and load:

[root@hcm92000 ~]# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf
[root@hcm92000 ~]# ln -s /u01/app/oracle/product/11.2.0.x/db_1/lib/libclntsh.so.11.1 /usr/local/lib/libclntsh.so.11.1 [root@hcm92000 ~]# ln -s /u01/app/oracle/product/11.2.0.x/db_1/lib/libnnz11.so /usr/local/lib/libnnz11.so
[root@hcm92000 ~]# ln -s /lib64/libfuse.so.2 /usr/local/lib/libfuse.so.2
[root@hcm92000 ~]# ldconfig

3. Add a line corresponding to your mount point in /etc/fstab

[root@hcm92000 ~]# echo "/u01/scripts/dbfsmount#sysadm@HR92DM00 /mnt/dbfs fuse rw,user,noauto,direct_io 0 0" >> /etc/fstab
[root@hcm92000 ~]# more /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults,size=2g        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-VM           swap                    swap    defaults        0 0
LABEL=Oracle11gR2       /u01                    ext3    defaults        1 2
LABEL=HCMDB             /opt/oracle/psft/ptdb   ext3    defaults        1 2
LABEL=TOOLS             /opt/oracle/psft/pt     ext3    defaults        1 2
LABEL=SES             /opt/oracle/psft/ses     ext3    defaults        1 2
/u01/scripts/dbfsmount#sysadm@HR92DM00 /mnt/dbfs fuse rw,user,noauto,direct_io 0 0
[root@hcm92000 ~]#

4. Create the script to mount the DBFS
In the /etc/fstab defined on step 3, I specified /u01/scripts/dbfsmount which is a script as following (SYSADM being the accessid’s password):
[root@hcm92000 ~]# cd /u01/scripts/
[root@hcm92000 scripts]# more dbfsmount
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.x/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
nohup $ORACLE_HOME/bin/dbfs_client $@ -o allow_other << PSWD &
SYSADM
PSWD
!! Do not forget the option “-o allow_other”, otherwise psadm2 won’t be granted for DBFS access (the file /etc/fuse.conf must contains a line with the value: user_allow_other). This script is called when the mount on /mnt/dbfs is raised on the system.
Then give the proper rights to be executed on “mount” command:
chmod 750 /u01/scripts/dbfsmount
chgrp fuse /u01/scripts/dbfsmount

5. Modify the dbstart script
In the directory /u01/scripts, you have the dbstart script which needs to be modified to run the mount command. Modify it as following, here is just an extract (in bold the added lines, actually just after the db startup):
[root@hcm92000 scripts]# grep mount -B 10 -A 10 dbstart
    fi
    su -s /bin/bash  $ORACLE_OWNER -c "$ORACLE_HOME/bin/sqlplus -s /nolog" <<SQL
connect / as sysdba
alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=$(hostname))(PORT=$DEFAULT_LISTENER_PORT))';
exit;
SQL     su -s /bin/bash  $ORACLE_OWNER <<MOUNT
if [ $ORACLE_SID = "HR92DM00" ]; then
echo "Mounting DBFS... "$ORACLE_SID
cd ~
mount /mnt/dbfs
fi
MOUNT }
stop_db_inst () {
    ret=0
    export ORACLE_SID ORACLE_HOME
    su -s /bin/bash  $ORACLE_OWNER -c "$ORACLE_HOME/bin/sqlplus -s /nolog" <<SQL
connect / as sysdba
shutdown immediate
[root@hcm92000 scripts]#
I made a test on the database name, just to make thing clear that I want the dbfs mounted for that peculiar database. However, it is not required on Peoplesoft Appliance since there’s only one database.

6. Reboot the server
Now it’s ready. Time to reboot the server and see.
You may see a warning regarding the /etc/fstab format. It's a blessing in disguise, I’d say.

And during the boot, you’ll see the echo message about the DBFS mounting (the nohup line can be ignored):


7. Check the DBFS filesystem availability:
[psadm2@hcm92000 ~]$ ls -lrt /mnt/dbfs/ReportRepository/HR92DM00
total 0
drwxr-xr-x 6 psadm2 oracle 0 May 14 11:42 20130514
[psadm2@hcm92000 HR92DM00]$
Here we can see only one day of reports.

8. Run a test, for instance AEMINITEST and check once again the DBFS filesystem:

[psadm2@hcm92000 ~]$ ls -lrt /mnt/dbfs/ReportRepository/HR92DM00
total 0
drwxr-xr-x 6 psadm2 oracle 0 May 14 11:42 20130514
drwxr-xr-x 3 psadm2 oracle 0 May 16 04:29 20130516
[psadm2@hcm92000 HR92DM00]$
The current date has been added. The posting was successful.

9. Double check the all configuration works from the db side:

[oracle@hcm92000 ~]$  export ORACLE_SID=HR92DM00
[oracle@hcm92000 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 16 04:30:17 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options
SQL> set lines 200 pages 100
SQL> select pathname from sysadm.T_REPORTREPOSITORY where filedata is not null;
PATHNAME
------------------------------------------------------
/HR92DM00/20130514/861/AE_AEMINITEST_1126.stdout
/HR92DM00/20130514/863/sysaud01_1128.out
/HR92DM00/20130514/863/SQR_SYSAUD01_1128.log
/HR92DM00/20130514/863/sysaud01_1128.PDF
/HR92DM00/20130514/862/SQR_DDDAUDIT_1127.log
/HR92DM00/20130514/862/dddaudit_1127.PDF
/HR92DM00/20130514/862/dddaudit_1127.out
/HR92DM00/20130514/866/AE_AEMINITEST_1131.stdout
/HR92DM00/20130516/867/AE_AEMINITEST_1132.stdout <—here is my last posted report

As of now, we don’t have to worry about the DBFS mount anymore.
So, why not have this configuration by default on future Peoplesoft Appliances and/or Peoplesoft OVM if any. It’s rather a good test to have on a demo.

Enjoy,

Nicolas.

Oracle 11gR2 DataBase FileSystem (DBFS) and Peopletools 8.53

Coming with Oracle 11gR2, the DBFS (DataBase FileSystem) feature improves the files storage.
So far, we had BLOB, files stored in the database, but we needed an access to the database with specific API to read/open them.
With DBFS, files are still stored in the database, but access may be from ‘outside’ the database, like for any other filesystem.

Peoplesoft can take all the advantages of this feature, especially useful for the batch reports. As of Peopletools 8.53, it’s now possible.
Storing the batch reports in such way, files being in the database (but still readable from outside the database), can make the administrator life easier regarding the backup. No need to worry anymore about backing up the report repository directory, it’s already done from within the database backup !
The process scheduler (batch server) will continue to post in the defined report repository (now the DBFS), and the database will handle it automatically.

Rather nice enhancement.

Important note, all the tests below are done on the Peoplesoft Appliance from April-2013, HCM9.2 Peopletools 8.53.02. Your situation may differ, so adapt your scripts and environment accordingly.

In the current situation, when posting, files are going to a pre-define directory in the configuration.properties, such as :
[psadm2@hcm92000 ~]$ ls -lrt /home/psadm2/psft/pt/8.53/psreports/HR92DM00/
total 24
drwxr-xr-x 7 psadm2 oracle 4096 Apr 24 07:38 20130424
drwxr-xr-x 5 psadm2 oracle 4096 Apr 27 08:49 20130427
drwxr-xr-x 3 psadm2 oracle 4096 May  8 04:49 20130508
drwxr-xr-x 3 psadm2 oracle 4096 May 13 11:13 20130513
You have to manage a backup all those files to be ready for a restore, if any.

Let’s have a look how to configure and use the DBFS now. Again, the main goal here is to avoid external file to manage backup for.

1. The kernel
If not installed, download and install the rpm kernel-devel-2.6.18-274.el5.i686 (http://public-yum.oracle.com):
[root@hcm92000 fuse-2.7.4]# more /etc/redhat-release
Red Hat Enterprise Linux Server release 5.7 (Tikanga)
[root@hcm92000 fuse-2.7.4]#
[root@hcm92000 Oracle_11.2.0.3.0_64bits]# rpm -q kernel-devel-2.6.18-274.el5.i686
kernel-devel-2.6.18-274.el5
[root@hcm92000 Oracle_11.2.0.3.0_64bits]#

2. Fuse
2.1 Download fuse utility, http://sourceforge.net/projects/fuse/files/fuse-2.X/2.7.4/
2.2 Install fuse :
[root@hcm92000 Oracle_11.2.0.3.0_64bits]# ls
fuse-2.7.4.tar.gz   database  
[root@hcm92000 Oracle_11.2.0.3.0_64bits]# gunzip fuse-2.7.4.tar.gz
[root@hcm92000 Oracle_11.2.0.3.0_64bits]# tar xvf fuse-2.7.4.tar
fuse-2.7.4/
...<snipped>
[root@hcm92000 fuse-2.7.4]# ls /usr/src/kernels
2.6.18-274.el5-i686
[root@hcm92000 fuse-2.7.4]# ./configure --prefix=/usr --with-kernel=/usr/src/kernels/2.6.18-274.el5-i686
checking build system type... x86_64-unknown-linux-gnu
...<snipped>
NOTE:     Detected that FUSE is already present in the kernel, so
NOTE:     building of kernel module is disabled.  To force building
NOTE:     of kernel module use the '--enable-kernel-module' option.
configure: creating ./config.status
config.status: creating Makefile
config.status: creating config.h
[root@hcm92000 fuse-2.7.4]#
[root@hcm92000 fuse-2.7.4]# make
...<snipped>
[root@hcm92000 fuse-2.7.4]# make install
...<snipped>
[root@hcm92000 fuse-2.7.4]#
[root@hcm92000 fuse-2.7.4]# /sbin/depmod
[root@hcm92000 fuse-2.7.4]# /sbin/modprobe fuse
[root@hcm92000 fuse-2.7.4]# chmod 666 /dev/fuse
[root@hcm92000 fuse-2.7.4]# echo "/sbin/modprobe fuse" >> /etc/rc.modules
[root@hcm92000 fuse-2.7.4]#  chmod 700 /etc/rc.modules

3. The tablespace
Connect on the Peoplesoft database, create the tablespace:
[oracle@hcm92000 ~]$ export ORACLE_SID=HR92DM00
[oracle@hcm92000 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 03:54:08 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options

SQL> show parameter db_securefile

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------db_securefile                        string                                       PERMITTED
SQL>
SQL> create tablespace dbfs_tbs datafile '/u01/app/oracle/oradata/HR92DM00/dbfs_tbs_01.dbf' size 500M reuse autoextend on next 200M segment space management auto;

Tablespace created.

SQL> grant dbfs_role to sysadm;

4. The filesystem
Connect on the database with your accessid (SYSADM) and create the filesystem (dbfs_tbs is the tablespace, ReportRepository will be the filesystem):
[oracle@hcm92000 ~]$ ls $ORACLE_HOME/rdbms/admin/dbfs*
/u01/app/oracle/product/11.2.0.x/db_1/rdbms/admin/dbfs_create_filesystem_advanced.sql  /u01/app/oracle/product/11.2.0.x/db_1/rdbms/admin/dbfs_drop_filesystem.sql
/u01/app/oracle/product/11.2.0.x/db_1/rdbms/admin/dbfs_create_filesystem.sql
[oracle@hcm92000 ~]$ sqlplus sysadm

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 03:57:56 2013

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options

SQL> @$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_tbs ReportRepository
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_REPORTREPOSITORY',
tbl_name => 'T_REPORTREPOSITORY', tbl_tbs => 'dbfs_tbs', lob_tbs => 'dbfs_tbs',
do_partition => false, partition_key => 1, do_compress => false, compression =>
'', do_dedup => false, do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_REPORTREPOSITORY',
provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_REPORTREPOSITORY',
store_mount=>'ReportRepository'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/ReportRepository', 16895);
end;
No errors.
SQL>

5. The mount point
5.1 As root, create the path directory first to be mounted within the create filesystem:
[root@hcm92000 fuse-2.7.4]# mkdir -p /mnt/dbfs
[root@hcm92000 fuse-2.7.4]# chmod -R a+rwx /mnt/dbfs

5.2 As root, create a new fuse group
[root@hcm92000 ~]# export PATH=/usr/sbin:$PATH;export PATH
[root@hcm92000 ~]# groupadd fuse

5.3 As root, add the fuse group to oracle and psadm2 (application/batch/pia server domain owner)
[root@hcm92000 ~]# usermod -G dba,fuse oracle
[root@hcm92000 ~]# usermod -G fuse psadm2

5.4 As oracle, create a file, /etc/fuse.conf to allow the users to read the new moint point:
[oracle@hcm92000 ~]$ more /etc/fuse.conf
user_allow_other

5.5 Put the accessid’s password in a file (SYSADM by default)
[oracle@hcm92000 ~]$ echo "SYSADM" > dbfspassword

5.6 As oracle user on your OS, mount the filesystem:
[oracle@hcm92000 ~]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib;export LD_LIBRARY_PATH
[oracle@hcm92000 ~]$ nohup dbfs_client SYSADM@HR92DM00 /mnt/dbfs -o allow_other < dbfspassword &
[1] 13293
[oracle@hcm92000 ~]$ nohup: appending output to `nohup.out'
[oracle@hcm92000 ~]$ more nohup.out
Password:
[oracle@hcm92000 ~]$

5.7 Check your moint points, you should see the new one:
[oracle@hcm92000 ~]$ mount
/dev/sda2 on / type ext3 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/sda1 on /boot type ext3 (rw)
tmpfs on /dev/shm type tmpfs (rw,size=2g)
/dev/sdb1 on /u01 type ext3 (rw)
/dev/sdc1 on /opt/oracle/psft/ptdb type ext3 (rw)
/dev/sdd1 on /opt/oracle/psft/pt type ext3 (rw)
/dev/sde1 on /opt/oracle/psft/ses type ext3 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
nfsd on /proc/fs/nfsd type nfsd (rw)
192.168.1.1:/software on /nfs/software type nfs (rw,addr=192.168.1.1)
dbfs-SYSADM@HR92DM00:/ on /mnt/dbfs type fuse (rw,nosuid,nodev,max_read=1048576,default_permissions,allow_other,user=oracle)

And the directory:
[oracle@hcm92000 ~]$ ls -lrt /mnt/dbfs/ReportRepository/
total 0
[oracle@hcm92000 ~]$

6. Change the Report repository of webserver
Connect as psadm2, pia owner, modify the variable ReportRepositoryPath in configuration.properties.
Shutdown your PIA, modify the file, and restart the PIA, such as:
[psadm2@hcm92000 bin]$ ./stopPIA.sh
Submitting shutdown command for WebLogic Server PIA at t3://hcm92000.phoenix.nga:8000

No activity will be logged to this window.
Server activity will be logged to /home/psadm2/psft/pt/8.53/webserv/peoplesoft/servers/PIA/logs/PIA_shutdown*

Stopping Weblogic Server...
Done
[psadm2@hcm92000 bin]$ vi ../applications/peoplesoft/PORTAL.war/WEB-INF/psftdocs/ps/configuration.properties
# Report Repository file path
# This is the installation default entered on the screen in setup.exe
# If one is not specified in the Web Profile then this is used.
## qc="7", sd="Report Repository File Path", rq="N", tip="This location is only used if this detail is not specified in the Web Profile", ld="This is the path to store PeopleSoft Reports"
#ReportRepositoryPath=/home/psadm2/psft/pt/8.53/psreports
ReportRepositoryPath=/mnt/dbfs/ReportRepository
[psadm2@hcm92000 bin]$ ./startPIA.sh
Attempting to start WebLogic Server PIA
No activity will be logged to this window.
Server activity will be logged to /home/psadm2/psft/pt/8.53/webserv/peoplesoft/servers/PIA/logs/PIA_*
PID for WebLogic Server PIA is: 11782
[psadm2@hcm92000 bin]$ tail -f /home/psadm2/psft/pt/8.53/webserv/peoplesoft/servers/PIA/logs/PIA_stdout.log
...
<May 14, 2013 4:11:32 AM EDT> <Notice> <WebLogicServer> <BEA-000329> <Started WebLogic Admin Server "PIA" for domain "peoplesoft" running in Production Mode>
<May 14, 2013 4:11:32 AM EDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING>
<May 14, 2013 4:11:32 AM EDT> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>
...

7. Now ready to test
From within the front end, run processes, for instances AEMINITEST, DDDAUDIT and/or SYSAUDIT. Standard procedure here, I won’t go through the screenshots for this step. I assume it is well known by the readers. You should be able to read the reports like anytime before. The change should be pretty transparent for the end users.

8. Check from the back end
From the OS side, you may check whether the files are there or not (it’s not the original location as shown earlier):
[psadm2@hcm92000 LOGS]$  ls -lrt /mnt/dbfs/ReportRepository/HR92DM00/20130514/*
/mnt/dbfs/ReportRepository/HR92DM00/20130514/861:
total 1
-rw-r--r-- 1 psadm2 oracle 292 May 14 07:52 AE_AEMINITEST_1126.stdout

/mnt/dbfs/ReportRepository/HR92DM00/20130514/863:
total 7
-rw-r--r-- 1 psadm2 oracle    0 May 14 08:11 sysaud01_1128.out
-rw-r--r-- 1 psadm2 oracle 1757 May 14 08:11 SQR_SYSAUD01_1128.log
-rw-r--r-- 1 psadm2 oracle 4309 May 14 08:11 sysaud01_1128.PDF

/mnt/dbfs/ReportRepository/HR92DM00/20130514/862:
total 7
-rw-r--r-- 1 psadm2 oracle 1757 May 14 08:12 SQR_DDDAUDIT_1127.log
-rw-r--r-- 1 psadm2 oracle 4401 May 14 08:12 dddaudit_1127.PDF
-rw-r--r-- 1 psadm2 oracle    0 May 14 08:12 dddaudit_1127.out
[psadm2@hcm92000 LOGS]$

And from the database side, you can query the dedicated table to your new filesystem:
SQL> select pathname from sysadm.T_REPORTREPOSITORY
SQL> /

PATHNAME
--------------------------------------------------------------------------------
/
/.sfs
/.sfs/RECYCLE
/.sfs/attributes
/.sfs/content
/.sfs/snapshots
/.sfs/tools
/HR92DM00
/HR92DM00/20130514
/HR92DM00/20130514/861
/HR92DM00/20130514/861/AE_AEMINITEST_1126.stdout
/HR92DM00/20130514/862
/HR92DM00/20130514/862/SQR_DDDAUDIT_1127.log
/HR92DM00/20130514/862/dddaudit_1127.PDF
/HR92DM00/20130514/862/dddaudit_1127.out
/HR92DM00/20130514/863
/HR92DM00/20130514/863/SQR_SYSAUD01_1128.log
/HR92DM00/20130514/863/sysaud01_1128.PDF
/HR92DM00/20130514/863/sysaud01_1128.out

19 rows selected.
You may see, there’s 7 files in there, corresponding to the produced files by the batches (FILEDATA is BLOB datatype):
SQL> select pathname from sysadm.T_REPORTREPOSITORY where filedata is not null;

PATHNAME
--------------------------------------------------------------------------------
/HR92DM00/20130514/861/AE_AEMINITEST_1126.stdout
/HR92DM00/20130514/863/sysaud01_1128.out
/HR92DM00/20130514/863/SQR_SYSAUD01_1128.log
/HR92DM00/20130514/863/sysaud01_1128.PDF
/HR92DM00/20130514/862/SQR_DDDAUDIT_1127.log
/HR92DM00/20130514/862/dddaudit_1127.PDF
/HR92DM00/20130514/862/dddaudit_1127.out

7 rows selected.

9. Potential errors
9.1 You may see when mounting (file nohup):
fuse: bad mount point `/mnt/dbfs': No such file or directory
Verify the directory exists and Oracle has right enough.

9.2 You may receive an error on posting, like :
Java Exception: Error while write to file:java.lang.SecurityException: Directory Creation Failed  (63,49)
Means, you probably forget the fuse group and/or add the file /etc/fuse.conf, and/or forget the option allow_other in the mount command.
Also, verify the parameter ReportRepositoryPath in the configuration.properties of the webserver. I was puzzled for quite a while until I see the value was mistakenly appended by a blank space…

Conclusions:
=> As of now, the database backup should be enough to restore data and reports !
Main side effect, depending of the number and size of the reports your application is producing, your database dedicated tablespace may dramatically grow up, take care about your archives, and backup space required.

=> Two of the requirements may dramatically limit the use: first, your WebServer must run on Linux, second, the DBFS must be hosted on Linux…

=> Also, note that the fuse does not support automount mode (fstab), after server boot, you should do it manually, or write your own script using wallet for authentication… Otherwise, the batch will create the reports directly onto the filesystem /mnt/dbfs, not in the database… making discrepancies between filesystems content and database. Furthermore, you will then get some troubles to get it mounted again.

Well, a nice try, very interesting to know it exists. Not sure though if many shops will implement it. Do you ?

Nicolas.

References:
Peopletools Peoplebooks: http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tadm/task_ImplementingTheOracleDatabaseFileSystem.html
Oracle online documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_fs.htm

Integration Network WorkCenter 8.53 : automated process

As of Peopletools 8.52, a lot has been done regarding the Integration Broker configuration and use through the Integration Network WorkCenter.
Many tasks have been made easier than ever for the configuration and maintenance of all the messaging between systems.
Few months ago, I showed how it can be set and used.

And even more, from the Peopletools 8.53, it was promised in the RVP that the monitoring could be automated through email:
“Automated Network Status and Real-Time Notification of Errors
In PeopleTools 8.52, checking the status of PeopleSoft systems in the Integration Network was a manual process. Administrators could go to a page in the Integration WorkCenter and see the status of all connected systems. New in 8.53, checking the status is an automated process, and the results of the check can be sent directly to an administrator.
Once configured, the Integration Network can monitor itself for errors.”

So, I was expecting a lot within this new feature. Could we work only with this Peopletools monitoring utility rather than a third party to monitor the messaging ? Let’s have a look.

Here the main page:
 
Of course, the configuration must be set, for more details, please read my previous blog entry (it was for Peopletools 8.52, but still valid in Peopletools 8.53).
Go to the “Network Status Setup” (this has been added in Peopletools 8.53):

Go to the SetUp, fill up an email:
 
Optionally, include diagnosis and information.
You can propagate the setting to your remote node(s):
  
Now the test. I wrongly configured a message USER_PROFILE between my HR and EP systems, then create a new user on HR.
Few minutes later, the magic is done ! An email was dropped to my inbox:
 
Magic, but rather cryptic, isn’t it ?

Firstly, at this very moment, I do not really care that much the XML input.

Secondly, whether the email subject contains the source node (publisher), I have no idea about the target node (subscriber). I do not have more idea about the application which send my out this email. A dedicated email source must be set up front.

Thirdly, the message “Unable to find a routing corresponding to the incoming request message” is way too standard. We could expect much more, at least the same details as such we can find from within the front-end (see below).

Fourthly, there’s no link to the application. It would be much easier to have such link, give the credentials on a login page and being redirected to the Integration Network WorkCenter.

Last and not least, this email is sent every X minutes (X being the interval defined in the setup of automated integration) until you fix it, or until the limit is reached (last number of days). Should it not be enough to receive the error only once for the last X minutes ? Apparently not, by default it is checking all the messaging remaining in error every 5 minutes, and such for the last 30 days !
During my tests, I was like spammed…

Coming from Peoplesoft to automate its own processes, I would expect much more.
This email input is quite disappointing.

That said, keep the given transactionID and go to the transactional tracker in the front-end:
 
Go to the “Search Details”, and paste the previously TransactionID indentified to be in error from the email:
 
Now, we have a little bit more information about the message:

Going to the “View Error/Info”, we will retrieve the exact same error we got through the email:

Nothing really helpful on this page. The previous page is, to my point of view, more helpful, you have the external service name, the publishing node and the service operation. You already know there’s an error, so you should be able to fix it. Such information would be nice in the email.

Now, to go further in my testing of this automated process, I’m shutting down the target messaging server (EP, the subscriber node).

And create a new user on HR, a message should be send to EP, at least a try. Obviously it shouldn't work.

Going to the monitoring overview, after a while, the message in “Retry” status:
Going to the details, it clearly states an error:

The error is quite obvious that time:
 
Unfortunately, I’ve never receive any email for that error ! I’m wondering if that’s because the status of the message is “Retry” and not “Error”.
It can remain endlessly in this status if I’m waiting for the email, unless I connect to the system and check it manually.
Again, disappointed.
Ok, I should read more carefully the RVP that I mentioned at the beginning :
”[…]Once configured, the Integration Network can monitor itself for *errors*.[…]”
It’s clear enough, *error*… But do we not have an error here ? And what’s the meaning of the automation if I have to connect to the front end to check for the other “problematic” status (I mean not “done”) ?

Not regarding the automation of monitoring, but still about the Network Status.
Checking the status by default returns green icons (only if everything is well configured):

Go to the “Network Status Setup”:

As said earlier, on this page we can configure the automated monitoring, but there’s also diagnostics section. Check all of them out, save, and return back to the Network Status page. Check it again:

Now there’s “Warning” on every and each node (drag the mouse on over the triangles) ! We did not have any problem though !
Going to the details:

And to the diagnostics :
 
Well, fine with me, that can be useful to have details and diagnostics, but why, oh why is it a yellow triangle with warning ?!?!

In the end, my conclusion…

Here has been discussed about:
1. The automated email of Network Integration
2. The monitoring of messages (Retry status is really not monitored?)
3. The Network Integration Status, warning…

I would say none of these points are entirely satisfying my expectations:
1. I would give much more information in the Email content: less cryptic content, more information about the error, give a link to the application.
2. The status of messages to be monitored by the automatically processed should be configurable. Such an email to be send when a message is coming to a given status (checkboxes for the status would have been even better). I don’t really see any advantages of the automation if we have to connect and check for the other status like “Retry”, “Timeout” which are also kind of error.
3. Whether I can understand the need of diagnostic, it’s certainly not a warning ! To me, a warning must always be considered as an error. But here there’s no problem, it is just the result of a configuration setting.

That’s a nice try, but I tend to think this is only a first try to be improved in the next Peopletools release !

Enjoy,

Nicolas

Note: all the tests from the above have been made on the delivered Peoplesoft Appliances from April-2013, HCM9.2 and FSCM9.2 on Peopletools 8.53.02.
References:
1. To configure an email address on your sandbox, you can follow the instructions of Hakan Biroglu: http://bloggingaboutoracleapplications.org/gmail-smtp-server-peoplesoft/
2. The online Peoplebooks have been greatly improved within the Peopletools 8.53, I used it for this blog entry:
http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tiba/intro_UsingtheIntegrationNetwork-388000.html