Oracle Database File System (DBFS) in Oracle Database 11g Release 2

Oracle Database File System (DBFS) in Oracle Database 11g Release 2
Oracle has quite a long history with database file systems. The Oracle Internet File System (iFS) was released in the Oracle 8i days. This product was later renamed to Oracle Content Management SDK. The introduction of XML DB in Oracle 9i Release 2 brought with it a database file system accessible from HTTP, FTP and WebDAV for the storage of XML files. Now Oracle 11g Release 2 introduces DBFS, the Oracle Database File System.
DBFS creates a file system interface on top of database tables that store files as SecureFile LOBs. External access to the file system is via a client program (dbfs_client), which is only available for Linux and Solaris platforms. The client is already installed on the database server, but can also be installed on client machines, giving them access to the centralized database file system. On Linux platforms the dbfs_client can be used to mount the database file system on a regular mount point. This done using the "Filesystem in Userspace" (FUSE) project. This allows Linux machines to access DBFS like any other physical file system.
In this article I'll show the steps necessary to mount the DBFS on a Linux server. I'm not going to discuss the DBFS Content API, DBFS SecureFile Store or DBFS Hierarchical Store directly, although some of these are called by scripts used in this article.
• Creating a File System
• FUSE Installation
• Yum FUSE Installation
• Media FUSE Installation
• Manual FUSE Installation
• Mounting a File System
• Wallet Creation
• Using /etc/fstab
• dbfs_client Command Interface
Related articles.
• Oracle Database File System (DBFS) PL/SQL APIs
• Oracle Database File System (DBFS) Enhancements in Oracle Database 12c Release 1
Creating a File System
Create a tablespace to hold the file system.
CONN / AS SYSDBA

CREATE TABLESPACE dbfs_ts
  DATAFILE '/u01/app/oracle/oradata/DB11G/dbfs01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;
Create a user, grant DBFS_ROLE to the user and make sure it has a quota on the tablespace. Trying to create a file system from the SYS user fails, so it must be done via another user.
CONN / AS SYSDBA

CREATE USER dbfs_user IDENTIFIED BY dbfs_user
  DEFAULT TABLESPACE dbfs_ts QUOTA UNLIMITED ON dbfs_ts;

GRANT CREATE SESSION, RESOURCE, CREATE VIEW, DBFS_ROLE TO dbfs_user;
Create the file system in tablespace by running the "dbfs_create_filesystem.sql" script as the test user. The script accepts two parameters identifying the tablespace and file system name.
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user/dbfs_user

SQL> @dbfs_create_filesystem.sql dbfs_ts staging_area
The script created a partitioned file system. Although Oracle consider this the best option from a performance and scalability perspective, it can have two drawbacks:
• Space cannot be shared between partitions. If the size of the files is small compared to the total file system size this is not a problem, but if individual files form a large proportion of the total file system size, then ENOSPC errors may be produced.
• File rename operations may require the file to be rewritten, which can be problematic for large files.
If these issues present a problem to you, you can create non-partitioned file systems using the "dbfs_create_filesystem_advanced.sql" script. In fact, the "dbfs_create_filesystem_advanced.sql" script is called by the "dbfs_create_filesystem.sql" script, which defaults many of the advanced parameters.
If we later wish to drop a file system, this can be done using the "dbfs_drop_filesystem.sql" script with the file system name.
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user/dbfs_user

SQL> @dbfs_drop_filesystem.sql staging_area
FUSE Installation
In order to mount the DBFS we need to install the "Filesystem in Userspace" (FUSE) software. If you are not planning to mount the DBFS or you are running on an Non-Linux platform, this section is unnecessary. The FUSE software can be installed manually, from the OEL media or via Oracle's public yum server. If possible, use the Yum installation.
Yum FUSE Installation
Configure the server to point to Oracle's public yum repository. The instructions for this are available at "http://public-yumn.oracle.com".
Next, install the kernel developent package. It may already be present, in which case you will see a "Nothing to do" message.
# yum install kernel-devel
Finally, install the FUSE software.
# yum install fuse fuse-libs
Media FUSE Installation
If you can't use the Yum installation method you can install FUSE from your OEL media.
First, check to see if the "kernel-devel" package is installed.
rpm -q kernel-devel
kernel-devel-2.6.18-128.el5
#
If not, then install it from your media.
# cd /media/cdrom/Server
# rpm -Uvh kernel-devel*
Then install the FUSE software from your media.
# cd /media/cdrom/Server
# rpm -Uvh fuse-2* fuse-libs-2*
Manual FUSE Installation
If you can't perform a Yum or media installation of FUSE, you can always install it manually. This assumes you have installed the kernel development package using one of the previous methods.
Download the FUSE 2.7.3 package from http://fuse.sourceforge.net/.
Determine the kernel directory.
# echo /usr/src/kernels/`uname -r`-`uname -p`
/usr/src/kernels/2.6.18-128.el5-x86_64
#
Install the FUSE package as the "root" user using the following commands, substituting your kernel directory.
# tar -xzvf fuse-2.7.3.tar.gz
# cd fuse-2.7.3
# ./configure --prefix=/usr --with-kernel=/usr/src/kernels/2.6.18-128.el5-x86_64
# make
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules
# chmod 700 /etc/rc.modules
Mounting a File System
The dbfs_client tool is used to mount file systems on Linux servers. The usage is displayed if you call it without any parameters.
$ dbfs_client
usage: dbfs_client <db_user>@<db_server> [options] <mountpoint>
  db_user:              Name of Database user that owns DBFS content repository filesystem(s)
  db_server:            A valid connect string for Oracle database server
                        (for example, hrdb_host:1521/hrservice)
  mountpoint:           Path to mount Database File System(s)
                        All the file systems owned by the database user will be seen at the mountpoint.
