ASH. Like a movie (1/3)

Here the Italian version

In Oracle 10g was introduced the "Intelligent Self-Management Infrastructure" [1].

Basically the idea is to collect the most informations with the minor impact on the system. Based on Heisenberg's Uncertainty Principle, we know that "the observer affects the observed" [2]. And this is true also for Oracle.

One component of the "Intelligent Self-Management Infrastructure" is ASH. In this post I would like to show you a special point of view base on an idea of Kyle Hailey [3].

We know that ASH is based on sampling made each second. This mean that there are the gaps between snapshots. Really we want analyze only long sessions (only "this long sessions" impact on the performance). So, if a session takes a lot of minutes before the end, you are sure to observe the work, even if the observation is made every second. Is like you see a movie, one frame each second.

Because a picture is worth a thousand words, I show you this picture from [3].

It shows how is possible to understand what is happening also if we don't know the story in real time.

And the wonderful thing is that the sampling is like the real time. In order to show that this last statement is true I used a query [4] which output is this chart. I created it using Microsoft excel.

The chart shows the difference between sampling (ASH) and real time (DB Time). In Y there are the "ASH"/"DB Time" values, while in X the "snap_id"/"time stamp" values.

Really, to build this chart, I delete some values. I'm investigating the root cause of those anomalies.

• Update 31/Mar/2016

I modified the chart, because I deleted one point from the graph. I'm writing a post on anomaly I observed.

• References

[1] http://www.oracle.com/technetwork/database/manageability/twp-manage-intelligent-infrastructu-133531.pdf
[2] http://www.oracle.com/technetwork/database/manageability/twp-40169-134162.pdf
[3] https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history
http://blog.orapub.com/20150812/Which-Is-Better-Time-Model-Or-ASH-Data.html
[4] http://www.evernote.com/l/ABzQFwwMxo5HyKs8pmpIcyVjmjH7vreMvVo/

lsasmdsk, Solaris version (ENG)

In order to associate the ASM disk in /dev/oracle directory with raw device, I made follofing script

#!/bin/ksh

DISK_ASM_DIR=/dev/oracle
DISK_OS_DIR=/dev/rdsk
DISK_OS_SCSI=${DISK_OS_DIR}/../../devices/scsi_vhci
DISK_OS_TYPE=brw #also crw

echo "ASM Disk        Device                                             Mj, Mn"
echo "--------------  -------------------------------------------------  ----------"
for _asm_disk_name in $(ls -l $DISK_ASM_DIR |grep -v total |awk '{print $(NF)}'); do
  asm_major_nr=$(ls -l ${DISK_ASM_DIR}/${_asm_disk_name} |awk 'BEGIN {FS=","}{print $1}'|awk '{print $NF}')
  asm_minor_nr=$(ls -l ${DISK_ASM_DIR}/${_asm_disk_name} |awk 'BEGIN {FS=","}{print $2}'|awk '{printf "%3s", $1}')
  scsi_disk=$(ls -l ${DISK_OS_SCSI} |grep ${asm_major_nr}|grep ${asm_minor_nr}|grep ^brw|sed 's/[@g|:| ]/,/g'|sed 's/,,*/,/g'|nawk -v amn="${asm_minor_nr}" 'BEGIN {FS=","} $6==amn {print $(NF-1)}')
  os_disk=$(ls /dev/rdsk |grep -i ${scsi_disk}|grep s0$)
  printf "%-15s %-50s [%-s,%4s]\n" $_asm_disk_name ${DISK_OS_DIR}/$os_disk ${asm_major_nr} ${asm_minor_nr}
done

This is an example of output

ASM Disk        Device                                             Mj, Mn
--------------  -------------------------------------------------  ----------
CRSOCR01        /dev/dsk/c5t600601605D102200355914322ACCDD11d0s0   [118, 480]
CRSVTG01        /dev/dsk/c5t600601605D102200345914322ACCDD11d0s0   [118, 488]
ORTST001        /dev/dsk/c5t600601605D1022006A5AB93EDDF9DD11d0s0   [118, 504]
ORTST002        /dev/dsk/c5t600601605D1022006B5AB93EDDF9DD11d0s0   [118, 496]
ORTST003        /dev/dsk/c5t600601605D1022006C5AB93EDDF9DD11d0s0   [118, 512]
WRADK001        /dev/dsk/c5t600601605D10220020BD730C29CCDD11d0s0   [118, 440]
WRADK002        /dev/dsk/c5t600601605D10220021BD730C29CCDD11d0s0   [118, 368]
WRADK003        /dev/dsk/c5t600601605D10220022BD730C29CCDD11d0s0   [118, 296]
WRADK004        /dev/dsk/c5t600601605D10220023BD730C29CCDD11d0s0   [118, 224]
WRADK005        /dev/dsk/c5t600601605D10220024BD730C29CCDD11d0s0   [118, 152]
WRADK006        /dev/dsk/c5t600601605D10220025BD730C29CCDD11d0s0   [118,  32]

ASH. Come un film (1/3)

Qui la versione Inglese

Nella versione 10g, Oracle ha introdotto l'"Intelligent Self-Management Infrastructure" [1]

L'idea di base di tale Infrastruttura è quella di raccogliere il maggior numero di informazioni con il minor impatto sul sistema. In base al principio di intederminazione di Heisenberg, sappiamo che osservando un fenomeno ne modifichiamo il comportamento. E questo ovviamente si applica anche all'rdbms [2].

Una delle componenti dell'infrastruttura è ASH.

In questo post vorrei mettere in evidenza un particolare punto di vista, basato su un idea di Kyle Hailey [3].

Sappiamo che ASH è basato su un campionamento effettuato ogni secondo. Questo ovviamente comporta dei buchi tra un'istantanea e l'altra. In raltà noi siamo interessatti solo a quelle sessioni che durano un certo periodo di tempo (soltanto le "sessioni lunghe" vengono analizzate: quelle di breve durata non hanno generalmente impatto sulle performance).
Così se una sessione impiega minuti prima di terminare, sono sicuro di osservare la sua attività, anche se questa non avviene in modo continuo ma solo ad intervalli di un secondo. E' come vedere un film a fotogrammi distanziati di un secondo.

(questa immagine è presa da [3])

Come sempre un'immagine vale più di mille parole. Quella qui sopra ci mostra come sia possibile determinare cosa stia succedendo anche se non ne conosciamo la storia istante per istante.

E la cosa più sbalorditiva è che il campionamento è quasi del tutto fedele al modello temporale. Per mostrare questo ho utilizzato una query [4] il cui risultato è il grafico in figura ottenuto con Microsoft Excell

Il grafico mostra la differenza tra misure campionate (ASH) e quelle ottenute in tempo reale (DB Time). In Y c'è il valore di "ASH"/"DB Time", mentre in X lo "snap_id/time stamp"

In realtà, per ottenere questo grafico, ho dovuto eliminare alcuni valori. Sto cercando ancora di capire quali sono le cause che producono tali anomalie.

• Update 31/Mar/2016

Ho modificato l'immagine perché ho eliminato un punto dak grafico. Sto scrivendo un post sulle anomalie che ho riscontrato.

• Riferimenti

[1] http://www.oracle.com/technetwork/database/manageability/twp-manage-intelligent-infrastructu-133531.pdf
[2] http://www.oracle.com/technetwork/database/manageability/twp-40169-134162.pdf
[3] https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history
http://blog.orapub.com/20150812/Which-Is-Better-Time-Model-Or-ASH-Data.html
[4] http://www.evernote.com/l/ABzQFwwMxo5HyKs8pmpIcyVjmjH7vreMvVo/

DB Time (2 of 3) (ENG)

Here the Italian version

DB Time (1 of 3)
DB Time (3 of 3) (soon)

DB Time = CPU Time + Wait Time

is the equation that ties the main components of the database:
- work time
- wait time

You can rewrite this equation as (you divide by DB Time and multiply by 100)

100 = (CPU Time/DB Time)*100 + (Wait Time/DB time)*100

This is the way the "AWR" shows us the "Top Timed Events". Let's see. The example that follows, is taken from one of the last tuning I made on a version of Oracle 10g

• AWR Report - 10.2.0.5 Version

 

  

 

DB Time = 440 mins = 26400 secs

log file switch (checkpoint incoplete)	11908 / 26400 => 0,45106 => 45,1%
gc buffer busy	5376 / 26400 => 0,20363636 => 20,4
PX Deq Credit: send blkd	1537 / 26400 => 0,058219696 => 5,8%
buffer busy waits	1358 / 26400 => 0,0514393939 => 5,1%

In later versions, the name of the column containing the percentage (second from right) "% DB Time"
 

• AWR Report - 11.2.0.4 Version

   

  

DB Time = 498 mins = 29880 secs

db file sequential read	2736 / 29880 => 0,09156 => 9,15%
db file scattered read	1753 / 29880 => 0,05866 => 5,86%
log file sync	1500 / 29880 => 0,05020 => 5,02%
row cache lock	1096 / 29880 => 0,03668 => 3,67%

• AWR Report - 12.1.0.2 Version

 

 

DB Time = 21 mins = 1260 secs

db file sequential read	57,5 / 1260 => 0,04563 => 4,56%
rdbms ipc reply	50,7 / 1270 => 0,04023 => 4,02%
DFS lock handle	18,2 / 1270 => 0,01433 => 1,43%
log file sync	13,6 / 1270 => 0,01070 => 1,07%

DB Time (1 of 3) DB Time (2 of 3) (soon)

DB Time (1 di 3) (ENG)

Here the Italian version

DB Time (2 of 3)
DB Time (3 of 3) (soon)

When you fight with tuning, you always have to deal with time. The end user, perceives all problems in term of response time. Time is the link from user experience with database performance.

In Oracle 10g, was introduced the “Intelligent Infrastructure”.

The goal of this infrastructure is:
- self-monitor
- self-diagnostic
- self-tuning

This was in 2004.

In this post and the next, I'm going to talk about 2 tools the Oracle “Intelligent Infrastructure” give us:
- DB Time
- ASH

DB Time

It is defined as the “amount of time spent within the database in processing an user request”.

It unit of measure is microseconds and you can get it directly from Data Dictionary. The views are:
- V$SYS_TIME_MODEL
- V$SESS_TIME_MODEL

When a process can't consume CPU, it have to wait in order to use CPU again. We know this wait as "wait event". In other words when a process can't use a CPU, it alert you that it need to wait and the reason of wait is the "wait event".

The important thing is that not always the waits impact the performance. If not, then we consider this waits as "Idle". Instead, if the wait have performance impact, then we consider the wait as “Non-Idle”.

So, an active process (that is, it's working) or consume CPU or wait on an Non-idle wait event.

You can write all of this, through a mathematical expression:

DB Time = CPU Time + Non-Idle Wait Time

We consider “Non-Idle” as obvious, so we rewrite the expression as:

DB Time = CPU Time + Wait Time

Really,  the right relation is:

DB Time = CPU Time + Wait Time + gap

This is because when the CPUs are too much active, then the “CPU Queue Time” grows, that is, grows the wait time of processes before they use a CPU again. Oracle calls this specific wait as “CPU Wait Time”. Obviously this is not happy name for a wait event.

You can find more details about "gap", using the below links (see references). In any case, here we are interested to the more usual expression:

DB Time = CPU Time + Wait Time

Reference

• What Is Light Green Oracle Database CPU Wait Time
• How OS CPU Run Queue Time Relates To Oracle Database DB Time
• Oracle Wait Events Investigated with Extended Stack Profiling and Flame Graphs
• AWR Ambiguity: Performance reasoning when the numbers don't add up

DB Time (2 of 3)

ASH: Introduction (Part 1 of 3)

Here, Italian version

There are tools that allow you a super fast sampling (10-100 times per second) using C programs. The goal of those tools is record information about a specific session. Really, in most case (the 99,9%) the monitoring can be done sampling 1 time per second, showing you both system load and system activity. This information could be difficult or too expansive to collect (there is also the problem that the tool impact the application itself) 

Most of the performance could be sampled, storing just the most important once and in a particular way: instead of to collect the exact statistics (information obtained instrumenting the C-code), ASH sample each second the status of all ACTIVE sessions (the session in "ACTIVE" state is a session executing a database call [1]) as well as the SQL that the session is running.

ASH stands for "Active Session History" and was introduced in Oracle 10g. The view of data dictionary is V$ACTIVE_SESSION_HISTORY. Over time the information collected from ASH have increased so release after release the columns of the view are grown in number. Following picture shows the change during the release

https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history/ash_across_versions.PNG?attredirects=0

References

• [1] Active Session = session currently running (spending time) in a database call (sessions with status=ACTIVE in v$session view)
• https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history

ASH: Introduzione (Parte 1 di 3)

Qui la versione inglese

Esistono tool che permettono un campionamento velocissimo (10-100 volte al secondo) utilizzando ad esempio programmi scritti in C, con lo scopo di raccogliere informazioni su una specifica sessione. In realtà nella maggioranza dei casi (il 99,9%) il monitoraggio può essere ottenuto con un semplice campionamento di 1 volta al secondo mostrando sia il carico sul sistema che informazioni sull'attività del sistema. Informazioni che altrimenti sarebbero troppo difficili o eccessivamente costose da collezionare (sorge anche il problema che un tool invasivo falsa il risultato finale dovuto a se stesso).

La maggior parte delle informazioni di perfornamce può essere collezionata non salvando ogni tipo di dato, ma soltatno quelle più importanti: invece di collezionare l'esatta statistica (informazione ottenuta con la "strumentazione" del codice, ovvero introducendo specifiche chiamate all'inrerno del codice C, verso il sistema operativo), ASH campiona ogni secondo lo stato di tutte le sessioni "ATTIVE" (per stato "ATTIVO" si intende una sessione che esegue una chiamata al database [1]) e l'SQL che quella sessione sta eseguendo.

ASH è un acronimo per "Active Session History" ed è stato introdotto in Oracle 10g. La vistadi riferimento del dizionario dati è V$ACTIVE_SESSION_HISTORY.
Nel tempo le informazioni raccolte in V$ASH sono aumentate e così release dopo release, la quantità di colonne in tale vista sono cresciute di numero. La seguente immagine mostra le veriazioni che si sono avute 


https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history/ash_across_versions.PNG?attredirects=0

Riferimenti

• [1] Active Session = sessioni attualmente in esecuzione (che consumano tempo) in una  "database call" (sessioni con "status"=ACTIVE in v$session view)

• https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history

lsamsdsk, AIX version (ENG)

In order to associate the ASM disk in /dev/oracle directory with raw device, I made follofing script

#!/bin/ksh

export DISK_ASM_DIR=/dev/oracle
export DISK_OS_DIR=/dev
export DISK_OS_TYPE=crw #also brw


echo "ASM Disk        Device               Mj,Mn"
echo "--------------  -----------------    ---------"
for _asm_disk_name in $(ls -l $DISK_ASM_DIR |grep -v total |awk '{print $(NF)}'); do
  asm_major_nr=$(ls -l ${DISK_ASM_DIR}/${_asm_disk_name} |awk 'BEGIN {FS=","}{print $1}'|awk '{print $NF}')
  asm_minor_nr=$(ls -l ${DISK_ASM_DIR}/${_asm_disk_name} |awk 'BEGIN {FS=","}{print $2}'|awk '{printf "%3s", $1}')
  os_disk=$(ls -l $DISK_OS_DIR |grep "${asm_major_nr},${asm_minor_nr}"|grep ^${DISK_OS_TYPE}|awk '{print $NF}')
 
  printf "%-15s %-20s [%-s,%3s]\n" $_asm_disk_name ${DISK_OS_DIR}/$os_disk ${asm_major_nr} ${asm_minor_nr}
done

This is an example of output

ASM Disk        Device               Mj,Mn
--------------  -----------------    ---------
DWHBOpDSK01     /dev/rhdisk93        [17, 92]
DWHBOpDSK02     /dev/rhdisk94        [17, 94]
DWHDBS.ocr      /dev/rhdisk4         [17,  4]
DWHDBS.ovd      /dev/rhdisk11        [17, 11]
DWHICTpDSK01    /dev/rhdisk58        [17, 57]
DWHICTpDSK02    /dev/rhdisk51        [17, 51]
DWHICTpDSK03    /dev/rhdisk59        [17, 59]
DWHICTpDSK04    /dev/rhdisk60        [17, 60]
DWHICTpDSK05    /dev/rhdisk52        [17, 52]
DWHICTpDSK06    /dev/rhdisk53        [17, 53]
DWHICTpDSK07    /dev/rhdisk54        [17, 54]
DWHICTpDSK08    /dev/rhdisk55        [17, 55]
DWHICTpDSK09    /dev/rhdisk61        [17, 62]
DWHICTpDSK10    /dev/rhdisk62        [17, 61]
DWHICTpDSK11    /dev/rhdisk56        [17, 56]
DWHICTpDSK12    /dev/rhdisk57        [17, 58]
DWHICTpDSK13    /dev/rhdisk63        [17, 63]
DWHICTpDSK14    /dev/rhdisk64        [17, 64]
DWHICTpDSK15    /dev/rhdisk116       [17,117]

The two numbers in parentheses are major and minor number respectively.

I checked this script on IBM AIX platform

cstat

There are several versions of "crsctl status resource" command. You can see the most cool version in [1].

In any case I made my personal output.

crsctl status resource  -v |\

      egrep -w "NAME|TYPE|TARGET|STATE|STATE_DETAILS"|\

      awk 'BEGIN {FS="=";

                    printf "%-35s %-20s %-25s %-10s %30s\n", "HA Resource", "Resource Type", "Target", "State", "Details";                    printf "%-73s \n", "---------------------------------   --------------       ---------------------     --------   --------------------------------------------------";}

                  {

switch ($1){
  case "NAME"         :  resname=$2; break;
  case "TYPE"         :  restyp=$2;  break;
  case "STATE"        :  resst=$2;   break
  case "TARGET"       :  restrg=$2;  break;
  case "STATE_DETAILS": {resdet=$2
    idx1=index(restyp , "."); subtyp=substr(restyp , idx1+1);
    idx2=index(subtyp, "."); typ=substr(subtyp, 1, idx2-1)
    # following if is added for grid 12.2
    if (typ=="database" && length($3)!=0) { resdet=resdet"="$3 }
    #
    switch (typ){
      case "appvipx"         : typ="App VIP";       break;
      case "diskgroup"       : typ="Diskgroup";     break;
      case "scan_listener"   : typ="SCAN Listener"; break;
      case "listener"        : typ="Listener";      break;
      case "asm"             : typ="ASM";           break;
      case "cvu"             : typ="CVU";           break;
      case "acfs"            : typ="ASM FS";        break;
      case "oc4j"            : typ="OC4J";          break;
      case "mgmtdb"          : typ="MGMT DB";       break;
      case "cluster_vip_net1": typ="Cluster VIP";   break;
      case "network"         : typ="Network (VIP)"; break;
      case "database"        : typ="Database";      break;
      case "ons"             : typ="Ora Notif Svc"; break;
      case "scan_vip"        : typ="SCAN VIP";      break;
      case "service"         : typ="Service";       break;
      case "volume"          : typ="Volume";        break;
      case "mgmtlsnr"        : typ="MGMT Listener"; break;
     }
     printf "%-35s %-20s %-25s %-10s %-10s\n", resname, typ , resst, restrg, resdet
                                  }; break; }

}'

Because of switch command, it works with awk version >= 4.0.2. If you are using an early version, then you have to change switch command with a if-then-else

crsctl status resource  -v |\
      egrep -w "NAME|TYPE|TARGET|STATE|STATE_DETAILS"|\
      /bin/gawk 'BEGIN {FS="=";
                    printf "%-35s %-20s %-25s %-10s %30s\n", "HA Resource", "Resource Type", "Target", "State", "Details";
                    printf "%-73s \n", "---------------------------------   --------------       ---------------------     --------   --------------------------------------------------";}
                  {
if ($1=="NAME")  resname=$2; else
if ($1=="TYPE")   restyp=$2; else
if ($1=="STATE")   resst=$2; else
if ($1=="TARGET") restrg=$2; else
if ($1=="STATE_DETAILS") { resdet=$2
    idx1=index(restyp , "."); subtyp=substr(restyp , idx1+1);
    idx2=index(subtyp, "."); typ=substr(subtyp, 1, idx2-1);
    # following if is added for grid 12.2
    if (typ=="database" && length($3)!=0) { resdet=resdet"="$3 }
    #
      if (typ=="appvipx" )               typ="App VIP"; else
      if (typ=="diskgroup " )          typ="Diskgroup"; else
      if (typ=="scan_listener " ) typ="SCAN Listener "; else
      if (typ=="listener" )             typ="Listener"; else
      if (typ=="asm" )                   typ="App VIP"; else
      if (typ=="cvu")                        typ="CVU"; else
      if (typ=="acfs")                    typ="ASM FS"; else
      if (typ=="oc4j")                      typ="OC4J"; else
      if (typ=="mgmtdb")                 typ="MGMT DB"; else
      if (typ=="cluster_vip_net1")   typ="Cluster VIP"; else
      if (typ=="network")          typ="Network (VIP)"; else
      if (typ=="database" )             typ="Database"; else
      if (typ=="ons")              typ="Ora Notif Svc"; else
      if (typ=="scan_vip" )             typ="SCAN VIP"; else
      if (typ=="service")                typ="Service"; else
      if (typ=="volume")                  typ="Volume"; else
      if (typ=="mgmtlsnr")         typ="MGMT Listener";
        printf "%-35s %-20s %-25s %-10s %-10s\n", resname, typ , resst, restrg, resdet}
}'

I checked this commands on Linux.


Following there is an output

HA Resource                         Resource Type        Target                    State                             Details
---------------------------------   --------------       ---------------------     --------   --------------------------------------------------
bck.dbCUSCTIp-bk.vip                App VIP              ONLINE on mdapora1d02     ONLINE
ora.DGCUSCTIPDB01.dg                Diskgroup            ONLINE on mdapora1d01     ONLINE
ora.DGCUSCTIPDB01.dg                Diskgroup            ONLINE on mdapora1d02     ONLINE
ora.DGCUSCTIPFS.LVCUSCTIPAK.advm    Volume               ONLINE on mdapora1d01     ONLINE     Volume device /dev/asm/lvcusctipak-156 is online
ora.DGCUSCTIPFS.LVCUSCTIPAK.advm    Volume               ONLINE on mdapora1d02     ONLINE     Volume device /dev/asm/lvcusctipak-156 is online
ora.LISTENER.lsnr                   Listener             ONLINE on mdapora1d01     ONLINE
ora.LISTENER.lsnr                   Listener             ONLINE on mdapora1d02     ONLINE
ora.LISTENER_CUSTCTIP.lsnr          Listener             ONLINE on mdapora1d01     ONLINE
ora.LISTENER_CUSTCTIP.lsnr          Listener             ONLINE on mdapora1d02     ONLINE
ora.LISTENER_SCAN1.lsnr             SCAN Listener        ONLINE on mdapora1d01     ONLINE
ora.LISTENER_SCAN2.lsnr             SCAN Listener        ONLINE on mdapora1d02     ONLINE
ora.LISTENER_SCAN3.lsnr             SCAN Listener        ONLINE on mdapora1d02     ONLINE
ora.MGMTLSNR                        MGMT Listener        ONLINE on mdapora1d02     ONLINE     169.254.62.41 10.52.254.20
ora.asm                             App VIP              ONLINE on mdapora1d01     ONLINE     Started
ora.asm                             App VIP              ONLINE on mdapora1d02     ONLINE     Started
ora.cusctip.db                      Database             ONLINE on mdapora1d01     ONLINE     Open
ora.cusctip.db                      Database             ONLINE on mdapora1d02     ONLINE     Open
ora.cvu                             CVU                  OFFLINE                   OFFLINE
ora.dggenmmpfs.lvgenmmpak.acfs      ASM FS               ONLINE on mdapora1d01     ONLINE     mounted on /dbGENMMp/akGENMMp01
ora.dggenmmpfs.lvgenmmpak.acfs      ASM FS               ONLINE on mdapora1d02     ONLINE     mounted on /dbGENMMp/akGENMMp01
ora.gencmpp.gencmpp_dem1.svc        Service              ONLINE on mdapora1d02     ONLINE
ora.gencmpp.gencmpp_dem1.svc        Service              ONLINE on mdapora1d01     ONLINE
ora.mdapora1d01.vip                 Cluster VIP          ONLINE on mdapora1d01     ONLINE
ora.mdapora1d02.vip                 Cluster VIP          ONLINE on mdapora1d02     ONLINE
ora.mdmcrp.db                       Database             ONLINE on mdapora1d01     ONLINE     Open
ora.mdmcrp.db                       Database             ONLINE on mdapora1d02     ONLINE     Open
ora.mgmtdb                          MGMT DB              ONLINE on mdapora1d02     ONLINE     Open
ora.net1.network                    Network (VIP)        ONLINE on mdapora1d01     ONLINE
ora.net1.network                    Network (VIP)        ONLINE on mdapora1d02     ONLINE
ora.net2.network                    Network (VIP)        ONLINE on mdapora1d01     ONLINE
ora.net2.network                    Network (VIP)        ONLINE on mdapora1d02     ONLINE
ora.oc4j                            OC4J                 ONLINE on mdapora1d02     ONLINE
ora.ons                             Ora Notif Svc        ONLINE on mdapora1d01     ONLINE
ora.ons                             Ora Notif Svc        ONLINE on mdapora1d02     ONLINE
ora.scan1.vip                       SCAN VIP             ONLINE on mdapora1d01     ONLINE
ora.scan2.vip                       SCAN VIP             ONLINE on mdapora1d02     ONLINE
ora.scan3.vip                       SCAN VIP             ONLINE on mdapora1d02     ONLINE
ora.sdcp.db                         Database             ONLINE on mdapora1d01     ONLINE     Open
ora.sdcp.db                         Database             OFFLINE                   OFFLINE

[1] http://blog.enkitec.com/2011/10/my-crsstat-script-improved-formatting-of-crs_stat-on-10g-and-11g/

Update

2017/08/08 - Oracle Grid 12cR2 show some differences with the prvious release.

This is from Grid 12.1.0.2 on a Linux RAC 2 nodes

ora.mdmcrp.db                       Database             OFFLINE                  OFFLINE    Instance Shutdown
ora.mdmcrp.db                       Database             ONLINE on srvora1d02     ONLINE     Open
ora.sdcp.db                         Database             ONLINE on srvora1d01     ONLINE     Open
ora.sdcp.db                         Database             OFFLINE                  OFFLINE    Open

This is from a Grid 12.2.0.1 on Linux Oracle Restart:

ora.fosi.db                         Database             OFFLINE                  OFFLINE    Instance Shutdown
ora.pcii.db                         Database             ONLINE on srvora2d       ONLINE     Open,HOME=/sbRdbms/app/oracle/product/12.1.0.2/dbh01

The new version of Oracle Grid, show now the ORACLE_HOME with which the database is started. So I modified the above script