Oracle Advanced Compression Licensing



  Tables of Contents

 

1.  Version History

Version
Date
Author
Remarks
1.0
27-Oct-10
*******
Initial Document

2.  Purpose

This document is intended to provide information on how to track Oracle Advanced Compression Usage.

3.  License Requirement

Use of Oracle Advanced Compression features requires extra license and is available only with Enterprise Edition.

4.  Oracle Advanced Compression - Overview

Oracle Advanced Compression provides comprehensive data compression capabilities to compress all types of data, backups, and network traffic in an application transparent manner. With Advanced Compression, Oracle includes table compression targeted at OLTP workloads, resulting in reduced storage consumption and improved query performance while incurring minimal write performance overhead. Advanced Compression can be used to compress any unstructured content using SecureFiles Compression. Deduplication provides the ability to automatically eliminate redundant copies of SecureFiles data. RMAN offers multiple backup compression levels, to meet the needs of various environments, including faster compression and better compression ratio choices. Note that the RMAN DEFAULT COMPRESS option does not require Advanced Compression. With Advanced Compression, Data Pump exports can now be compressed to reduce disk space requirements. Note that the COMPRESSION=METADATA_ONLY option for Data Pump does not require Advanced Compression, and is the default behavior. Finally, Data Guard can compress redo data resulting in reduced network traffic and faster gap resolution.
The Oracle Advanced Compression option contains the following features:
·         Data Guard Network Compression
·         Data Pump Compression (COMPRESSION=METADATA_ONLY does not require the Advanced Compression option)
·         Multiple RMAN Compression Levels (RMAN DEFAULT COMPRESS does not require the Advanced Compression option)
·         OLTP Table Compression
·         SecureFiles Compression and Deduplication.

5.  Salient Points


  • Oracle Advanced Compression was introduced in 11gR1.
  • Basic table compression is available in Oracle 10gR1 onwards. Basic table (compression does not require advanced compression license.
  • Its usage can be tracked via DBA_FEATURE_USAGE_STATISTICS view. This view is available from 10gR1 (10.1) onwards. This view can be used to track other licensable feature usage as well. Please see Appendix A.
  • Oracle Advanced Compression usage can be tracked by querying DBA_FEATURE_USAGE_STATISTICS view, for following features :-


Advanced Compression
License
Required
Name
Description
Y
Backup ZLIB Compression ZLIB compressed backups are being used.
N
Backup BZIP2 Compression BZIP2 compressed backups are being used.
N
Backup BASIC Compression BASIC compressed backups are being used.
Y
Backup LOW Compression LOW compressed backups are being used.
Y
Backup MEDIUM Compression MEDIUM compressed backups are being used.
Y
Backup HIGH Compression HIGH compressed backups are being used.
Y
SecureFile Compression (user) SecureFile Compression is being used
N
SecureFile Compression (system) SecureFile Compression is being used by system users
Y
SecureFile Deduplication (user) SecureFile Deduplication is being used
N
SecureFile Deduplication (system) SecureFile Deduplication is being used by system users
Y
HeapCompression
Heap Compression is being used

And in DBA_TABLES.COMPRESSED_FOR column has one of the following values:-
- FOR ALL OPERATIONS
- OLTP
- QUERY LOW
- QUERY HIGH
- ARCHIVE LOW
- ARCHIVE HIGH
Y
Hybrid Columnar Compression Hybrid Columnar Compression is used
Y
(Conditional)
Data Guard Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
( With “Compression used: TRUE” in FEATURE INFO column. )
Y
(Conditional)
Oracle Utility Datapump (Export) Oracle Utility Datapump (Export) has been used.
( With “compression used: >0” in FEATURE INFO column. )
Y
(Conditional)
Oracle Utility Datapump (Import) Oracle Utility Datapump (Import) has been used.
( With “compression used: >0” in FEATURE INFO column. )
               

6.  How to disable Oracle Advanced Compression?

To disable advanced compression features, some solutions/workarounds (if available) are summarized here.

6.1.              OLTP Table Compression:


To disable OLTP compression set following init.ora parameter.

6.2.              SecureFile (Lob) Compression.

This is harder. You can set Parameter DB_SECUREFILE to NEVER or IGNORE. With this setting it is not possible to create securefile LOBs, but on the other hand your users might be clever enough to reset this parameter to PERMITTED which they can do with 'alter session' privilege. A disadvantage is that you would not allow (or try to) securefiles at all, which would not be necessary, as securefiles are allowed also without ACO. As this is not bullet proof, cause of the 'alter session' thing, the only save way would be not to use ASSM Tablespaces as SecureFiles are only supported on ASSM Tablespaces. You would need to create the tablespaces with 'segment space management manual' clause. But also think about performance impacts when using tablespaces with freelists.

6.3.              Data Pump Compression

It is impossible to avoid its usage without revoking right to use data pump at all.

Enhancement Request has been created for this
Bug 8478082: DISALLOW UNLICENSED COMPRESSION FEATURE WHILE USING DATA PUMP

6.4.              Fast ZLIB RMAN Compression


This is a thing the DBA can control, he knows if database is licensed or not and which compression method to use. Old BZIP2 Compression introduced with 10g is still usable without ACO license. This feature is not enabled by default.

Here is RMAN syntax for making sure Basic BZIP2 Compression is used:
11gR1:
CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
11gR2:
CONFIGURE COMPRESSION ALGORITHM 'BASIC';
Or for both, reset to default:
CONFIGURE COMPRESSION ALGORITHM CLEAR;

6.5.              Data Guard Log Transport Compression


This can also be controlled by the DBA who sets up a data guard environment and is also not enabled by default.

And again some syntax:
DGMGRL> edit database 'DB_SITE1' set property RedoCompression='DISABLE';
this results into a log_archive_dest_N parameter with an attribute 'compression=disable'.
In 11gR2 I came across an init.ora Parameter in documentation called ENABLE_OPTION_ADVANCED_COMPRESSION. The Parameter is mentioned when explaining the compression attribute of log_archive_dest_N.
Also there is an ORA Error which reveals there is something like this:
# oerr ora 43850
43850, 0000, "Advanced Compression Option parameter is required to be enabled"
// *Document: YES
// *Cause:    An Advanced Compression Option (ACO) feature was specified
//            without enabling the ACO parameter.
// *Action:   Set or Update the ACO parameter (enable_option_advanced_compression)
//            to TRUE.
I felt this must be the solution but did find any more about this, so I asked Oracle in a Service Request. Their answer was disillusioning  :-|  :
BUG: 8596419
RDBMS 11.2 RAM DATA PRODID-5 PORTID-46
Abstract: REMOVE THE COMPRESSION OPTION PARAMETER FROM CODE AND DOCS
Status: 80,Development to Q/A
Fixed In Ver: 11.2
That's a pitty. I would be a good idea to give the dba the control of feature enabled or not.

6.6.              Summary

Preventing Advanced Compression options from being used is a hard thing, you might say - not even possible. Reliable disabling also takes away some EE licensed features or can raise performance problems.

7.  Appendix A - DBA_FEATURE_USAGE_STATISTICS


Column
Datatype
NULL
Description
DBID
NUMBER
NOT NULL
Database identifier of the database being tracked
NAME
VARCHARE(64)
NOT NULL
Name of the feature (See Table below)
VERSION
VARCHAR2(17)
NOT NULL
Database version in which the feature was tracked
DETECTED_USAGES
NUMBER
NOT NULL
Number of times the system has detected usage for the feature
TOTAL_SAMPLES
NUMBER
NOT NULL
Number of times the system has woken up and checked for feature usage
CURRENTLY_USED
VARCHAR2(5)

Indicates whether usage was detected the last time the system checked (TRUE) or not (FALSE)
FIRST_USAGE_DATE
DATE

First sample time the system detected usage of the feature
LAST_USAGE_DATE
DATE

Last sample time the system detected usage of the feature
AUX_COUNT
NUMBER

This column stores feature-specific usage data in number format.
FEATURE_INFO
CLOB

This column stores feature-specific usage data in character format.
LAST_SAMPLE_DATE
DATE

Amount of time (in seconds) between the last two usage sample times
LAST_SAMPLE_PERIOD
NUMBER

Amount of time (in hours) between the last two usage sample times
SAMPLE_INTERVAL
NUMBER

Sample interval
DESCRIPTION
VARCHAR2(128)

Description of the feature and usage detection logic (See Table)

DBA_FEATURE_USAGE_STATISTICS Statistics
Name
Description
Logfile Multiplexing Multiple members are used in a single log file group
Bigfile Tablespace Bigfile tablespace is being used
Transportable Tablespace Transportable tablespace is being used
Read Only Tablespace Read only tablespace is being used
Deferred Open Read Only Deferred open read only feature is enabled
Active Data Guard - Real-Time Query on Physical Standby Active Data Guard real-time query is enabled on a physical standby
Backup Rollforward Backup Rollforward strategy is being used to backup the database.
Data Recovery Advisor Data Recovery Advisor (DRA) is being used to repair the database.
Resource Manager Oracle Database Resource Manager is being used to manage database resources.
Server Flash Cache Database Smart Flash Cache is being used with this database.
Server Parameter File The server parameter file (SPFILE) was used to startup the database.
Shared Server The database is configured as Shared Server, where one server process can service multiple client programs.
Spatial There is at least one usage of the Oracle Spatial index metadata table.
Locator There is at least one usage of the Oracle Locator index metadata table.
ADDM ADDM has been used.
SQL Access Advisor SQL Access Advisor has been used.
Undo Advisor Undo Advisor has been used.
SQL Tuning Advisor SQL Tuning Advisor has been used.
Segment Advisor Segment Advisor has been used.
SQL Workload Manager SQL Workload Manager has been used.
Tune MView Tune MView has been used.
SQL Performance Analyzer SQL Performance Analyzer has been used.
SQL Repair Advisor SQL Repair Advisor has been used.
Advanced Replication Advanced Replication has been enabled.
ASO native encryption and checksumming ASO network native encryption and checksumming is being used.
Audit Options Audit options in use.
Automatic Maintenance - Optimizer Statistics Gathering Automatic initiation of Optimizer Statistics Collection
Automatic Maintenance - Space Advisor Automatic initiation of Space Advisor
Automatic Maintenance - SQL Tuning Advisor Automatic initiation of SQL Tuning Advisor
Automatic Segment Space Management (system) Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user) Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Management Automatic Storage Management has been enabled
Automatic Undo Management Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
AWR Baseline At least one AWR Baseline has been created by the user
AWR Baseline Template At least one AWR Baseline Template has been created by the user
AWR Report At least one Workload Repository Report has been created by the user
Backup Encryption Encrypted backups are being used.
Baseline Adaptive Thresholds Adaptive Thresholds have been configured.
Baseline Static Computations Static baseline statistics have been computed.
Change-Aware Incremental Backup Track blocks that have changed in the database.
Client Identifier Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions Clusterwide Global Transactions is being used.
Crossedition Triggers Crossedition triggers is being used.
CSSCAN Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Character Semantics Character length semantics is used in Oracle Database
Character Set Character set is used in Oracle Database
Data Guard Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining There exist Oracle Data Mining models in the database.
Dynamic SGA The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editions Editions is being used.
Editioning Views Editioning views is being used.
EM Database Control EM Database Control Home Page has been visited at least once.
EM Grid Control EM Grid Control Database Home Page has been visited at least once.
Diagnostic Pack Diagnostic Pack related EM page has been visited at least once.
Tuning Pack Tuning Pack related EM page has been visited at least once.
File Mapping File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.
Flashback Database Flashback Database, a rewind button for the database, is enabled
Flashback Data Archive Flashback Data Archive, a historical repository of changes to data contained in a table, is used
Internode Parallel Execution Internode Parallel Execution is being used.
Label Security Oracle Label Security, that enables label-based access control Oracle applications, is being used.
Oracle Database Vault Oracle Database Vault is being used
Locally Managed Tablespaces (system) There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user) There exists user tablespaces that are locally managed in the database.
Messaging Gateway Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.
Very Large Memory Very Large Memory is enabled.
Automatic Memory Tuning Automatic Memory Tuning is enabled.
Automatic SGA Tuning Automatic SGA Tuning is enabled.
Encrypted Tablespaces Encrypted Tablespaces is enabled.
MTTR Advisor Mean Time to Recover Advisor is enabled.
Multiple Block Sizes Multiple Block Sizes are being used with this database.
OLAP - Analytic Workspaces OLAP - the analytic workspaces stored in the database.
OLAP - Cubes OLAP - number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.
Oracle Managed Files Database files are being managed by Oracle.
Oracle Secure Backup Oracle Secure Backup is used for backups to tertiary storage.
Parallel SQL DDL Execution Parallel SQL DDL Execution is being used.
Parallel SQL DML Execution Parallel SQL DML Execution is being used.
Parallel SQL Query Execution Parallel SQL Query Execution is being used.
Partitioning (system) Oracle Partitioning option is being used - there is at least one partitioned object created.
Partitioning (user) Oracle Partitioning option is being used - there is at least one user partitioned object created.
Oracle Text Oracle Text is being used - there is at least one oracle text index
PL/SQL Native Compilation PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.
Real Application Clusters (RAC) Oracle Real Application Clusters (Oracle RAC) is configured.
Recovery Area The recovery area is configured.
Recovery Manager (RMAN) Recovery Manager (RMAN) is being used to backup the database.
RMAN - Disk Backup Recovery Manager (RMAN) is being used to backup the database to disk.
RMAN - Tape Backup Recovery Manager (RMAN) is being used to backup the database to tape.
Backup ZLIB Compression ZLIB compressed backups are being used.
Backup BZIP2 Compression BZIP2 compressed backups are being used.
Backup BASIC Compression BASIC compressed backups are being used.
Backup LOW Compression LOW compressed backups are being used.
Backup MEDIUM Compression MEDIUM compressed backups are being used.
Backup HIGH Compression HIGH compressed backups are being used.
Long-term Archival Backup Long-term archival backups are being used.
Multi Section Backup Multi section backups are being used.
Block Media Recovery Block Media Recovery is being used to repair the database.
Restore Point Restore Points are being used as targets for Flashback
Compression Advisor Compression Advisor has been used.
Real-Time SQL Monitoring Real-Time SQL Monitoring Usage.
SQL Tuning Set (user) A SQL Tuning Set has been created in the database in a user schema.
SQL Tuning Set (system) A SQL Tuning Set has been created in the database in the SYS schema.
Automatic SQL Tuning Advisor Automatic SQL Tuning Advisor has been used.
SQL Profile SQL profiles have been used.
Database Replay: Workload Capture Database Replay: Workload was ever captured.
Database Replay: Workload Replay Database Replay: Workload was ever replayed.
Streams (system) Oracle Streams processes have been configured
Streams (user) Users have configured Oracle Streams AQ
Transparent Gateway Heterogeneous Connectivity, access to a non-Oracle system, has been configured.
Virtual Private Database (VPD) Virtual Private Database (VPD) policies are being used.
Workspace Manager There is at least one version enabled table.
XDB XDB feature is being used.
Application Express Application Express feature is being used.
LOB Persistent LOBs are being used.
Object Object feature is being used.
Extensibility Extensibility feature is being used.
SQL Plan Management SQL Plan Management has been used.
Rules Manager Rules Manager and Expression Filter
Oracle Utility Datapump (Export) Oracle Utility Datapump (Export) has been used.
Oracle Utility Datapump (Import) Oracle Utility Datapump (Import) has been used.
Oracle Utility SQL Loader (Direct Path Load) Oracle Utility SQL Loader (Direct Path Load) has been used.
Oracle Utility Metadata API Oracle Utility (Metadata API) has been used.
Oracle Utility External Table Oracle Utility External Table has been used.
Result Cache The Result Cache feature has been used.
Transparent Data Encryption Transparent Database Encryption is being used. There is at least one column or tablespace that is encrypted.
Oracle Multimedia Oracle Multimedia has been used
Oracle Multimedia DICOM Oracle Multimedia DICOM (Digital Imaging and COmmunications in Medicine) has been used
Materialized Views (User) User Materialized Views exist in the database
Change Data Capture Change Data Capture exit in the database
Services Oracle Services.
Semantics/RDF A semantic network has been created indicating usage of the Oracle Semantics Feature.
SecureFiles (user) SecureFiles is being used
SecureFiles (system) SecureFiles is being used by system users
SecureFile Encryption (user) SecureFile Encryption is being used
SecureFile Encryption (system) SecureFile Encryption is being used by system users
SecureFile Compression (user) SecureFile Compression is being used
SecureFile Compression (system) SecureFile Compression is being used by system users
SecureFile Deduplication (user) SecureFile Deduplication is being used
SecureFile Deduplication (system) SecureFile Deduplication is being used by system users
Segment Advisor (user) Segment Advisor has been used. There is at least one user task executed.
HeapCompression Heap Compression is being used
Hybrid Columnar Compression Hybrid Columnar Compression is used
Segment Shrink Segment Shrink has been used.
Job Scheduler Job Scheduler feature is being used.
Oracle Java Virtual Machine (user) OJVM has been used by at least one non-system user
Oracle Java Virtual Machine (system) OJVM default system users
Exadata Exadata is being used

8.  References


  • Oracle Open World 2009 presentation

  • Oracle 11gR2 Docs

  • How to disable Oracle Advanced Compression options

4 comments:

  1. Hey there just wanted to give you a quick heads up. The words in your post seem to be running off the screen in Internet explorer.
    I'm not sure if this is a formatting issue or something to do with browser compatibility but I thought I'd post to let
    you know. The style and design look great though!
    Hope you get the issue fixed soon. Cheers

    Check out my site: travis county divorce free

    ReplyDelete
    Replies
    1. Friend, Thanks for your info, I have changed my site layout.

      Delete
  2. Hey,
    HCC (QUERY LOW and QUERY HIGH) need not Advance Compression license.

    ReplyDelete
  3. Hey,
    HCC (QUERY LOW and QUERY HIGH) need not Advanced Compression license.
    HCC is include in Exadata Storage license.

    ReplyDelete