Like most of Oracle’s products, Enterprise Manager comes with options that are enabled by default, but licensed when used. In order to prevent these options from being used and to avoid having to pay the corresponding license fee, your company may ask you to list the status of these options on a regular basis.
On Oracle Enterprise Manager, these options are called “Management Packs” :
- Oracle Cloud Management Pack for Oracle Database
- Oracle Cloud Management Pack for Oracle Fusion Middleware
- Application Replay Pack
- Management Pack for Oracle Coherence
- Database Diagnostics Pack
- Oracle Database Lifecycle Management Pack
- Oracle Data Masking and Subsetting Pack
- Database Tuning Pack
- Configuration Management Pack for Applications
- WLS Management Pack EE
Recently, my client asked me to automate the collection of the status of these management packs. The information is easily retrievable from the Enterprise Manager console (Setup=>Management Packs=>Management Pack Access), but there is no possibility to export the list to Microsoft Excel for example, which is not practical to include in a reporting.
After some research, I developed a query that extracts this information from the OMR database in CSV format.
First of all: the query is not very “pretty”, but the way the Management Pack information is stored in the OMR database does not allow to do better (if you have a better solution, I am interested 🙂 )
In order to execute the query, you need to connect to the OMR database with the SYSMAN user, or another user that has access to the objects in this schema. Then you just have to execute the following query (it’s better to copy it in a sql file and then execute the file from sqlplus in order to get a correct formatting) :
set head off
set markup csv on
select 'Target', 'Type','Hostname','Management Pack for Oracle Coherence','Oracle Database Lifecycle Management Pack','Database Diagnostics Pack','Database Tuning Pack','WLS Management Pack EE','Oracle Cloud Management Pack for Oracle Database','Application Replay Pack','Oracle Data Masking and Subsetting Pack','Configuration Management Pack for Applications','Oracle Cloud Management Pack for Oracle Fusion Middleware' from dual;
select
targets.target_name,
targets_type.type_display_name,
targets.host_name,
nvl2(to_char(coherence_mgmt_usage.coherence_mgmt_usage),'E', nvl2(to_char(coherence_mgmt_qualify.coherence_mgmt_qualify),'D','NA')) coherence_mgmt,
nvl2(to_char(db_lifecycle_mgmt_usage.db_lifecycle_mgmt_usage),'E', nvl2(to_char(db_lifecycle_mgmt_qualify.db_lifecycle_mgmt_qualify),'D','NA')) db_lifecycle_mgmt,
nvl2(to_char(db_diag_usage.db_diag_usage),'E', nvl2(to_char(db_diag_qualify.db_diag_qualify),'D','NA')) db_diag,
nvl2(to_char(db_tuning_usage.db_tuning_usage),'E', nvl2(to_char(db_tuning_qualify.db_tuning_qualify),'D','NA')) db_tuning,
nvl2(to_char(wls_mgmt_usage.wls_mgmt_usage),'E', nvl2(to_char(wls_mgmt_qualify.wls_mgmt_qualify),'D','NA')) wls_mgmt,
nvl2(to_char(cloud_db_mgmt_pack_usage.cloud_db_mgmt_pack_usage),'E', nvl2(to_char(cloud_db_mgmt_pack_qualify.cloud_db_mgmt_pack_qualify),'D','NA')) cloud_db_mgmt_pack,
nvl2(to_char(asreplay_usage.asreplay_usage),'E', nvl2(to_char(asreplay_qualify.asreplay_qualify),'D','NA')) asreplay,
nvl2(to_char(db_mas_usage.db_mas_usage),'E', nvl2(to_char(db_mas_qualify.db_mas_qualify),'D','NA')) db_mas,
nvl2(to_char(nos_config_usage.nos_config_usage),'E', nvl2(to_char(nos_config_qualify.nos_config_qualify),'D','NA')) nos_config,
nvl2(to_char(cloud_mw_mgmt_pack_usage.cloud_mw_mgmt_pack_usage),'E', nvl2(to_char(cloud_mw_mgmt_pack_qualify.cloud_mw_mgmt_pack_qualify),'D','NA')) cloud_mw_mgmt_pack
from
(select target_name, count(*) coherence_mgmt_usage from mgmt_license_view where pack_name='coherence_mgmt' group by target_name) coherence_mgmt_usage,
(select target_type, count(*) coherence_mgmt_qualify from mgmt_license_definitions where pack_label='coherence_mgmt' group by target_type) coherence_mgmt_qualify,
(select target_name, count(*) db_lifecycle_mgmt_usage from mgmt_license_view where pack_name='db_lifecycle_mgmt' group by target_name) db_lifecycle_mgmt_usage,
(select target_type, count(*) db_lifecycle_mgmt_qualify from mgmt_license_definitions where pack_label='db_lifecycle_mgmt' group by target_type) db_lifecycle_mgmt_qualify,
(select target_name, count(*) db_diag_usage from mgmt_license_view where pack_name='db_diag' group by target_name) db_diag_usage,
(select target_type, count(*) db_diag_qualify from mgmt_license_definitions where pack_label='db_diag' group by target_type) db_diag_qualify,
(select target_name, count(*) db_tuning_usage from mgmt_license_view where pack_name='db_tuning' group by target_name) db_tuning_usage,
(select target_type, count(*) db_tuning_qualify from mgmt_license_definitions where pack_label='db_tuning' group by target_type) db_tuning_qualify,
(select target_name, count(*) wls_mgmt_usage from mgmt_license_view where pack_name='wls_mgmt' group by target_name) wls_mgmt_usage,
(select target_type, count(*) wls_mgmt_qualify from mgmt_license_definitions where pack_label='wls_mgmt' group by target_type) wls_mgmt_qualify,
(select target_name, count(*) cloud_db_mgmt_pack_usage from mgmt_license_view where pack_name='CLOUD_DB_MGMT_PACK' group by target_name) cloud_db_mgmt_pack_usage,
(select target_type, count(*) cloud_db_mgmt_pack_qualify from mgmt_license_definitions where pack_label='CLOUD_DB_MGMT_PACK' group by target_type) cloud_db_mgmt_pack_qualify,
(select target_name, count(*) asreplay_usage from mgmt_license_view where pack_name='asreplay' group by target_name) asreplay_usage,
(select target_type, count(*) asreplay_qualify from mgmt_license_definitions where pack_label='asreplay' group by target_type) asreplay_qualify,
(select target_name, count(*) db_mas_usage from mgmt_license_view where pack_name='db_mas' group by target_name) db_mas_usage,
(select target_type, count(*) db_mas_qualify from mgmt_license_definitions where pack_label='db_mas' group by target_type) db_mas_qualify,
(select target_name, count(*) nos_config_usage from mgmt_license_view where pack_name='nos_config' group by target_name) nos_config_usage,
(select target_type, count(*) nos_config_qualify from mgmt_license_definitions where pack_label='nos_config' group by target_type) nos_config_qualify,
(select target_name, count(*) cloud_mw_mgmt_pack_usage from mgmt_license_view where pack_name='CLOUD_MW_MGMT_PACK' group by target_name) cloud_mw_mgmt_pack_usage,
(select target_type, count(*) cloud_mw_mgmt_pack_qualify from mgmt_license_definitions where pack_label='CLOUD_MW_MGMT_PACK' group by target_type) cloud_mw_mgmt_pack_qualify,
mgmt$target targets,
mgmt$target_type_def targets_type
where
targets.target_type = targets_type.target_type
and
targets.target_name=coherence_mgmt_usage.target_name(+) and targets.target_type=coherence_mgmt_qualify.target_type(+)
and
targets.target_name=db_lifecycle_mgmt_usage.target_name(+) and targets.target_type=db_lifecycle_mgmt_qualify.target_type(+)
and
targets.target_name=db_diag_usage.target_name(+) and targets.target_type=db_diag_qualify.target_type(+)
and
targets.target_name=db_tuning_usage.target_name(+) and targets.target_type=db_tuning_qualify.target_type(+)
and
targets.target_name=wls_mgmt_usage.target_name(+) and targets.target_type=wls_mgmt_qualify.target_type(+)
and
targets.target_name=cloud_db_mgmt_pack_usage.target_name(+) and targets.target_type=cloud_db_mgmt_pack_qualify.target_type(+)
and
targets.target_name=asreplay_usage.target_name(+) and targets.target_type=asreplay_qualify.target_type(+)
and
targets.target_name=db_mas_usage.target_name(+) and targets.target_type=db_mas_qualify.target_type(+)
and
targets.target_name=nos_config_usage.target_name(+) and targets.target_type=nos_config_qualify.target_type(+)
and
targets.target_name=cloud_mw_mgmt_pack_usage.target_name(+) and targets.target_type=cloud_mw_mgmt_pack_qualify.target_type(+)
and
targets.target_type in(select distinct target_type from mgmt_license_definitions where target_type <> 'weblogic_j2eeserver')
order by 1;
The first “select” statement is only used to generate the column headers. The second “select” will retrieve the list of targets present on your Enterprise Manager repository, and for each of them, check which Management Pack is activated. You will get: “E” if the management pack is enabled, “D” if it is disabled, or “NA” if not applicable for the target type.
Note: the query will not tell you if the Management Pack has been used or not! I could not find this information in Enterprise Manager.
Fortunately, if you want to disable these management packs, the operation is easily done from the console using the “Pack Based Batch Update” option:
You can also use the “Auto Licensing” option in order to automatically disable the management packs for targets you will later add to Enterprise Manager.
I hope you find this post useful. Stay tuned for more DBA stuff!
