Delphix Products

We have created a VDB from a database residing on Exadata. Some of the tables/partitions on the exadata side use the HCC compression feature. When you attempt to access those partitions or tables on the VDB you get the above error. Has Delphix any workaround to this or suggestions?

Hi Proinnsias,

Unfortunately HCC compressed data cannot be accessed from Delphix storage, due to Oracle restrictions. The HCC data needs to be uncompressed before it is linked as a dSource in Delphix, something like an 'ALTER TABLE ... MOVE' type command.

Many Thanks,

Scott.

Proinnsias,
Scott is right, and unfortunately it is an Oracle restriction. And, unless all of your non-prod database storage was Exadata, dNFS-mounted ZFSSA, or FC or iSCSI-mounted Pillar Axiom, this was an issue you are already dealing with today.

But, I do want to highlight even with uncompressing the HCC tables, the data is still filtered, compressed, and de-duped inside of Delphix. Instead of 10X storage savings of some tables with HCC, you get a 10X storage savings of the whole database! Multiply that by however many copies of that production database exist across your whole estate, and the savings are even more impressive.

Also, with Delphix, our compression technology does not get in the way of what you want to do with your data. Oracle specifically says HCC is not for OLTP databases, and to use OLTP compression (a type of AC) for those use cases. Delphix doesn't place any such restrictions on your data. 

I also want to point out that this is just a lockin with HCC. Oracle does not have these restrictions with AC, if you didn't want to fully decompress that database. 

I just wanted to provide some additional food for thought. Hope this helps.

An alternative and simpler possibility is to ingest data compressed with HCC, then, at the time of VDB provisioning, a hook can be invoked to decompress the HCC-compressed objects.
In this way, source still continues using HCC and VDB can perform correctly.

Proinnsias,

If it helps, here is a simplified shell-script from the Configure Clone hook to execute ALTER TABLE ... MOVE on any HCC tables or partitions to recompress them as OLTP or ROW STORE ADVANCED compressed.  Please realize that you must be licensed for the Oracle Advanced Compress option to use OLTP or ROW STORE ADVANCED compression, so if you are not licensed, please alter the script to use BASIC or NOCOMPRESS as you prefer.

#!/bin/bash
#================================================================================
# File:         vdb_hcc_decompress.sh
# Type:         bash script
# Author:       Delphix Field Services
# Date:         11-February 2019
#
# Copyright and license:
#
#       Licensed under the Apache License, Version 2.0 (the "License"); you may
#       not use this file except in compliance with the License.
#
#       You may obtain a copy of the License at
#
#               http://www.apache.org/licenses/LICENSE-2.0
#
#       Unless required by applicable law or agreed to in writing, software
#       distributed under the License is distributed on an "AS IS" basis,
#       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#
#       See the License for the specific language governing permissions and
#       limitations under the License.
#
#       Copyright (c) 2019 by Delphix.  All rights reserved.
#
# Description:
#
#    Simplified script to find all HCC (hybrid-columnar compressed) tables and
#    partitions and recompress them to OLTP/ADVANCED ROW STORE compression.
#
# Command-line parameters:
#
#    (none)
#
# Environment inputs expected:
#
#       ORACLE_SID, ORACLE_HOME, LD_LIBRARY_PATH
#
# Modifications:
#       TGorman 04feb19 first version
#================================================================================
#
if [[ "${ORACLE_SID}" = "" ]]
then
    echo "'date': ERROR - ORACLE_SID must be set; aborting..."
    exit 1
fi
if [[ "${ORACLE_HOME}" = "" ]]
then
    echo "'date': ERROR - ORACLE_HOME must be set; aborting..."
    exit 1
fi
if [ ! -d ${ORACLE_HOME} ]
then
    echo "'date': ERROR - unable to find \"${ORACLE_HOME}\" directory; aborting..."
    exit 1
fi
if [ ! -d ${ORACLE_HOME}/bin ]
then
    echo "'date': ERROR - unable to find \"${ORACLE_HOME}/bin\" directory; aborting..."
    exit 1
fi
if [ ! -x ${ORACLE_HOME}/bin/sqlplus ]
then
    echo "'date': ERROR - unable to find \"${ORACLE_HOME}/bin/sqlplus\" executable; aborting..."
    exit 1
fi
#
echo "'date': INFO - decompress HCC tables/partitions beginning..."
${ORACLE_HOME}/bin/sqlplus / as sysdba > /tmp/vdb_hcc_decompress_${ORACLE_SID}.log 2>&1 << __EOF__
whenever oserror exit failure
whenever sqlerror exit failure
set serveroutput on size 1000000
declare
    --
    cursor get_compressed
    is
    select    owner, table_name, '' partition_name
    from    dba_tables
    where    partitioned = 'NO'
    and    compression = 'ENABLED'
    and    (compress_for in ('QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH') or (compress_for = 'BASIC' and table_name = 'XYZ'))
    union
    select    table_owner, table_name, partition_name
    from    dba_tab_partitions
    where    compression = 'ENABLED'
    and    (compress_for in ('QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH') or (compress_for = 'BASIC' and table_name = 'XYZ'))
    order by 1, 2, 3;
    --
    v_errcontext        varchar2(255);
    v_errmsg        varchar2(128);
    v_version        varchar2(32);
    v_compress_clause    varchar2(128);
    --
begin
    --
    /*
     * retrieve the Oracle database version to determine what syntax to use for ALTER TABLE:
     *
     * If Oracle12c or above, then use the ROW STORE COMPRESS ADVANCED syntax, otherwise if
     * Oracle11g or below, then use the COMPRESS FOR OLTP syntax...
     */
    v_errcontext := 'fetch version from v\$instance';
    select    case when to_number(substr(version,1,instr(version,'.',1)-1)) >= 12
            then 'move row store compress advanced'
            else 'move compress for oltp'
        end
    into    v_compress_clause
    from    v\$instance;
    --
    /*
     * retrieve all non-partitioned tables or table partitions which are HCC (i.e.
     * hybrid-columnar compressed) and change them to OLTP/ADVANCED row-store compressed...
     */
    v_errcontext := 'open/fetch get_compressed';
    for x in get_compressed loop
        --
        if x.partition_name is null then
            v_errcontext := 'alter table "' || x.owner || '"."' || x.table_name || '"';
        else
            v_errcontext := 'alter table "' || x.owner || '"."' || x.table_name || '" partition "' || x.partition_name || '"';
        end if;
        --
        v_errcontext := v_errcontext || ' ' || v_compress_clause;
        dbms_output.put_line(v_errcontext);
        --
        execute immediate v_errcontext;
        --
        v_errcontext := 'fetch/close get_compressed';
        --
    end loop;
    --
exception
    when others then
        v_errmsg := sqlerrm;
        raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);
end;
/
__EOF__
if (( $? != 0 ))
then
    echo "'date': ERROR - decompress HCC tables/partitions failed; aborting..."
    exit 1
fi
#
echo "'date': INFO - decompress HCC tables/partitions succeeded"

Please know that this script is provided without any warranty or guarantees.  If you choose to use it, then you own it and are responsible for it.  It is provided merely as a starting point for your own customization.

Hope this helps?

Thanks!

-Tim