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!
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
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!
what about pluggable, cluster database?
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;
Hello,
Your query returning Duplicates
Hi,
Can you please add lifecycle status to get the output to differentiate the lifecycle status.
Thanks.
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
Hi Antoine,
Could we find the port number of target databases using a sql query ?
Best regards;
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
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
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
*
ERROR at line 1:
ORA-00904: “???ORCL_GTP_LIFECYCLE_STATUS???”: invalid identifier
I get above error – can you help?
Hi Raj,
For some reason, WordPress replaces single quotes with other characters in the comments section.
Try replacing the single quotes in the comment (’) with real simple quotes (in Notepad for example).
It should work.