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
No comments:
Post a Comment