Oracle Database File System creates a file system interface for files and directories stored in a database. When the file system is mounted programs can store and manipulate files that are held in the database as on any other file system without the need to handle BLOBS or CLOBS.
The client tool dbfs_client can be used to access the file system externally from any Linux or Solaris machine in which the Oracle client is installed. On Linux systems dbfs_client can be used to mount the DBFS using the FUSE project (Filesystem in Userspace).
This document will detail the steps to create and mount an Oracle Database File System on Linux.
Installing FUSE
Download FUSE 2.7.3 from http://fuse.sourceforge.net/
Verify that you have the kernel-devel package installed.
[oracle@ora1 ~]$ rpm -qa | grep kernel-devel kernel-devel-2.6.18-8.el5 [oracle@ora1 ~]$
As the root user install FUSE using the following steps.
Verify the kernel directory.
[root@ora1 ~]# ls /usr/src/kernels 2.6.18-8.el5-i686 [root@ora1 ~]#
In the configure step you will need the full path the kernel directory. Below are the steps to build and install FUSE. I have removed the output from many of the steps for clarity.
[root@ora1 ~]# tar -xzvf fuse-2.7.3.tar.gz [root@ora1 fuse-2.7.3]# ./configure --prefix=/usr --with-kernel=/usr/src/kernels/2.6.18-8.el5-i686 [root@ora1 fuse-2.7.3]# make [root@ora1 fuse-2.7.3]# make install [root@ora1 fuse-2.7.3]# /sbin/depmod [root@ora1 fuse-2.7.3]# /sbin/modprobe fuse [root@ora1 fuse-2.7.3]# chmod 666 /dev/fuse [root@ora1 fuse-2.7.3]# echo "/sbin/modprobe fuse" >> /etc/rc.modules
Create the Tablespace to be used as the File System and User to own it
Create the tablespace to be used as the file system.
SQL> create tablespace dbfs 2 datafile '/u03/app/oracle/oradata/orcl/dbfs01.dbf' 3 size 500M autoextend on next 1M; Tablespace created. SQL>
Create the user that will be used to create and own the file system. The user needs the following privileges: CONNECT, CREATE TABLE, CREATE PROCEDURE and DBFS_ROLE. The role DBFS_ROLE is needed so the user can create file systems.
SQL> create user oradbfs identified by password 2 default tablespace dbfs quota unlimited on dbfs; User created. SQL> grant connect, create table, create procedure, dbfs_role to oradbfs; Grant succeeded. SQL>
Creating a File System
The file system is created by running the ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql script. This script takes two parameters, the tablespace name and the file system name.
The dbfs_create_filesystem.sql script calls the ORACLE_HOME/rdbms/admin/dbfs_create_filesystem_advanced.sql script. This script takes six parameters listed below.
Tablespace Name – name of the tablespace to hold the file system.
Filesystem Name – name to give the file system
compression – compress-high, compress-medium, nocompress
deduplicate – deduplicate, nodeduplicate
encrypt – encrypt, noencrypt
partition – non-partition, partition, partition-by-itemname, partition-by-guid, partition-by-path.
The dbfs_create_filesystem.sql script creates a file system with nocompress, nodeduplicate, noencrypt, and partition options.
According to the Oracle documentation, partitioning is the best performing and scalable way to create a file system in DBFS. Partitioning creates multiple physical segments in the database and files are distributed randomly in the partitions.
There are some things to keep in mind concerning partitioning. It is possible to receive an [ENOSPC]: No space left on device error while there is free space available because space cannot be shared between partitions. A rename operation can cause the file to be rewritten to another partition.
Below we create the file system DBFS. The output from the script was removed for clarity.
[oracle@ora1 ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@ora1 admin]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 15 12:13:43 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> connect oradbfs Enter password: Connected. SQL> @dbfs_create_filesystem.sql dbfs dbfs
Mounting the DBFS Store
Now that we have the file system created we can mount it using dbfs_client. Before executing dbfs_client ensure that LD_LIBRARY_PATH has the correct path to the Oracle client libraries.
Below are the options that can be used with dbfs_client.
[oracle@ora1 ~]$ 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@ora1 ~]$
For this example all wee need to pass is the connection information and location in which to mount the file system.
[oracle@ora1 ~]$ dbfs_client oradbfs@orcl /u01/app/oracle/dbfs Password:
Note: when mounting the database file system above the command does not return after entering the password until the file system is unmounted.
See the post Using an Oracle Wallet with dbfs_client to learn how to configure an Oracle Wallet so you can perform the mount in the background.
If you open another terminal window you can see that the DBFS file system was mounted using df.
[oracle@ora1 ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 18G 15G 1.8G 90% / tmpfs 1014M 528M 486M 53% /dev/shm /dev/sdb1 40G 677M 37G 2% /u03 dbfs 5.3M 144K 5.2M 3% /u01/app/oracle/dbfs [oracle@ora1 ~]$
Switching to the directory we see the dbfs folder representing our DBFS file system.
[oracle@ora1 orcl]$ cd /u01/app/oracle/dbfs [oracle@ora1 dbfs]$ ls dbfs [oracle@ora1 dbfs]$ ls -alh total 4.0K drwxr-xr-x 3 root root 0 Apr 15 14:12 . drwxr-xr-x 10 oracle oinstall 4.0K Apr 15 12:25 .. drwxrwxrwx 3 root root 0 Apr 15 12:35 dbfs [oracle@ora1 dbfs]$
Below are the contents of the dbfs file system. At the moment there is only one item, a hidden directory called sfs.
[oracle@ora1 dbfs]$ cd dbfs [oracle@ora1 dbfs]$ ls -alh total 0 drwxrwxrwx 3 root root 0 Apr 15 14:16 . drwxr-xr-x 3 root root 0 Apr 15 14:16 .. drwxr-xr-x 7 root root 0 Apr 15 12:14 .sfs [oracle@ora1 dbfs]$ cd .sfs [oracle@ora1 .sfs]$ ls attributes content RECYCLE snapshots tools [oracle@ora1 .sfs]$
Before going too much further lets look in the database to see what tables were created.
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ T_DBFS SFS$_FSTP_1 SQL>
The table T_DBFS contains information associated with files and folders on a file system such as name, owner, permissions, etc.
SQL> describe t_dbfs Name Null? Type ----------------------------------------- -------- ---------------------------- VOLID NOT NULL NUMBER CSNAP# NOT NULL NUMBER LSNAP# NUMBER PATHNAME NOT NULL VARCHAR2(1024) ITEM NOT NULL VARCHAR2(256) PATHTYPE NOT NULL NUMBER(38) FILEDATA BLOB POSIX_NLINK NUMBER(38) POSIX_MODE NUMBER(38) POSIX_UID NUMBER(38) POSIX_GID NUMBER(38) STD_ACCESS_TIME NOT NULL TIMESTAMP(6) STD_ACL VARCHAR2(1024) STD_CHANGE_TIME NOT NULL TIMESTAMP(6) STD_CONTENT_TYPE VARCHAR2(1024) STD_CREATION_TIME NOT NULL TIMESTAMP(6) STD_DELETED NOT NULL NUMBER(38) STD_GUID NOT NULL NUMBER(38) STD_MODIFICATION_TIME NOT NULL TIMESTAMP(6) STD_OWNER VARCHAR2(32) STD_PARENT_GUID NOT NULL NUMBER(38) STD_REFERENT VARCHAR2(1024) OPT_HASH_TYPE VARCHAR2(32) OPT_HASH_VALUE VARCHAR2(128) OPT_LOCK_COUNT NUMBER(38) OPT_LOCK_DATA VARCHAR2(128) OPT_LOCK_STATUS NUMBER(38) SQL>
On a fresh file system build the T_DFS has seven items.
SQL> select item from t_dbfs; ITEM -------------------------------------------------------------------------------- .sfs content tools ROOT RECYCLE attributes snapshots 7 rows selected. SQL>
Below is the description of the SFS$_FSTP_1 table.
SQL> describe sfs$_fstp_1 Name Null? Type ----------------------------------------- -------- ---------------------------- VOLID NOT NULL NUMBER CSNAP# NOT NULL NUMBER LSNAP# NUMBER STD_GUID NOT NULL NUMBER(38) PROPNAME NOT NULL VARCHAR2(32) PROPVALUE VARCHAR2(1024) TYPECODE NOT NULL NUMBER(38) SQL>
You can create and delete files and directories just like any other file system.
[oracle@ora1 oracle]$ cd /u01/app/oracle/dbfs/dbfs/ [oracle@ora1 dbfs]$ ls -alh total 0 drwxrwxrwx 3 root root 0 Apr 15 14:16 . drwxr-xr-x 3 root root 0 Apr 16 14:59 .. drwxr-xr-x 7 root root 0 Apr 15 12:14 .sfs [oracle@ora1 dbfs]$ mkdir oracle [oracle@ora1 dbfs]$ cd oracle [oracle@ora1 oracle]$ cp ~/oracle/*.ora . [oracle@ora1 oracle]$ rm tnsnames.ora [oracle@ora1 oracle]$ ls -alh total 512 drwxr-xr-x 2 oracle oinstall 0 Apr 16 15:18 . drwxrwxrwx 4 root root 0 Apr 16 14:59 .. -rw-r--r-- 1 oracle oinstall 337 Apr 16 15:00 sqlnet.ora [oracle@ora1 oracle]$
The directory and file added are reflected in the database.
SQL> select pathname from t_dbfs; PATHNAME -------------------------------------------------------------------------------- / /.sfs /.sfs/RECYCLE /.sfs/attributes /.sfs/content /.sfs/snapshots /.sfs/tools /oracle /oracle/sqlnet.ora 9 rows selected. SQL>
Unmounting the DBFS Store
To unmount the DBFS file system use the command fusermount
[oracle@ora1 oracle]$ fusermount -u /u01/app/oracle/dbfs