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
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.......