cat oracle_status_output.py
from prometheus_client import Gauge,start_http_server
import random
import subprocess
import time
import cx_Oracle
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_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‘,[‘mylabelname‘]) #tablespace tablespace_used = Gauge(‘tablespace_used‘,[‘mylabelname‘]) #wait_classes wait_class = Gauge(‘wait_class‘,[‘mylabelname‘]) #sga_info sga_info = Gauge(‘sga_info‘,[‘mylabelname‘]) #hits_info hits_info = Gauge(‘hits_info‘,[‘mylabelname‘]) #archive_log_size archive_log_info = Gauge(‘archive_log_info‘,[‘mylabelname‘]) #Oracle lock lock_info = Gauge(‘lock_info‘,[‘mylabelname‘]) #redo_info redo_info = Gauge(‘redo_info‘,[‘mylabelname‘]) #net_info net_info = Gauge(‘net_info‘,[‘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,sum(bytes/1024/1024) total from dba_temp_files group by TABLESPACE_NAME) c,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
