RE: Oracle license requirements for unused options

Yes, I fat fingered something do I'm resending.   This one should be copy and 
pastable, however for formatting you can always go to the Metalink note 
1317265.1  hit link at bottom.   New to this version of word so not sure why 
all formatting is stripped.

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 all
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 dual
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 25, 2011 1:00 PM
To: Patterson, Joel; Jay.Miller@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Cc: Jay.Miller@xxxxxxxxxxxxxxxx
Subject: RE: Oracle license requirements for unused options
Importance: High

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@xxxxxxxxxxxxxxxx'; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Tuesday, October 18, 2011 10:16 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Thursday, May 19, 2011 3:53 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx [mailto:Joel.Patterson@xxxxxxxxxxx]
Sent: Thursday, May 19, 2011 9:46 AM
To: Brandon.Allen@xxxxxxxxxxx; 
norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx; Miller, Jay; 
oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Allen, Brandon
Sent: Wednesday, May 18, 2011 1:47 PM
To: Dunbar, Norman (Capgemini); Jay.Miller@xxxxxxxxxxxxxxxx; 
oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx]

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-l

Other related posts: