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
- Options
- Components inside the database
- Active Data Guard
- Further Readings
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):
- shutdown DB
- run
chopt disable <dm|olap|partitioning|rat>
- startup DB and check results
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
- shutdown all databases residing in this
$ORACLE_HOME
- only after that, run the
make
andrelink
- then startup your databases again
- 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):
- a startup trigger, using the undocumented
ALTER DATABASE CLOSE
command when detecting being run on a standby
works sometimes, but is not reliable; often complains it cannot close the database as there are already processes running („CLOSE: Active sessions prevent database close operation; Error 1093 during database close“), at least on 12.1 - a startup trigger, running
alter system set dg_broker_start=FALSE scope=memory
when on a standby
defeats the purpose of Data Guard: if the broker is not running, you cannot usedgmgrl
to issue a simple switchover – not to speak of FSFO ALTER SYSTEM SET _query_on_physical = FALSE
works relyable (on 12c stand-alone; reported to have issues on Oracle 18 PDB) – but as described above, you cannot use it without losing support
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.
Further Readings
- Licensing Quick Guide
- The most common license compliance issues seen: the license metric definitions state that the Oracle software is required to be licensed once the software is “installed and/or running”
- Uninstalling Oracle Database Products: Which & How to? (unanswered)
- Tracking Database Feature Usage (DBA_FEATURE_USAGE_STATISTICS)
- Preventing standby databases opening in Active DataGuard mode + chopt
- Management Packs, Restricted Use Licenses and the Enterprise ...
- Datenbank Lizenzen: Oracle Management Packs
- Oracle License Price Lists (PDFs are linked from there)
- Lizenzverstößen richtig vorbeugen (incl. links to helpful scripts)
- Database Licensing Information: Options and Packs (here from 11g; 12c here at a 3rd party and here dug out at the official site)
- Datenbank 12c: Neue Lizenzfallen
- Deinstallation von Oracle Datenbank Optionen (PDF; 11g)
- Enabling and Disabling Oracle Database Options and Management Packs (Oracle 11.2 - 18)
-
for better results, find some helpers linked from this article ↩︎
-
Note that using Tuning Pack (which is a separate license) for SQL tuning etc also implies an Oracle Diagnostic license. ↩︎
-
if you had Flashback Database enabled, you can convert the broken primary into a standby – but you'd still need to reverse roles ↩︎
-
easiest way for that is probably dropping and recreating its configuration ↩︎