DBFS options:
  -o direct_io          Bypass the Linux page cache. Gives much better performance for large files.
                        Programs in the file system cannot be executed with this option.
                        This option is recommended when DBFS is used as an ETL staging area.
  -o wallet             Run dbfs_client in background.
                        Wallet must be configured to get credentials.
  -o failover           dbfs_client fails over to surviving database instance with no data loss.
                        Some performance cost on writes, especially for small files.
  -o allow_root         Allows root access to the filesystem.
                        This option requires setting 'user_allow_other' parameter in '/etc/fuse.conf'.
  -o allow_other        Allows other users access to the file system.
                        This option requires setting 'user_allow_other' parameter in '/etc/fuse.conf'.
  -o rw                 Mount the filesystem read-write. [Default]
  -o ro                 Mount the filesystem read-only. Files cannot be modified.
  -o trace_file=STR     Tracing <filename> | 'syslog'
  -o trace_level=N      Trace Level: 1->DEBUG, 2->INFO, 3->WARNING, 4->ERROR, 5->CRITICAL [Default: 4]
  -h                    help
  -V                    version
[oracle@oel5-11gr2 admin]$
First we need to create a mount point with the necessary privileges as the "root" user.
# mkdir /mnt/dbfs
# chown oracle:oinstall /mnt/dbfs
Next, add a new library path.
# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf
Create symbolic links to the necessary libraries in the directory pointed to by the new library path. Note. Depending on your installation the "libfuse.so.2" library may be in an alternative location.
# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 /usr/local/lib/libclntsh.so.11.1
# ln -s $ORACLE_HOME/lib/libnnz11.so /usr/local/lib/libnnz11.so
# ln -s /lib64/libfuse.so.2 /usr/local/lib/libfuse.so.2
Issue the following command.
# ldconfig
The file system we've just created is mounted with the one of the following commands from the "oracle" OS user.
$ # Connection prompts for password and holds session.
$ dbfs_client dbfs_user@DB11G /mnt/dbfs

$ # Connection retrieves password from file and releases session.
$ nohup dbfs_client dbfs_user@DB11G /mnt/dbfs  < passwordfile.f &

$ # Connection authenticates using wallet and releases session.
$ nohup dbfs_client -o wallet /@DB11G_DBFS_USER /mnt/dbfs &
The wallet authentication is the safest method as the others potentially expose the credentials. Creation of a wallet is discussed later, but it is part of the Advanced Security option.
Once mounted, the "staging_area" file system is now available for use.
# ls -al /mnt/dbfs
total 8
drwxr-xr-x 3 root root    0 Jan  6 17:02 .
drwxr-xr-x 3 root root 4096 Jan  6 14:18 ..
drwxrwxrwx 3 root root    0 Jan  6 16:37 staging_area
# ls -al /mnt/dbfs/staging_area
total 0
drwxrwxrwx 3 root   root     0 Jan  6 16:37 .
drwxr-xr-x 3 root   root     0 Jan  6 17:02 ..
drwxr-xr-x 7 root   root     0 Jan  6 14:00 .sfs
#
To unmount the file system issue the following command from the "root" OS user.
# fusermount -u /mnt/dbfs
Wallet Creation
To create a wallet issue the following commands as the "oracle" OS user.
$ mkdir -p $HOME/oracle/wallet
$ $ORACLE_HOME/bin/mkstore -wrl $HOME/oracle/wallet -create
Add the following lines to the "$ORACLE_HOME/network/admin/sqlnet.ora" file.
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $HOME/oracle/wallet) ) )
SQLNET.WALLET_OVERRIDE = TRUE
Add the credentials to the wallet for a specific connect string. This could be the existing connect string (DB11G in mycase), but in this example I've used something a little more specific (DB11G_DBFS_USER).
$ mkstore -wrl $HOME/oracle/wallet -createCredential DB11G_DBFS_USER dbfs_user dbfs_user
Make sure the connect string is present in the "$ORACLE_HOME/network/admin/tnsnames.ora" file. In this case I just copied my existing connect string and gave it the new name.
DB11G_DBFS_USER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel5-11gr2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G.WORLD)
    )
  )
The file system can now be mounted using the wallet.
$ nohup dbfs_client -o wallet /@DB11G_DBFS_USER /mnt/dbfs &
Using /etc/fstab
Apart from having a familiar syntax, the use of the "/etc/fstab" file for mounting seems a little pointless since FUSE does not support automount, so you will still have to manually mount the file systems after a reboot. Actually this is fortunate as the automount would probably happen before the database was up, which would of course would be an issue.
Note: When this article was first written, the documentation for this step was not complete and the method proposed by Oracle did not work. The documentation has since been altered, but unfortunately the latest proposed method seems to work only when mounted from the "root" user (not recommended), rather than the "oracle" user. As a result I have switched to using the method proposed by Frits Hoogland in the article here. It works fine from the "oracle" user, but it I can't get it to work with the Wallet. Personally, I prefer to use the mount commands listed in the Mounting a File System section.
To allow mount operations from the "/etc/fstab", issue the following commands as the "root" user.
# groupadd fuse
# usermod -a -G fuse oracle
Add the following line into the "/etc/fstab" file.
/sbin/mount.dbfs#dbfs_user@DB11G_DBFS_USER /mnt/dbfs fuse rw,user,noauto,direct_io 0 0
Create the "/sbin/mount.dbfs" file with the following contents.
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
nohup $ORACLE_HOME/bin/dbfs_client $@ << FEEDPWD &
dbfs_user
FEEDPWD
Set the permissions using the following commands.
# chmod 750 /sbin/mount.dbfs
# chgrp fuse /sbin/mount.dbfs
The filesystem should now be mounted using the following command from the "oracle" OS user.
$ mount /mnt/dbfs
As before, the filesystem is unmounted using the following command.
$ fusermount -u /mnt/dbfs
dbfs_client Command Interface
As only Linux clients will be able to mount the file system, the majority of clients will access the file system via the dbfs_client command line, which provides a variety of UNIX-like file system command using the following syntax.
dbfs_client <db_user>@<db_serverr> --command <command> [switches] [arguments]
Commands include some typical UNIX file system commands like ls, cp, rm, mkdir. Arguments are typically file or directory names. The available switches are -a (all files), -l (long format) and -R (recursive).
Paths within the DBFS are referenced using the "dbfs:/file-system-name/" prefix. If this is not present, the path is assumed to be on the local filesystem of the machine running the client software.
Here are some examples of commands using the dbfs_client with the file system and wallet created earlier.
$ # Directory listing.
$ dbfs_client /@DB11G_DBFS_USER --command ls -a -l dbfs:/staging_area/
drwxr-xr-x              root            root               0    Jan 06 19:32    .sfs
$

$ # Make a directory and get directory listing.
$ dbfs_client /@DB11G_DBFS_USER --command mkdir dbfs:/staging_area/test_dir
$
$ dbfs_client /@DB11G_DBFS_USER --command ls -a -l dbfs:/staging_area/
drwx------            oracle        oinstall               0    Jan 07 11:41    dbfs:/staging_area/test_dir
drwxr-xr-x              root            root               0    Jan 06 19:32    .sfs
$

$ # Copy files to and from DBFS
$ dbfs_client /@DB11G_DBFS_USER --command cp /tmp/test.txt dbfs:/staging_area/test_dir/
/tmp/test.txt -> dbfs:/staging_area/test_dir/test.txt
$
$ dbfs_client /@DB11G_DBFS_USER --command cp dbfs:/staging_area/test_dir/test.txt /tmp/test_copy.txt
dbfs:/staging_area/test_dir/test.txt -> /tmp/test_copy.txt
$

$ # Remove file
$ dbfs_client /@DB11G_DBFS_USER --command rm dbfs:/staging_area/test_dir/test.txt
Unlinking file dbfs:/staging_area/test_dir/test.txt
$
The command list is quite limited at the moment, but the message returned from unsupported commands suggests more are on their way.
$ dbfs_client /@DB11G_DBFS_USER --command rmdir dbfs:/staging_area/test_dir
rmdir is not supported right now
$
Switches must applied separately (-a -l), not grouped (-al) or the results are unpredictable. Also, wildcards do not work.
For more information see:
• Oracle Database File System (DBFS) PL/SQL APIs
• Oracle Database File System (DBFS) Enhancements in Oracle Database 12c Release 1
• Introducing the Oracle Database File System
• DBFS File System Client
• Oracle database filesystem (DBFS) done the easy way!
• Using The Secure External Password Store (Doc ID 340559.1)