Oracle 11g

Oracle licensing options usage – report from Oracle support

This entry was posted in Licensing My Oracle Support Oracle 11g Oracle 12c on by

Less than a month ago, Oracle support released a major update of the reporting script for options/features/packs usage. It can be found at:

Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2, 12c, 12cR2 and 18c (Doc ID 1317265.1)

It is a simple script that takes no time to execute. It will show both product and feature usage, including parameters that enable packs, like control_management_pack_access (Diagnostics and Tuning part) and enable_ddl_logging (Lifecycle Management pack). When run on a container database, it will show the feature usage per PDB.

It is important to know that it is based on DBA_FEATURE_USAGE_STATISTICS view, which is updated once a week. You can trigger an manual update using the information at How to Manually Refresh Dba_feature_usage_statistics (Doc ID 1629485.1) 

SQL> connect / as sysdba
SQL> alter session set "_SWRF_TEST_ACTION"=53;
SQL> alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
SQL> select MAX(LAST_SAMPLE_DATE) from dba_feature_usage_statistics;

More details on each paid feature of Oracle Database Options and Packs can be found in the Oracle 12.2 Database Licensing Information guide (same for 11.2, 12.1 and 18c).

An example of the output of the script can be found below:

Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2, 12c, 12cR2 and 18c (Doc ID 1317265.1)

SQL> @options_packs_usage_statistics.sql
OVERALL INFORMATION

HOST_NAME                               |INSTANCE_NAME   |DATABASE_NAME |OPEN_MODE       |DATABASE_ROLE   |CREATED            |      DBID|VERSION    |BANNER
----------------------------------------|----------------|--------------|----------------|----------------|-------------------|----------|-----------|--------------------------------------------------------------------------------
anjovm1                                 |ANJOCDB         |ANJOCDB       |READ WRITE      |PRIMARY         |2018.01.31_08.47.22| 380093466|12.2.0.1.0 |Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PARAMETER                     |VALUE
------------------------------|--------------------
control_management_pack_access|DIAGNOSTIC
enable_ddl_logging            |FALSE


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MULTITENANT INFORMATION (Please ignore errors in pre 12.1 databases)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CON_ID|NAME                          |OPEN_MODE       |RESTRICTED|REMARKS
------|------------------------------|----------------|----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     1|CDB$ROOT                      |READ WRITE      |NO        |*CURRENT CONTAINER is CDB$ROOT. Information for all open PDBs will be listed.
     2|PDB$SEED                      |READ ONLY       |NO        |
     3|ANJOPDB1                      |READ WRITE      |NO        |

The multitenant architecture with one user-created pluggable database (single tenant) is available in all editions without the Multitenant Option.
If more than one PDB containers are created, then Multitenant Option licensing is needed



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>> Selecting from CDB_FEATURE_USAGE_STATISTICS


DBA_FEATURE_USAGE_STATISTICS (DBA_FUS) INFORMATION - MOST RECENT SAMPLE BASED ON LAST_SAMPLE_DATE

CON_ID|LAST_DBA_FUS_DBID|LAST_DBA_FUS_VERS|LAST_DBA_FUS_SAMPLE|SYSDATE            |REMARKS
------|-----------------|-----------------|-------------------|-------------------|--------------------------------------------------------------------
     1|        380093466|12.2.0.1.0       |2018.09.01_06.37.39|2018.09.06_09.19.25|
     3|        380093466|12.2.0.1.0       |2018.09.01_18.37.40|2018.09.06_09.19.25|


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PRODUCT USAGE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CON_NAME                      |PRODUCT                                            |USAGE                   |LAST_SAMPLE_DATE   |FIRST_USAGE_DATE   |LAST_USAGE_DATE
------------------------------|---------------------------------------------------|------------------------|-------------------|-------------------|-------------------
--ALL--                       |Active Data Guard                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Advanced Analytics                                 |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Advanced Compression                               |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Advanced Security                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Database In-Memory                                 |CURRENT_USAGE           |2018.09.01_18.37.40|2018.06.02_06.28.39|2018.09.01_18.37.40
--ALL--                       |Database Vault                                     |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Diagnostics Pack                                   |CURRENT_USAGE           |2018.09.01_18.37.40|2018.05.26_12.27.59|2018.09.01_06.37.39
--ALL--                       |Label Security                                     |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Multitenant                                        |NO_USAGE                |2018.09.01_06.37.39|                   |
--ALL--                       |OLAP                                               |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Partitioning                                       |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |RAC or RAC One Node                                |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Real Application Clusters                          |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Real Application Clusters One Node                 |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Real Application Testing                           |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Spatial and Graph                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |Tuning Pack                                        |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |.Database Gateway                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |.Exadata                                           |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |.GoldenGate                                        |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |.HW                                                |NO_USAGE                |2018.09.01_18.37.40|                   |
--ALL--                       |.Pillar Storage                                    |NO_USAGE                |2018.09.01_18.37.40|                   |

CON_NAME                      |PRODUCT                                            |USAGE                   |LAST_SAMPLE_DATE   |FIRST_USAGE_DATE   |LAST_USAGE_DATE
------------------------------|---------------------------------------------------|------------------------|-------------------|-------------------|-------------------
CDB$ROOT                      |Active Data Guard                                  |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Advanced Analytics                                 |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Advanced Compression                               |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Advanced Security                                  |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Database In-Memory                                 |CURRENT_USAGE    |2018.09.01_06.37.39|2018.06.02_06.28.39|2018.09.01_06.37.39
CDB$ROOT                      |Database Vault                                     |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Diagnostics Pack                                   |CURRENT_USAGE           |2018.09.01_06.37.39|2018.05.26_12.27.59|2018.09.01_06.37.39
CDB$ROOT                      |Label Security                                     |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Multitenant                                        |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |OLAP                                               |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Partitioning                                       |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |RAC or RAC One Node                                |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Real Application Clusters                          |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Real Application Clusters One Node                 |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Real Application Testing                           |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Spatial and Graph                                  |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |Tuning Pack                                        |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |.Database Gateway                                  |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |.Exadata                                           |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |.GoldenGate                                        |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |.HW                                                |NO_USAGE                |2018.09.01_06.37.39|                   |
CDB$ROOT                      |.Pillar Storage                                    |NO_USAGE                |2018.09.01_06.37.39|                   |

