Wednesday, December 28, 2011

Oracle datafiles and Veritas Quick I/O files

If you want to have a maximum performance in datafile access you should use raw devices for datafiles, but most modern operating systems manage file access very well, you can use ASM for Oracle database storage management, and managing raw devices requires a skilled sysadmin and is time-consuming, therefore almost all the time the extra speed gained with raw devices is not worth the extra skills and effort required to maintain raw devices.

But if you have an Oracle database in a Solaris server with Veritas VxFS filesystems with Quick I/O included (that is, licensed), then you can use Quick I/O access with regular files; this way you can have and manage regular files but at the same time you can access them like raw devices. An unprivileged user can create Quick I/O links and is very easy to do; first you have to create your datafile at operating system level with qiomkfile:

oracle@myserver$ qiomkfile -h 32k -s 16000M /myfs/mydatabase/mydatafile.dbf

oracle@myserver$ ls -la /myfs/mydatabase
total 32768066
drwxr-xr-x 3 oracle dba 96 Sep 24 11:32 .
drwxr-xr-x 61 oracle dba 1024 Sep 23 16:57 ..
-rw-r--r-- 1 oracle dba 16777248768 Sep 24 11:32 .mydatafile.dbf
lrwxrwxrwx 1 oracle dba 26 Sep 24 11:32 mydatafile.dbf -> .mydatafile.dbf::cdev:vxfs:
drwxr-xr-x 2 oracle dba 96 Sep 23 16:50 lost+found

The secret is that .mydatafile.dbf is a regular (but single contiguous extent) file, and
mydatafile.dbf is a link to .mydatafile.dbf::cdev:vxfs: but the operating system recognizes the ::cdev:vxfs: suffix as Quick I/O and access .mydatafile.dbf like a raw device; that's why the .mydatafile.dbf::cdev:vxfs: file must not exist. The qiomkfile parameters are simple; -h is the extra space added to the file to use it as an Oracle datafile (in this case 16,000 megabytes plus 32 kilobytes) because Oracle adds one database block (DB_BLOCK_SIZE parameter) to each datafile created, and -s is the required size of the Oracle datafile that should match the DATAFILE ... SIZE clause.

This was the difficult part; in order to create datafiles that use Quick I/O features you just have to use the REUSE clause:

SQL> CREATE TABLESPACE MYTBL DATAFILE '/myfs/mydatabase/mydatafile.dbf' SIZE 16000M REUSE;

Tablespace created.

SQL> ALTER TABLESPACE MYTBL ADD DATAFILE '/myfs/mydatabase/mydatafile2.dbf' SIZE 16000M REUSE;

Tablespace altered.

And talking about dropping tablespaces with Quick I/O datafiles, don't use the INCLUDING CONTENTS AND DATAFILES clause or you will get an error, just drop the tablespace and erase the files with rm at operating system level.

More information:

Veritas Storage Foundation for Oracle Administrator’s Guide (chapter 4)
Veritas Storage Foundation 5.0 Software

No comments:

Post a Comment