Digging deep in Oracle Grid Repository for Information

How Grid Repository Stores Data?

The agents upload data at MGMT_METRIC_RAW table which is retained for 7 days.
Raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR.
After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls into 1-day records.
These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.

Table Name                   Retention Parameter                  Retention Days
MGMT_METRICS_RAW             mgmt_raw_keep_window                    7
MGMT_METRICS_1HOUR           mgmt_hour_keep_window                   31
MGMT_METRICS_1DAY            mgmt_day_keep_window                    365

To query data in above tables, we need to know TARGET_GUID and METRIC_GUID.
For easier access using names “target names” or “metric names” we can use 3 views MGMT$METRIC_DETAILS, MGMT$METRIC_HOURLY, and MGMT$METRIC_DAILY.
These views owned by SYSMAN are based on these 3 main tables but having extra columns like ‘target_name’, ‘metric_name’ for easier referrence.

<
Following SQL will return definitions of MGMT$METRIC_DETAILS,METRIC_HOURLY & METRIC_DAILY

select VIEW_NAME
, TEXT_LENGTH
, TEXT
from dba_views
where OWNER = ‘SYSMAN’
and VIEW_NAME in ( ‘MGMT$METRIC_DETAILS’
, ‘MGMT$METRIC_HOURLY’
, ‘MGMT$METRIC_DAILY’ );

Let’s have a look at attributes stored at these views which we could use for reporting.
The common attributes are “Property_name” & “target_type”.

select unique property_name
from mgmt$target_properties
order by 1;
———————————————
CRSHome
CRSSoftwareVersion
CRSVersion
CRSVersionCategory
ClusterName

DBDomain
DBName
DBVersion
InstanceName
SID

DataGuardStatus
DatabaseName
DatabaseType
DatabaseUser
DbBlockSize
OpenMode
OracleHome
StartTime
StatisticsLevel
background_dump_dest
core_dump_dest

ListenAddress
ListenPort
ListenerOraDir
LsnrName

HARDWARE_TYPE
Machine
MachineName
IP_address
CPUCount

OS
OS_patchlevel

RACInstNum
RACOption

select unique target_type
from mgmt$target
order by 1;
———————————————
cluster
composite
host
j2ee_application
metadata_repository
netapp_filer
oracle_csa_collector
oracle_database
oracle_emd
oracle_emrep
oracle_ias_farm
oracle_listener
rac_database
weblogic_domain
weblogic_j2eeserver

Let’s combine these 2 views to locate some interesting facts.

List Targets with TNS Listener ports configured :

SELECT mgmt$target.host_name
 , mgmt$target.target_name
 , mgmt$target.target_type
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 and ( mgmt$target.target_type = 'oracle_listener' )
 and ( mgmt$target_properties.property_name = 'Port' );

Devora02       LISTENER_ora02                       oracle_listener Port 1529
Devora01       LISTENER_ora01                       oracle_listener Port 1529
Devora04       LISTENER_ora04                       oracle_listener Port 1529

List Machine_Names, CPU Count & Database Verion for Licensing

SELECT mgmt$target.host_name
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 AND ( mgmt$target_properties.property_name in ( 'CPUCount','DBVersion' ) )
 GROUP BY mgmt$target.host_name
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 order by mgmt$target.host_name;

List Dataguard Instances mounted in APPLY mode

SELECT mgmt$target.host_name
 , mgmt$target.target_name
 , mgmt$target.target_type
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 and ( mgmt$target.target_type = 'oracle_database' )
 and ( mgmt$target_properties.property_name = 'OpenMode' )
 and PROPERTY_VALUE like 'READ%ONLY%WITH%APPLY%';

List RAC databases and their Attributes like ClusterName, Dataguard Status.
Change "property_name" attribute per your need

SELECT mgmt$target.host_name
 , mgmt$target.target_name
 , mgmt$target.target_type
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 and ( mgmt$target.target_type = 'rac_database' )
 and ( mgmt$target_properties.property_name in ( 'RACOption'
 , 'DBName'
 , 'DBDomain'
 , 'DBVersion'
 , 'ClusterName'
 , 'DataGuardStatus'
 , 'MachineName'
 , 'Role'
 , 'SID' ) )
 order by mgmt$target.host_name, mgmt$target.target_name,
mgmt$target_properties.property_name;  

RACNode1        DEV   rac_database ClusterName     crs1                          
RACNode1        DEV   rac_database DBDomain        oracledbasupport.co.uk                
RACNode1        DEV   rac_database DBName          DEV                         
RACNode1        DEV   rac_database DBVersion       10.2.0.3.0                    
RACNode1        DEV   rac_database DataGuardStatus                               
RACNode1        DEV   rac_database MachineName     RAC01-vip
RACNode1        DEV   rac_database RACOption       YES                           
RACNode1        DEV   rac_database Role            NORMAL                        
RACNode1        DEV   rac_database SID             DEV1                        
RACDGNode1       PROD rac_database ClusterName     RACDGNodecrs                        
RACDGNode1       PROD rac_database DBDomain        oracledbasupport.co.uk                      
RACDGNode1       PROD rac_database DBName          PROD                              
RACDGNode1       PROD rac_database DBVersion       11.2.0.2.0                          
RACDGNode1       PROD rac_database DataGuardStatus Physical Standby                    
RACDGNode1       PROD rac_database MachineName     RACDGNode-vip
RACDGNode1       PROD rac_database RACOption       YES                                 
RACDGNode1       PROD rac_database Role            NORMAL                              
RACDGNode1       PROD rac_database SID             PROD2

If you are having performance hit on Grid database, use following SQL to locate most active segemnts.
You can then think of archiving data in grid else moving them on speedy spindles.

select sum ( B.logical_reads_total )
 , sum ( B.physical_reads_total )
 , sum ( B.physical_writes_total )
 , sum ( buffer_busy_waits_total )
 , c.object_name
 , c.owner
 from DBA_HIST_SNAPSHOT A
 , DBA_HIST_SEG_STAT B
 , dba_objects C
 where A.Snap_id = b.snap_id
 and c.object_id = b.obj#
 and A.BEGIN_INTERVAL_TIME >= to_Date ( '17-May-2011 08:00'
 , 'DD-MON-YYYY HH24:MI' )
 and A.END_INTERVAL_TIME <= to_Date ( '17-May-2011 12:00'
 , 'DD-MON-YYYY HH24:MI' )
 group by c.object_name
 , c.owner
 order by 1 desc;

Change order by
1: For most Read Segments
3: For most Writes on a segment
4: For Waits

SQL to report Oracle init parameters for a Target database

  SELECT   target_name,
           target_type,
           name,
           VALUE
    FROM   MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
   WHERE       A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
              AND TARGET_TYPE = 'rac_database'  -- Choose TARGET_TYPE
           AND name LIKE 'remote_listener%'     -- Look for a relevant Parameter
GROUP BY   target_name,
           target_type,
           name,
           VALUE
ORDER BY   Target_name, name ;

Following SQL will report All Database Target details monitored thru grid

select t.host_name
 as     host
 , ip.property_value IP
 , t.target_name
 as     name
 , decode ( t.type_qualifier4
 , ' '
 , 'Normal'
 , t.type_qualifier4 )
 as     type
 , dbv.property_value
 as     version
 , port.property_value port
 , SID.property_value SID
 , logmode.property_value
 as     "Log Mode"
 , oh.property_value
 as     "Oracle Home"
 from mgmt$target t
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'DBVersion' ) dbv
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'Port' ) port
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'SID' ) sid
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'log_archive_mode' ) logmode
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'OracleHome' ) oh
 , ( select tp.target_name
 as     host_name
 , tp.property_value
 from mgmt$target_properties tp
 where tp.target_type = 'host'
 and tp.property_name = 'IP_address' ) ip
 where t.target_guid = port.target_guid
 and port.target_guid = sid.target_guid
 and sid.target_guid = dbv.target_guid
 and dbv.target_guid = logmode.target_guid
 and logmode.target_guid = oh.target_guid
 and t.host_name = ip.host_name
 order by 1, 3;