This is part of DBA role: know and prospect the use of Oracle Products for Oracle contract periodical review, isn’t?
It usually represent a huge problem, or, at least, demands a long time to refresh your spread sheet…
Well, If you use OEM, we offer you a better option! 😀
(I said ‘we’, because Dieison Santos came to me with this problem theese days…. So we talked about, I gave some directives and he mainly solved the problem. This way, great part of ‘we’ should be ‘he’… haha)
Without further, here’s a query that can map your environment (at least your Oracle database products):
You can use it to automate a report and set thresholds. Be creative… 😉
PS: From now, I’ll post all in english. Just for fun.
select
distinct(ddi.host_name) "Host",
(case
when opt.name like '%Active Data Guard%' then 'Oracle Active Data Guard'
when opt.name like '%Advanced Compression%' then 'Oracle Advanced Compression'
when opt.name like '%Audit Vault%' then 'Oracle Audit Vault'
when opt.name like '%Database Vault%' then 'Oracle Database Vault'
when opt.name like '%Partitioning (User)%' then 'Oracle Partitioning'
when opt.name like '%Real Application Clusters%' then 'Oracle Real Application Clusters'
when opt.name like '%Real Application Testing%' then 'Oracle Real Application Testing'
when (opt.name like '%ADDM%' or
opt.name like '%Automatic Database Diagnostic Monitor%' or
opt.name like '%Automatic Workload Repository%' or
opt.name like '%AWR%' or
opt.name like '%Baseline%' or
opt.name like '%Diagnostic Pack%' ) then 'Oracle Diagnostic Pack'
when (opt.name like '%SQL Monitoring%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Profile%' or
opt.name like '%SQL Tuning%' or
opt.name like '%SQL Access Advisor%' or
opt.name like '%Tuning Pack%') then 'Oracle Tuning Pack'
when opt.name like '%Change Management Pack%' then 'Oracle Change Management Pack'
when ddi.edition like 'Enterprise Edition' then 'Oracle Database Enterprise Edition'
else opt.name
end) "Produto Oracle",
hcd.num_cores "Cores",
ohs.virtual "Virtual",
hcd.impl "Processador",
ddi.dbversion "Versao"
from
mgmt$hw_cpu_details hcd,
mgmt$os_hw_summary ohs,
mgmt$db_dbninstanceinfo ddi,
(select
h.host_name as host,
h.target_name as database_name,
i.instance_name as instance_name,
h.target_type as target_type,
h.target_guid as target_guid,
f.DBID,
f.NAME,
f.CURRENTLY_USED,
f.DETECTED_USAGES,
f.FIRST_USAGE_DATE,
f.LAST_USAGE_DATE,
f.VERSION,
f.LAST_SAMPLE_DATE,
f.LAST_SAMPLE_PERIOD,
f.TOTAL_SAMPLES,
f.AUX_COUNT,
f.DESCRIPTION
from
mgmt_db_featureusage f,
mgmt_targets h,
mgmt_db_dbninstanceinfo_ecm i,
gc$ecm_gen_snapshot s
where
s.is_current = 'Y' and
s.snapshot_guid = i.ecm_snapshot_id and
s.target_guid = f.target_guid and
h.target_type in ('oracle_database','rac_database') and
s.target_type = h.target_type and
s.snapshot_type in ('oracle_dbconfig','oracle_racconfig') and
f.DETECTED_USAGES>0
) opt
where
hcd.target_guid=ohs.target_guid
and ohs.host_name=ddi.host_name
and ddi.target_guid=opt.target_guid
and ( opt.name like '%Active Data Guard%' -- Active Data Guard
or opt.name like '%Advanced Compression%' -- Advanced Compression
or opt.name like '%Audit Vault%' -- Audit Vault
or opt.name like '%Database Vault%' -- DB Vault
or opt.name like '%Partitioning (user)%' -- Partitioning
or opt.name like '%Real Application Clusters%' --RAC
or opt.name like '%Real Application Testing%' -- RAT
or opt.name like '%ADDM%' -- Diagnostic Pack
or opt.name like '%Automatic Database Diagnostic Monitor%' -- Diagnostic Pack
or opt.name like '%Automatic Workload Repository%' -- Diagnostic Pack
or opt.name like '%AWR%' -- Diagnostic Pack
or opt.name like '%Baseline%' -- Diagnostic Pack
or opt.name like '%Diagnostic Pack%' -- Diagnostic Pack
or opt.name like '%SQL Monitoring%' -- Tuning Pack
or opt.name like '%SQL Performance%' -- Tuning Pack
or opt.name like '%SQL Profile%' -- Tuning Pack
or opt.name like '%SQL Tuning%' -- Tuning Pack
or opt.name like '%SQL Access%' -- Tuning Pack
or opt.name like '%Tuning Pack%' -- Tuning Pack
or opt.name like '%Change Management Pack%' -- Change Management Pack
or ddi.edition like 'Enterprise Edition')
order by ddi.host_name;
Matheus.
Innovative in everything he does, Cassiano acts as Oracle DBA since 2007 and works everyday with APEX since 2009. Cassiano is a great teammate and is always willing to help someone and learn new things.
https://br.linkedin.com/in/cassianoroloff
cassianoroloff@gmail.co
Focused on Fusion Middleware and Application technologies, Jack is friend of everyone and always available to reach out the others. Jack has good architecture comprehension and skills to manage solutions on different technologies.
Passionate Database Administrator, experienced as consultant on heterogeneous environments and always trying new ways to do everything. Great as teammate, Rafael is always available to discuss new ideas and innovation.
Professional in the IT area since 2006, working with Oracle applications and database. Solid experience in installation, customization and performance tuning of Fusion Middleware products.
Detail-oriented, Maiquel has almost a scientific approach in all his duties. Maiquel is the GoldenGate guy, acting focused on Integration Solutions. Maiquel also keep studying and feel comfortable about databases.