Recover HCC Compressed Tables to non Exadata storage

Is this for me? (always helpful for busy people)

As many of you know by now, Exadata comes with an exclusive way to compress information, based on columns, and with a different storage unit called “Compression Unit”, that responds by the name of Hybrid Columnar Compression (HCC). The only storage in the world that can read this compressed information is Exadata Storage Server. So if you have your main production database inside an Exadata box, and you want to duplicate that database to non-Exadata storage (for instance SAN), then this article is for you.

Stuff you need to know first:

- As of this day Golden Gate development teams haven’t been able to capture information from compressed tables (HCC or other Oracle compression algorithm) but they are working on it

- If you use standby database (Data Guard) either in active or on mount mode you need to do these steps first, before you start syncronizing

- HCC is a very good compression technique. I’ve seen hundreds of examples and very few of them don’t have a 10 to 20 average compression ratio. So you need to have 10 to 20 times the space in your non-Exadata storage before you consider using the following steps. The only way to compensate the need for that amount of space is to “explode” the HCC tables to OLTP compressed tables. I’ll show you that too.

The Steps

These steps were done inside an Exadata X2-2 Full Machine on a server pool with 4 nodes, but can be reproduced on any other Exadata V2/X2 Machine.

The following example will:

1. Create a tablespace called “knod”

2. Create a table “lisboa” inside the tablespace, with an HCC compression level of 2 (Query High). The table will be loaded during creation with the view “DBA_EXTENTS” to create some volume (28674 rows).

3. Backup the tablespace “knod” with RMAN to the FRA that resides on the ASM diskgroup called “RECO” (inside Exadata).

4. Restore the tablespace from Exadata to a non-Exadata storage, using RMAN.

5. Query the table and get the ORA-64307 Error.

6. Try to move the table back to a tablespace inside Exadata (called “tbs_with_hcc”) and see that it can’t read it either, throwing the 64307 error again.

7. Try to DML on the table and watch that only the INSERT statement would work, because you can add data, you just can’t read the one that’s there.

8. To put you out of your misery the table will be decompressed (it could be recompressed with OLTP compression too in this stage)

9. And now you can query or DML on top of the table that everything is working normally (the table was decompressed from it’s HCC format).

10. Finally we count the bytes it takes up (3 MB), OLTP compress it and count them again (0.625) to see that the table was 3 times smaller.

Main Conclusion: You don’t have to “explode” your HCC tables and then OLTP compressed them, you can go *directly* from HCC on Exadata to OLTP compression on non-Exadata storage.

The Script

SQL> create tablespace knod datafile '+DATA_DM01' size 1g;

Tablespace created.

SQL> create table lisboa
  2  tablespace knod
  3  compress for query high
  4  as
  5  select * from dba_extents
  6  ;

Table created.

Elapsed: 00:00:12.00
SQL> select count(*) from lisboa;

  COUNT(*)
----------
     28674

RMAN> backup tablespace knod;

Starting backup at 23-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/orcl/datafile/knod.3800.746570399
channel ORA_DISK_1: starting piece 1 at 23-MAR-11
channel ORA_DISK_1: finished piece 1 at 23-MAR-11
piece handle=+RECO/orcl/backupset/2011_03_23/nnndf0_tag20110323t204617_0.366.746570777 tag=TAG20110323T204617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAR-11

---

run {
sql 'alter tablespace "KNOD" offline immediate';
set newname for datafile '+DATA/orcl/datafile/knod.3799.746573393' to '/home/lmc/rec/knod.dbf';
restore tablespace 'KNOD';
switch datafile all;
recover tablespace 'KNOD';
sql 'alter tablespace "KNOD" online';
}

---

SQL> desc lisboa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> select count(*) from lisboa;
select count(*) from lisboa
*
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

SQL> alter table lisboa move tablespace tbs_with_hcc;
alter table lisboa move tablespace tbs_with_hcc
            *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

SQL> insert into lisboa select * from dba_extents where rownum < 2;

1 row created.

SQL> commit;

SQL> update lisboa
  2  set owner = owner;
update lisboa
       *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

SQL> delete lisboa where owner = 'KUS';
delete lisboa where owner = 'KUS'
       *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

------------------- BUT !!!!!!!!!!!!! ----------------------
If you alter table move 'it with NOCOMPRESS things will be different:

SQL> alter table lisboa move nocompress;

Table altered.

SQL> select count(*) from lisboa;

  COUNT(*)
----------
     28675

-- Now I can even do all the DML I can:

SQL> r
  1* update lisboa set owner = 'USK' where owner = 'SYSMAN'

1951 rows updated.

SQL> commit;

Commit complete.

SQL> delete lisboa where owner = 'USK';

1951 rows deleted.

SQL> commit;

Commit complete.

-- ****************************************************************
SQL> select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LISBOA';
SUM(BYTES)/1024/1024
--------------------
                   3

SQL> alter table lisboa move compress for oltp;

Table altered.

SQL>  select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LISBOA';

SUM(BYTES)/1024/1024
--------------------
                .625

-- **************************************************************

—

Feel free to bash or inquire.

LMC

About these ads