Thinking Out Loud

June 1, 2021

Query OEM mgmt$(target|target_properties)

Filed under: emcli,Grid Control — mdinh @ 9:41 pm

TARGET_TYPE oracle_database can be a database or an instance; however, rac_database is a database

select t.TARGET_TYPE, t.TYPE_QUALIFIER3, count(*)
from mgmt$target_properties p, mgmt$target t
where p.TARGET_GUID=t.TARGET_GUID
and p.PROPERTY_NAME='DataGuardStatus'
group by t.TARGET_TYPE, t.TYPE_QUALIFIER3
order by 2,1 desc
;

There is 1 single instance database, 105 RAC databases, 210 RAC instances.

TARGET_TYPE                    TYPE_QUALIFIER3        COUNT(*)
------------------------------ -------------------- ----------
rac_database                   DB                          105
oracle_database                DB                            1
oracle_database                RACINST                     210

DataGuardStatus does not mean Data Guard exist unless PROPERTY_VALUE is populated

select 
t.TARGET_TYPE, t.TYPE_QUALIFIER3, 
NVL(REGEXP_REPLACE(p.property_value,'[[:space:]]'),'NO DataGuard') PROPERTY_VALUE, count(*)
from mgmt$target_properties p, mgmt$target t
where p.TARGET_GUID=t.TARGET_GUID
and p.PROPERTY_NAME='DataGuardStatus'
group by t.TARGET_TYPE, t.TYPE_QUALIFIER3, PROPERTY_VALUE
order by 2,1 desc
;

There are 48 RAC Primary and 49 RAC Physical Standby because 1 RAC database has 2 Physical Standby.

TARGET_TYPE                    TYPE_QUALIFIER3      PROPERTY_VALUE                   COUNT(*)
------------------------------ -------------------- ------------------------------ ----------
rac_database                   DB                   NO DataGuard                            8
rac_database                   DB                   PhysicalStandby                        49
rac_database                   DB                   Primary                                48
oracle_database                DB                   NO DataGuard                            1
oracle_database                RACINST              NO DataGuard                           16
oracle_database                RACINST              PhysicalStandby                        98
oracle_database                RACINST              Primary                                96

Here’s how to determine the values for TYPE_QUALIFIER1-4

SQL> select distinct NVL(REGEXP_REPLACE(TYPE_QUALIFIER1,'[[:space:]]'),NULL) TYPE_QUALIFIER from mgmt$target order by 1;

SQL> c/TYPE_QUALIFIER1/TYPE_QUALIFIER2
SQL> c/TYPE_QUALIFIER2/TYPE_QUALIFIER3
SQL> c/TYPE_QUALIFIER3/TYPE_QUALIFIER4

SQL to gather primary and standby targets.

-- db.sql
set echo off lines 300 pages 500 trimsp on tab off
col HOST_NAME       for a30
col TARGET_TYPE     for a20
col TYPE1           for a9
col TYPE3           for a9
col TYPE4           for a9
col PROPERTY_VALUE  for a23
col PROPERTY_NAME   for a17
col TARGET_NAME     for a60
BREAK ON HOST_NAME SKIP 1 ON PROPERTY_VALUE ON TARGET_TYPE ON PROPERTY_NAME ON TYPE3
select
  REGEXP_SUBSTR(t.HOST_NAME,'[^.]+',1,1) host_name,
--  REGEXP_SUBSTR(t.TARGET_NAME,'[^.]+',1,1) target_name, t.TARGET_TYPE,
  t.TARGET_NAME, t.TARGET_TYPE,
  NVL(REGEXP_REPLACE(property_value,'[[:space:]]'), 'Primary: NO DataGuard') PROPERTY_VALUE,
  p.PROPERTY_NAME,
  TYPE_QUALIFIER1 type1, TYPE_QUALIFIER3 type3,
  (CASE TYPE_QUALIFIER4 WHEN 'FullLLFile+CDB' THEN 'CDB' WHEN 'FullLLFile' THEN 'DB' ELSE NULL END) type4
from mgmt$target_properties p, mgmt$target t
where p.TARGET_GUID=t.TARGET_GUID
and   p.PROPERTY_NAME='DataGuardStatus' -- Find Data Guard
and   t.TYPE_QUALIFIER3='DB'            -- Find Database
order by PROPERTY_VALUE desc, t.TARGET_TYPE, t.HOST_NAME, type1 ASC, type4
;

Leave a Comment »

No comments yet.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: