Oct 18 2009

11gR2 New Feature – DBFS Database File System

Category: 11g,Databaseittichai @ 8:15 pm

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: , , ,

4 Responses to “11gR2 New Feature – DBFS Database File System”

  1. Blogroll Report 16/10/2009-23/10/2009 « Coskan’s Approach to Oracle says:

    [...] Ittichai Chammavanijakul-11gR2 New Feature – DBFS Database File System [...]

  2. Simon says:

    I think the reason why the dbfs_client never returns when you mount is it needs to stay running to handle the operations for the mounted file system. Mounting of DBFS is implemented using FUSE, which enables filesystems on Linux to exist in user mode (rather than the kernel). The dbfs_client stays running, so it can receive the FUSE requests from the kernel, pass them on to the Oracle DB, and then send the response back to the kernel. If you exit dbfs_client, then the kernel will no longer have anyone to send the FUSE requests to, and operations on the mounted FS will fail.

    Common approach I use in situations like this is:
    ( nohup command 2&1 > command.log & )

    • ittichai says:

      Simon,
      Yes, after reading your comment, I went back and looked at Oracle document. There, it mentioned about how to free terminal (as well as read the password from a file) using nohup as you suggested. I already updated the blog content. Thanks for your inputs.

      Ittichai

  3. Duluth Garage Door installation says:

    Bloggers are generally under appreciated, many thanks for spending some time to post this.

Leave a Reply