CON_NAME                      |PRODUCT                                            |USAGE                   |LAST_SAMPLE_DATE   |FIRST_USAGE_DATE   |LAST_USAGE_DATE
------------------------------|---------------------------------------------------|------------------------|-------------------|-------------------|-------------------
ANJOPDB1                      |Active Data Guard                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Advanced Analytics                                 |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Advanced Compression                               |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Advanced Security                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Database In-Memory                                 |CURRENT_USAGE           |2018.09.01_18.37.40|2018.06.02_18.28.39|2018.09.01_18.37.40
ANJOPDB1                      |Database Vault                                     |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Diagnostics Pack                                   |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Label Security                                     |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |OLAP                                               |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Partitioning                                       |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |RAC or RAC One Node                                |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Real Application Clusters                          |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Real Application Clusters One Node                 |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Real Application Testing                           |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Spatial and Graph                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |Tuning Pack                                        |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |.Database Gateway                                  |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |.Exadata                                           |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |.GoldenGate                                        |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |.HW                                                |NO_USAGE                |2018.09.01_18.37.40|                   |
ANJOPDB1                      |.Pillar Storage                                    |NO_USAGE                |2018.09.01_18.37.40|                   |


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
FEATURE USAGE DETAILS
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CON_NAME                      |PRODUCT                                            |FEATURE_BEING_USED                                      |USAGE                   |LAST_SAMPLE_DATE   |      DBID|VERSION    |DETECTED_USAGES|TOTAL_SAMPLES|CURRENTLY_USED|FIRST_USAGE_DATE   |LAST_USAGE_DATE    |EXTRA_FEATURE_INFO
------------------------------|---------------------------------------------------|--------------------------------------------------------|------------------------|-------------------|----------|-----------|---------------|-------------|--------------|-------------------|-------------------|--------------------------------------------------------------------------------
CDB$ROOT                      |Active Data Guard                                  |Active Data Guard - Real-Time Query on Physical Standby |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Active Data Guard                                  |Global Data Services                                    |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Analytics                                 |Data Mining                                             |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Advanced Index Compression                              |SUPPRESSED_DUE_TO_BUG   |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Backup HIGH Compression                                 |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Backup LOW Compression                                  |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Backup MEDIUM Compression                               |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Backup ZLIB Compression                                 |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Data Guard                                              |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |HeapCompression                                         |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Heat Map                                                |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Hybrid Columnar Compression Row Level Locking           |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Information Lifecycle Management                        |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Oracle Advanced Network Compression Service             |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |Oracle Utility Datapump (Export)                        |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |             10|           31|TRUE          |2018.05.19_10.21.15|2018.09.01_06.37.39|compression used: 0 times
CDB$ROOT                      |Advanced Compression                               |Oracle Utility Datapump (Import)                        |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |SecureFile Compression (user)                           |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Compression                               |SecureFile Deduplication (user)                         |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Security                                  |Data Redaction                                          |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Security                                  |Encrypted Tablespaces                                   |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Security                                  |Oracle Utility Datapump (Export)                        |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |             10|           31|TRUE          |2018.05.19_10.21.15|2018.09.01_06.37.39|encryption used: 0 times
CDB$ROOT                      |Advanced Security                                  |Oracle Utility Datapump (Import)                        |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Security                                  |SecureFile Encryption (user)                            |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Advanced Security                                  |Transparent Data Encryption                             |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Database In-Memory                                 |In-Memory Aggregation                                   |CURRENT_USAGE           |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |             14|           31|TRUE          |2018.06.02_06.28.39|2018.09.01_06.37.39|
CDB$ROOT                      |Database In-Memory                                 |In-Memory Column Store                                  |CURRENT_USAGE           |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |             14|           31|TRUE          |2018.06.02_06.28.39|2018.09.01_06.37.39|
CDB$ROOT                      |Database Vault                                     |Oracle Database Vault                                   |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Database Vault                                     |Privilege Capture                                       |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |ADDM                                                    |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |AWR Baseline                                            |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |AWR Baseline Template                                   |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |AWR Report                                              |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |Automatic Workload Repository                           |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |Baseline Adaptive Thresholds                            |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |Baseline Static Computations                            |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Diagnostics Pack                                   |EM Performance Page                                     |CURRENT_USAGE           |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              5|           31|TRUE          |2018.05.26_12.27.59|2018.09.01_06.37.39|
CDB$ROOT                      |Label Security                                     |Label Security                                          |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Multitenant                                        |Oracle Multitenant                                      |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |             31|           31|TRUE          |2018.01.31_15.38.04|2018.09.01_06.37.39|AUX_COUNT=1
CDB$ROOT                      |OLAP                                               |OLAP - Analytic Workspaces                              |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |OLAP                                               |OLAP - Cubes                                            |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Partitioning                                       |Partitioning (user)                                     |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Partitioning                                       |Zone maps                                               |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |RAC or RAC One Node                                |Quality of Service Management                           |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Real Application Clusters                          |Real Application Clusters (RAC)                         |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Real Application Clusters One Node                 |Real Application Cluster One Node                       |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Real Application Testing                           |Database Replay: Workload Capture                       |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Real Application Testing                           |Database Replay: Workload Replay                        |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Real Application Testing                           |SQL Performance Analyzer                                |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Spatial and Graph                                  |Spatial                                                 |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Tuning Pack                                        |SQL Access Advisor                                      |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Tuning Pack                                        |SQL Monitoring and Tuning pages                         |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Tuning Pack                                        |SQL Profile                                             |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |Tuning Pack                                        |SQL Tuning Advisor                                      |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.Database Gateway                                  |Gateways                                                |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.Database Gateway                                  |Transparent Gateway                                     |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.Exadata                                           |Cloud DB with EHCC                                      |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.Exadata                                           |Exadata                                                 |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.GoldenGate                                        |GoldenGate                                              |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.HW                                                |Hybrid Columnar Compression                             |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.HW                                                |Hybrid Columnar Compression Conventional Load           |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.HW                                                |Hybrid Columnar Compression Row Level Locking           |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.HW                                                |Sun ZFS with EHCC                                       |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.HW                                                |ZFS Storage                                             |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.HW                                                |Zone maps                                               |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.Pillar Storage                                    |Pillar Storage                                          |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
CDB$ROOT                      |.Pillar Storage                                    |Pillar Storage with EHCC                                |NO_CURRENT_USAGE        |2018.09.01_06.37.39| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |

CON_NAME                      |PRODUCT                                            |FEATURE_BEING_USED                                      |USAGE                   |LAST_SAMPLE_DATE   |      DBID|VERSION    |DETECTED_USAGES|TOTAL_SAMPLES|CURRENTLY_USED|FIRST_USAGE_DATE   |LAST_USAGE_DATE    |EXTRA_FEATURE_INFO
------------------------------|---------------------------------------------------|--------------------------------------------------------|------------------------|-------------------|----------|-----------|---------------|-------------|--------------|-------------------|-------------------|--------------------------------------------------------------------------------
ANJOPDB1                      |Active Data Guard                                  |Active Data Guard - Real-Time Query on Physical Standby |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Active Data Guard                                  |Global Data Services                                    |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Analytics                                 |Data Mining                                             |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Advanced Index Compression                              |SUPPRESSED_DUE_TO_BUG   |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Backup HIGH Compression                                 |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Backup LOW Compression                                  |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Backup MEDIUM Compression                               |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Backup ZLIB Compression                                 |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Data Guard                                              |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |HeapCompression                                         |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Heat Map                                                |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Hybrid Columnar Compression Row Level Locking           |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Information Lifecycle Management                        |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Oracle Advanced Network Compression Service             |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Oracle Utility Datapump (Export)                        |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |Oracle Utility Datapump (Import)                        |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              5|           31|FALSE         |2018.02.17_09.22.16|2018.03.17_11.41.30|
ANJOPDB1                      |Advanced Compression                               |SecureFile Compression (user)                           |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Compression                               |SecureFile Deduplication (user)                         |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Security                                  |Data Redaction                                          |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Security                                  |Encrypted Tablespaces                                   |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Security                                  |Oracle Utility Datapump (Export)                        |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Security                                  |Oracle Utility Datapump (Import)                        |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              5|           31|FALSE         |2018.02.17_09.22.16|2018.03.17_11.41.30|
ANJOPDB1                      |Advanced Security                                  |SecureFile Encryption (user)                            |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Advanced Security                                  |Transparent Data Encryption                             |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Database In-Memory                                 |In-Memory Aggregation                                   |CURRENT_USAGE           |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |             14|           31|TRUE          |2018.06.02_18.28.39|2018.09.01_18.37.40|
ANJOPDB1                      |Database In-Memory                                 |In-Memory Column Store                                  |CURRENT_USAGE           |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |             14|           31|TRUE          |2018.06.02_18.28.39|2018.09.01_18.37.40|
ANJOPDB1                      |Database Vault                                     |Oracle Database Vault                                   |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Database Vault                                     |Privilege Capture                                       |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |ADDM                                                    |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |AWR Baseline                                            |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |AWR Baseline Template                                   |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |AWR Report                                              |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |Automatic Workload Repository                           |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |Baseline Adaptive Thresholds                            |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |Baseline Static Computations                            |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Diagnostics Pack                                   |EM Performance Page                                     |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Label Security                                     |Label Security                                          |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |OLAP                                               |OLAP - Analytic Workspaces                              |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |OLAP                                               |OLAP - Cubes                                            |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Partitioning                                       |Partitioning (user)                                     |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Partitioning                                       |Zone maps                                               |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |RAC or RAC One Node                                |Quality of Service Management                           |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Real Application Clusters                          |Real Application Clusters (RAC)                         |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Real Application Clusters One Node                 |Real Application Cluster One Node                       |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Real Application Testing                           |Database Replay: Workload Capture                       |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Real Application Testing                           |Database Replay: Workload Replay                        |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Real Application Testing                           |SQL Performance Analyzer                                |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Spatial and Graph                                  |Spatial                                                 |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Tuning Pack                                        |SQL Access Advisor                                      |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Tuning Pack                                        |SQL Monitoring and Tuning pages                         |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Tuning Pack                                        |SQL Profile                                             |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |Tuning Pack                                        |SQL Tuning Advisor                                      |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.Database Gateway                                  |Gateways                                                |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.Database Gateway                                  |Transparent Gateway                                     |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.Exadata                                           |Cloud DB with EHCC                                      |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.Exadata                                           |Exadata                                                 |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.GoldenGate                                        |GoldenGate                                              |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.HW                                                |Hybrid Columnar Compression                             |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.HW                                                |Hybrid Columnar Compression Conventional Load           |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.HW                                                |Hybrid Columnar Compression Row Level Locking           |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.HW                                                |Sun ZFS with EHCC                                       |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.HW                                                |ZFS Storage                                             |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.HW                                                |Zone maps                                               |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.Pillar Storage                                    |Pillar Storage                                          |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |
ANJOPDB1                      |.Pillar Storage                                    |Pillar Storage with EHCC                                |NO_CURRENT_USAGE        |2018.09.01_18.37.40| 380093466|12.2.0.1.0 |              0|           31|FALSE         |                   |                   |

USER is "SYS"

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DESCRIPTION:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The two reports, PRODUCT USAGE and FEATURE USAGE DETAILS, provide usage statistics for Database Options, Management Packs
and their corresponding features.
Information is extracted from DBA_FEATURE_USAGE_STATISTICS view.

DBA_FEATURE_USAGE_STATISTICS view is updated once a week, so it may take up to 7 days for the report to reflect usage changes.
DBA_FEATURE_USAGE_STATISTICS view contains a different set of entries for each VERSION and DBID occurring in the database history.
The weekly refresh process updates only the current row set.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
NOTES:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The report lists all detectable products and features, used or not used.
The CURRENTLY_USED column in the DBA_FEATURE_USAGE_STATISTICS view indicates if the feature in question was used during the last sampling interval
or is used at the refresh moment.
CURRENT_USAGE represents usage tracked over the last sample period, which defaults to one week.
PAST_OR_CURRENT_USAGE example: Datapump Export entry indicates CURRENTLY_USED='TRUE' and FEATURE_INFO "compression used" counter
indicates a non zero value that could have been triggered by past or current (last week) usage.
For historical details check FIRST_USAGE_DATE, LAST_USAGE_DATE, LAST_SAMPLE_DATE, TOTAL_SAMPLES, DETECTED_USAGES columns
Leading dot (.) denotes a product that is not a Database Option or Database Management Pack

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DISCLAIMER:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Information provided by the reports is to be used for informational purposes only and does not represent your license entitlement or requirement.
The usage data may indicate, in some cases, false positives.
This may be due to inclusion of usage by sample schemas (such as HR, PM, SH...) or system/internal usage.

Please refer to MOS DOC ID 1317265.1 and 1309070.1 for more information.

End of script (v 18.1 Apr-2018)

Check Advanced Compression option usage on Oracle database

This entry was posted in Audit Oracle Oracle 11g Oracle 12c on by

To check if the “advanced compression” Oracle database option was used on your environment is not so easy, as there are several activities that can activate this option.

For example for a mix Oracle 11.2.0.4 and 12.1.0.2 environment we can use the following query:

select * from DBA_FEATURE_USAGE_STATISTICS
where ((name like '%Compress%' and name not in ('Backup BASIC Compression','Backup BZIP2 Compression','SecureFile Compression (system)')) 
  and detected_usages>0)
or (name='Data Guard' and FEATURE_INFO like '%Compression used: TRUE%')
or (name in ('Oracle Utility Metadata API','Oracle Utility Datapump (Export)','Oracle Utility Datapump (Import)') 
  and version like '11.2.%' and feature_info not like '%compression used: 0 times%')
or (name in ('Oracle Utility Metadata API','Oracle Utility Datapump (Import)') 
  and version like '12.%' and feature_info like '%compression%')
or (name in ('Oracle Utility Datapump (Export)') 
  and version like '12.%' and feature_info not like '%compression used: 0 times%');

Why do we need so many filters? Because the text that shows if compression was used changes depending on the utility and Oracle version!
Below 3 different examples when compression was not used:

Version Utilities FEATURE_INFO text
12.1 Oracle Utility Datapump (Export) invoked: 1 times, compression used: 0 times (BASIC algorithm used: 0 times, LOW algorithm used: 0 times, MEDIUM algorithm used: 0 times, HIGH algorithm used: 0 times), encryption used: 0 times (AES128 algorithm used: 0 times, AES192 algorithm used: 0 times, AES256 algorithm used: 0 times, PASSWORD mode used: 0 times, DUAL mode used: 0 times, TRANSPARENT mode used: 0 times), parallel used: 0 times, full transportable used: 0 times
12.1 Oracle Utility Datapump (Import)

Oracle Utility Metadata API

 invoked: 1 times, parallel used: 0 times, full transportable used: 0 times
11.2 Oracle Utility Metadata API invoked: 1 times, compression used: 0 times, encryption used: 0 times

Also, seems that for “Data Guard” utility the text has capital letters “%Compression used: TRUE%”.

More information about which features enable the usage of Advanced Compression option can be find here.

And how to avoid the usage of the Advanced Compression option by error on Mathias Zarick blog.

Back to the basics: correct parameter from SPFILE in ASM

This entry was posted in ASM Oracle Oracle 11g on by

When you change the size of memory and forget to update SGA_MAX_SIZE it might happen:

oracle@anjo01:/home/oracle [] srvctl start database -d p05ltm02
PRCR-1079 : Failed to start resource ora.p05ltm02.db
CRS-5017: The resource action "ora.p05ltm02.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00823: Specified value of sga_target greater than sga_max_size

As this is a clustered database with ASM, here the easiest way to solve:

1. Find the full path of the SPFILE on ASM

oracle@anjo01:/home/oracle [p05ltm02_1] srvctl config database -d p05ltm02 -a
Database unique name: p05ltm02
Database name: ltm02
Oracle home: /oracle/app/product/11.2.0.4
Oracle user: oracle
Spfile: +DGDATA/p05ltm02/spfileltm02.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: sp_ha01
Database instances:
Disk Groups: DGDATA,DGFRA
Mount point paths:
Services: p05ltm02_app.anjo01
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: p05ltm02
Candidate servers: anjo01,anjo02
Database is enabled
Database is policy managed

2. Create a PFILE from SPFILE on the local filesystem

oracle@anjo01:/home/oracle [p05ltm02_1] sqh
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 09:33:47 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create pfile='/tmp/pfile.txt' from spfile='+DGDATA/p05ltm02/spfileltm02.ora';
File created.

SQL> exit
Disconnected

3. Correct the damn parameter 

oracle@anjo01:/home/oracle [p05ltm02_1] vi /tmp/pfile.txt

4. Recreate the SPFILE on ASM:

oracle@anjo01:/home/oracle [p05ltm02_1] sqh
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 09:34:44 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile='+DGDATA/p05ltm02/spfileltm02.ora' from pfile='/tmp/pfile.txt';
File created.

SQL> exit
Disconnected

5. And finally restart the database:

oracle@anjo01:/home/oracle [p05ltm02_1] srvctl start database -d p05ltm02

Oracle October 2015 CPU – quick and dirty install on 11.2.0.4 and 12.1.0.2 4

This entry was posted in AIX OPatch Oracle Oracle 11g Oracle 12c PSU Security on by

[Update 17.11.2015 – Thanks Lars Johan Ulveseth for the extra testing and comment – see below. In fact I’ve only tested the PSU installation on a non-CDB 12c database. There is a missing step, added now, when you are in a multitenant configuration with pluggable databases. Script updated based on Lars comments.]

Following my January post, here are basic instructions to install the latest Oracle security patch. While one should always read the documentation, below you can find how to install CPU Oct 2015  on single instance. Please count about 5 minutes downtime on your database (a bit more if it is the first time you are doing this).

If you are single instance database (no RAC) and either 11.2.0.4 or 12.1.0.2 here are the quick summary of the installation. Tested on Linux and AIX:

Oracle 12.1.0.2 – October 2015 CPU installation

OPatch: You need to update the OPatch tool, minimum version is 12.1.0.1.7. Direct download from Oracle support.

Bundle 12.1.0.2 – Patch 21520444 – Combo OJVM PSU 12.1.0.2.5 and Database PSU 12.1.0.2.5 for UNIX

  • Patch 21359755 – Database Patch Set Update 12.1.0.2.5 (Oct2015) –> RAC-Rolling Installable
  • Patch 21555660 – Oracle JavaVM Component 12.1.0.2.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"

1. Download + Upgrade OPatch (Opatch download direct link)

