Now i'm on dodgy ground talking about this topic and i'm caveating this right at the start of this post. Do not trust ANY information found on random blog sites to do with licencing of Oracle databases - the only source you can trust is the Oracle licence team themselves.
This post is about a short report i created from the cloud control OMS repository to give some information about the licence usage across all the databases that it knows about. This is somewhat of a shortcut to get a lot of the information Oracle may require as part of an audit but it will still require some manual work to collect everything.
The query at the end of the post will generate that report but i want to lay out some further caveats about the content of the report before i share that - so read this bit carefully....
1) The report relies on information gathered from mgmt$db_featureusage - this is in turn collecting information from the DBA_FEATURE_USAGE_STATISTICS view. It therefore has all the restrictions/mistakes that this has
2) Partitioning does seem to be reported correctly as far as i can tell
3) For ASO (advanced security option) i know we use TDE and securefile encryption - both of these seem to report OK - however there are many other usages of this option that may or may not show up
4) For ACO (Advanced compression) i know we are only using securefile compression and deduplication - I am not checking for 'OLTP' style compression (which doesnt seem to be recorded)
5) For diag/tuning (i report as DIAG) - i just list everything as used if the db is greater than 9i as there seems to be no effective way to report if it's used or not (for example selecting from a view can count as usage....)
6) any other options are not in scope for this report (though could be added with a little work) RAC etc
7) The cpu counts shown may not reflect the actual cpu count relevant for licencing - i.e. multipliers based on cpu type etc are ignored
So with all those caveats out of the way here is the SQL (i run it as sysman but any user with rights should be able to use it)
with db_size as
(select target_guid, sum(file_size) as dbsz
from MGMT$DB_DATAFILES t
group by target_guid)
select d.target_name,
d.host_name,
d.dbversion,
d.is_64bit,
(case
when (select count(*)
from MGMT$DB_FEATUREUSAGE M
where name = 'Partitioning (user)'
and currently_used = 'TRUE'
and M.database_name = d.target_name) > 0 THEN
'TRUE'
ELSE
'FALSE'
END) Partitioning,
(case
when (select count(*)
from MGMT$DB_FEATUREUSAGE M
where name in ('Transparent Data Encryption',
'SecureFile Encryption (user)')
and currently_used = 'TRUE'
and M.database_name = d.target_name) > 0 THEN
'TRUE'
ELSE
'FALSE'
END) ASO,
(case
when (select count(*)
from MGMT$DB_FEATUREUSAGE M
where name in ('SecureFile Deduplication (user)',
'SecureFile Compression (user)')
and currently_used = 'TRUE'
and M.database_name = d.target_name) > 0 THEN
'TRUE'
ELSE
'FALSE'
END) ACO,
(case
when (dbversion) not like '9.2%' THEN
'TRUE'
ELSE
'FALSE'
END) DIAG,
db_size.dbsz / (1024 * 1024 * 1024) as "Database Size",
os.os_summary,
os.freq,
os.mem,
os.disk,
os.cpu_count
from MGMT$DB_DBNINSTANCEINFO d, MGMT$OS_HW_SUMMARY os, db_size
where os.host_name = d.host_name
and db_size.target_guid = d.target_guid
This produces an output in the following format
I blanked out the db name and hostname and didnt show all the columns off to the right - but you get the idea.
For our few hundred instances (a medium sized repo according to the new summary screen in cloud control) this report ran in under 1 second......
So in summary don't trust what I've written here but maybe use it as a jump start in collecting the information
And finally again - don't trust what I've written here ( i think i caveated that enough now?)
Excellent report!
ReplyDelete