Oracle Instances Inventory Details Custom Views - Oracle Visibility Extensions

Oracle Instances Inventory Details Custom Views - Oracle Visibility Extensions

  • Author: Nicolas Rousseau
  • Author Email Address: nrousseau@flexera.com
  • Solution Type: Custom Report
  • Flexera Product & Version: FlexNet  Manager 2016-2020R1
  • Environment: On Premise Only
  • Development Effort (Days): 4
  • Implementation Effort (Days): 0.1
  • Disclaimer:
  • SOLUTIONS ARE PROVIDED ON AN "AS IS" BASIS. NEITHER FLEXERA NOR ITS SUPPLIERS MAKE ANY WARRANTIES, EXPRESS OR IMPLIED, STATUTORY OR OTHERWISE, INCLUDING BUT NOT LIMITED TO WARRANTIES OF MERCHANTABILITY, TITLE, FITNESS FOR A PARTICULAR PURPOSE OR NONINFRINGEMENT. LICENSEE MAY HAVE OTHER STATUTORY RIGHTS. HOWEVER, TO THE FULL EXTENT PERMITTED BY LAW, THE DURATION OF STATUTORILY REQUIRED WARRANTIES, IF ANY, WILL BE LIMITED TO THE SHORTER OF (I) THE STATUTORILY REQUIRED PERIOD OR (II) THIRTY (30) DAYS FROM LICENSEE’S ACCEPTANCE OF THE AGREEMENT.

 

Summary

Create custom views to understand more details on Oracle instances. Particularly, the document embedded gives the code for implementing an automated generation of the Options.csv file every day and import into a reporting tables that will give all the details on DBA_FEATURE_USAGE_STATISTICS or OEM LMS evidences

Synopsis

Business need and approach

Oracle instances details are collected by the FlexNet Inventory agent. The agent runs an LMS verified script that collects a large quantity of information that is made available by FlexNet Manager in multiple places… but that is not available a flat way (across servers, across instances):

  • Options features inventory details that are stored in the very cryptic and huge “ORCL_OPTIONS.csv” file you can download from the Oracle instances details page (OracleLMSEvidence.zip). Note that the Oracle transparency feature from FNMS 2020R1, allows, instance per instance, to give the “why” of a used instance.
  • All Oracle Applications Account (NR). Oracle Users, that are actually a mix of Oracle DB users and Oracle Enterprise Business Suite users in the details of each Named User Plus or Oracle Application User license
  • Server Inventory Date, Oracle Inventory Date in the database of Inventory Manager, using the “InventoryReport view”.
  • Instance details (exact version, high water mark, creation date, “isUsed” for options instances accessible from the “attribute” column of the Oracle instances

This document gives the steps to create in an on premise FNMS instance 6 views and implement the ORCL_OPTIONS.csv generation and re-import. These views augment the transparency you can get on Oracle data.

The six views are the following

  • Oracle Options Features Usage Details (NR). This view is compatible from FNMS 2020R1 onward, that gives a flat view of the feature usages you can find instance per instance when clicking on the “Yes” link in the instance option details (Oracle Transparency)
  • Oracle Instances details incl. Exact Version (NR). This view shows the same columns as the Oracle Instances screen, plus the Database exact version and additional columns (cost center, corporate unit… that you can easily extend with more attributes or links.
  • Oracle Options Features Details (NR). Oracle features raw usage information (from ORCL_OPTIONS.csv), filtered on DBA_FEATURE_USAGE_STATISTICS and OEM queries. This view can be extended to more queries types.
  • All Oracle Applications Accounts (NR). Instance users (Database and EBS)
  • Oracle Instances Inventory Date From Inventory Manager (NR). Instance inventory dates details such as last Oracle inventory Date, last Oracle inventory date of the server, last inventory date of the server etc, extracted from the FNM Inventory database
  • Oracle Instances Options Details (NR). all details of options and instances (installation date, last used date etc.) extracted from the instances attributes tables (that you can access through the attribute table on an instance detail). This report can have a large number of rows.

The approach of the views is the following:

  • They create procedures: nr_XXX in the FNMSCompliance database
  • They create the four associated views, in the FNMP.dbo.ComplianceSavedSearch_MT table, that basically launch the stored procedures to display the view.
  • All views filter the data on “authorized devices” for the user

The changes can be simply reverted by deleting the stored procedures and the view from FNMP Web.

Views preview

Oracle Options Features Usage Details (NR)

This view is for customers in FNMS 2020R1 and later versions

Preview

OptionsUsageDetailsReportPreview.png

Oracle Instances details incl. Exact Version (NR)

Preview

InstanceDetailsIncludingExactVersionReportPreview.png

Oracle Options Features Details (NR)

  • Oracle features raw usage information (from ORCL_OPTIONS.csv), filtered on DBA_FEATURE_USAGE_STATISTICS and OEM queries. This view can be extended to more queries types.

Preview

OracleLMSFeatures.png

  • Oracle Instances Inventory Date From Inventory Manager (NR)

    • Instance inventory dates details such as last Oracle inventory Date, last Oracle inventory date of the server, last inventory date of the server etc, extracted from the FNM Inventory database

    Preview

OracleInventoryDates.png

  • All Oracle Applications Accounts (NR)

    • Instance users (Database and EBS)

    Preview

OracleUsers.png

  • Oracle Instances Options Details (NR)

    • Oracle instances options details: all details of options and instances (installation date, last used date etc.

    Preview

OptionsDetails.png

Was this article helpful? Yes No
No ratings
Comments

The embedded document above seems to be for Adobe and Office reports. Can you please embed the document relevant to Oracle options reporting? 

True, the document look like it's for Adobe and office optimization, also is possible to implement report like in multitenant environment?

 

My bad, sorry I uploaded the wrong document... I upload the current version!

Hi Nrousseau

If you could possible to clarify me the Powersheel script, where the query you stated below in LMS file extraction. 

I would like to know and I was confused - what Document Name and File from dbo also you mentioned Document. where document name 

# Select-Statement for filename & BLOB
$SQL = "SELECT [DocumentName], [DocumentFile] FROM [dbo].[Document] WHERE [DocumentName] = 'OracleLMSAuditEvidence.zip'"

Version history
Revision #:
6 of 6
Last update:
‎Jun 23, 2021 09:27 AM
Updated by:
 
Contributors