unzip ${PATCH_LOC}/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME

2. Stop database+listeners+agent

3. Apply DB PSU (21359755 ) but do NOT run DB PSU post install steps

cd ${PATCH_LOC}
unzip p2*
cd 2*
cd 21359755/
$ORACLE_HOME/OPatch/opatch apply

3. Apply OJVM PSU patch

cd ../21555660/
$ORACLE_HOME/OPatch/opatch apply

4. Restart database [and open pluggable databases] in upgrade mode

SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;

5. Run post install steps

cd $ORACLE_HOME/OPatch
./datapatch -verbose

6. Restart database [and open pluggable databases] in normal mode

SQL> shutdown;
SQL> startup;
SQL> alter pluggable database all open;





Oracle 11.2.0.4 – October 2015 CPU installation


Bundle 11.2.0.4 – Patch 21744335 – Combo OJVM PSU 11.2.0.4.5 and Database SPU 11.2.0.4 (CPUOct2015)


    

  • Patch 21352646 – Database Security Patch Update 11.2.0.4.0 (CPUOCT2015) –> RAC-Rolling Installable
    • 

  • Patch 21555791 – Oracle JavaVM Component 11.2.0.4.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
    • 



export PATCH_LOC="/tmp"
cd ${PATCH_LOC}
unzip p2*.zip
cd 2*



1. Shutdown databases and services

2. Apply DB PSU (21352646) but do NOT run DB PSU post install steps


cd 21352646
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate



3. Apply OJVM PSU patch


cd ../21555791/
$ORACLE_HOME/OPatch/opatch apply



4. Run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.


cd $ORACLE_HOME/sqlpatch/21555791
sqlplus / as sysdba 



SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;



cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba



SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;



 


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle wallet on Windows registry – share with other users
This entry was posted in  Oracle  Oracle 11g  Security  on  by  
        

        
I installed Oracle wallet to access passwordless to the remote databases, so I could do backups without specifying the password on any script.


The sqlnet.ora has the following information concerning the wallet:


WALLET_LOCATION =
(SOURCE =
(METHOD = reg)
(METHOD_DATA = (KEY = DEFAULT)))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE



The other day, I got the need of giving access to remote databases to another windows user, so he could run a refresh schema/database script.


He was getting the following error when trying to access any database:


ORA-12578: TNS:wallet open failed



While I could just have set a different TNS_ADMIN variable pointing to a sqlnet.ora with


SQLNET.WALLET_OVERRIDE = FALSE



this was not the solution for him to use an automatic script.


The solution was to export/import the following branch of the registry from my windows user to the other windows user:


\\HKEY_CURRENT_USER\SOFTWARE\ORACLE\WALLETS



Note that these entries contain the encrypted password to the wallet, allowing the user to add/remove/modify/see the contents, including passwords, of the current wallet.


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle automatic maintenance, expdp and ORA-01466
This entry was posted in  Datapump  Oracle  Oracle 11g  Oracle 12c  on  by  
        

        
At a client request I’ve implemented a daily consistent export of few schemas, using something like:


expdp "/ as sysdba" SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp



I was set to run at 22:00 every night.


Next day the log showed some errors like:


ORA-31693: Table data object "USER1"."TABLE_TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed



Looking closer, it was due to the automatic statistics collection, done within the daily maintenance window of Oracle. The LAST_ANALYZED column of DBA_TABLES changed, as well as LAST_DDL_TIME on DBA_OBJECTS. I did not expect neither I see nowhere else complaining about this interference between the maintenance windows and a export data pump. Strange!


To avoid any problem in the future we decided to change the maintenance window time and lenght.

On Oracle 11g and 12c there is a window per day. So to change lets say to 19:00 and having a duration of 2 hours you do:


EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));



    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Datapump elapsed time on 11.2.0.4
This entry was posted in  Datapump  Oracle  Oracle 11g  on  by  
        

        
Data-Pump-for-OracleToday I notice a small difference when you export/import using Datapump and you connect to a 11.2.0.4 database: there is the elapsed time at the end.


Both times I was using the binary from Oracle 11.2.0.4:


Export: Release 11.2.0.4.0 - Production on Sun Mar 22 21:07:25 2015



