Recover HCC partitioned tables to non-Exadata storage – The Parallel Quest


If you read my previous post, you might have noticed that I was using a non-partitioned table, of a somewhat small size. The basic stuff is there, but if you’re managing bigger tables you might want to do it using partitions and do it using parallel DDL, to speed things up.

This raises a new challenge: you might not want to uncompress all your partitions, and those that you decide to uncompress should be able to do it in parallel. The sole propose of this post is to show you how can you do it, assuming you want to uncompress all your HCC compressed partitions in the non-Exadata storage… and in parallel :-D

Now the steps

1. Create 4 different tablespaces to accommodate the four partitions of the table we’re about to create

2. Create a table called BRAGA (3rd biggest city in Portugal and the finalists in the UEFA Europa Cup next 18th May in Dublin’s Arena). This table is hash partitioned, with four partitions pointing to the four tablespaces created previously. The table will use HCC compression at the default level (QUERY HIGH), which uses the exact same algorithms and transformations as ARCHIVE LOW, but with a smaller Compression Unit (CU).

3. Direct Load the table with info from the Data Dictionary, creating 415805 rows (about 70MB of uncompressed info).

4. Check the space taken by the CUs: 2.75MB. A compression ratio around 25!

5. Gather stats for the table and its partitions

6. Check how the hashing algorithm spread the data across the four partitions. It should be balanced. I’ve written another post this year about this. You should choose the column with the biggest cardinality to spread the data more evenly. It should be a number around 415805/4=103952 rows for each partition.

7. Backup the tablespaces

8. Recover them back to non-Exadata storage, this time to a local disk in node 1 (/home/oracle/lmc). I was using only one instance to avoid having ORA-01157 errors blowing in my face.

9. Check if the datafiles are on the expected place

10. Query table BRAGA and get error ORA-64307 thrown at you

11. Now it’s the tricky bit! First you modify the compression status of each partition to “nocompress” and then …

12. … you uncompressed them at will and in parallel (after enabling parallel DDL at the session level). Each ALTER TABLE braga MOVE PARTITION  will be a parallel operation in itself, and can be done in parallel amongst themselves to raise the bar!

13. After the uncompression gets done you’ll see that the table is readable and has 69MB!

14. To play around a bit I’ve decided to compress them again but using OLTP compression and in the end I measure the ratio. Nice one too.

Now the code

create tablespace knod1
 datafile '+DGDATA' size 100m
 autoextend on next 250m maxsize 5g;
 create tablespace knod2
 datafile '+DGDATA' size 100m
 autoextend on next 250m maxsize 5g;
 create tablespace knod3
 datafile '+DGDATA' size 100m
 autoextend on next 250m maxsize 5g;
 create tablespace knod4
 datafile '+DGDATA' size 100m
 autoextend on next 250m maxsize 5g;
create table lmc.braga ( --column mimic dba_segments
 OWNER                VARCHAR2(30),
 SEGMENT_NAME            VARCHAR2(81),
 PARTITION_NAME         VARCHAR2(30),
 SEGMENT_TYPE            VARCHAR2(18),
 SEGMENT_SUBTYPE        VARCHAR2(10),
 TABLESPACE_NAME        VARCHAR2(30),
 HEADER_FILE            NUMBER,
 HEADER_BLOCK            NUMBER,
 BYTES                NUMBER,
 BLOCKS             NUMBER,
 EXTENTS            NUMBER,
 INITIAL_EXTENT         NUMBER,
 NEXT_EXTENT            NUMBER,
 MIN_EXTENTS            NUMBER,
 MAX_EXTENTS            NUMBER,
 MAX_SIZE            NUMBER,
 RETENTION            VARCHAR2(7),
 MINRETENTION            NUMBER,
 PCT_INCREASE            NUMBER,
 FREELISTS            NUMBER,
 FREELIST_GROUPS        NUMBER,
 RELATIVE_FNO            NUMBER,
 BUFFER_POOL            VARCHAR2(7),
 FLASH_CACHE            VARCHAR2(7),
 CELL_FLASH_CACHE        VARCHAR2(7)
 )
 partition by hash(header_block)
 partitions 4
 store in (knod1, knod2, knod3, knod4)
 compress for query high
 ;

Direct-Load it so HCC can kick-in:

SQL> insert /*+ APPEND */ into lmc.braga
 2   select * from dba_segments;
415805 rows created.
select sum(bytes)/1024/1024 Megs
 from dba_extents
 where segment_name = 'BRAGA';
MEGS
 ----------
 2.75
-- Collect Stats
 exec dbms_stats.gather_table_stats('LMC','BRAGA');
select partition_name, num_rows
 from user_tab_partitions
 where table_name='BRAGA';
PARTITION_NAME             NUM_ROWS
 ------------------------------ ----------
 SYS_P61985               101624
 SYS_P61986               104043
 SYS_P61987               103247
 SYS_P61988               106891

Nice Distribution! This means we’ve picked the right column to hash partition it.

Now let’s move it to non-Exadata storage:

1st) Backup.
You can do it one by one or backup them all with the same rman command, see my example:

RMAN> backup tablespace knod1;
Starting backup at 13-MAY-11
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=693 instance=orcl1 device type=DISK
 channel ORA_DISK_1: starting full datafile backup set
 channel ORA_DISK_1: specifying datafile(s) in backup set
 input datafile file number=01006 name=+DGDATA/orcl/datafile/knod1.1153.750943521
 channel ORA_DISK_1: starting piece 1 at 13-MAY-11
 channel ORA_DISK_1: finished piece 1 at 13-MAY-11
 piece handle=+DGARCH/orcl/backupset/2011_05_13/nnndf0_tag20110512t120530_0.279.750945931 tag=TAG20110512T120530 comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
 Finished backup at 13-MAY-11
