HCC on non-Exadata - How Oracle is detecting storage type

This is next part of HCC compression series on non-Exadata. When 11.2.0.3 has been released Oracle announced that HCC compression will be possbile on ZFS Appliance and Axiom Pillar storage and patch 13041324 has been released as well. I have blogged about it and was able to run HCC on ZFS Appliance simulator and on default Linux NFS as well. After some time Oracle raised bug "Bug 13362079  HCC compression should not be allowed on dNFS with non ZFS or Pillar" and it has been fixed in PSU 11.2.0.3.1. (patch 13343438). After applying that PSU I was unable to create HCC compressed table anymore.

I was wondering how Oracle is checking storage type as far as I know NFS doesn't have that functionality. I have compared wireshark network dumps from old and new Oracle version and there was no difference. At that point I was thinking that maybe new firmware upgrade for ZFS Appliance is required but unfortunately I couldn't download it and apply to my simulator. After discussion on Twitter with @GregRahn @alexgorbachev @AlexFatkulin @kevinclosson I have been told that Oracle can use SNMP to check storage type. That's a new idea and I recalled that I have seen SNMP related error in trace files but I have ignored it as is appear for 11.2.0.3 with patch and for 11.2.0.3.1 as well.

This time I decided to dig it out. So this is a first error I have seen from DBWR trace:

test_dbw0_11626.trc: [1332587314469093] skgnfs_setup_snmp:250: dlopen errno = 0, errstr = libnetsnmp.so: cannot open shared object file: No such file or directory

So there is no shared library in system (server is running OEL 5.6) or some symlinks are missing. Let's try with new symlink

ln -s /usr/lib64/libnetsnmp.so.10 /usr/lib64/libnetsnmp.so

Let's check DBWR trace now - looks better library has been found but target host is not responding.

test_dbw0_3575.trc: [1334661892496086] skgnfs_query_snmp:1831: Timeout error 2 for server 10.10.10.60

I have connected to my ZFS Appliance simulator and I have enabled SNMP using network=0.0.0.0/0 as network filter. I have restarted Oracle and there was no SNMP errors in DBWR trace anymore. I have enabled wireshark again and this what have been captured.

12:44:14.276823 IP 10.10.10.51.20671 > 10.10.10.60.snmp:  GetRequest(33)  E:sun.2.225.1.4.2.0
12:44:14.289691 IP 10.10.10.60.snmp > 10.10.10.51.20671:  GetResponse(59)  E:sun.2.225.1.4.2.0=[|snmp]

Now we can use snmpwalk to check what ZFS Appliance simulator is responding to SNMP request.

[root@dg1 mibs]# snmpwalk -O n -v 1 -c public 10.10.10.60 .1 | grep 225
.1.3.6.1.4.1.42.2.225.1.4.1.0 = STRING: "sunstore"
.1.3.6.1.4.1.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage VirtualBox"
.1.3.6.1.4.1.42.2.225.1.4.3.0 = STRING: "2011.04.24.1.0,1-1.8"
.1.3.6.1.4.1.42.2.225.1.4.4.0 = Timeticks: (938601700) 108 days, 15:13:37.00
.1.3.6.1.4.1.42.2.225.1.4.5.0 = Timeticks: (938601700) 108 days, 15:13:37.00
.1.3.6.1.4.1.42.2.225.1.4.6.0 = Timeticks: (329800) 0:54:58.00
.1.3.6.1.4.1.42.2.225.1.4.7.0 = STRING: "f2513e14-f8c2-6d7e-fc29-bbd8078aad24"
.1.3.6.1.4.1.42.2.225.1.4.8.0 = STRING: "unknown"
.1.3.6.1.4.1.42.2.225.1.4.9.0 = STRING: "Oracle 000-0000"
.1.3.6.1.4.1.42.2.225.1.5.1.0 = STRING: "AKCS_UNCONFIGURED"
.1.3.6.1.4.1.42.2.225.1.6.1.2.1 = STRING: "zfspool/default/zfstest"
.1.3.6.1.4.1.42.2.225.1.6.1.3.1 = STRING: "zfspool"
.1.3.6.1.4.1.42.2.225.1.6.1.4.1 = STRING: "default"
.1.3.6.1.4.1.42.2.225.1.6.1.5.1 = STRING: "zfstest"
.1.3.6.1.4.1.42.2.225.1.6.1.6.1 = STRING: "/export/zfstest"
.1.3.6.1.4.1.42.2.225.1.6.1.7.1 = Counter32: 7
.1.3.6.1.4.1.42.2.225.1.6.1.8.1 = Counter32: 0
.1.3.6.1.4.1.42.2.225.1.6.1.9.1 = Counter32: 7

