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)
[...]

EDIT : As Brian pointed out in comment, this query is no longer valid if you have Oracle 18c or higher databases registered in OEM! You can use the following one instead.
Moreover, this query has the advantage of returning the exact version instead of the major version (“12.1.0.2.190115” instead of “12cR102” for example) :

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, prop.PROPERTY_VALUE DATABASE_VERSION, os.TARGET_NAME SERVER, os.TYPE_QUALIFIER1 OS, os.TYPE_QUALIFIER2 OS_VERSION
from SYSMAN.MGMT$TARGET db, SYSMAN.MGMT$TARGET os, SYSMAN.MGMT$TARGET_PROPERTIES prop
where db.HOST_NAME = os.TARGET_NAME
and db.TARGET_GUID = prop.TARGET_GUID
and prop.PROPERTY_NAME='Version'
and db.target_type='oracle_database'
and os.target_type='host'
order by 1;

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!

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

  1. Can you please check 18c?

    select
    tgt.type_qualifier1,
    prop.property_value,
    from mgmt$target tgt
    join mgmt$target_properties prop on tgt.target_guid = prop.target_guid

    TYPE_QUALIFIER1 PROPERTY_VALUE
    12cR2 18.0.0.0.0

    1. Hi Brian,

      You’re right, starting with Oracle 18c, the query output doesn’t seem to return the correct version anymore.
      I corrected my query using the view you mentioned (“mgmt$target_properties”) and the output is correct now.
      I will update the post right now to indicate this change, thank you very much!

    1. Hi Misha,

      In order to retrieve the pluggable databases, you can modify the “where” clause of the query in this way.
      For PDBs, replace “and db.target_type=’oracle_database'” with “and db.target_type=’oracle_pdb'”.
      For cluster databases, replace “and db.target_type=’oracle_database'” with “and db.target_type=’rac_database'”

      If you want to retrieve the list of instances, cluster databases and PDBs in the same query, you can add them all in the WHERE clause. Replace “and db.target_type=’oracle_database'” with “and (db.target_type=’oracle_database’ or db.target_type=’rac_database’ or db.target_type=’oracle_pdb’)”.

      In order to identify the type of resources, you can add the column “db.target_type” in the SELECT clause.

      Full query:

      select db.TARGET_NAME DATABASE_NAME, db.target_type TARGET_TYPE, prop.PROPERTY_VALUE DATABASE_VERSION, os.TARGET_NAME SERVER, os.TYPE_QUALIFIER1 OS, os.TYPE_QUALIFIER2 OS_VERSION
      from SYSMAN.MGMT$TARGET db, SYSMAN.MGMT$TARGET os, SYSMAN.MGMT$TARGET_PROPERTIES prop
      where db.HOST_NAME = os.TARGET_NAME
      and db.TARGET_GUID = prop.TARGET_GUID
      and prop.PROPERTY_NAME=’Version’
      and (db.target_type=’oracle_database’ or db.target_type=’rac_database’ or db.target_type=’oracle_pdb’)
      and os.target_type=’host’
      order by 1;

  2. Hi,

    Can you please add lifecycle status to get the output to differentiate the lifecycle status.

    Thanks.

    1. Hi Lavanya,

      I don’t use the lifecycle management pack in my current environment, but according to Oracle documentation, this query should give you the lifecycle status of your database (or “NA” if no lifecycle status is set for your target):

      select
      db.TARGET_NAME DATABASE_NAME,
      db.target_type TARGET_TYPE,
      prop.PROPERTY_VALUE DATABASE_VERSION,
      os.TARGET_NAME SERVER,
      os.TYPE_QUALIFIER1 OS,
      os.TYPE_QUALIFIER2 OS_VERSION,
      nvl(lifecyle.property_value, ‘NA’) LIFECYLE
      from
      SYSMAN.MGMT$TARGET db
      join
      SYSMAN.MGMT$TARGET os on db.HOST_NAME = os.TARGET_NAME
      join
      SYSMAN.MGMT$TARGET_PROPERTIES prop on db.TARGET_GUID = prop.TARGET_GUID
      left outer join
      SYSMAN.MGMT$TARGET_PROPERTIES lifecyle on (db.TARGET_GUID=lifecyle.TARGET_GUID and lifecyle.PROPERTY_NAME=’orcl_gtp_lifecycle_status’)
      where
      prop.PROPERTY_NAME=’Version’
      and
      (db.target_type=’rac_database’ or db.target_type=’oracle_database’ or db.target_type=’oracle_pdb’)
      and
      os.target_type=’host’
      order by 1;

      (I rewrote the query using the ANSI standard to make it more readable, but it’s the same as the previous one.)

      Can you try it and tell me if it works?

      Thanks,

      Antoine

  3. Hi Antoine,

    Could we find the port number of target databases using a sql query ?

    Best regards;

    1. Hi Hamza,

      You can get this information 2 ways, but it’s not perfect:

      – You can retrieve the listener(s) running on the same host as your database, and get the listening port in the “MGMT$TARGET_PROPERTIES” view (PROPERTY_NAME = Port).
      This solution is not perfect because a listener can be running on a server, but not serve a specific database.
      (Let’s say we have 2 Oracle instances on a server, and 2 listeners listening on different ports. You won’t be able to differentiate which listener serves which database).

      – You can also get the properties “Port” for the database in the “MGMT$TARGET_PROPERTIES” view, but if the Oracle instance does not use “Dynamic Listener Registration” but use “Static Listener Registration” , the information returned will be incorrect.

      Unfortunately, I don’t see any other way to get this information with 100% reliability.

      Regards,
      Antoine

  4. How to find open mode for all instances in grid and not only one shown in mgmt$target_properties becaue many PDBs are not there?
    Thx

Leave a Reply

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