Still loving the idea to use Oracle Enterprise Manager as a ‘kind of ‘ Software Asset Management Tool, and get regular centralized reports that tells me what Oracle software is running and if there are changes in use of licenses over time. The necessary data is already there in the OEM repository, so there should be standard report facilities to get me informed (other then my own sql-reports from the repository). And guess what, these reports are available.

The Usage Tracking Reports, including the two reports which are important for monitoring the use of licensing, were already present in OEM 12C (wrote about it a while ago) , and I was curious if the usability and functionality improved in OEM 13C (as told me at the demogrounds of OOW2015..).

To start with the usability: the integration of BI Publisher in OEM 13c seems a bit tighter and more stable, and I think most people quickly find their way in the new layout. But you still need to perform a few steps before getting some information in the reports. And the big question: is it worth the effort, am I able to monitor the use of licenses and other Oracle assets in a decent way?

First I will describe the steps to get some tables populated, then a few reports will be run with special interest in the Database Usage Tracking Summary Report as this report should give me an insight of the use of options in my databases.

The scope: when choosing Enterprise –> Reports –>  BI Publisher reports, it’s about the following reports:

It’s not possible to run the first report ‘Database Usage Tracking Report’ interactively. Don’t need to by the way, it’s actually a report for Oracle LMS when they might ask you to deliver some data.

Steps to be taken for getting some results:

– Enable the metric ‘Feature Usage’ ( Doc ID 1970236.1).

– Setting up Usage Credentials target databases.

– Enable the metric collection using monitoring templates and add targets.

– Configure FTP server settings for scheduling reports.

And then you should be able to run some reports, scheduled or interactively.

Enable the metric ‘Feature Usage’ ( Doc ID 1970236.1)

Check the settings of the metric ‘Feature Usage’ in order to populate and get the right data from the view SYSMAN.MGMT$DB_FEATUREUSAGE ( Doc ID 1970236.1):

Go to Oracle Database –> Monitoring—>  Metric and Collection Settings:

Click tab ‘other metric collections’. Search for ‘Feature Usage’.

Click disable, and in the next screen switch it to ‘enable’. In this screen I put the frequency on 1 hour. Default it is 24 hours.

The next step is to collect the data for the database tracking usage reports, as described in the documentation by configuring usage credentials.

http://docs.oracle.com/cd/E63000_01/EMADM/usage_reports.htm#EMADM14375

Setting up Usage Credentials Target Database

Choose –> Setup -> Security –> Monitoring credentials

Choose ‘set credentials ‘, fill in the sys user/password with role sysdba (!), test and save.

Enabling the metric collection using monitoring templates.

One important remark : you need Diagnostic Pack. Snippet from the documentation:

There are two metrics to be set: weekly and hourly. Hourly will be monitoring at session level, so there could be lot of data. Be careful with this.

Weekly metric:

Choose Enterprise –> Monitoring –> Monitoring templates

Click ‘apply’

Don’t forget to add the target-database to the template (I did at first… ).

Click Finish

Wait until the target has reached the status ‘passed’.

Hourly Metrics:

Same method as weekly: Enterprise –> Monitoring–> Monitoring templates

Click apply and don’t forget to add the target.

After finish: wait until target has reached the status ‘passed’.

To get data from the report ‘Database Usage Tracking Report’, which only can be run in batch, the ftp-server must be configured. To get output from Database Usage Tracking report, you’ll have to configure the report also.

Configure FTP-server within BI Publisher

This is needed for scheduling reports.

Enterprise –> Reports –> BI Publisher Reports –> BI Publisher URL (!)

Click on this small ‘BI Publisher’-URL packed between big lines and log in BI Publisher.

Like this: https://<server>.local:9851/xmlpserver

Click Administration

Click FTP at Delivery.

Fill in the FTP server information (in this case the OEM-server itself):

In my case the OEM server wasn’t configured as a ftp-server, so I installed vsftpd on the OEM server:

# Yum install vsftpd

Configure FTP server in the Database Usage Tracking Report

Click on ‘catalog’ and choose Database Usage Tracking Report.

