Site Logo

IzzySoft


Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Buy at Amazon for 16,99 €
Das inoffizielle Android-SystemhandbuchDas inoffizielle Android-Systemhandbuch
Buy at Amazon for 3,94 €
Die besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und StudiumDie besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und Studium
Buy at Amazon for 1,40 €
As of 2020-10-21 07:20
prices & availability might be subject to change.

Avoid unintentional use of unlicensed Options

Larry's Money Printer: Unlicensed Options unintentionally used

As you can read in The most common license compliance issues seen: Oracle's license metric definitions state that the Oracle software is required to be licensed once the software is “installed and/or running”. Now the first problem is: Using the Universal Installer, you do not even get the chance to unselect options you do not want to use, and never had any intention to. Still, they are installed. Using DBCA, by default most of them also end up directly in your databases. You'd think to receive a warning, or at least a hint how to disable unneeded features – but nada. Not even a warning on what to avoid.

Tapping into such a trap is as easy as to SELECT something from a table (like dba_hist_*, which requires the Diagnostics Pack license). Or performing a Switchover via Data Guard, which leaves your former Standby Database open in read-only mode with apply, and thus requires the Active Data Guard license. Each usage is logged. So comes the auditor, he brings the bills – and raises some fees back to the date you innocently tapped into that trap: about 10% of an Enterprise License for the Diagnostics Pack, about 25% for Active Data Guard, and so on.

How to avoid that? Turn those features off. If you can. For some it's quite easy – for others (like Active Data Guard) close to impossible. To list what components are installed, you can use opatch lsinventory -detail.

IMPORTANT: Whatever steps from this article you try, you do so on your own risk! I urge you to first verify it on an installation you can live with if it breaks, have good backups, and so on. As I haven't tried most of this stuff yet myself, I cannot tell what side-effects there might be (likely).

Contents

Find out what was used already

Set feedback off
Set linesize 122
Set pagesize 50
Set trimspool on
Col name             format a55     heading "Feature"
Col version          format a10     heading "Version"
Col detected_usages  format 999,990 heading "Detected|usages"
Col currently_used   format a06     heading "Curr.|used?"
Col first_usage_date format a10     heading "First use"
Col last_usage_date  format a10     heading "Last use"
Col nop noprint
Break on nop skip 1 on name
Select decode(detected_usages,0,2,1) nop,
       name, version, detected_usages, currently_used,
       to_char(first_usage_date,'DD/MM/YYYY') first_usage_date,
       to_char(last_usage_date,'DD/MM/YYYY') last_usage_date
from dba_feature_usage_statistics
order by nop, 1, 2;

Got something „tagged“? So you already fell into that trap. To get out of that, the best way is a clean reinstall of the database, and migrating your application data over.1

Options

SQL> select * from v$option;

This statement lists all options (column parameter) and whether it is available (column value).

Disabling options using the chopt utility

Few options can be disabled using the chopt („change option“) utility: Data Mining (dm), OLAP (olap), Partitioning (partitioning) and Real Application Testing (rat):

To know which ones are currently enabled (with 12c, after a fresh install: all of them), simply take a look at the banner when you start SQL*Plus:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 25 13:20:50 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

The last two lines before the SQL prompt („with the XXX options“) tell you. Might look a little different for you (if you e.g. didn't install ASM). In case the banner was disabled, also check with select * from v$option;.

NOTE: after applying a CPU/RU patch, you will have to repeat your chopt actions. At least with Oracle 12.2, patching seems to reactive disabled options.

Disable options using make and relinking

Several of these can be disabled by „patching them out“ of the Oracle libraries, see e.g. Enabling and Disabling Database Options. The Oracle documentation itself refers to the used command in different places, e.g. to temporarily disable database vault if you e.g. forgot the password. Unclear is whether you have to repeat this action after upgrading your $ORACLE_HOME (e.g. when applying the quarterly CPU/RU patches):

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off
cd $ORACLE_HOME/bin
relink all

Note above blog article (and others) also adds the ioracle parameter to the make call (make -f ins_rdbms.mk part_off ioracle)

When following this approach, make sure to

  1. shutdown all databases residing in this $ORACLE_HOME
  2. only after that, run the make and relink
  3. then startup your databases again
  4. check with select * from v$option whether your action was successful

According to above mentioned blog article, the following options can be dealt with this way:

Database Option ON OFF
Data Mining dm_on dm_off
Data Mining Scoring Engine dmse_on dmse_off
Database Vault dv_on dv_off
Label Security lbac_on lbac_off
Partitioning part_on part_off
Real Application Clusters rac_on rac_off
Spatial sdo_on sdo_off
Real Application Testing rat_on rat_off
OLAP olap_on olap_off
Automatic Storage Management asm_on asm_off
Context Management Text ctx_on ctx_off

So if you e.g. want to disable Database Vault, OLAP, RAT, RAC and Spatial, you'd run

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off olap_off rat_off rac_off sdo_off ioracle
cd $ORACLE_HOME/bin
./relink all

To find out what might be active from these, you can check with v$option (is it installed? true/false), dba_registry (version, status) and dba_feature_usage_statistics (did you already use it). And apart from the options themselves (which are part from the „binary installation“), there might be database objects involved you installed via SQL scripts e.g. on database creation (DBCA loves to do that). A PDF from DOAG, though only reaching up to 11g, might give you some clues on this. It also extensively links to MOS, where again you might find hints to updates.

NOTE: after applying a CPU/RU patch, you will have to repeat these steps. At least with Oracle 12.2, patching seems to reactive disabled options.

Disabling Diagnostics and Tuning Packs

Another tricky pack is Oracle Diagnostics – very easily triggered by accident (like a SELECT on the wrong view, creating an AWR report, using ADDM, or even ADR for trace/alert log files2). But also the easiest to avoid:

SQL> ALTER SYSTEM SET control_management_pack_access = "NONE";

Which will disable both, Diagnostics and Tuning packs. If you only want to get rid of one but keep the other, specify the one you want to keep instead of the NONE. Valid options are NONE, DIAGNOSTIC, DIAGNOSTIC+TUNING (looks like Tuning can only be enabled together with Diagnostic).

That's all – now all those performance views will be „empty“ (as will be your AWR or ASH reports, which need this option). But you will find that the relevant jobs are still active:

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                       OPERATION_NAME                STATUS
--------------------------------- ----------------------------- --------
auto optimizer stats collection   auto optimizer stats job      ENABLED
auto space advisor                auto space advisor job        ENABLED
sql tuning advisor                automatic sql tuning task     ENABLED

Justified question: what for? And what against:

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);

  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);

END;
/

No need to collect what you can't use anyway. And while on it, make sure DDL logging is off (it usually is – but if not, it requires the Database Lifecycle Management Pack to be bought):

SQL> show parameter ddl_logging

NAME                 TYPE       VALUE
-------------------- ---------- -----
enable_ddl_logging   boolean    TRUE

SQL> alter system set enable_ddl_logging = FALSE;

Components inside the database

SQL> set lines 250 pages 100
SQL> col comp_name for a100
SQL> select comp_name,version from dba_registry order by 1;

Complemented by the license requirement (no guarantees on that, as Oracle might change it anytime), the schema a component's objects reside in (if known), and the COMP_ID it is listed with in the inventory:

COMP_ID COMP_NAME SCHEMA License
APEX Oracle Application Express APEX_050000 free
CONTEXT Oracle Text CTXSYS included (?)
OLS Oracle Label Security LBACSYS extra
ORDIM Oracle Multimedia ORDSYS ?
OWM Oracle Workspace Manager WMSYS included
SDO Spatial MDSYS extra
XDB Oracle XML Database XDB included
XOQ Oracle OLAP API OLAPSYS extra
? Advanced Compression extra
? Active Data Guard extra
? Enterprise User Security extra (Oracle Directory Services)
? Oracle Diagnostics extra
? Oracle Partitioning extra
? Oracle RAC extra
? Oracle Real Application Testing extra
? Oracle Tuning Pack extra
? DICOM

Uninstalling components

Note that I have not tried any of these approaches myself, so this is just a collection of my findings so far.

For several components, you can find verbose instructions in the blog series Remove and Clean Up Components from Oracle Database 11.2 – 12.2. This covers mostly options you can find by running select * from dba_registry.

For others, Silent Deinstallation describes how to uninstall unwanted components using the Oracle Universal Installer in „silent mode“. The basic syntax is

./runinstaller -deinstall -silent DEINSTALL_LIST={"<component_name>","<version>"}

Available components can e.g. be found in the unpacked installer files: <unpacked_database_installer_zip>/database/stage/products.xml, and an example entry looks like

<COMP NAME="oracle.dbdev" VER="12.2.0.1.0" ...
   <EXT_NAME>Oracle SQL Developer</EXT_NAME>

Active Data Guard

This is the biggest audacity of all: if you have a simple stand-alone database with a simple stand-alone standby attached and want to use Data Guard properly without the features of the Active Data Guard licence, you simply can't – as soon as you perform a switch-over via dgmgrl, the trap springs: instead of closing the previous primary database and bring it into MOUNT status before making it the standby, dgmgrl only switches roles (primary/standby) – not the states (open/mount) as it was done up to Oracle 10g. And it gets worse: once that happens, you cannot simply revert it and be done – apart from the use being registered, whenever you open your standby in read-only mode even without apply you're in active data guard mode again, as the primary will register this and mark it for use of active block recovery, which again requires the license.

Many DBAs complained, tons of blogs were written on this topic. But the only working fix will cost you your support, as DocId 2269239.1 points out:

There is no way to disable ADG, just prevent its usage by ensuring the physical standby database is always mounted when Media Recovery (MRP) runs.
NOTE: Hidden parameter "_query_on_physical" is NOT an option to prevent Active Data Guard usage. It should NOT be used at all in any version of the Oracle Database. It is unsupported to be set unless Oracle Support advises it for diagnostic reasons.

There were several approaches tried (this article lists some of them):

So you'd have to either establish a grid environment, or install Oracle Clusterware – both adding complexity, maintenance overhead and functionality you don't need. And because it's so clear that you cannot opt out easily, the license fee is defty: about 25% of what an Oracle Enterprise Edition already costs you. Or you cannot do a simple dgmgrl switchover which does not require Active Data Guard – because that would require Active Data Guard when finished.

What are you left here with then? Using dgmgrl for FSFO only – and either perform switchover manually (also here; you'd need to to reverse roles after a failover3 – plus fix the dgmgrl setup4) or not at all, having downtimes instead, potentially risking your SLAs and uptime guarantees you might have given.


  1. for better results, find some helpers linked from this article ↩︎

  2. Note that using Tuning Pack (which is a separate license) for SQL tuning etc also implies an Oracle Diagnostic license. ↩︎

  3. if you had Flashback Database enabled, you can convert the broken primary into a standby – but you'd still need to reverse roles ↩︎

  4. easiest way for that is probably dropping and recreating its configuration ↩︎

2020-05-18