Oracle Database Enterprise Edition has several types of data compression. Most seasoned Oracle DBAs will understand how to best use these features, but may not fully understand which uses require a license and how to avoid accidentally using a paid feature. This brief article will point out differences between Basic and Advanced Compression and highlight some of the most common unintentional uses of Advanced Compression that we see when working with clients.
For a more complete discussion of Advanced Compression Option (ACO) features, reference the Oracle white paper at the following link: http://www.oracle.com/technetwork/database/options/compression/index.html
Basic vs Advanced
Advanced Compression is an extra cost option in Oracle Database Enterprise Edition. Advanced Compression includes features like Heat Map, Index Compression and Network Compression. ACO also gives you more compression methods that allow you to optimize for compute performance or for storage limitations. Basic Compression is free, but it is not always easy to distinguish exactly which uses may require a license. Here are examples of tables using Basic Compression.
reate table t1 compress basic …
alter table t1 compress basic …
And some other entities using Advanced Compression.
create index … compress advanced;
create tablespace … compress advanced;
Simple right? Just always follow the
compress keyword with
basic and you are all set! This is good practice if you have not purchased Advanced Compression, but there are more gotchas to be aware of.
RMAN Backup Compression
RMAN Backup Compression defaults to BASIC. This mode does not require the Oracle Advanced Compression option.
LOW, MEDIUM, and
HIGH compression options do require Advanced Compression.
Data Pump Compression
Data Pump Export has options for data compression. When exporting, select command line option
COMPRESSION=NONE to disable compression. The default Data Pump option
COMPRESSION=METADATA_ONLY is also ‘safe’. This option does not require Oracle Advanced Compression.
These two options do require Advanced Compression.
expdp … COMPRESSION=ALL
expdp … COMPRESSION=DATA_ONLY
Keep in mind when you import data with Data Pump, that one of the methods above will be used to extract the data. Since the decompression happens inline, you do not have a chance to specify the decompression method on a Data Pump import.
For example, if you create a Data Pump export with
COMPRESSION=ALL on a host that is licensed for Advanced Compression and then perform a Data Pump import of that data on a different host, you must ensure that the second host server is also licensed for Advanced Compression.
You can perform an “online move” to move a partition online without affecting DML operations. But be wary of doing this with tables using compression if you are not licensed for Oracle Advanced Compression. Consider the following command
alter table test move partition part2
Even if the ‘test’ table uses BASIC compression, the Online Move requires Advanced Compression.
There are other well documented features that utilize Oracle Advanced Compression. Heat Map, Index Compression and Network Compression are all covered in the Oracle white paper mentioned at the top of this post.
If you use compression and are not licensed for Oracle Advanced Compression on all host servers running Oracle workloads, keep the considerations mentioned here in mind.
Understanding Oracle licensing can be complex and go beyond just counting cores and users. House of Brick can help you understand your Oracle licensing footprint and provides services for maintaining the license compliance on an ongoing basis. Contact the House of Brick License Management team at https://houseofbrick.com/solutions/license-management/