Click ‘edit’ at Database Usage Tracking Report.

In the left bottom corner: ‘Bursting’:

Edit the value of PARAMETER1 (MyFTPServer) and PARAMETER4 (/home/oracle/ftp).

Schedule the database usage tracking report

As earlier explained, this report is only interesting for LMS goals. But for the record, it’s nice to see what’s in it, to be in control of what you are sending to LMS.

A fast way to schedule this report is to click on ‘catalog’ and choose Database Usage Tracking Report.

Choose ‘schedule’ at the report ‘Database Usage Tracking Report’, and fill in when the report should be run.

After a while there should be output (csv-file) in the directory on the FTP-server:

Running the Database Usage Summary Tracking Report.

This can be done interactively:

Choose from Enterprise –> Reports –> BI Publisher Reports the Database Usage Tracking Summary Report:

This report immediately give the report: Only the Tuning Pack has been detected. This is de repository database of Oracle Enterprise Manager, and Diagnostic and Tuning is a default setting in the database (Diagnostic Pack is a prerequisite pack for Tuning Pack).

Unfortunatley this report doesn’t know if it’s virtual (it is !).

But watch the Report Date, the report will not generate every time the report is clicked on! Also this report does not refresh when clicking on the refresh button.

Let’s run a little test and enable an option in the target database:

– Created a partitioned table in a non-system schema –> Option Partitioning should be noticed.

– Alter system set enable_ddl_logging=TRUE  –> Option Life Cycle Management could be noticed, but not agreed in the management pack settings, so should not be in the list.

I’m a little impatient, so forcing of the update of the (target) view DBA_FEATURE_USAGE_STATISTICS .

SQL>  EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

The agent should notice the new data within a while. And perhaps a force upload  (emctl upload agent) . Running the report interactively gives no changes in the meantime, it will not refresh. But it’s possible to schedule the report which will give the actual results in the following manner:

Scheduling:

Settings: format (PDF), FTP server (MyFTPServer), directory en username/password of the destination:

New output in this file:

Partitioning has been detected, next to the already detected Tuning Pack. But Advanced Compression? I’m sure I used no Advanced Compression, not now nor in the past. And nothing in the view SYSMAN.MGMT$DB_FEATUREUSAGE . To check this more thorough, I ran the ReviewLite script (V16.2) from LMS on the database. The output shows no use Advanced Compression as far as I can see.

Digging deeper in the report: Noticed that the column  feature_info in the view SYSMAN.MGMT$EM_LMS_DBA_FEA_USAGE_STAT at the indication is not populated with the string ‘compression’.

At my example the content of feature_info was:

• Oracle Utility Datapump (Import) invoked: 1 times, parallel used: 0 times, full transportable used: 0 times

That’s why the following code in the report gives a false result :

• trim(substr(to_char(feature_info), instr(to_char(feature_info), ‘compression used: ‘,1,1) + 18, 2)) != ‘0’
• this code gives the result ‘ta’ , and that’s indeed not equal to ‘0’.

So gives this report still useful information? For a final verdict this should be run in a proper environment to get a better idea. But I think the report will give a general idea from the used options but cannot be used for LMS-compliancy. That’s not a big thing, tools as Flexera also has a lot of flaws in their reports and when you read the small prints, only tools as ReviewLite are fully approved at the moment.

Best advice is to configure this report and wait until a patch resolves the major issues. I think it’s worth in the end.

FMW Usage tracking

This report can also run interactively.

Oracle Web Tier as Option. Or is it just a column for remarks and the most right column for the licensable options? Time will tell.

Host tracking detail

This report is empty. I’ll take this out of scope at this moment.

Host Usage Tracking Summary Report

This report also runs interactively.

This looks o.k. to me, except that’s not known if it’s virtual (it is!).

Resources

My Oracle Support, Doc ID 1970236.1 : https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=39241286906883&id=1970236.1&_adf.ctrl-state=lsgh373ef_57

Usage report documentation: http://docs.oracle.com/cd/E63000_01/EMADM/usage_reports.htm#EMADM14375