Archive

Tag Archives: oracle database exadata filesystem dbfs linux

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.

Follow

Get every new post delivered to your Inbox.

Join 1,883 other followers

%d bloggers like this: