RE: Oracle license requirements for unused options
Date: Tue, 25 Oct 2011 13:00:11 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA17B62B5551_at_JAXMSG01.crowley.com>
Hey everyone, check it out. It is possible. I know you've been trying and trying :) Works on 10g too.
A mere 150 lines give or take don't cha know.
You betha, yah.
Note: 1317265.1 hit link at bottom.
SET LINESIZE 180;
SET PAGESIZE 1000;
SET FEEDBACK OFF;
SET COLSEP '|';
WHENEVER SQLERROR EXIT SQL.SQLCODE;
COL "Host Name" FORMAT A30; COL "Option/Management Pack" FORMAT A60; COL "Used" FORMAT A5;
with features as(
select a OPTIONS, b NAME from
(
select 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual union all
select 'Advanced Compression', 'HeapCompression' from dual union allU:\Oracle\admin\Scripts\SEE\Auditing\Options_usage\option_usage.sql select 'Advanced Compression', 'Backup BZIP2 Compression' from dual union all
select 'Advanced Compression', 'Backup DEFAULT Compression' from dual union all
select 'Advanced Compression', 'Backup HIGH Compression' from dual union all
select 'Advanced Compression', 'Backup LOW Compression' from dual union all
select 'Advanced Compression', 'Backup MEDIUM Compression' from dual union all
select 'Advanced Compression', 'Backup ZLIB, Compression' from dual union all
select 'Advanced Compression', 'SecureFile Compression (user)' from dual union all
select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual union all
select 'Advanced Compression', 'Data Guard' from dual union all
select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual union all
select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual union all
select 'Advanced Security', 'ASO native encryption and checksumming' from dual union all
select 'Advanced Security', 'Transparent Data Encryption' from dual union all
select 'Advanced Security', 'Encrypted Tablespaces' from dual union all
select 'Advanced Security', 'Backup Encryption' from dual union all
select 'Advanced Security', 'SecureFile Encryption (user)' from dual union all
select 'Change Management Pack', 'Change Management Pack (GC)' from dual union all
select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual union all
select 'Data Mining', 'Data Mining' from dual union all
select 'Diagnostic Pack', 'Diagnostic Pack' from dual union all
select 'Diagnostic Pack', 'ADDM' from dual union all
select 'Diagnostic Pack', 'AWR Baseline' from dual union all
select 'Diagnostic Pack', 'AWR Baseline Template' from dual union all
select 'Diagnostic Pack', 'AWR Report' from dual union all
select 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual union all
select 'Diagnostic Pack', 'Baseline Static Computations' from dual union all
select 'Tuning Pack', 'Tuning Pack' from dual union all
select 'Tuning Pack', 'Real-Time SQL Monitoring' from dual union all
select 'Tuning Pack', 'SQL Tuning Advisor' from dual union all
select 'Tuning Pack', 'SQL Access Advisor' from dual union all
select 'Tuning Pack', 'SQL Profile' from dual union all
select 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual union all
select 'Database Vault', 'Oracle Database Vault' from dual union all
select 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual union all
select 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual union all
select 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual union all
select 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual union all
select 'Exadata', 'Exadata' from dual
union all
select 'Label Security', 'Label Security' from dual union all
select 'OLAP', 'OLAP - Analytic Workspaces' from duaU:\Oracle\admin\Scripts\SEE\Auditing\Options_usage\option_usage.sqll union all
select 'Partitioning', 'Partitioning (user)' from dual union all
select 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual union all
select 'Real Application Testing', 'Database Replay: Workload Capture' from dual union all
select 'Real Application Testing', 'Database Replay: Workload Replay' from dual union all
select 'Real Application Testing', 'SQL Performance Analyzer' from dual union all
select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual union all
select 'Total Recall', 'Flashback Data Archive' from dual )
)
select t.o "Option/Management Pack",
t.u "Used",
d.DBID "DBID",
d.name "DB Name",
i.version "DB Version",
i.host_name "Host Name",
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time"
from
(select OPTIONS o, DECODE(sum(num),0,'NO','YES') u
from
(
select f.OPTIONS OPTIONS, case
when f_stat.name is null then 0
when ( ( f_stat.currently_used = 'TRUE' and
f_stat.detected_usages > 0 and
(sysdate - f_stat.last_usage_date) < 366 and
f_stat.total_samples > 0
)
or
(f_stat.detected_usages > 0 and
(sysdate - f_stat.last_usage_date) < 366 and
f_stat.total_samples > 0)
) and
( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')
or
(f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and
f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0')
)
then 1
else 0
end num
from features f,
sys.dba_feature_usage_statistics f_stat
where f.name = f_stat.name(+)
) group by options) t,
v$instance i,
v$database d
order by 2 desc,1
;
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: Patterson, Joel
Sent: Tuesday, October 18, 2011 10:29 AM
To: 'Jay.Miller_at_tdameritrade.com'; oracle-l_at_freelists.org
Subject: RE: Oracle license requirements for unused options
This could possibly help... It's as close as I have come. You can put whatever NAME you wish from the dba_feature_usage_statistics view. I just added 'Diagnostic Pack' for you.
Let me know if you get something better. I'm still looking for Gartners warning about 'Total Recall','Advanced Compression','Active Data Guard' and 'Real Application Testing' being separately chargeable which I do not see listed in this view. (Gartner Publication Date: 6 February 2009, ID Number: G00164790). (btw someone just handed me the article... so I guess they are about three years late... anyway, I'm looking for a definitive way :).
So if anyone has info on these other four...
SELECT a.*
FROM (
SELECT dfs.*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY version
DESC) rno
FROM DBA_FEATURE_USAGE_STATISTICS dfs
WHERE detected_usages > 0
AND name IN
(
'Advanced Security',
'Automatic Database Diagnostic Monitor',
'Data Mining',
'Diagnostic Pack',
'Label Security',
'Partitioning (user)',
'RMAN - Tape Backup',
'Real Application Clusters (RAC)',
'SQL Access Advisor',
'SQL Tuning Advisor',
'SQL Tuning Set',
'Spatial',
'Transparent Gateway'
)
) a
WHERE a.rno = 1
;
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com
Sent: Tuesday, October 18, 2011 10:16 AM
To: oracle-l_at_freelists.org
Subject: RE: Oracle license requirements for unused options
Revisiting this thread from back in May... Does anyone know of a method in 10g to check whether a Diagnostic Pack option was ever used? I'm concerned that someone might at some point have run an awrrpt on a dev database without realizing that required a license and want to check whether it has ever happened.
For 11g Oracle provides scripts to check this (option_usages.sql) but they don't seem to work in 10g.
Appreciate any help in advance.
BTW - people might find this of interest: http://searchoracle.techtarget.com/news/2240035131/Common-traps-in-Oracle-licensing-audits I have verified that when we uninstall certain options and then patch the database the options come back.
Thanks,
Jay Miller
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com
Sent: Thursday, May 19, 2011 3:53 PM
To: oracle-l_at_freelists.org
Subject: RE: Oracle license requirements for unused options
Also, even if you didn't install an option as a base install applying the 10.2.0.4 patch will install some options automatically.
The impression I'm getting is that whether Oracle charges for installed and unused options depends on whether you're on good terms with them or not.
Jay Miller
Sr. Oracle Database Administrator
201.369.8355
From: Joel.Patterson_at_crowley.com [mailto:Joel.Patterson_at_crowley.com]
Sent: Thursday, May 19, 2011 9:46 AM
To: Brandon.Allen_at_OneNeck.com; norman.dunbar.capgemini_at_environment-agency.gov.uk; Miller, Jay; oracle-l_at_freelists.org
Subject: RE: Oracle license requirements for unused options
I think Brandon has a point. The management packs are all installed, and you must constantly go in and 'uncheck' them in EM. Oracle will install everything by default unless you can stop it, and for example 'partitioning' is used by EM, whether you use it or not.
So Spatial Data, or partitioning, OLAP, RAT, Data Mining... seem to go in easily enough, but have you tried to remove them (which includes checking that you are doing it correctly)?
Having said that, I removed OLAP which I believe was all I needed to have everything removed for 11g. But 10g can still display options from previous DBA as shown by Sqlplus when logging in, (so 'upgrading' would keep them).
Alas,
Joel Patterson
Database Administrator
904 727-2546
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Allen, Brandon Sent: Wednesday, May 18, 2011 1:47 PM
To: Dunbar, Norman (Capgemini); Jay.Miller_at_tdameritrade.com; oracle-l_at_freelists.org Subject: RE: Oracle license requirements for unused options
I'm still not convinced that a license is required for unused options. I think there is a key distinction between the documentation that mentions licensing based on usage and those that mention licensing based on installation. My interpretation is as follows:
- installation-based licensing - only pertains to the main database server software, e.g. "Oracle Database SE/EE" must be licensed for all processors on the host where it is installed
- usage-based licensing - only pertains to specific pieces of add-on functionality such as the extra options (e.g. Partitioning) and management packs (e.g. Diagnostics Pack).
I'm not positive if that's correct, so don't take my word for it, but everything I've read so far seems to be pretty consistent on those two points.
Regards,
Brandon
-----Original Message-----
From: Dunbar, Norman (Capgemini) [mailto:norman.dunbar.capgemini_at_environment-agency.gov.uk]
I'm in total agreement with you regarding the wording of the vast
majority of Oracle docs
<snip>
Hopefully though, the above should show that "installed and/or run" as
frequently quoted by Oracle in their documents, means installing an
option or feature does require a license.
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 25 2011 - 12:00:11 CDT