Oracle Grid Infrastructure includes the Cluster Health Monitor (CHM) which regularly captures OS-related performance information. In early versions, CHM use a Berkeley DB for its data store. In Grid Infrastructure 12.1.0.2, it is now required to use an Oracle database for the data store. This Oracle database is called the Grid Infrastructure Management Repository (GIMR). Many people are already aware that the GIMR runs with the database name “-MGMTDB” and runs on only one node of the GI cluster. Should that node become available, GI will automatically start the GIMR on a remaining node.
The above paragraph is about all of the background information I am going to provide on the GIMR. If the reader wants to know more, they can certainly do a web search for info on how to manage (what little management is needed of this database), and how to start and stop the database and its dedicated listener.
This blog post intends to educate the reader on how to to access the GIMR database and extract meaningful information from it. More web searching can show how to use command line utilities to export data from the GIMR. And there is a graphical utility, CHMOSG, that can be used to view the CHM data in the repository. But just for fun, I thought I would show how to get to the data directly.
First, you need to know which node the database is running on. On any node, I can issue the following:
[oracle@host01 bin]$ cd /u01/app/crs12.1.0.2 [oracle@host01 bin]$ ./crs_stat -t | grep -i mgmt ora.MGMTLSNR ora....nr.type ONLINE ONLINE host01 ora.mgmtdb ora....db.type ONLINE ONLINE host01
The above shows the database and the listener are running on host01. Now that I know the instance’s node, I can sign on to that node, and set my environment variables to connect to the instance. This database runs out of the Grid Infrastructure home, not the RDBMS home. So I need to set my ORACLE_HOME correctly. Also, the instance name starts with a dash so I need to wrap the SID in double quotes.
[oracle@host01 ~]$ export ORACLE_HOME=/u01/app/crs12.1.0.2 [oracle@host01 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@host01 ~]$ export ORACLE_SID="-MGMTDB"
I can now connect to the instance and verify I am connected to the proper one.
[oracle@host01 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 21 15:17:21 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> connect / as sysdba Connected. SQL> select instance_name from v$instance;
INSTANCE_NAME ---------------- -MGMTDB
This database is an Oracle multitenant database, which one PDB. I need to determine the PDB name. The PDB name will be the same as the cluster name. I can remind myself of the cluster name by querying V$ACTIVE_SERVICES.
SQL> select name,con_id 2 from v$active_services;
NAME CON_ID ----------------------------------------------------- ---------- my_cluster 3 -MGMTDBXDB 1 _mgmtdb 1 SYS$BACKGROUND 1 SYS$USERS 1
SQL> alter session set container=my_cluster;
Session altered.
Only one service has the container id not equal to 1 (1 is the CDB) so it must be the PDB I am looking for. I modify my session to use the PDB as its container.
My next task is to get a list of tables owned by CHM.
SQL> select table_name from dba_tables where owner='CHM' 2 order by table_name;
TABLE_NAME -------------------------------------------------------------------------------- CHMOS_ACTIVE_CONFIG_INT_TBL CHMOS_ASM_CONFIG_INT_TBL CHMOS_CPU_INT_TBL CHMOS_DEVICE_INT_TBL CHMOS_FILESYSTEM_INT_TBL CHMOS_NIC_INT_TBL CHMOS_PROCESS_INT_TBL CHMOS_STATIC_CONFIG_INT_TBL CHMOS_SYSTEM_PERIODIC_INT_TBL CHMOS_SYSTEM_SAMPLE_INT_TBL
Just 10 tables in the schema. The first table in the list shows some configuration information about the CHM monitored hosts.
SQL> select hostname,NUMPHYCPUS,NUMCPUS,NUMDISKS 2 from CHM.CHMOS_ACTIVE_CONFIG_INT_TBL;
HOSTNAME NUMPHYCPUS NUMCPUS NUMDISKS
---------- ---------- ---------- ----------
host01 1 2 3
For the best results avoid excessive alcohol or a heavy dose can kill your purpose of taking female enhancers is to allow you to maximize the full potential of treatment for ED there exists a silver levitra on line lining. It reduces hypertension and also acts as levitra in india price diuretic. Sildenafil citrate is one of the most energetic ingredient to viagra from canada pharmacy settle down the erection issue. This was like a single person - someone who's never even been able to get a second date online levitra - providing marriage counseling. host02 1 2 3
I can see that CHM is gathering information about two nodes in the cluster. I can see the number of physical CPU’s for each node and the number of total cores (2). These nodes also have 3 disks.
We can also learn information about the OS.
SQL> select hostname,osname,chiptype 2 from CHM.CHMOS_STATIC_CONFIG_INT_TBL;
HOSTNAME OSNAME CHIPTYPE ---------- ---------- -------------------- host01 Linux Intel(R) host02 Linux Intel(R)
There is a good amount of information in these tables and it just takes some trial and error to figure out what is in there. For example, I can use this query to get a count of processes running on host01 ordered over time.
select begintime,count(*) from CHM.CHMOS_PROCESS_INT_TBL where hostname='host01' group by begintime order by begintime;
I intentionally did not include the output as it would be too long for a blog post. Here are a few more sample queries that you can try on your GIMR database.
Disk I/O activity for a specific host over time.
select begintime, DISK_BYTESREADPERSEC/1024/1024 as MB_READ_SEC, DISK_BYTESWRITTENPERSEC/1024/1024 as MB_WRITE_SEC, DISK_NUMIOSPERSEC as IO_PER_SEC from CHM.CHMOS_SYSTEM_SAMPLE_INT_TBL where hostname='host01' order by begintime;
Swapping on a specific host over time.
select begintime,swpin,swpout from CHM.CHMOS_SYSTEM_SAMPLE_INT_TBL where hostname='host01' order by begintime;
The next SQL statement will compute a histogram of disk I/O activity. I’m sure someone else can come up with a more elegant version as my SQL statements tend to be more brute-force.
select first.num_count as "<=10ms", second.num_count as "<=20ms", third.num_count as "<=50ms", fourth.num_count as "<=100ms", fifth.num_count as "<=500ms", final.num_count as ">500ms" from (select count(*) as num_count from CHM.CHMOS_DEVICE_INT_TBL where devid='sda1' and latency between 0 and 10) first, (select count(*) as num_count from CHM.CHMOS_DEVICE_INT_TBL where devid='sda1' and latency between 11 and 20) second, (select count(*) as num_count from CHM.CHMOS_DEVICE_INT_TBL where devid='sda1' and latency between 21 and 50) third, (select count(*) as num_count from CHM.CHMOS_DEVICE_INT_TBL where devid='sda1' and latency between 51 and 100) fourth, (select count(*) as num_count from CHM.CHMOS_DEVICE_INT_TBL where devid='sda1' and latency between 101 and 500) fifth, (select count(*) as num_count from CHM.CHMOS_DEVICE_INT_TBL where devid='sda1' and latency > 500) final;
<=10ms <=20ms <=50ms <=100ms <=500ms >500ms
---------- ---------- ---------- ---------- ---------- ----------
150693 10 1 0 0 0
There is a good amount of information in the CHM schema. I expect mostly that this information is just educational and most people will not be querying the CHM tables directly. But this is good information to know and may help others.
