Oracle 监控

发布时间：2020-12-12 13:12:33 所属栏目：百科来源：网络整理

导读：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: ????

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_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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!