Oracle is looking for sun.2.225.1.4.2.0 and this id .1.3.6.1.4.1.42.2.225.1.4.2.0. is matching all numbers. It is returning "Sun ZFS Storage VirtualBox" value and this is a type of NFS server. I think that word "VirtualBox" in name is a non matching one. To confirm that I have googled for screen shots and I found this link. On page 11 I found a information from physical ZFS Appliance which I was looking for and name looks like "Sun ZFS Storage 7xxx".
I hope Oracle will include simulator name as proper name for HCC. In my opinion it can be used to HCC evaluation like dbms_compression package but in different scale. From businesses perspective no one will use simulator for production workload.

But is there any way to test it now ? ZFS Appliance type is not configurable during simulator installation process but there are two options:
- libnetsnm.so is a open source library so it can be amended to return proper value but this is a hard way
- Other possibility to realize that Sun ZFS Appliance simulator is just Solaris box. And there is a chance that as many of other parameters in UNIX type name is saved in text file and if you are able to access this file you can change it.After a few tries and was able to change it and now my simulator is returning following values. I did it only for education purpose and I'm not sure if I can share all steps I did.
At the end SNMP is returning different name

[root@dg1 ~]# snmpwalk -O n -v 1 -c public 10.10.10.60 .1 | grep 225
.1.3.6.1.4.1.42.2.225.1.4.1.0 = STRING: "sunstore"
.1.3.6.1.4.1.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage 7420"

Now it is time to try HCC in version 11.2.0.3.1 on new configured simulator ZFS and YES it is working again.

Disclaimer:
According to Oracle license Hybrid Column Compression can be run on ZFS Appliance and Axiom Pillar storage only. This post is for eduction purposes only to understand how DB is detecting storage type and how to enable it if you have proper hardware.


regards,
Marcin

Quick note about Hybrid Columnar Compression running on my laptop

As I mentioned in previous post Oracle allowed users to use Hybrid Columnar Compression on ZFS appliance. Two days ago there was a note in MOS about bug/enhancement but there was no patch for it. Yesterday I was able to download patch number 13041324 for Oracle 11.2.0.3 and successfully applied it against my installation on virtual machine. After that I run my Oracle's Sun Unified Storage Simulator and I created a new table space on it using NFS share together with Oracle Direct NFS client. Next I try to create compressed table using old existing table space on local ASM disk and it fail as expected. Than I give a try on new table space and this time table has been created :

SQL> create tablespace SUNNFSTS datafile '/nfs/sunnfsts.dbf' size 10M;
Tablespace created.

SQL> create table SCOTT.test1 tablespace USERS  compress for archive high as select * from dba_objects where rownum < 100;
create table SCOTT.test1 tablespace USERS  compress for archive high as select * from dba_objects where rownum < 100
                                                                                      *
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this
storage type

SQL> create table SCOTT.test tablespace SUNNFSTS  compress for archive high as select * from dba_objects where rownum < 100;
Table created.
SQL> exit

Looks like it is working and HCC can be tested without expensive hardware. I hope I will find some time to write down all my notes and create proper blog post about installation and configuration.
regards,
Marcin

Oracle 11.2.0.3 and Hybrid Columnar Compression Support

Oracle announced support for Hybrid Columnar CompressionSupport for ZFS Storage Appliances and Pillar Axiom Storage Systems on Sep 30^th. According to Steve Tunstall's blog these feature should work with 11.2.0.3 and any ZFS Appliance. I have tested it on Oracle Enterprise Linux 5.5 with 11.2.0.3 and ZFS Appliance software emulator and it didn’t work. I have read Steve post again and he mentioned in comment that additional patch is required. There is was no information about it on MOS yesterday evening but today morning I found following note - "Bug 13041324 - Enhancement to allow HCC on ZFS and Pillar Storage". I still can’t find patch and I’m really tempted to try it on my virtual machines and has a possibility to test Oracle HCC feature. Hope this patch will be available soon.

Regards,

Marcin