How to Multiply the Capacity of Oracle Storage Systems

With Hybrid Columnar Compression

by Tom Luckenbach

How to enable Hybrid Columnar Compression on Oracle Pillar Axiom storage systems, which can effectively multiply your storage capacity by 10 or 15 times, improve performance, and save you hardware costs.  


Published February 2013

This article discusses how to enable Hybrid Columnar Compression when using Oracle's Pillar Axiom storage systems.

Want to comment on or discuss this article? See the post on Facebook. Have a similar article to share? Bring it up on Facebook and let's discuss.

Note: Refer to the "See Also" section for a link to a white paper that delves further into the technical details and business benefits of using Hybrid Columnar Compression.

About Hybrid Columnar Compression

Hybrid Columnar Compression enables high levels of data compression and provides cost savings, space savings, and performance improvements due to reduced I/O. It is optimized to use both database and storage capabilities on Oracle's storage devices and is an enabling technology for both warehouse compression and archive compression.

Average storage savings can range from 10x to 15x depending on which Hybrid Columnar Compression level is implemented; real-world customer benchmarks have resulted in storage savings of up to 204x.

Even an average storage savings of 10x can reduce and often eliminate the need to purchase new storage for several years. For example, a 100-terabyte database achieving 10x storage savings would utilize only 10 terabytes of physical storage. With 90 terabytes of storage now available, storage purchases could potentially be delayed for a significant amount of time. The 90 terabytes of storage could even be used to store up to nine more databases with 100 terabytes of data compressed to 10 terabytes of actual disk space each.

Prerequisites and Overview of Steps

Hybrid Columnar Compression is supported only on Oracle's storage systems. The steps in the following sections outline the process of setting up Oracle Database for use with Hybrid Columnar Compression on the Pillar Axiom 600 storage system. The procedure is very similar regardless of whether you are creating a new database or you want to compress tables from an existing database.

Hybrid Columnar Compression on a Pillar Axiom storage system is available with Oracle Database 11g Release 2. There is only one prerequisite for enabling Hybrid Columnar Compression on a Pillar Axiom storage system: you must use Oracle Automatic Storage Management for volume management.

Note: Refer to the "See Also" section for a link to more information about Oracle Automatic Storage Management.

There are three basic steps you must perform to create or use a database to leverage Hybrid Columnar Compression:

  1. Install or update the Oracle Database and Grid Infrastructure software to the required patch levels (see the next section for details).
  2. Create or modify one or more Oracle Automatic Storage Management disk groups that have the proper attribute settings to support Hybrid Columnar Compression.
  3. Create compressed data—by creating new tables in a new or existing database—for the compressed data on the Oracle Automatic Storage Management disk groups you set up for compression.

These steps are explored in more detail in the following sections.

Installing the Software and Patches

  1. Install Oracle Database and Grid Infrastructure 11.2.0.3 using patch 10404530 and patch 13041324.

    Note: You may also install a patch set update (PSU), such as Patch 13923374 — Oracle's Grid Infrastructure 11.2.0.3.3, because it contains the Hybrid Columnar Compression patch (13041324) and others.

  2. Update the OPatch utility to version 11.2.0.3 in both catalogs: GI_HOME and DB_HOME.

Creating an Oracle Automatic Storage Management Disk Group

To enable Hybrid Columnar Compression within a database, you must change three attribute settings in the underlying Oracle Automatic Storage Management disk group:

  • Attribute compatible.asm must be set to 11.2.0.3 or higher.
  • Attribute compatible.rdbms must be set to 11.2.0.3 or higher.
  • Attribute storage.type must be set to AXIOM.

For more details on setting disk group attributes, look for the reference to Hybrid Columnar Compression in this document.

