Extract the list of all databases with server and OS version of Oracle Enterprise Manager Cloud Control repository

Today, I’m going to share with you a simple SQL query that will allow you to quickly extract useful information from your OEM repository about your databases:

  • Database name
  • Database version
  • Server name
  • Server OS
  • OS version

The query can be very useful if you need to fill in a new CMDB for example.

Prerequisites

My script has been tested against Oracle Enterprise Manager Grid Control 12c and 13c.

You need to have access to the “MGMT$TARGET” view on the OEM repository database (SYSMAN, SYS or SYSTEM user will do the trick!).

Query

set lines 200
col DATABASE_NAME for a40
col DATABASE_VERSION for a20
col SERVER for a40
col OS for a20
col OS_VERSION for a60
select db.TARGET_NAME DATABASE_NAME, db.TYPE_QUALIFIER1 DATABASE_VERSION, os.TARGET_NAME SERVER, os.TYPE_QUALIFIER1 OS, os.TYPE_QUALIFIER2 OS_VERSION
from SYSMAN.MGMT$TARGET db, SYSMAN.MGMT$TARGET os 
where db.HOST_NAME = os.TARGET_NAME 
and db.target_type='oracle_database'
and os.target_type='host'
order by 1;

The output looks like:

DATABASE_NAME        DATABASE_VERSION     SERVER                     OS                   OS_VERSION
-------------------- -------------------- -------------------------- -------------------- -------------------------------------------------------
ORCL1                11gR202              aixlab01					 AIX                  7.1.0.0
ORCL2                10gR204              hplab01					 HP-UX                B.11.31
ORCL3                11gR202              linuxlab01				 Linux                Red Hat Enterprise Linux Server release 6.7 (Santiago)
[...]

You can build a lot of queries this way. Remember that the view “MGMT$TARGET” contains one row for each resource (listener, database, host, agent, …). Therefore, if you want to get all information with a single query, you need to join the view to itself.

Stay tuned for more DBA stuff!

Leave a Reply

Your email address will not be published. Required fields are marked *