Oracle 监控-白红宇

Oracle 监控

阅读量：5973 次

发布时间：2019-06-19

本文共 6033 字，大约阅读时间需要 20 分钟。

cat oracle_status_output.py

from prometheus_client import Gauge,start_http_server

import random

import subprocess

import time

import cx_Oracle

class Oracle_Status_Output:

def __init__(self,db_name,db_password,db_tns):

try:

self.db = cx_Oracle.connect(db_name,db_password,db_tns)

self.cursor = self.db.cursor()

except Exception as e:

print('Wrong')

print(e)

def oracle_status_select(self,sql):

try:

self.cursor.execute(sql)

v_result=self.cursor.fetchall()

return v_result

except Exception as e:

print(e)

def close(self):

self.db.close()

if __name__ == "__main__":

start_http_server(9300)

#session

session = Gauge('session','Description of gauge', ['mylabelname'])

process = Gauge('process','Description of gauge', ['mylabelname'])

#tablespace

tablespace_used = Gauge('tablespace_used','Description of gauge', ['mylabelname'])

#wait_classes

wait_class = Gauge('wait_class','Description of gauge', ['mylabelname'])

#sga_info

sga_info = Gauge('sga_info','Description of gauge', ['mylabelname'])

#hits_info

hits_info = Gauge('hits_info','Description of gauge', ['mylabelname'])

#archive_log_size

archive_log_info = Gauge('archive_log_info','Description of gauge', ['mylabelname'])

#Oracle lock

lock_info = Gauge('lock_info','Description of gauge', ['mylabelname'])

#redo_info

redo_info = Gauge('redo_info','Description of gauge', ['mylabelname'])

#net_info

net_info = Gauge('net_info','Description of gauge', ['mylabelname'])

print('Prometheus Gauge Data type initialization ok')

while True:

#time.sleep(0.5)

pro_db = Oracle_Status_Output('dbadmin','QazWsx12','localhost:1521/paydb')

v_session=pro_db.oracle_status_select("select initcap(s.TYPE)||'_'||initcap(s.STATUS) status,count(s.STATUS) from v$session s group by s.TYPE,s.STATUS order by status")

v_process=pro_db.oracle_status_select("select 'Process_Count',count(1) from v$process")

v_tablespace=pro_db.oracle_status_select("SELECT x.tablespace_name||'(%)',x.USED_RATE FROM (select * from (select a.TABLESPACE_NAME TABLESPACE_NAME,ROUND((a.total-b.free_space)/a.total*100,2) as USED_RATE from (select TABLESPACE_NAME,sum(bytes/1024/1024) total from dba_data_files group by TABLESPACE_NAME) a,(select TABLESPACE_NAME,sum(bytes/1024/1024) free_space from dba_free_space group by tablespace_name) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME) UNION all select * from (select c.TABLESPACE_NAME TABLESPACE_NAME,ROUND((c.total-d.free_space)/c.total*100,2) as USED_RATE from (select TABLESPACE_NAME,sum(bytes/1024/1024) total from dba_temp_files group by TABLESPACE_NAME) c,(select TABLESPACE_NAME,sum(FREE_SPACE/1024/1024) free_space from dba_temp_free_space group by tablespace_name)d where c.TABLESPACE_NAME=d.TABLESPACE_NAME)) X order by USED_RATE desc")

v_wait_class=pro_db.oracle_status_select("SELECT s.WAIT_CLASS,sum(s.TIME_WAITED/1000) FROM v$system_event s group by s.WAIT_CLASS order by s.WAIT_CLASS")

v_shared_pool=pro_db.oracle_status_select("select sg.NAME||'(GB)',trunc(sg.BYTES/1024/1024/1024,2) from v$sgainfo sg where sg.NAME in ('Buffer Cache Size','Shared Pool Size') order by sg.name")

v_shared_pool_free_size=pro_db.oracle_status_select("SELECT 'shared_pool_free'||'(GB)',trunc(sp.BYTES/1024/1024/1024,2) FROM V$SGASTAT sp WHERE NAME = 'free memory' and pool='shared pool'")

v_library_cache_hits=pro_db.oracle_status_select("select 'Library_Cache_Hits'||'(%)',trunc(sum(ly.PINHITS)/sum(ly.PINS)*100,2) from v$librarycache ly")

v_buffer_pool_cache_hits=pro_db.oracle_status_select("SELECT 'Buffer_Pool_Cache_Hits'||'(%)',(trunc(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)),2))*100 FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT'")

v_sql_hard_prse_hit=pro_db.oracle_status_select("select 'Hard_Parse_Hits'||'(%)',trunc((select st.VALUE from v$sysstat st where st.NAME='parse count (hard)')/(select st.VALUE from v$sysstat st where st.NAME='parse count (total)') *100,2) from dual")

v_archive_log_info=pro_db.oracle_status_select("select 'Archive_Log_Size' || '(MB)',case when trunc(sum(s.BLOCKS * s.BLOCK_SIZE)/1024/1024, 2) is null then 0 else trunc(sum(s.BLOCKS * s.BLOCK_SIZE)/1024/1024,2) end from v$archived_log s where s.DEST_ID = 1 and s.COMPLETION_TIME >= trunc(sysdate) and s.COMPLETION_TIME < trunc(sysdate + 1)")

v_archive_log_count=pro_db.oracle_status_select("select 'Archive_Log_Count',count(1) from v$archived_log s where s.DEST_ID = 1 and s.COMPLETION_TIME >= trunc(sysdate) and s.COMPLETION_TIME < trunc(sysdate + 1)")

v_locke_count = pro_db.oracle_status_select("select 'Lock_Count', count(1) from v$session s where s.STATUS='ACTIVE' AND s.LOCKWAIT is not null and s.BLOCKING_SESSION_STATUS='VALID'")

v_redo_info = pro_db.oracle_status_select("SELECT initcap(NAME), VALUE FROM V$SYSSTAT where NAME in ('redo entries','redo buffer allocation retries')")

v_net_info = pro_db.oracle_status_select("SELECT initcap(NAME)||'(KB)',round(VALUE/1024,2) FROM V$SYSSTAT where NAME in ('bytes received via SQL*Net from client','bytes sent via SQL*Net to client')")

print('Object initialization success ')

pro_db.close()

v_status=()

v_status_name=()

def ResultOutput(v_status,v_status_name):

for i in range(int(len(v_status))):

v_status_name.labels(mylabelname=v_status[i][0]).set(v_status[i][1])

try:

ResultOutput(v_session,session)

ResultOutput(v_process,process)

ResultOutput(v_tablespace,tablespace_used)

ResultOutput(v_wait_class,wait_class)