The following steps illustrate how to create Oracle Automatic Storage Management disk groups that are compatible with Hybrid Columnar Compression:

  1. Create a set of LUNs on the Pillar Axiom storage system and map them to the target server.
  2. If necessary, restart the server or rescan the bus to import and create the LUN multipath devices. Oracle's Pillar Axiom Storage Services Manager GUI will report the raw disk names of devices on the host server to be used for Oracle Automatic Storage Management selection when Oracle's Pillar Axiom Path Manager is installed on the server. These LUN (disk) devices can be earmarked for Oracle Automatic Storage Management selection.

    Note: It is recommended that you use the whole disk for Oracle Automatic Storage Management disks. There is no need to create any explicit partitions.

    For example, with Oracle Linux, the root user would use the following command to tag the entire disk as an Oracle Automatic Storage Management candidate:

    # oracleasm createdisk DATA01 /dev/mapper/2000b080000001269
    

    With Oracle Solaris, the root user must run the following command to change the ownership to the oracle user and group for each of the devices to be used in Oracle Automatic Storage Management, for example:

    # chown -L oracle:oinstall /dev/rdsk/c0t000B080001003904d0s2
    

    The mpathadm list LU command will list out the multipath devices as well.

  3. Using the Grid Infrastructure 11.2.0.3 asmca tool, create one or more disk groups using a level of redundancy set to External.

    Note: When using Oracle Linux, you must (currently) set the default path to the disks to /dev/oracleasm/disks. Otherwise, the disks might not be properly identified as Pillar Axiom storage.

  4. Set the compatibility level and storage type using the disk group set compatible and set attribute commands. For example, Listing 1 shows an example for a disk group called DATA.

    [Oracle @ myhost] $ export ORACLE_HOME=/u01/app/11.2.0.3/grid  
    [Oracle @ myhost] $ export ORACLE_SID=+ASM
    [Oracle @ myhost] $ sqlplus / as sysasm
    SQL * Plus: Release 11.2.0.3.0 Production on Tue May 1 18:04:06 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.    Connected to:  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options    
      
    SQL> alter system set 'compatible'='11.2.0.3' scope=spfile sid='*';
    System altered.
    SQL> alter diskgroup DATA set attribute 'compatible.asm'='11.2.0.3';
    Disk group altered.   
    SQL> alter diskgroup DATA set attribute 'compatible.rdbms'='11.2.0.3';
    Disk group altered. 
    SQL> alter diskgroup DATA set attribute 'storage.type'='AXIOM';
    Disk group altered. 
    

    Listing 1

    Note: There is a check that verifies whether the disk group is really Pillar Axiom storage. If you attempt to execute the last SQL command on non-Oracle storage, you get an error message similar to this:

    ORA-15287: could not set disk group attribute storage.type due to incompatible disks  
    ORA-15285: disk '/dev/mapper/XXXXXXXX' violates disk group attribute storage.type 
    

Creating the Compressed Data

The remaining steps deal with the data in the database. Whether you are creating a new database or creating new tables in an existing database for the compressed data, the basic requirement is the same. That is, in all cases, you will use table spaces on Oracle Automatic Storage Management disk groups that are compatible with Hybrid Columnar Compression.

You can either create the Hybrid Columnar Compression compressed table on an existing disk group or create a new disk group, depending on space or operational considerations. The key item to heed in this step is that the compatibility setting for the database must be consistent with the disk groups. That is, you must also set the database compatibility to the same version as the disk groups, which is 11.2.0.3 or higher.

Ultimately, there is a variety of methods for actually creating the compressed data. Here are two example SQL statements for creating compressed table data using Hybrid Columnar Compression. The first command below will make a copy of the table for an archive use case. The second command will compress the table data directly (rather than making a new table) using the "alter table move" method. It will compress the data "on the fly" with the designated type of compression suitable for an online use case.

SQL> create table MYDATA_COMP_ARCHIVE compress for ARCHIVE HIGH as select * from MYDATA;
SQL> alter table MYDATA move compress for QUERY LOW;

For a more comprehensive discussion of Hybrid Columnar Compression, especially around approaches for table migration and other essential best practices, please review this white paper: "Hybrid Columnar Compression (HCC) on Exadata."

A backup of an Hybrid Columnar Compression–enabled Oracle database will retain the settings upon recovery. If compatible=11.2.0.3 has been set on the source Oracle database, then Hybrid Columnar Compression will be available upon recovery of a properly patched destination.

If you are creating a new database, you can use the Oracle Database Creation Assistant (dbca). Here is a "trick" to set the database compatibility when using the dbca tool. When you are on Step 6 of 12, you might get a warning about mismatched compatibility between the Oracle Automatic Storage Management disk group and the database. To satisfy this requirement and defeat the warning, temporarily set Storage Type to File System, and then click through to Step 9. Click All Initialization Parameters to set the compatible parameter to 11.2.0.3. Then, go back to Step 6, proceeding as usual, except this time set Storage Type to Oracle Automatic Storage Management.

See Also

About the Author

Tom Luckenbach has been working in the enterprise hardware and software marketplace since 1988. For the last 10 years, he has focused on enterprise storage solutions. Even though he was not born in California, he calls San Francisco his hometown. He is married to a California-born writer and lives with their three kids in Durham, North Carolina. He enjoys fly-fishing when he can.

Revision 1.0, 02/27/2013

Follow us:
Blog | Facebook | Twitter | YouTube