But the output is slight different at the end of the job:


Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
...
Job "RMAN"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Mar 22 21:10:07 2015 elapsed 0 00:02:21



Or:


Connected to: Oracle Database 11g Release 11.2.0.3.0 - Production
...
Job "RMAN"."SYS_EXPORT_SCHEMA_03" successfully completed at 21:07:43



 


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle January 2015 CPU – quick and dirty install on 11.2.0.4 and 12.1.0.2 1 
This entry was posted in  Oracle  Oracle 11g  Oracle 12c  PSU  Security  on  by  
        

        
While one should always read the documentation accompanying the patches, the new January 2015 (and in some extend also October 2014) are more complex to install with a java and a db components.


If you are single instance database (no RAC) and either 11.2.0.4 or 12.1.0.2 here are the quick summary of the installation. Tested on Linux and Solaris SPARC:


Oracle 12.1.0.2 – January 2015 CPU installation


Bundle 12.1.0.2 – Patch 20132434


    

  • Patch 19769480 – Database Patch Set Update 12.1.0.2.2 (Jan2015) –> RAC-Rolling Installable
    • 

  • Patch 19877336 – Oracle JavaVM Component 12.1.0.2.2 Database PSU (JAN2015) –> Non RAC-Rolling Installable
    • 



export PATCH_LOC="/tmp"



1. Download + Upgrade OPatch


unzip ${PATCH_LOC}/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME



2. Stop database+listeners+agent


3. Apply DB PSU (19769480) but do NOT run DB PSU post install steps


cd ${PATCH_LOC}
unzip p2*
cd 2*
cd 19769480/
$ORACLE_HOME/OPatch/opatch apply



3. Apply OJVM PSU patch


cd ../19877336/
$ORACLE_HOME/OPatch/opatch apply



4. Restart database


5. Run post install steps


cd $ORACLE_HOME/OPatch
./datapatch -verbose





 


Oracle 11.2.0.4 – January 2015 CPU installation


 


Bundle 11.2.0.4 – Patch 20132517


    

  • Patch 19854503 – Database Security Patch Update 11.2.0.4.0 (CPUJAN2015) –> RAC-Rolling Installable
    • 

  • Patch 19877440 – Oracle JavaVM Component 11.2.0.4.2 Database PSU (JAN2015) –> Non RAC-Rolling Installable
    • 



export PATCH_LOC="/tmp"
cd ${PATCH_LOC}
unzip p20132517_112040_Linux-x86-64.zip
cd 2*



1. Shutdown databases and services

2. Apply DB PSU (19854503) but do NOT run DB PSU post install steps


cd 19854503
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate



3. Apply OJVM PSU patch


cd ../19877440/
$ORACLE_HOME/OPatch/opatch apply



4. For 11.2.0.3 and 11.2.0.4 run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.


cd $ORACLE_HOME/sqlpatch/19877440
sqlplus / as sysdba



SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;



cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba



SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;



 


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Add path to ASMCMD prompt
This entry was posted in  ASM  Oracle  Oracle 11g  Oracle 12c  RAC  on  by  
        

        
Just discovered this morning how to show the path on the ASMCMD tool and could not be easier:


anjovm2:/home/grid:+ASM2 $ asmcmd -p
ASMCMD [+] > cd dgdata
ASMCMD [+dgdata] > cd db12c
ASMCMD [+dgdata/db12c] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfiledb12c.ora
ASMCMD [+dgdata/db12c] > cd datafile
ASMCMD [+dgdata/db12c/datafile] >



    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
ORA-17628: Oracle error 19505 returned by remote Oracle server
This entry was posted in  DataGuard  Oracle  Oracle 11g  Oracle 12c  on  by  
        

        
Today I was doing a procedure for duplicating databases at a client environment. One error that pop up time to time was the one below:


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/13/2014 18:49:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 05/13/2014 18:49:00
ORA-17628: Oracle error 19505 returned by remote Oracle server



At several websites you can see different causes:


– Control file location specification was wrong;


– db_file_name_convert were not specified;


– FRA diskgroup inexisting in the target;


– etc.


I had the following two cases on my experiments:


– destination database directory not existing on the DGDATA diskgroup (+DGDATA/<db_unique_name>)


– I did not connect to auxiliary using a standard network naming (I was using ezconnect).