结合上次博文关于php连接oracle数据库，本次结合rrdtool绘图工具，绘制一些自定义的监控图，虽然oracle的EM已经很完美了，但是往往根据业务需求对一些自定义或者说业务环境关心的问题无法监控，此时我们就需要自己来搞定了。

首先用php写一个rrdtool数据库文件的生产脚本

  1. #!/usr/bin/php -f  
  2. <?php                                           
  3.    
  4. define('WD','/u01/app/oracle/awr');  
  5. $cs         = $_SERVER['argv'][1];  
  6. $user       = 'system';  
  7. $pass       = 'oracle';   
  8.    
  9. /* open a new connection */  
  10. $ds = oci_connect($user, $pass, $cs)  
  11.         or die ("Cannot connect to Oracle Database ".$cs."\n");  
  12.    
  13. /* setting client nls environment */  
  14. $sql = "alter session set nls_timestamp_format='MM/DD/YY HH24:MI'";  
  15. $stmt = oci_parse($ds, $sql);  
  16. oci_execute($stmt);  
  17. oci_free_statement($stmt);                                           
  18.    
  19. /* create directory that will contain rrds (if not exists) */  
  20. if(!file_exists(WD.'/'.$cs))  
  21.                 mkdir(WD.'/'.$cs);  
  22. if(!file_exists(WD.'/'.$cs.'/wait'))  
  23.                 mkdir(WD.'/'.$cs.'/wait');                     
  24.    
  25. /* function to create new RRDs */  
  26. function createRRD($name, $interval, $cs) {  
  27.         $hb = $interval*5; //heartbeat  
  28.         $cmd="rrdtool create ".WD."/".$cs."/wait/${name}.rrd -s ".$interval." \  
  29.                 -b \"now -3month\" DS:waits:DERIVE:$hb:0:U \  
  30.                 DS:mswaited:DERIVE:$hb:0:U \  
  31.                 RRA:AVERAGE:0.5:1:1440 RRA:AVERAGE:0.5:30:336 \  
  32.                 RRA:AVERAGE:0.5:120:372 RRA:AVERAGE:0.5:720:730 \  
  33.                 RRA:MIN:0.5:1:1440 RRA:MIN:0.5:30:336 \  
  34.                 RRA:MIN:0.5:120:372 RRA:MIN:0.5:720:730 \  
  35.                 RRA:MAX:0.5:1:1440 RRA:MAX:0.5:30:336 \  
  36.                 RRA:MAX:0.5:120:372 RRA:MAX:0.5:720:730 \  
  37.                 RRA:LAST:0.5:1:1440";  
  38.         //print $cmd."\n";  
  39.         return passthru($cmd);  
  40. }                                                                                
  41.    
  42. /* take the snapshot frequency from dba_hist_wr_control  
  43.  to create the RDD with correct heartbeat value */  
  44. $sql = 'select extract(hour from snap_interval)*3600 +  
  45. extract(minute from snap_interval)*60 as SEED from DBA_HIST_WR_CONTROL';  
  46. $stmt = oci_parse($ds, $sql);  
  47. oci_execute($stmt);  
  48. $row = oci_fetch_assoc($stmt);  
  49. $interval = $row['SEED'];  
  50. unset($row);  
  51. oci_free_statement($stmt);                                                
  52.    
  53. /* statement definition that will collect  
  54.  all snapshots for a certain wait event with more than  
  55.  a certain amonut of time waited.  
  56.  Gathering ALL EVENTS could be time consuming and useless.  
  57.  I fetch rows ordered by event_name rather  
  58.  then by date because I can update many values  
  59.  into the same rrd with very few rrdupdate commands  
  60. */  
  61. $sql = 'select s.END_INTERVAL_TIME END_INTERVAL_TIME,  
  62.     g.EVENT_NAME, g.WAIT_CLASS, g.TOTAL_WAITS,  
  63.     round(g.TIME_WAITED_MICRO/1000) MS  
  64.   from DBA_HIST_SNAPSHOT s,  
  65.    dba_hist_bg_event_summary g,  
  66.    v$instance i  
  67.  where s.SNAP_ID=g.SNAP_ID and g.wait_class!=\'Idle\'  
  68.   and g.TIME_WAITED_MICRO&gt;100000  
  69.   and s.instance_number=i.instance_number  
  70.   and s.instance_number=g.instance_number  
  71.  order by 2,1';                                        
  72.    
  73. /* default prefetch size (148) matches default snapshot retention (24hx7dd) */  
  74. $stmt = oci_parse($ds, $sql);  
  75. oci_set_prefetch($stmt, 148);  
  76. oci_execute($stmt);  
  77.    
  78. $i=0;  
  79. $oldevent="";  
  80. while ($row = oci_fetch_assoc($stmt)) {  
  81.         if ($oldevent != $row['EVENT_NAME']) {  
  82.                 //NEW EVENT DETECTED: WILL START A NEW UPDATE CMD  
  83.                 if ($i != 0 &amp;&amp; !empty($cmd)) {  
  84.                         /* not the first occurrence,  
  85.                          I bet there's something in my buffer */  
  86.                         passthru($cmd);  
  87.                 }  
  88.                 $cleanName = preg_replace ("([^[:alnum:]_-])","_",$row['EVENT_NAME']);  
  89.                 // if there is no rrd for this event, I create a new one  
  90.                 if (!file_exists(WD."/".$cs."/wait/${cleanName}.rrd")) {  
  91.                         createRRD($cleanName, $interval, $cs);  
  92.                 }  
  93.                 /*  
  94.                 * I initialize a new update command. This string act as a buffer: I append many  
  95.                 * values to be updated so I'll update many values in a single command line:  
  96.                 * less forks of rrdtool and less file opens: the whole update process has an  
  97.                 * enormous improvement.  
  98.                 */  
  99.                 $precmd="rrdtool update ".WD."/".$cs."/wait/${cleanName}.rrd ";  
  100.                 $lastcmd="rrdtool info ".WD."/".$cs."/wait/${cleanName}.rrd".  
  101.                         "| grep last_update | awk '{print \$NF}'";  
  102.                 $last=trim(`$lastcmd`);  
  103.                 printf ("%s - %s - last: %d\n", $row['EVENT_NAME'], $cleanName, $last);  
  104.                 $i=0;  
  105.                 $cmd=$precmd;  
  106.                 $oldevent=$row['EVENT_NAME'];  
  107.         }  
  108.         $time=strtotime($row['END_INTERVAL_TIME']);  
  109.         //print "time: ".$time."  last: ".$last."\n";  
  110.         if ( $time &gt; $last ) {  
  111.                 $cmd.=" ".$time.":".$row['TOTAL_WAITS'].":".$row['MS'];  
  112.                 $i++;  
  113.         }  
  114.         if ($i &gt;= 40) {  
  115.                 // when I reach 40 values per commandline I force  
  116.                 // the update: next loop will reinitialize a new commandline.  
  117.                 passthru($cmd);  
  118.                 $cmd=$precmd;  
  119.                 $i=0;  
  120.         }  
  121.         unset($row);  
  122.    
  123. }  
  124. if ($i != 0) {  
  125.         /* one more update pending in my buffer */  
  126.         passthru($cmd);  
  127. }  
  128. oci_free_statement($stmt);  
  129. oci_close($ds);  
  130. ?>

通过运行以上脚本可以生成相关rrd文件 

  1. # ll  
  2. total 3864  
  3. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 Streams_AQ__enqueue_blocked_on_low_memory.rrd  
  4. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 buffer_busy_waits.rrd  
  5. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 control_file_parallel_write.rrd  
  6. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 control_file_sequential_read.rrd  
  7. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 cursor__pin_S_wait_on_X.rrd  
  8. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 db_file_scattered_read.rrd  
  9. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 db_file_sequential_read.rrd  
  10. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 events_in_waitclass_Other.rrd  
  11. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__cache_buffers_chains.rrd  
  12. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__library_cache.rrd  
  13. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__library_cache_lock.rrd  
  14. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__redo_writing.rrd  
  15. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__row_cache_objects.rrd  
  16. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__shared_pool.rrd  
  17. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 library_cache_load_lock.rrd  
  18. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 library_cache_lock.rrd  
  19. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_buffer_space.rrd  
  20. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_parallel_write.rrd  
  21. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sequential_read.rrd  
  22. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_single_write.rrd  
  23. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_switch_completion.rrd  
  24. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sync.rrd  
  25. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 os_thread_startup.rrd 

使用rrdtool命令绘图如下

使用rrdtool自定义绘图监控Oracle数据库_rrdtool