ZFS is recommended for any Oracle database version in single instance mode. ZFS can be used with an Oracle RAC database when it is available as a NFS-shared file system.
Review the following recommendations below for tuning ZFS for an Oracle database:
Verify that you are running the latest Oracle Solaris release
Start with the latest Oracle Solaris 10 or Oracle Solaris 11 release, with the Solaris 10 9/10 release as a minimum starting point.
Create LUNs for your ZFS storage pools, if needed
Use your storage array tools to create LUNs that will be presented to the ZFS storage pool. Or, consider using whole disks for your mirrored ZFS storage pools. For more information, see Chapter 3, Managing Oracle Solaris ZFS Storage Pools, in Managing ZFS File Systems in Oracle Solaris 11.2 .
Create a storage pool for data files for tables, index, undo and temp data
Consider creating a mirrored storage pool to provide a higher level of data redundancy. For example:
# zpool status dbpool pool: dbpool state: ONLINE scan: none requested config: NAME STATE READ WRITE CKSUM dbpool ONLINE 0 0 0 mirror-0 ONLINE 0 0 0 c0t5000C500335F95E3d0 ONLINE 0 0 0 c0t5000C500335F907Fd0 ONLINE 0 0 0 mirror-1 ONLINE 0 0 0 c0t5000C500335BD117d0 ONLINE 0 0 0 c0t5000C500335DC60Fd0 ONLINE 0 0 0 errors: No known data errors
For databases with high redo log activity, such as a typical OLTP database with many commits, use a separate LUN for a separate log device.
Create a storage pool for the archivelog
If available, a system's internal disk can handle this type of load. The archivelog file system can also be a file system in the dbpool.
# zpool create archivepool c0t5000C500335E106Bd0
Create the ZFS file systems and set the specific file system properties by using the following guidelines
Create separate file systems for redo, archive, undo, and temp database components using the following recordsize:
Oracle Solaris 11 and earlier releases - 128K
Oracle Solaris 11.1 and later releases - 1M
The general rule is to set the file system recordsize = db_block_size for the file systems that contains Oracle data files. For table data and index components, create a file system with an 8 KB record size. Also consider providing metadata caching hints for your database file systems by using the primarycache property. For more information about ZFS file system properties, see Introducing ZFS Properties in Managing ZFS File Systems in Oracle Solaris 11.2 .
Create file systems for the table data files and index data files with an 8 KB recordsize. Use the default value for primarycache.
# zfs create -o recordsize=8k -o mountpoint=/my_db_path/index dbpool/index # zfs set logbias=throughput dbpool/index # zfs get primarycache,recordsize,logbias dbpool/index NAME PROPERTY VALUE SOURCE dbpool/index primarycache all default dbpool/index recordsize 8K local dbpool/index logbias throughput local
Create file systems for temporary and undo table spaces
For Oracle Solaris 11 and earlier releases, use the default recordsize and primarycache values.
# zfs create -o mountpoint=/my_db_path/temp dbpool/temp # zfs set logbias=throughput dbpool/temp # zfs create -o mountpoint=/my_db_path/undo dbpool/undo # zfs set logbias=throughput dbpool/undo
For Oracle Solaris 11.1 and later releases, use the following recordsize and default primarycache values.
# zfs create -o recordsize=1m -o mountpoint=/my_db_path/temp dbpool/temp # zfs set logbias=throughput dbpool/temp # zfs create -o recordsize=1m -o mountpoint=/my_db_path/undo dbpool/undo # zfs set logbias=throughput dbpool/undo
Create a storage pool for redo logs with a separate log device. For databases with high redo log activity, such as a typical OLTP database with many commits, use a separate log device LUN.
Partition the disk into two slices, a small slice, s0, in the 64 to 150 MB range, for the separate log device. The s1 slice contains the remaining disk space for the redo log.
# zpool create redopool c0t50015179594B6F11d0s1 log c0t50015179594B6F11d0s0 # zpool status redopool pool: redopool state: ONLINE scan: none requested config: NAME STATE READ WRITE CKSUM redopool ONLINE 0 0 0 c0t50015179594B6F11d0s1 ONLINE 0 0 0 logs c0t50015179594B6F11d0s0 ONLINE 0 0 0 errors: No known data errors
Create a file system for redo logs in the redo pool.
For Oracle Solaris 11 and earlier releases, use the default file system values for recordsize and primarycache.
# zfs create -o mountpoint=/my_db_path/redo redopool/redo # zfs set logbias=latency redopool/redo
For Solaris 11.1 and later releases, use the following recordsize and default primarycache values.
# zfs create -o recordsize=1m -o mountpoint=/my_db_path/redo redopool/redo # zfs set logbias=latency redopool/redo
Create a file system for archivelog files in the archive pool.
For Oracle Solaris 11 and earlier releases, enable compression using the default value for recordsize and set primarycache to metadata
# zfs create -o compression=on -o primarycache=metadata -o mountpoint= /my_db_admin_path/archive archivepool/archive # zfs get primarycache,recordsize,compressratio,compression,available, used,quota archivepool/archive NAME PROPERTY VALUE SOURCE archivepool/archive primarycache metadata local archivepool/archive recordsize 128K default archivepool/archive compressratio 1.32x - archivepool/archive compression on local archivepool/archive available 40.0G - archivepool/archive used 10.0G - archivepool/archive quota 50G local
For Solaris 11.1 and later releases - Enable compression, set primarycache to metadata and use the following recordsize value:
# zfs create -o compression=on –o recordsize=1M \ -o mountpoint=/my_db_admin_path/archive archivepool/archive # zfs get primarycache,recordsize,compressratio,compression,\ available,used,quota archivepool/archive NAME PROPERTY VALUE SOURCE archivepool/archive primarycache all local archivepool/archive recordsize 1M local archivepool/archive compressratio 1.32x - archivepool/archive compression on local archivepool/archive available 40.0G - archivepool/archive used 10.0G - archivepool/archive quota 50G local
Consider setting quotas so that your database file systems have sufficient disk space to operate and taking snapshots of your database file systems. In addition, set a reservation on a dummy file system to reserve 10-20% of pool space to maintain pool performance.
# zfs set reservation=20gb dbpool/freespace
For additional information about tuning storage arrays and memory resources, see the white paper at http://www.oracle.com/technetwork/server-storage/solaris/config-solaris-zfs-wp-167894.pdf.
Additional Oracle database configuration recommendations
Configuring Your Oracle Database on ZFS File Systems in the following white paper:
http://www.oracle.com/technetwork/server-storage/solaris/config-solaris-zfs-wp-167894.pdf
Dynamic SGA Tuning of Oracle Database on Oracle Solaris with DISM white paper:
Oracle 11g Installation Guides
Oracle Database Quick Installation Guide 11g Release 2 (11.2) for Oracle Solaris on SPARC (64-Bit)
http://docs.oracle.com/cd/E11882_01/install.112/e24349/toc.htm
Oracle Database Quick Installation Guide 11g Release 2 (11.2) for Oracle Solaris on x86-64 (64-Bit)
http://docs.oracle.com/cd/E11882_01/install.112/e24351/toc.htm