Oracle Instances Inventory Details Custom Views - Oracle Visibility Extensions
- Author: Nicolas Rousseau
- Author Email Address: email@example.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
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.
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
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.
This view is for customers in FNMS 2020R1 and later versions
Oracle Instances details incl. Exact Version (NR)
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.
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)
- Oracle instances options details: all details of options and instances (installation date, last used date etc.