Wednesday, October 12, 2011

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

9 comments:

Anonymous said...

hello,
i was doing the same thing but i also have the ORA-64307 error.
Could you tell me what are the tmandatory tablespace specifications?

Marcin Przepiorowski said...

You need to apply patch and create table space using NFS on Sun Storage Appliance simulator. It won't work on local disk or ASM.

Anonymous said...

thks you very much marcin.

Kevin Closson said...

Hello Marcin,

Is there any chance you could export a file system from your linux VM and mount it loop-back so as to test HCC object creation in a loop-back NFS mount? It should work. We'd all be very interested.

Marcin Przepiorowski said...

Hi Kevin,
I have test with NFS file system mounted on local host and it is not working. There are two differences: first -
there is no channels in v$dnfs_channels and
second in alert log - Sun NFS has a entry "to filer".
I will test it now with OpenFiler and will blog about all this stuff soon.

Thanks for your feedback.
Marcin

Pavel Ruzicka said...

The loop-back worked for me (patch 13041324 - HCC ON ZFS AND PILLAR STORAGE is required).

PSU 11.2.0.3.1 has been released yesterday (16.1.2012) and "fixed bugs" contains.. surprise.. 13362079 - HCC SHOULD NOT BE ENABLED FOR NON ZFS/ PILLAR STORAGE ARRAY

Pavel Ruzicka said...

The loop-back test worked for me (as mentioned, patch 13041324 - HCC ON ZFS AND PILLAR STORAGE is required).

PSU 11.2.0.3.1 (patch 13343438) has been released and it contains.. surprise.. 13362079 - HCC SHOULD NOT BE ENABLED FOR NON ZFS/ PILLAR STORAGE ARRAY

Kevin Closson said...

Pavel,

Have you applied 13362079 and traced it to see how it determines the NFS filer is Oracle as opposed to, say, EMC VNX or NetApp?

Marcin Przepiorowski said...

Hi Kevin,

I will check this when I setup my lab back. Maybe Pavel will do it faster.

regards,
Marcin