Thursday, May 16, 2013

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.
DBFS_009
And during the boot, you’ll see the echo message about the DBFS mounting (the nohup line can be ignored):
DBFS_010

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.

Tuesday, May 14, 2013

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

Monday, May 13, 2013

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:
INW_853_049 
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):
INW_853_050
Go to the SetUp, fill up an email:
INW_853_021 
Optionally, include diagnosis and information.
You can propagate the setting to your remote node(s):
INW_853_051  
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:
INW_853_031 
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:
INW_853_032 
Go to the “Search Details”, and paste the previously TransactionID indentified to be in error from the email:
INW_853_033 
Now, we have a little bit more information about the message:
INW_853_034
Going to the “View Error/Info”, we will retrieve the exact same error we got through the email:
INW_853_035
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).
INW_853_036
And create a new user on HR, a message should be send to EP, at least a try. Obviously it shouldn't work.
INW_853_037
Going to the monitoring overview, after a while, the message in “Retry” status:INW_853_039
Going to the details, it clearly states an error:
INW_853_040
The error is quite obvious that time:
INW_853_041 
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):
INW_853_052
Go to the “Network Status Setup”:
INW_853_053
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:
INW_853_054
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:
INW_853_055
And to the diagnostics :
INW_853_056 
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://hakanbiroglu.blogspot.nl/2013/03/using-gmail-as-smtp-server-for.html
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