Oracle database filesystem (DBFS) done the easy way!

I was introduced to the database filesystem by Kevin Closson during an Exadata seminar. At first it struck me as a little odd. What could be a valid reason to store files or a filesystem inside the database? When doing research on the internet, I found a description for setup on Tim’s Oracle Base website.

The need for a database filesystem

Well, with databases which need to handle large amounts of data outside of the database (for loading for example, CSV files) being able to dynamically add filesystem space using the database is easy “for us database administrators”. When filesystem space outside of the database is needed we need to ask the sysadmin for X gigabytes of (non-database) space, which the sysadmin needs to ask from the SAN admin in most cases. That means it is impossible in almost any case to have a large filesystem at will.

In most cases, if we need to load large amounts of data, the database already has large amounts of space available which can be used. It would be very convenient of we (dedicated or temporarily) can allocate some of the database space to be filesystem space!

That is how I see the database filesystem. Obviously you could think of a number of other reasons to use it, and also can think of reasons not to use it. Nonetheless: let’s take a look further.

How would I like a database filesystem?

Reading the documentation, there are two choices for using the database filesystem on the database server: using the commandline tool (dbfs_client) to set it up, or using the start/stop system of the operating system start it up.

I like the database filesystem to be an help to the DBA exclusively. In my opinion, it doesn’t need to be up always. I see it as a tool for the DBA. I think it’s perfect if the DBA can start it up in an easy way (and shut it down).

A commandline tool with some options is not really handy. Starting up using the start/stop system (which is in the documentation : I don’t know if that really works…filesystems are mounted before the database is started (probably the database needs to use some of these filesystems to be mounted). This means that the database filesystem will not (can not) be available after the filesystems are mounted, because the database in which the filesystem data resides, is not started yet. I haven’t tested if it gets setup in some state which later picks up the database instance if it’s ready and then becomes usable.

Well, let’s set it up:

Setup the database

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 17 13:17:23 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create tablespace dbfs;

Tablespace created.

SQL> create user dbfs identified by dbfs
2 default tablespace dbfs
3 temporary tablespace temp
4 quota unlimited on dbfs;

User created.

SQL> grant create session, resource, create view, dbfs_role to dbfs;

Grant succeeded.

SQL> exit
$

Next we need to go into $ORACLE_HOME/rdbms/admin, and execute a script to create the filesystem inside the database:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus dbfs/dbfs

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 17 13:24:22 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @dbfs_create_filesystem.sql dbfs filesys1 -- tablespace filesystem
...
...lot's of output, snipped for readability...
...
$

The scripts exits automatically, so you end up on the commandline. You need to review the script output to see if everything went alright.

Setup Linux for DBFS

Create a directory which can act as the mountpoint for the filesystem:
# mkdir /mnt/dbfs
# chown oracle.oinstall /mnt/dbfs

Install fuse (I use Oracle’s Yum facility, see: http://public-yum.oracle.com/):
# yum install fuse fuse-libs

Make needed libraries available:
# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf

# 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

Reload the dynamic loadable library cache:
# ldconfig

Setup fstab:
# echo "/sbin/mount.dbfs#dbfs@bioinfo1 /mnt/dbfs fuse rw,user,noauto,direct_io 0 0" >> /etc/fstab

Create /sbin/mount.dbfs as a script, in order to set the ORACLE_HOME variable, and provide password. This is the content of the /sbin/mount.dbfs script:
#!/bin/bash
export ORACLE_HOME=/oracle/db/11.2.0.1
nohup $ORACLE_HOME/bin/dbfs_client $@ << FEEDPWD &
dbfs
FEEDPWD

Set appropriate rights on the script:
# chmod 750 /sbin/mount.dbfs
# chgrp fuse /sbin/mount.dbfs

Putting the password in the script isn’t the most secure way to do it, but I assume the database server is only accessed by the sysadmin and the Oracle user. An alternative is to setup a wallet.

Usage

The database filesystem can be mounted by the ‘oracle’ user using:
$ mount /mnt/dbfs

A line saying “nohup: appending output to `nohup.out'” will be displayed, because of the usage of nohup.

The database filesystem can be ‘umounted’ using:
$ fusermount -u /mnt/dbfs

The filesystem can be dropped using the script ‘dbfs_drop_filesystem.sql’ in $ORACLE_HOME/rdbms/admin/. If you are experimenting, do use the ‘dbfs_drop_filesystem’ prior to dropping the dbfs user.

About these ads
9 comments
  1. Hi.

    Cool. I’ll give it a go.

    Cheers

    Tim…

  2. Hi.

    You have a typo:

    “libnzz11″ should be “libnnz11″
    :)

    Cheers

    Tim…

  3. Jan said:

    hey,
    we’ve got another use of dbfs (currently oracle content services):
    – +10 billion files
    – a large tier based infrastructure accessing these files
    – a mass of postprocessing instances doing signal analysis and postprocessing
    – client, role and user based security restrictions on folders (in nearly 2.000.000 folders)
    – easy role based access by application servers in the name of special or registered users (where these restrictions impact file access etc.)
    – webservice interfaces, ftp and of course native client interface

    dbfs is nice to have for such environments but it’s “search” performance is not a big benefit in case of massive data updates/inserts. everything will be indexed and therefore the ocs/dbfs locks entries, locks security tables for updates etc. pp. and this takes a lot(!) of time. (“for update wait X” statements are such a performance killer…)

    if you sipmly need a file system capable of storing files, restrictions etc. and accessing by applications (in userspace with multiple roles) like webservice etc. but don’t need to search for every attribute of these files/folders then dbfs is just the wrong decision.

  4. Ran through it and the mount failed at the end … so I’m going back through it all to see if I can figure out why, and I’ve a small comment to make on your naming choices …
    Calling both the tablespace and the user ‘dbfs’ means we don’t know if you’re using the tablespace or the user (or indeed the user’s password) in subsequent commands. Choosing different (obvious) names could add to the clarity.
    eg dbfsts, dbfsus, dbfspw

    Otherwise, it does look simple (even though it didn’t work for me!)

    UBG

    • I understand your point. For me this works very well, if you feel you should change username and tablespace, please do!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,136 other followers

%d bloggers like this: