Oracle 监控

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)
            ResultOutput(v_shared_pool,sga_info)
            ResultOutput(v_shared_pool_free_size,sga_info)
            ResultOutput(v_library_cache_hits,hits_info)
            ResultOutput(v_buffer_pool_cache_hits,hits_info)
            ResultOutput(v_sql_hard_prse_hit,hits_info)
            ResultOutput(v_archive_log_info,archive_log_info)
            ResultOutput(v_archive_log_count,archive_log_info)
            ResultOutput(v_locke_count,lock_info)
            ResultOutput(v_redo_info,redo_info)
            ResultOutput(v_net_info,net_info)
            pro_db.close()
        except:
            pass
        print(time.strftime("%Y%m%d %H:%M:%S"),'------ Successful capture of monitoring data')
        print('')
        time.sleep(0.3)
        if False:
            print('False==================================================')
            break