RMAN> backup tablespace knod2,knod3,knod4;
Starting backup at 13-MAY-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=01007 name=+DGDATA/orcl/datafile/knod2.1154.750943521
 input datafile file number=01008 name=+DGDATA/orcl/datafile/knod3.1155.750943523
 input datafile file number=01009 name=+DGDATA/orcl/datafile/knod4.1156.750943525
 channel ORA_DISK_1: starting piece 1 at 13-MAY-11
 channel ORA_DISK_1: finished piece 1 at 13-MAY-11
 piece handle=+DGARCH/orcl/backupset/2011_05_13/nnndf0_tag20110512t120633_0.278.750945995 tag=TAG20110512T120633 comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
 Finished backup at 13-MAY-11

Now restore to “/home/oracle/lmc”:

run {
 sql 'alter tablespace "KNOD1" offline immediate';
 sql 'alter tablespace "KNOD2" offline immediate';
 sql 'alter tablespace "KNOD3" offline immediate';
 sql 'alter tablespace "KNOD4" offline immediate';
 set newname for datafile '+DGDATA/orcl/datafile/knod1.1153.750943521' to '/home/oracle/lmc/knod1.dbf';
 restore tablespace 'KNOD1';
 set newname for datafile '+DGDATA/orcl/datafile/knod2.1154.750943521' to '/home/oracle/lmc/knod2.dbf';
 restore tablespace 'KNOD2';
 set newname for datafile '+DGDATA/orcl/datafile/knod3.1155.750943523' to '/home/oracle/lmc/knod3.dbf';
 restore tablespace 'KNOD3';
 set newname for datafile '+DGDATA/orcl/datafile/knod4.1156.750943525' to '/home/oracle/lmc/knod4.dbf';
 restore tablespace 'KNOD4';
 switch datafile all;
 recover tablespace 'KNOD1';
 recover tablespace 'KNOD2';
 recover tablespace 'KNOD3';
 recover tablespace 'KNOD4';
 sql 'alter tablespace "KNOD1" online';
 sql 'alter tablespace "KNOD2" online';
 sql 'alter tablespace "KNOD3" online';
 sql 'alter tablespace "KNOD4" online';
 }

After a while everything is back to non-Exadata storage:

[oracle@db01 lmc]$ ll
 total 410144
 -rw-r----- 1 oracle oinstall 104890368 May 13 18:12 knod1.dbf
 -rw-r----- 1 oracle oinstall 104890368 May 13 18:13 knod2.dbf
 -rw-r----- 1 oracle oinstall 104890368 May 13 18:13 knod3.dbf
 -rw-r----- 1 oracle oinstall 104890368 May 13 18:13 knod4.dbf
 [oracle@db01 lmc]$ pwd
 /home/oracle/lmc

Let’s try to query the table:

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

This is fine! Now let’s uncompress it in parallel:

SQL> ALTER SESSION ENABLE PARALLEL DDL;
Session altered.
SQL> alter table braga modify partition SYS_P61985 nocompress;
Table altered.
SQL> alter table braga modify partition SYS_P61986 nocompress;
Table altered.
SQL> alter table braga modify partition SYS_P61987 nocompress;
Table altered.
SQL> alter table braga modify partition SYS_P61988 nocompress;
Table altered.
SQL> select sum(bytes)/1024/1024 Megs
 from dba_extents
 where segment_name = 'BRAGA';  2    3
MEGS
 ----------
 2.75
SQL> select count(*)
 2  from braga;
 from braga
 *
 ERROR at line 2:
 ORA-64307: hybrid columnar compression is only supported in tablespaces
 residing on Exadata storage
Still can't!!!!!!! because we haven't released it yet.
SQL> alter table braga move partition SYS_P61985 parallel;
Table altered.
SQL> alter table braga move partition SYS_P61986 parallel;
Table altered.
SQL> alter table braga move partition SYS_P61987 parallel;
Table altered.
SQL> alter table braga move partition SYS_P61988 parallel;
Table altered.
SQL> select count(*)
 2  from braga;
COUNT(*)
 ----------
 415805
SQL> select sum(bytes)/1024/1024 Megs
 from dba_extents
 where segment_name = 'BRAGA';  2    3
MEGS
 ----------
 69

Wow! Looks like the ratio was 69/2.75=25 or in orageek language:

SQL> select round(69/2.75) Ratio from dual;
RATIO
 ----------
 25

Now that we got kicked off Exaland, we can only use OLTP compression. Let’s do it to all four partitions:

alter table braga modify partition SYS_P61985
 compress for oltp;
 alter table braga modify partition SYS_P61986
 compress for oltp;
 alter table braga modify partition SYS_P61987
 compress for oltp;
 alter table braga modify partition SYS_P61988
 compress for oltp;
 -- Gosh I was dumm not naming the partitions :-(

Now rebuild them:

alter table braga move partition SYS_P61985;
 alter table braga move partition SYS_P61986;
 alter table braga move partition SYS_P61987;
 alter table braga move partition SYS_P61988;
SQL> select sum(bytes)/1024/1024 Megs
 from dba_extents
 where segment_name = 'BRAGA';  2    3
MEGS
 ----------
 16

Still a good ratio:

SQL> select round(69/16) Ratio from dual;
RATIO
 ----------
 4

Hope this was of some utility.

LMC

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s