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.