Querying the Oracle SYSMAN MGMT$ Views SYSMAN.MGMT$

DRAFT - contains typos

A project I'm working on required myself and another DBA to assemble an accurate list of all the Oracle databases in the company. The company has many Oracle databases in a variety of locations. The versions vary from 8i to 11g on a variety of platforms. Some are RAC clusters, some are standalone. The inventory needs to include RAC and standalone but exclude Weblogic and OCJ4.

The company uses Oracle Enterprise Manager OEM and Oracle Management Server OMS. As a consequence we can see all the information we need is in OEM. So we started down the path of querying the data out of the SYSMAN schema, in particular the MGMT$ views.

At first I thought the MGMT$ views would be widely known and fairly well documented, much like the V$ views. Well, so far, I haven't found much of anything that is helpful about SYSMAN and MGMT$. Perhaps (most likely) its buried somewhere but being short on time and patience we decided to dig in and figure it out for ourselves.

What follows are some comments, SQL queries and guesses about what's in these views. In the end, we found what we wanted. Along they way, we opened a MetaLink TAR, hoping for a nice tech note and documentation from Oracle Support. This we did not get. We got some advice, but not all of it was on target.

Here's the goal. We want to fill in a spreadsheet with these columns (for all production databases):

OS
OS Version
# Active CPU's
Server IP Address
Server Name
Database Listener TCP Ports
Instance Name
Database Version

This is fairly trivial foer a single instance by querying V$ views and using a korn shell script to parse through oratab. The problem with this approach is that you then have to visit each machine and instance. If I had a shell account on each machine (or if Single Sign On was perfect) I could write an 'expect' script to automate this, but without universal access to all the machines (and given that we do not know how many there are) the task became daunting and time consuming.

OMS SYSMAN MGMT$ views to the rescue!

The IP address and host names is the information we went after first. Using this query we found a few views with 'ADDRESS' in the column name:

Please note that there seems to be two columns used for the unique value for join conditions into these views. One is TARGET_GUID and the other is HOST_NAME.

SELECT table_name '.' column_name
FROM all_tab_cols
WHERE owner = 'SYSMAN'
AND table_name LIKE 'MGMT$%'
AND column_name LIKE '%ADDRESS%';

OK so if we want ADDRESSes then there located in these tables and columns:

MGMT$STORAGE_REPORT_NFS.NFS_SERVER_IP_ADDRESS

MGMT$HW_NIC.MAC_ADDRESS
MGMT$HW_NIC.BROADCAST_ADDRESS
MGMT$HW_NIC.INET_ADDRESS

MGMT$CSA_HOST_NICS.MAC_ADDRESS
MGMT$CSA_HOST_NICS.BROADCAST_ADDRESS
MGMT$CSA_HOST_NICS.INET_ADDRESS
MGMT$CSA_COLLECTIONS.OS_ADDRESS_LENGTH_IN_BITS
MGMT$CSA_CLIENTS.OS_ADDRESS_LENGTH_IN_BITS

I am not sure what the MGMT$CSA% views are used for, and they seem to all be empty. But the MGMT$HW_NIC table is populated and contains accurate data on the hardware of the systems that the management server manages. This is good news.

So if we go after the IP Addresses in SYSMAN.MGMT$HW_NIC with a simple query like this

SELECT * 
FROM mgmt$hw_nic 
ORDER BY host_name;

You will see that there are duplicate entries for each HOST. The duplicates are due to the loopback adapters and can be removed by adding a simple WHERE clause to ignore entries with NULL values int he INET_ADDRESS column and any entry that has an INET_ADDRESS of 127.0.0.1

So now our query looks like this

SELECT * 
FROM mgmt$hw_nic 
WHERE inet_address IS NOT NULL
AND inet_address <> '127.0.0.1' 
ORDER BY host_name;

Which is fairly close except on our system we found some hosts had duplicate entries that were identical except for the value of the SNAPSHOT_GUID column. Since we do not need nor understand what this column is we can dispose of it with a DISTINCT. This leads us to this query which produces a good list of the hosts and NICs in our Oracle production environment (i.e. managed by OMS). We hand validated the list and it is accurate. Here's the final query:

SELECT DISTINCT host_name, inet_address
FROM mgmt$hw_nic 
WHERE inet_address IS NOT NULL
AND inet_address <> '127.0.0.1' 
ORDER BY host_name;

Great so now we need the other items, like OS, etc. We can find these in a view called MGMT$OS_HW_SUMMARY which conveniently has these items for us.

Skipping all the details on the detective work here's the revised join of these two views:

SELECT a.os_summary, a.host_name, a.domain, a.vendor_name, a.system_config, a.ma, a.cpu_count, b.inet_address, a.host_name
FROM mgmt$os_hw_summary a,
mgmt$hw_nic b
WHERE a.host_name=b.host_name AND
inet_address IS NOT NULL AND 
inet_address <> '127.0.0.1';

Now we have the hardware informationwe just need the database and listener information.

Additional detective work leads us to determine that the MGMT$TARGET_COMPONENTS view has all the information we need on the instances. If we join MGMT$TARGET_COMPONENTS against MGMT$OS_HW_SUMMARY and MGMT$HW_NIC were in business. Here's the query that gets us the data we need, saving us from having to ask a fleet of DBAs to log into their databases and email us the information we need.

SELECT a.os_summary AS "OS and OS Version",
'LOCATION' AS "Location",
a.cpu_count AS "# Active CPUs",
b.inet_address AS "Server IP Address",
a.host_name AS "SERVER NAME",
'TCP_PORT' AS "Database TCP Ports",
c.target_name AS "Instance Name",
'Oracle' AS "DB Type",
c.component_version AS "DB Version" 
FROM mgmt$os_hw_summary a,
mgmt$hw_nic b,
mgmt$target_components c
WHERE a.host_name=c.host_name AND
b.host_name=c.host_name AND
b.inet_address IS NOT NULL AND 
b.inet_address <> '127.0.0.1' AND
c.target_type = 'oracle_database'
ORDER BY 5,7 
/

In the next installment......the core tables for the MGMT$ views will be investigated and exposed. For those of you who need more SYSMAN information that I presented here please take a look at the normalized views called

MGMT$TARGET
MGMT$TARGET_COMPONENTS

The complete list of the TARGET tables is

MGMT$TARGET
MGMT$TARGET_ASSOCIATIONS
MGMT$TARGET_COMPONENTS
MGMT$TARGET_COMPOSITE
MGMT$TARGET_FLAT_MEMBERS
MGMT$TARGET_MEMBERS
MGMT$TARGET_METRIC_COLLECTIONS
MGMT$TARGET_METRIC_SETTINGS
MGMT$TARGET_POLICIES
MGMT$TARGET_POLICY_EVAL_SUMM
MGMT$TARGET_POLICY_SETTINGS
MGMT$TARGET_PROPERTIES
MGMT$TARGET_TYPE
MGMT$TARGET_TYPE_DEF
MGMT$TARGET_TYPE_PROPERTIES

Until next time.......