Hybrid Columnar Compression (HCC) is one of the Exadata features but lately Oracle has been pushing this featurei to other Oracle hardware like the ZFS Storage Appliance and Axiom Pillar Storage series. We recently got a ZFS Storage Appliance (ZFSSA) at VX Company, so we are now able to use HCC on the Oracle Database Appliance (ODA). To use HCC we need to create a tablespace with datafiles on a ZFS Storage Appliance, in order to de so we going to hookup our ODA using directNFS (dNFS). I am not going in the details of explaining dnfs in this blogpost, there are enough blogs around who have excellent background information on dnfs:
http://www.pythian.com/news/34425/oracle-direct-nfs-how-to-start/
https://blogs.oracle.com/XPSONHA/entry/using_dnfs_for_test_purposes
So lets setup dnfs for our HCC table on my ODA. First we create a mountpoint (on all the RAC nodes):
[root@vxoda12 ~]# mkdir /mnt/vxzfs [root@vxoda12 ~]# chown oracle:oinstall /mnt/vxzfs
Add the NFS export to fstab (on all the RAC nodes), this should of course be a NFS share on our ZFSSA
[root@vxoda11 ~]# cat /etc/fstab|grep nfs vxzfs.oracle.vxcompany.local:/export/odatestdrive /mnt/vxzfs nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 [root@vxoda11 ~]# mount /mnt/vxzfs/ [root@vxoda11 ~]# mount | grep nfs sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw) vxzfs.oracle.vxcompany.local:/export/odatestdrive on /mnt/vxzfs type nfs (rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,nfsvers=3,timeo=600,addr=10.12.0.211)
Next create a file called oranfstab, if you are on a RAC Instance don’t forget to change local IP address on the other nodes:
[oracle@vxoda11 [odadb011] trace]$ vi $ORACLE_HOME/dbs/oranfstab server: vxzfs.oracle.vxcompany.local path: 10.12.0.211 local: 10.12.0.202 export: /export/odatestdrive mount: /mnt/vxzfs
Change the Oracle ODM library to the Oracle ODM NFS library (on all the RAC nodes):
[oracle@vxoda11 [] ~]$ cd $ORACLE_HOME/lib [oracle@vxoda11 [] lib]$ ls -al libodm11.so lrwxrwxrwx 1 oracle oinstall 12 Nov 8 13:15 libodm11.so -> libodmd11.so [oracle@vxoda11 [] lib]$ ln -sf libnfsodm11.so libodm11.so [oracle@vxoda11 [] lib]$ ls -al libodm11.so lrwxrwxrwx 1 oracle oinstall 14 Nov 13 12:54 libodm11.so -> libnfsodm11.so
Restart the database:
[oracle@vxoda11 [odadb011] lib]$ srvctl stop database -d odadb01 -o immediate [oracle@vxoda11 [odadb011] lib]$ srvctl start database -d odadb01 [oracle@vxoda11 [odadb011] lib]$ srvctl status database -d odadb01 Instance odadb011 is running on node vxoda11 Instance odadb012 is running on node vxoda12
Check alertlog you should now see:
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
Create a tablespace on the NFS share
SYS@odadb011 AS SYSDBA> create bigfile tablespace dnfs datafile '/mnt/vxzfs/dnfs.dbf' size 500G extent management local autoallocate;
Now you should see some entries in v$dnfs_servers:
SYS@odadb012 AS SYSDBA> col dirname form a50
SYS@odadb012 AS SYSDBA> col svrname form a50
SYS@odadb012 AS SYSDBA> select * from v$dnfs_servers;
INST_ID ID SVRNAME DIRNAME MNTPORT NFSPORT WTMAX RTMAX
---------- ---------- -------------------------------------------------- -------------------------------------------------- ---------- ---------- ---------- ----------
1 1 vxzfs.oracle.vxcompany.local /export/odatestdrive 59286 2049 1048576 1048576
SYS@odadb012 AS SYSDBA>
If we then try to create a HCC table in our dNFS tablespace we see this:
SYS@kjj2 AS SYSDBA> create table t3 compress for archive low tablespace dnfs_kjj as select * from dba_objects;
create table t3 compress for archive low tablespace dnfs_kjj as select * from dba_objects
*
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type
Apperently Oracle does not know that we are storing our data on a ZFS Appliance. To see what is going we can take a look at the traffic between my ODA en ZFSSA using tcpdump. When looking at the dump in wireshark we see these packages going between ODA and the ZFSSA:
So Oracle tries to do SNMP calls to the ZFSSA, lets see what happens if we do a get to the MIB displayed in the tcp frame above:
[root@vxoda11 ~]# snmpget -v1 -c public 10.12.0.211 1.3.6.1.4.1.42.2.225.1.4.2.0 Timeout: No Response from 10.12.0.211.
We need to enable SNMP on the ZFSSA (needless to say you need to replace the e-maill address with proper one):
user@localhost:~$ ssh admin@vxzfs.oracle.vxcompany.local
Password:
Last login: Sun Nov 18 18:20:32 2012 from 10.12.0.252
Waiting for the appliance shell to start ...
vxzfs:> configuration services snmp
vxzfs:configuration services snmp> set network=10.12.0.0/24
network = 10.12.0.0/24 (uncommitted)
vxzfs:configuration services snmp> set syscontact=<someusername>@vxcompany.com
syscontact = <someusername>@vxcompany.com (uncommitted)
vxzfs:configuration services snmp> enable
vxzfs:configuration services snmp> commit
vxzfs:configuration services snmp> show
Properties:
<status> = online
community = public
network = 10.12.0.0/24
syscontact = <someusername>@vxcompany.com
trapsinks = 127.0.0.1
vxzfs:configuration services snmp>
Our snmpget should now work:
[root@vxoda11 ~]# snmpget -v1 -c public 10.12.0.211 1.3.6.1.4.1.42.2.225.1.4.2.0 SNMPv2-SMI::enterprises.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage 7120"
Now due to Unpublished Bug 12979161 we need to make some symlinks so that dNFS finds the correct snmp libraries libnetsnmp.so, apparently this is fixed in 11.2.0.4:
[oracle@vxoda11 [odadb011] ~]$ locate libnetsnmp.so /usr/lib64/libnetsnmp.so.10 /usr/lib64/libnetsnmp.so.10.0.3 [root@vxoda11 ~]# cd /usr/lib64/ [root@vxoda11 lib64]# ln -s libnetsnmp.so.10.0.3 libnetsnmp.so
Last step is to restart our database so it can pickup symlinks for the snmp libraries:
[oracle@vxoda11 [kjj1] ~]$ srvctl stop database -d kjj -o immediate [oracle@vxoda11 [kjj1] ~]$ srvctl start database -d kjj
Now we are able to create our HCC table on our ZFSSA tablespace:
SYS@odadb011 AS SYSDBA> create table t1 compress for archive low tablespace dnfs as select * from dba_objects; Table created. SYS@kjj2 AS SYSDBA> select compression, compress_for from dba_tables where table_name='T2'; COMPRESS COMPRESS_FOR -------- ------------ ENABLED ARCHIVE LOW
Happy compressing!
Future Veterans 