Coskan’s Approach to Oracle

June 11, 2009

How to use SYSMAN schema without EM

Filed under: Basics, Tips — coskan @ 4:18 pm

As you all know Oracle Enterprise Manager can provide incredible amount of information for the database by gathering information from the database. This information is not just in V$ / DBA_ or any internal table, there are also other tables under SYSMAN schema for providing information to EM repository.

on 11.1.0.6 Windows XP version for single instance EM Repository, my DB shows 681 tables under SYSMAN schema

on 10.2.0.4 HP-UX box for single instance EM Repository, my DB shows 341 tables under SYSMAN schema

As you can see it doubled up on 11G. New features brings new tables to this schema.

I could not find good documentation about the tables under SYSMAN schema but the structure of the table names are meaningfull enough to understand what they can be used for. For most of them The naming is like MGMT_X_Y. X stands for the generic name like HC (in my understanding Hardware Components/Configuration or something else ) and Y is for information under the generic root like HARDWARE_MASTER. Table full name is MGMT_HC_HARDWARE_MASTER

Lets start with this table and see what we can get from the table

SQL> @printtab "select * from sysman.MGMT_HC_HARDWARE_MASTER"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
VENDOR_NAME                   : Dell Inc.
SYSTEM_CONFIG                 : Latitude D620
MACHINE_ARCHITECTURE          : x86
CLOCK_FREQ_IN_MHZ             : 133
MEMORY_SIZE_IN_MB             : 2038
LOCAL_DISK_SPACE_IN_GB        : 74.53
CPU_COUNT                     : 2
CPU_BOARD_COUNT               :
IOCARD_COUNT                  : 1
FAN_COUNT                     :
POWER_SUPPLY_COUNT            :
-----------------

As you can see Tables under SYSMAN schema can give you very nice information, which you can’t find in any V$ view or DBA_ table especially for Host system.

Lets see what else we can get under MGMT_HC_%

SQL> select table_name from dba_tables where table_name like 'MGMT_HC%';

TABLE_NAME
------------------------------
MGMT_HC_SYSTEM_SUMMARY
MGMT_HC_HARDWARE_MASTER
MGMT_HC_CPU_DETAILS
MGMT_HC_IOCARD_DETAILS
MGMT_HC_NIC_DETAILS
MGMT_HC_OS_SUMMARY
MGMT_HC_OS_PROPERTIES
MGMT_HC_OS_COMPONENTS
MGMT_HC_FS_MOUNT_DETAILS
MGMT_HC_VENDOR_SW_SUMMARY
MGMT_HC_VENDOR_SW_COMPONENTS

We can get Operating system summary from MGMT_HC_OS_SUMMARY (this is my favourite on windows because it is hard to find 32bit 64 bit information even from OS itself)

SQL> @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : Microsoft Windows XP Workstation
VENDOR_NAME                   : Microsoft Corporation
BASE_VERSION                  : 5.1
UPDATE_LEVEL                  : Service Pack 2
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 3933
ADDRESS_LENGTH_IN_BITS        : 32-bit
PATCHES                       : 152
-----------------

PL/SQL procedure successfully completed.

on HP-UX same table shows

SQL>  @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : 66E304347A471561E044000000000000
NAME                          : HP-UX
VENDOR_NAME                   : Hewlett-Packard Co.
BASE_VERSION                  : B.11.11
UPDATE_LEVEL                  : U
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 15598.066
ADDRESS_LENGTH_IN_BITS        : 64
-----------------

Do you want to learn which operating system fixes are installed on the box, then MGMT_HC_OS_COMPONENTS is the right table for you

SQL> @printtab "select * from sysman.MGMT_HC_OS_COMPONENTS where rownum<4"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB867282
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB867282
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873333
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873333
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873339
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873339
INSTALLATION_DATE             :
-----------------

PL/SQL procedure successfully completed.

As you see these tables are there and ready to be used. There are too many other tables under SYSMAN schema, which might have valuable info for your needs. I think my duty is finished here, remaining is homework for you, to discover the information under the tables listed via this sql command


select table_name from dba_tables where owner='SYSMAN' and num_rows>0;

About these ads

6 Comments »

  1. Interesting blog Coskan. It is true that there is very little documentation about these tables or indeed about how to update the OMS schema to change details. I will have to set that printab feature up. I think it comes from Tom Kyte doesn’t it.

    John

    Comment by John Hallas — June 14, 2009 @ 11:32 am

  2. Thanks for that Coskan, I am just setting up my new 11g test database and intend to compare with my old 10g one – I’ll check out the sysman tables using your blog as a start point.

    Comment by mwidlake — June 14, 2009 @ 3:32 pm

  3. Good One Coskan, the sysman-schema could do with a little more attention. Lots of potential useful stuff out there.
    Another idea for a blog-aggregate?

    nb: I’m ashamed to say I only just started exploring your blog. But a good Read it is.
    Thanks!

    Comment by PdV — June 18, 2009 @ 6:50 pm

  4. Thank you all for you comments lads,
    I am glad that you enjoyed the post

    Comment by coskan — June 23, 2009 @ 11:27 am

  5. Really good article, thanks…

    Comment by anutwalidera — June 29, 2009 @ 8:47 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 206 other followers

%d bloggers like this: