HCC Tables on ZFS Storage Appliance

I’ve just been creating an HCC compressed table on a non-Exadata database server using storage on a ZFS Storage Appliance.

I hit quite a number of problems which proved quite difficult to pin-point. I had to change a number of things before I could finally get it working.

So first of all I created a mount point on the database server pointing to a share I’d created on the ZFS Storage Appliance –

mount -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp zfs-server:/export/hcc_demo /mnt/hcc_demo

These mount options are taken from “Mount Options for Oracle files when used with NFS on NAS devices (Doc ID 359515.1)”.

However, I then started hitting this problem when trying to create the tablespace –

SQL> create tablespace HCC_DEMO datafile '/mnt/hcc_demo/hcc_demo01.dbf' size 100M autoextend on maxsize unlimited;
create tablespace HCC_DEMO datafile '/mnt/hcc_demo/hcc_demo01.dbf' size 100M autoextend on maxsize unlimited
*
ERROR at line 1:
ORA-01119: error in creating database file '/mnt/hcc_demo/hcc_demo01.dbf'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

I looked for some time for a fix but couldn’t, so in the end I configured Oracle Direct NFS (dNFS). You would definitely want to do this anyway when using data files on NFS, so it isn’t a huge problem. I would have liked to have gotten to the bottom of what the actual NFS locking problem was though.

Now we can create the tablespace –

SQL> create tablespace HCC_DEMO datafile '/mnt/hcc_demo/hcc_demo01.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

We can also query v$dnfs_servers to show that dNFS is being used.

The next problem I hit was in the table creation itself (trying to use HCC) –

SQL> create table test (col1 number, col2 varchar2(50)) compress for query high tablespace hcc_demo;
create table test (col1 number, col2 varchar2(50)) compress for query high tablespace hcc_demo
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for
tablespaces on this storage type

In the end I found a couple of potential problems. One is explained in the following article –

Oracle Database Appliance (ODA): Create table with HCC fails with ORA-64307 on ZFS storage appliance (Doc ID 1464869.1)

This relates to SNMP library files, but is a bug fixed in 11.2.0.4 (which I’m using). So that wasn’t the problem. The actual problem was that SNMP wasn’t enabled on the ZFS Storage Appliance. I enabled it as follows –

I then confirmed that it was working by running the following command from the database server –

snmpget -v1 -c public zfs-server 1.3.6.1.4.1.42.2.225.1.4.2.0

This gives a positive response.

Finally I unmount the NFS share, mount it again, and restart the database instance.

This time, I can now create the HCC compressed table –

SQL> create table test (col1 number, col2 varchar2(50)) compress for query high tablespace hcc_demo;

Table created.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: