How to list all my Oracle Products from Application park?

YES!
I knew you would like the last post!

So, remains a doubt. What about my Oracle Application park?
Be soft. I’m glad to help. At real, Dieison Santos and me. As I said in the last post, it was his problem theese days… 😛

Here is a query to list your Oracle Application Products (including Oracle SOA Suite, of course) from OEM.

Use wisely:

select distinct * from (
select (LBL_HOSTNAME) "Host",
(CASE
when LBL_PRODUCTNAME like 'WebLogic Server' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%WebTier and Utilities%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%EM Platform (OMS)%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%Web Services Manager%' then 'Diagnostics Pack for Internet Application Server'
when LBL_PRODUCTNAME like '%Application Server 10g%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%' then 'Oracle Enterprise Single Sign-On Suite'
when LBL_PRODUCTNAME like '%Business Intelligence%' then 'Oracle Business Intelligence Suite Enterprise Edition Plus'
when LBL_PRODUCTNAME like '%Oracle SOA Suite%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%Oracle BAM%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g%' then 'Oracle WebCenter Portal'
when LBL_PRODUCTNAME like '%Oracle Business Process Management%' then 'Unified Business Process Management Suite'
when LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%' then 'Oracle WebCenter Content'
when LBL_PRODUCTNAME like '%Oracle Application Server Guard%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Configuration%' then 'Configuration Management Pack for Internet Application Server'
else
LBL_PRODUCTNAME
end)
"Produto",
LBL_BASEVERSION "Versao",
LBL_PROCESSOR "Processador",
lbl_virtual "VIrtual",
DECODE(LBL_CPUS,null,1,LBL_CPUS) "CPUS"
from (SELECT M.EXTERNAL_NAME LBL_PRODUCTNAME,
M.NAME LBL_COMPONENTNAME,
M.BASE_VERSION LBL_BASEVERSION,
M.HOST_NAME LBL_HOSTNAME,
p.virtual lbl_Virtual,
p.system_config || nvl2(p.freq, p.freq || ' MHz FSB ', '') LBL_PROCESSOR,
p.cpu_count LBL_CPUS
FROM (MGMT$SOFTWARE_COMPONENTS M INNER JOIN mgmt$os_hw_summary p ON
M.HOST_NAME = P.HOST_NAME))
where   (
LBL_PRODUCTNAME like 'WebLogic Server'
or LBL_PRODUCTNAME like '%WebTier and Utilities%'
or LBL_PRODUCTNAME like '%EM Platform (OMS)%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
or LBL_PRODUCTNAME like '%Application Server 10g%'
or LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%'
or LBL_PRODUCTNAME like '%Business Intelligence%'
or LBL_PRODUCTNAME like '%Oracle SOA Suite%'
or LBL_PRODUCTNAME like '%Oracle BAM%'
or LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g'
or LBL_PRODUCTNAME like '%Oracle Business Process Management%'
or LBL_PRODUCTNAME like '%Application Server Configuration%'
or LBL_PRODUCTNAME like '%Oracle Application Server Guard%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
) order by "Produto");

Matheus.

Advertisements

How to list all my Oracle Products from Database park?

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.