The DBFS (Database File System), one of the new 11gR2 features, takes advantage of the SecureFiles feature (which is new in 11gR1). The SecureFiles provide powerful file storage features (including de-duplication, compression, etc.) which removes performance barrier to storing files in the database. This stands in contrast to LOB (now called the BasicFiles). Not only does the DBFS provide the standard file system interface (path names, directories and links) to store and access files in the database, its benefits also include security and performance from using SecureFiles.
DBFS configuration
1. Follow the DBFS prerequisites and installation instructions here.
2. Install the FUSE package.
Since oracle user will do sudo, add it into /etc/sudoers.
$ tar -xzvf fuse-2.7.3.tar.gz
$ cd fuse-2.7.3
$ ./configure --prefix=/usr/src/kernels/`uname -r`-`uname -p`
$ make
$ sudo su
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules
3. Create a database user and a tablespace to store data.
SQL> create user dbfs_admin identified by dbfs_admin;
SQL> grant create session, resource, create view to dbfs_admin;
SQL> grant dbfs_role to dbfs_admin;
SQL> create tablespace tbs_dbfs datafile '/u1/oradata/DB11LNX/dbfs.dbs' size 100m;
The tablespace must be the Automatic Segment Space Management (ASSM) in order to use the SecureFiles. This is the default setting in 11g.
3. Create a file system using dbfs_create_filesystem.sql (located at $ORACLE_HOME/rdbms/admin).
The dbfs_create_filesystem.sql creates a partitioned file system which stores data in the multiple physical segments. The files will be distributed randomly in these partitions. This way it gives the best performance and scalability.
$ cd $ORACLE_HOME/rdbms/admin
SQL> conn dbfs_admin/dbfs_admin
SQL> @dbfs_create_filesystem.sql tbs_dbfs staging_area
Note that the last argument (e.g., staging_area) will be visible as the name of the file system.
When creating a new file system, a new partitioned table will be created having its name from the file system’s name with T_ prefix.
SQL> select table_name, partition_name from user_tab_partitions
where table_name like '%STAGING_AREA';
TABLE_NAME PARTITION_NAME
----------------------------- ------------------------------
T_STAGING_AREA SYS_P141
T_STAGING_AREA SYS_P142
T_STAGING_AREA SYS_P143
T_STAGING_AREA SYS_P144
T_STAGING_AREA SYS_P145
T_STAGING_AREA SYS_P146
T_STAGING_AREA SYS_P147
T_STAGING_AREA SYS_P148
T_STAGING_AREA SYS_P149
T_STAGING_AREA SYS_P150
T_STAGING_AREA SYS_P151
T_STAGING_AREA SYS_P152
T_STAGING_AREA SYS_P153
T_STAGING_AREA SYS_P154
T_STAGING_AREA SYS_P155
T_STAGING_AREA SYS_P156
16 rows selected.
4. Verify by copying files into the exposed file system.
SQL> conn dbfs_admin/dbfs_admin
SQL> !cat ~/dbfs_show_content.sql
col pathname format a40
col pathtype format a10
col contents format a20
select pathname, pathtype,
utl_raw.cast_to_varchar2(filedata) as contents
from dbfs_content
order by std_creation_time;
This shows the default directories currently in database.
SQL> @~/dbfs_show_content.sql
PATHNAME PATHTYPE CONTENTS
------------------------------ ---------- ------------------------------
/staging_area directory
/staging_area/.sfs directory
/staging_area/.sfs/RECYCLE directory
/staging_area/.sfs/attributes directory
/staging_area/.sfs/content directory
/staging_area/.sfs/snapshots directory
/staging_area/.sfs/tools directory
7 rows selected.
Let’s create a directory named test_dir. Please note that base directory name staing_area is from the file system created previously. The dbfs_client can be executed from any systems which meet the prerequisite requirements mentioned in the step 1.
{client}$ dbfs_client dbfs_admin@DB11LNX --command mkdir dbfs:/staging_area/test_dir
Password: dbfs_admin
A new directory test_dir is now visible as a new record.
SQL> @~/dbfs_show_content.sql
PATHNAME PATHTYPE CONTENTS
------------------------------ ---------- ------------------------------
/staging_area directory
/staging_area/.sfs directory
/staging_area/.sfs/RECYCLE directory
/staging_area/.sfs/attributes directory
/staging_area/.sfs/content directory
/staging_area/.sfs/snapshots directory
/staging_area/.sfs/tools directory
/staging_area/test_dir directory
8 rows selected.
Copy a file into it.
{client}$ echo "hello world" > /tmp/dbfs_file
{client}$ dbfs_client dbfs_admin@DB11LNX --command cp /tmp/dbfs_file dbfs:/staging_area/test_dir
Password: dbfs_admin
/tmp/dbfs_file -> dbfs:/staging_area/test_dir/dbfs_file
SQL> @~/dbfs_show_content.sql
PATHNAME PATHTYPE CONTENTS
---------------------------------------- ---------- ------------------------------
/staging_area directory
/staging_area/.sfs directory
/staging_area/.sfs/attributes directory
/staging_area/.sfs/tools directory
/staging_area/.sfs/snapshots directory
/staging_area/.sfs/RECYCLE directory
/staging_area/.sfs/content directory
/staging_area/test_dir directory
/staging_area/test_dir/dbfs_file file hello world
9 rows selected.
Optionally you can also mount this file system on the client, so the file operations can be done without invoking dbfs_client every time.
Create a mount point. (*)
{client}# mkdir /mnt/dbfs
{client}# chown oracle:dba /mnt/dbfs
{client}# chmod 755 /mnt/dbfs
$ dbfs_client dbfs_admin@DB11LNX /mnt/dbfs
password: dbfs_admin
:
Somehow on my test system, the prompt never returned even though the file system is mounted successfully. So in order to test it, I left this window open, and execute the remaining of commands in a new window.
The prompt will never return using above syntax. However, the following example will read the password from a file, mount a file system, and then free the terminal. (Thanks Simon for pointing this out.)
$ nohup dbfs_client dfs_admin@DB11LNX /mnt/dbfs < passwordfile.f &
Note that now I can perform all standard Unix file/directory syntaxes to this mount point.
{client}$ echo "hello world 2" > /tmp/dbfs_file2
{client}$ cp /tmp/dbfs_file2 /mnt/dbfs/staging_area/test_dir
SQL> @~/dbfs_show_content.sql
PATHNAME PATHTYPE CONTENTS
---------------------------------------- ---------- ------------------------------
/staging_area directory
/staging_area/.sfs directory
/staging_area/.sfs/attributes directory
/staging_area/.sfs/tools directory
/staging_area/.sfs/snapshots directory
/staging_area/.sfs/RECYCLE directory
/staging_area/.sfs/content directory
/staging_area/test_dir directory
/staging_area/test_dir/dbfs_file file hello world
/staging_area/test_dir/dbfs_file2 file hello world2
10 rows selected.
There are a lot of administrative options you can do from here including using Oracle wallet so no password will be prompted when mounting a DBFS store. You can also mount DBFS through the fstab. See instructions in the Oracle 11gR2′s DBFS File System Client.
One of the claimed benefits of using DBFS is I/O throughput performance in a range of 5-7 GB/sec. I plan to perform performance tests and report the results in the next post. Stay tune!.
(*) Initially, I received this error when trying to run dbfs_client.
dbfs_client: error while loading shared libraries: libfuse.so.2: cannot open shared object file: No such file or directory
By creating a softlink to the /lib folder, it’s solved the problem.
# ln -s /usr/src/kernels/2.6.18-92.el5-i686/lib/libfuse.so.2 /lib/libfuse.so.2
Tags: 11gR2, Database File System, DBFS, New Feature
