In a customer project, we are running several ZS7-2 Oracle ZFS Storage Appliances ZFSSA in bundled with Private Cloud Appliances PCA. The ZFSSA is a very powerful storage solution with a lot of functionality like OISP (Oracle Intelligent Storage Protocol), or – when an ADVANCED COMPRESSION license is yours – Hybrid Columnar Compression HCC.
But once in a day when I tried to create a Hybrid Columnar Compression enabled tablespace in a 19c pluggable database in our test system, I got this error:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
SQL> CREATE BIGFILE TABLESPACE ts_data_hcc_01 2 DATAFILE SIZE 200m AUTOEXTEND ON NEXT 10m MAXSIZE 100G 3 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE 4 DEFAULT TABLE COMPRESS FOR ARCHIVE LOW INDEX COMPRESS 5 ADVANCED LOW SEGMENT SPACE MANAGEMENT AUTO; CREATE BIGFILE TABLESPACE ts_data_hcc_01 * ERROR at line 1: ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
Investigation
This was easy, the MOS document Exadata Hybrid Columnar Compression” Is Not Working in Exadata Using ZFS Storage Appliance (ZFSSA) and Failing with [ORA-64307: Exadata Hybrid Columnar Compression is not Supported for Tablespaces on This Storage Type] (Doc ID 2393108.1) mentions, to check if the SNMP service on ZFSSA is enabled.A dNFS issue could be excluded very fast, because regular uncompressed tablespaces could be created.
And here we go: ZFSSA BUI – Configuration – Services: DISABLED 🙁
You can check the ZFSSA SNMP functionality by a snmpget command, executed on the database server:
$ snmpget -v1 -c public 192.168.123.45 1.3.6.1.4.1.42.2.225.1.4.2.0 Timeout: No Response from 192.168.123.45.
FYI: The IP address of the ZFSSA exports are stored in $ORACLE_HOME/dbs/oranfstab.
$ cat oranfstab | grep server server: 192.168.123.45
So let’s enable it and see what’s happen now.
The command snmpget shows the ZFSSA enabled service now and gives us a feedback.
$ snmpget -v1 -c public 192.168.123.45 1.3.6.1.4.1.42.2.225.1.4 SNMPv2-SMI::enterprises.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage 7370"
Test 1 – Create HCC enabled Tablespace after SNMP restart
Another try, same error. Only to enable the service on ZFSSA has no effect.
SQL> CREATE BIGFILE TABLESPACE ts_data_hcc_01 2 DATAFILE SIZE 200m AUTOEXTEND ON NEXT 10m MAXSIZE 100G 3 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE 4 DEFAULT TABLE COMPRESS FOR ARCHIVE LOW INDEX COMPRESS 5 ADVANCED LOW SEGMENT SPACE MANAGEMENT AUTO; CREATE BIGFILE TABLESPACE ts_data_hcc_01 * ERROR at line 1: ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
We restart the database instance.
SQL> SHUT IMMEDIATE SQL> STARTUP
Test 2 – Create HCC enabled Tablespace after SNMP and Database Instance restart
SQL> CREATE BIGFILE TABLESPACE ts_data_hcc_01 2 DATAFILE SIZE 200m AUTOEXTEND ON NEXT 10m MAXSIZE 100G 3 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE 4 DEFAULT TABLE COMPRESS FOR ARCHIVE LOW INDEX COMPRESS 5 ADVANCED LOW SEGMENT SPACE MANAGEMENT AUTO; Tablespace created.
Verification:
SQL> SELECT tablespace_name, def_tab_compression,compress_for 2 FROM dba_tablespaces 3 WHERE tablespace_name ='TS_DATA_HCC_01'; TABLESPACE_NAME DEF_TAB_ COMPRESS_FOR ------------------------------ -------- ------------------------------ TS_DATA_HCC_01 ENABLED ARCHIVE LOW
What’s happen
During a database instance startup, there is a check to verify if a ZFSSA with enabled SNMP is available. This is visible in the alertlog:
2022-03-15T15:59:20.132454+01:00 Found NAS server Sun ZFS Storage 7370
And once the database instance has recognized it, HCC enabled tablespaces and tables are allowed. Even when you stop the SNMP service now, HCC can be used. Only when the database instance is restarted again, there is no such entry in the alertlog. Which means: no HCC.
Summary
The ZFS Storage Appliance SNMP service is essential to benefit of the hybrid Columnar Compression feature. Never disable it. Ok, now you ask yourself: why this service was disabled? It was a simple mistake of mine during a support case analysis… MOS recommended to disable the SMTP service. And as you can see, the SMTP service is above SNMP service.
Or in Bart Simpson’s style:
