def ApiOracleSnapList(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
start_time = request.query_params.get('start_time', None)
end_time = request.query_params.get('end_time', None)
if not (start_time and end_time):
# default data of 1 day
end_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
start_time = (
datetime.datetime.now() +
datetime.timedelta(days=-1)).strftime('%Y-%m-%d %H:%M:%S')
sql = """
SELECT dbid,
to_char(s.startup_time, 'yyyy-mm-dd hh24:mi:ss') snap_startup_time,
to_char(s.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss') begin_interval_time,
to_char(s.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_interval_time,
s.snap_id, s.instance_number,
(cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*86400 as span_in_second
from dba_hist_snapshot s, v$instance b
where s.end_interval_time >= to_date('{}','yyyy-mm-dd hh24:mi:ss') and s.end_interval_time <= to_date('{}','yyyy-mm-dd hh24:mi:ss')
and s.INSTANCE_NUMBER = b.INSTANCE_NUMBER order by snap_id
""".format(start_time, end_time)
snap_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleSnapListSerializer(snap_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleRedoLogSwitch(request):
tags = request.GET.get('tags')
redo_range = request.GET.get('redo_range')
oracle_params = get_oracle_params(tags)
if int(redo_range) == 1:
sql = '''
select 'hh'||to_char(first_time, 'hh24') stat_date,
count(1) log_count,
(select bytes / 1024 / 1024 sizem from v$log where rownum < 2) log_size
from v$log_history
where to_char(first_time, 'yyyymmdd') < to_char(sysdate, 'yyyymmdd')
and to_char(first_time, 'yyyymmdd') >=
to_char(sysdate - 1, 'yyyymmdd')
group by to_char(first_time, 'hh24'),to_char(first_time, 'dy')
order by to_char(first_time, 'hh24')'''
else:
sql = '''
select to_char(first_time, 'yyyy-mm-dd') stat_date,
count(1) log_count,
(select bytes / 1024 / 1024 sizem from v$log where rownum < 2) log_size
from v$log_history
where to_char(first_time, 'yyyymmdd') < to_char(sysdate, 'yyyymmdd')
and to_char(first_time, 'yyyymmdd') >= to_char(sysdate - {}, 'yyyymmdd')
group by to_char(first_time, 'yyyy-mm-dd'), to_char(first_time, 'dy')
order by to_char(first_time, 'yyyy-mm-dd')
'''.format(redo_range)
redoswitch_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleRedoSwitchSerializer(redoswitch_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleBlockingSession(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''
select to_char(a.logon_time, 'yyyy-mm-dd hh24:mi') logon_time,
a.sid,
a.sql_id,
a.event,
a.blocking_session,
a.username,
a.osuser,
a.machine,
a.program,
a.module,
b.sql_text,
c.owner,
c.object_name,
c.object_type
from v$session a, v$sql b, dba_objects c, v$process p
where a.state in ('WAITING')
and a.wait_class != 'Idle'
and p.addr = a.paddr
and a.sql_id = b.sql_id(+)
and a.sql_child_number = b.CHILD_NUMBER(+)
and a.row_wait_obj# = c.object_id(+)
'''
blocking_session_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleBlockingSession(blocking_session_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def create_oracle_report(tags, oracle_params, report_type, begin_snap,
end_snap):
print('task begin!')
# db_conn = OracleBase(oracle_params).connection()
db_conn_cdb = OracleBase(oracle_params).connection_cdb()
oracle_report = OracleReport(db_conn_cdb, tags, oracle_params)
oracle_report.get_report(report_type, begin_snap, end_snap)
def ApiOracleUserGrant(request):
tags = request.GET.get('tags')
user = request.GET.get('user')
oracle_params = get_oracle_params(tags)
sql = "select grantee,privilege,admin_option from dba_sys_privs where grantee = '{}' ".format(user)
grant_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleUserGrantSerializer(grant_list,many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleUserRole(request):
tags = request.GET.get('tags')
user = request.GET.get('user')
oracle_params = get_oracle_params(tags)
sql = "select grantee,granted_role, admin_option,default_role from dba_role_privs where grantee = '{}' ".format(user)
role_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleUserRoleSerializer(role_list,many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleUndoTableSpaceUsed(request):
tags = request.GET.get('tags')
undotablespace_name = request.GET.get('undotablespace_name')
oracle_params = get_oracle_params(tags)
sql = "select tablespace_name, status, sum(bytes) / 1024 / 1024 MB from dba_undo_extents " \
"where tablespace_name = '{}' group by tablespace_name, status".format(undotablespace_name)
undoused_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleUndoTableSpaceUsedSerializer(undoused_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleTopSql(request):
tags = request.GET.get('tags')
type = request.GET.get('type')
oracle_params = get_oracle_params(tags)
dic_proc = {
'cpu': 'pro_top_cpu_sql',
'phys': 'pro_top_phys_sql',
'logic': 'pro_top_logic_sql'
}
proc_name = dic_proc.get(type)
db_conn = OracleBase(oracle_params).connection()
OracleBase(oracle_params).call_proc(proc_name, db_conn)
sql = "select COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12 from snap_show_config " \
"union all " \
"select RATE,SQL_ID,SQL_EXEC_CNT,VAL1,VAL2,VAL3,VAL4,VAL5,VAL6,VAL7,VAL8,VAL9 from snap_show"
res = OracleBase(oracle_params).django_query(sql, db_conn)
serializer = OracleTopSql(res, many=True)
snap_json = JSONRenderer().render(serializer.data)
return HttpResponse(snap_json)
def ApiOracleTableSpaceLargeObject(request):
tags = request.GET.get('tags')
tablespace_name = request.GET.get('tablespace_name')
oracle_params = get_oracle_params(tags)
sql = "select owner, segment_name, round(sum(bytes) / 1024 / 1024 ,2) gbytes from " \
"dba_segments where tablespace_name = '{}' " \
"group by owner, segment_name having sum(bytes) / 1024 / 1024 > 10".format(tablespace_name)
largeobject_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleTableSpaceLargeObjectSerializer(largeobject_list,many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleTempTableSpaceSessionUsed(request):
tags = request.GET.get('tags')
temptablespace_name = request.GET.get('temptablespace_name')
oracle_params = get_oracle_params(tags)
sql = "SELECT S.sid ,S.username, S.osuser, P.spid, S.module,S.program,SUM(T.blocks) * TBS.block_size / 1024/1024 mb_used," \
"T.tablespace,COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P " \
"WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name AND T.tablespace='{}' " \
"GROUP BY S.sid,S.serial#,S.username,S.osuser,P.spid,S.module,S.program,TBS.block_size,T.tablespace".format(temptablespace_name)
sessionused_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleTempTableSpaceSessionUsedSerializer(sessionused_list,many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleProfile(request):
tags = request.GET.get('tags')
profile = request.GET.get('profile')
oracle_params = get_oracle_params(tags)
sql = """
select profile,resource_name,resource_type,limit
from dba_profiles where profile = '{}'
""".format(profile)
profile_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleProfileSerializer(profile_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleUser(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''select username,profile,to_char(created,'yyyy-mm-dd hh24:mi:ss') created,
account_status,
to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') lock_date,
to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') expiry_date,
default_tablespace,temporary_tablespace
from dba_users order by created desc '''
user_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleUserSerializer(user_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleTableSpaceDayUsed(request):
tags = request.GET.get('tags')
tablespace_name = request.GET.get('tablespace_name')
oracle_params = get_oracle_params(tags)
sql = "select to_char(trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'dd'),'yyyy-mm-dd') m_date,b.name," \
"round(sum(tablespace_usedsize) / 1024 / 1024) used_mb " \
"from dba_hist_tbspc_space_usage a, v$tablespace b " \
"where a.tablespace_id = b.ts# and b.name = '{}' " \
"and to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')>sysdate-6 " \
"group by to_char(trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'dd'),'yyyy-mm-dd'),b.name".format(tablespace_name)
dayused_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleTableSpaceDayUsedSerializer(dayused_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleUndoTableSpaceSessionUsed(request):
tags = request.GET.get('tags')
undotablespace_name = request.GET.get('undotablespace_name')
oracle_params = get_oracle_params(tags)
sql = "SELECT r.name rbs,nvl(s.username, 'None') oracle_user,s.osuser client_user,p.username unix_user,s.program,s.sid," \
"s.serial#,p.spid unix_pid,t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024 as undo_mb," \
"TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,TO_CHAR(sysdate - (s.last_call_et) / 86400, 'mm/dd/yy hh24:mi:ss') as last_txn," \
"t.START_TIME transaction_starttime " \
"FROM v$process p,v$rollname r,v$session s,v$transaction t,v$parameter x" \
" WHERE s.taddr = t.addr AND s.paddr = p.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size' ".format(undotablespace_name)
sessionused_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleUndoTableSpaceSessionUsedSerializer(sessionused_list,many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleSequenceUsed(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,
cache_size,last_number,
round((max_value - last_number) / (max_value - min_value), 2) * 100 pct_used
from dba_sequences s
where s.sequence_owner not in ('SYS','SYSTEM','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS',
'CTXSYS','ANONYMOUS','XDB','XS$NULL','ORDDATA','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDSYS','MDSYS','OLAPSYS',
'MDDATA','SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','APEX_030200','FLOWS_FILES',
'APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT','SCOTT') '''
sequence_used_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleSequenceUsedSerializer(sequence_used_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleBlockCount(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''
select event, count(1) cnt
from v$session
where wait_class != 'Idle'
group by event
'''
block_all = OracleBase(oracle_params).django_query(sql)
row_lock_count = sum([each['CNT'] for each in block_all if each['EVENT']=='enq: TX - row lock contention'])
all_block_count = sum([each['CNT'] for each in block_all])
block_count_data = {'ROW_LOCK':row_lock_count,'ALL':all_block_count}
return HttpResponse(json.dumps(block_count_data))
def ApiOracleSessionCount(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''
select a.status,count(*) cnt from v$session a where a.type='USER' group by a.status
'''
session_all = OracleBase(oracle_params).django_query(sql)
active_session_count = 0
inactive_session_count = 0
for each in session_all:
if each['STATUS'] == 'ACTIVE':
active_session_count = each['CNT']
if each['STATUS'] == 'INACTIVE':
inactive_session_count = each['CNT']
session_count_data = {'ACTIVE':active_session_count,'INACTIVE':inactive_session_count}
return HttpResponse(json.dumps(session_count_data))
def ApiOracleTopSegment(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''
select *
from (select a.owner,
a.segment_name,
a.partition_name,
a.segment_type,
a.tablespace_name,
a.bytes / 1024 / 1024 segment_size,
row_number() over(order by a.bytes desc) RN
from dba_segments a)
where rn <= 50
'''
top_segment_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleTopSegmentSerializer(top_segment_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def ApiOracleActiveSession(request):
tags = request.GET.get('tags')
oracle_params = get_oracle_params(tags)
sql = '''
select to_char(a.logon_time, 'yyyy-mm-dd hh24:mi') logon_time,
a.sql_id,
a.event,
a.blocking_session,
a.username,
a.osuser,
a.process,
a.machine,
a.program,
a.module,
b.sql_text,
b.LAST_LOAD_TIME,
to_char(b.last_active_time, 'yyyy-mm-dd hh24:mi:ss') last_active_time,
c.owner,
c.object_name,
a.last_call_et,
a.sid,
a.SQL_CHILD_NUMBER,
c.object_type,
p.PGA_ALLOC_MEM,
a.p1,
a.p2,
a.p3,
'kill -9 ' || p.spid killstr,
'ps -ef|grep '|| p.spid ||'|grep LOCAL=NO|awk ''{print $2}''|xargs kill -9' kill_sh
from v$session a, v$sql b, dba_objects c, v$process p
where a.status = 'ACTIVE'
and p.addr = a.paddr
and a.sql_id = b.sql_id(+)
-- and a.wait_class <> 'Idle'
and a.sql_child_number = b.CHILD_NUMBER(+)
and a.row_wait_obj# = c.object_id(+)
and a.type = 'USER' '''
active_session_list = OracleBase(oracle_params).django_query(sql)
serializer = OracleActiveSession(active_session_list, many=True)
json = JSONRenderer().render(serializer.data)
return HttpResponse(json)
def check_oracle(tags, oracle_params):
db_version = oracle_params['db_version']
host = oracle_params['host']
port = oracle_params['port']
service_name = oracle_params['service_name']
linux_params = {
'hostname': oracle_params['host'],
'port': oracle_params['sshport_os'],
'username': oracle_params['user_os'],
'password': oracle_params['password_os']
}
check_time = now()
db_conn = OracleBase(oracle_params).connection()
db_conn_cdb = OracleBase(oracle_params).connection_cdb(
) if db_version == 'Oracle12c' else db_conn
if db_conn and db_conn_cdb:
# db信息监控
checklog.logger.info('{}:开始获取Oracle数据库监控信息'.format(tags))
# 数据库
dbname, db_unique_name, database_role, open_mode, log_mode, dbid, flashback_on, platform, created = database_info(
db_conn)
# 容量
datafile_size = round(get_datafile_size(db_conn)[0], 2)
tempfile_size = round(get_tempfile_size(db_conn)[0], 2)
archivelog_size = round(get_archivelog_size(db_conn)[0], 2)
# 实例
inst_id, instance_name, hostname, startup_time, version = instance_info(
db_conn)
updays = (datetime.now() - startup_time).days
# 连接数
max_process, current_process, process_used_percent = process_info(
db_conn_cdb)
# 归档
archive_used_percent = get_archived(db_conn)
# 审计
audit_trail = para(db_conn, 'audit_trail')
is_rac = para(db_conn, 'cluster_database')
# 默认Undo表空间
undo_tablespace = para(db_conn, 'undo_tablespace')
# Oraclestat
oraclestat = OracleStat(oracle_params, db_conn)
oraclestat.get_oracle_stat()
time.sleep(1)
oracle_data = oraclestat.get_oracle_stat()
# 状态数据
oracle_osstat = oracle_data['os']
oracle_stat = oracle_data['stat']
oracle_wait = oracle_data['wait']
oracle_sess = oracle_data['sess']
oracle_mem = oracle_data['mem']
oracle_load = oracle_data['load']
# PGA使用率
is_pga = para(db_conn, 'pga_aggregate_target')
if int(is_pga) > 0:
pga_target_size, pga_used_size, pga_used_percent = pga(db_conn)
else:
pga_target_size, pga_used_size, pga_used_percent = (0, 0, 0)
if database_role == 'PHYSICAL STANDBY':
adg_trans_lag, adg_trans_value = adg_trans(db_conn_cdb)
adg_apply_lag, adg_apply_value = adg_apply(db_conn_cdb)
else:
adg_trans_lag = 'None'
adg_apply_lag = 'None'
adg_trans_value = 0
adg_apply_value = 0
# 锁等待信息
lock_wait_res = get_lockwait_count(db_conn)
dic_lock_wait = {each[0]: each[1] for each in lock_wait_res}
enq_tx_row_lock_contention = dic_lock_wait.get(
'enq: TX - row lock contention', 0)
enq_tm_contention = dic_lock_wait.get('enq: TM - contention', 0)
row_cache_lock = dic_lock_wait.get('row cache lock', 0)
library_cache_lock = dic_lock_wait.get('library cache lock', 0)
enq_tx_contention = dic_lock_wait.get('enq: TX - contention', 0)
lock_wait_others = sum(each[1] for each in lock_wait_res) - (
enq_tx_row_lock_contention + enq_tm_contention + row_cache_lock +
library_cache_lock + enq_tx_contention)
checklog.logger.info('{}：写入oracle_stat采集数据'.format(tags))
clear_table(tags, 'oracle_stat')
insert_data_values = {
**locals(),
**oracle_osstat,
**oracle_wait,
**oracle_load,
**oracle_sess,
**oracle_stat,
**oracle_mem
}
insert_data_sql = "insert into oracle_stat(tags,host,port,service_name,hostname,platform,num_cpus,physical_memory,inst_id,instance_name,db_version," \
"dbid,created,dbname,db_unique_name,database_role,open_mode,updays,audit_trail,log_mode,is_rac,undo_tablespace,flashback_on," \
"datafile_size,tempfile_size,archivelog_size," \
"archive_used_percent,max_process,current_process,process_used_percent,pga_target_size,pga_used_size," \
"pga_used_percent,pga_size,sga_size,memory_used_percent,logons_cumulative,qps,tps,exec_count,user_commits,user_rollbacks," \
"consistent_gets,logical_reads,physical_reads,physical_writes,block_changes,redo_size,redo_writes,total_parse_count," \
"hard_parse_count,bytes_received,bytes_sent,io_throughput,total_sessions,active_sessions,active_trans_sessions," \
"blocked_sessions,dbtime,dbcpu,log_parallel_write_wait,log_file_sync_wait,log_file_sync_count," \
"db_file_scattered_read_wait,db_file_scattered_read_count,db_file_sequential_read_wait,db_file_sequential_read_count," \
"row_lock_wait_count,enq_tx_row_lock_contention,enq_tm_contention,row_cache_lock,library_cache_lock,enq_tx_contention,lock_wait_others," \
"adg_trans_lag,adg_apply_lag,adg_trans_value,adg_apply_value,status,check_time) " \
"values('{tags}','{host}',{port},'{service_name}','{hostname}','{platform}',{num_cpus},{physical_memory},{inst_id},'{instance_name}','{version}'," \
"{dbid},'{created}','{dbname}','{db_unique_name}','{database_role}','{open_mode}',{updays},'{audit_trail}','{log_mode}','{is_rac}','{undo_tablespace}','{flashback_on}'," \
"{datafile_size},{tempfile_size},{archivelog_size}," \
"{archive_used_percent},{max_process},{current_process},{process_used_percent},{pga_target_size},{pga_used_size}," \
"{pga_used_percent},{pga_size},{sga_size},{memory_used_percent},{logons_cumulative},{qps},{tps},{exec_count},{user_commits},{user_rollbacks}," \
"{consistent_gets},{logical_reads},{physical_reads},{physical_writes},{block_changes},{redo_size},{redo_writes},{total_parse_count}," \
"{hard_parse_count},{bytes_received},{bytes_sent},{io_throughput},{total_sessions},{active_sessions},{active_trans_sessions}," \
"{blocked_sessions},{dbtime},{dbcpu},{log_parallel_write_wait},{log_file_sync_wait},{log_file_sync_count}," \
"{db_file_scattered_read_wait},{db_file_scattered_read_count},{db_file_sequential_read_wait},{db_file_sequential_read_count}," \
"{row_lock_wait_count},{enq_tx_row_lock_contention},{enq_tm_contention},{row_cache_lock},{library_cache_lock},{enq_tx_contention},{lock_wait_others}," \
"'{adg_trans_lag}','{adg_apply_lag}',{adg_trans_value},{adg_apply_value},0,'{check_time}' )"
insert_sql = insert_data_sql.format(**insert_data_values)
mysql_exec(insert_sql)
checklog.logger.info(
'{}：获取Oracle数据库监控数据(数据库名：{} 数据库角色：{} 数据库状态：{})'.format(
tags, dbname, database_role, open_mode))
archive_table(tags, 'oracle_stat')
# control file
clear_table(tags, 'oracle_controlfile')
controlfile_list = get_controlfile(db_conn)
for each in controlfile_list:
control_name, size = each
insert_data_sql = "insert into oracle_controlfile(tags,host,port,service_name,name,size,check_time)" \
"values('{tags}','{host}',{port},'{service_name}','{control_name}',{size},'{check_time}')"
insert_sql = insert_data_sql.format(**locals())
mysql_exec(insert_sql)
# redolog
clear_table(tags, 'oracle_redolog')
redolog_list = get_redolog(db_conn)
for each in redolog_list:
group_no, thread_no, type, sequence_no, size, archived, status, member = each
insert_data_sql = "insert into oracle_redolog(tags,host,port,service_name,group_no,thread_no,type,sequence_no," \
"size,archived,status,member,check_time)" \
"values('{tags}','{host}',{port},'{service_name}',{group_no},{thread_no},'{type}',{sequence_no}," \
"{size},'{archived}','{status}','{member}','{check_time}')"
insert_sql = insert_data_sql.format(**locals())
insert_sql = insert_sql.replace('None', 'NULL')
mysql_exec(insert_sql)
# 表空间
clear_table(tags, 'oracle_tablespace')
tbsinfo_list = tablespace(db_conn)
for tbsinfo in tbsinfo_list:
tablespace_name, datafile_count, total_size, free_size, used_size, max_free_size, percent_used, percent_free, used_mb = tbsinfo
insert_data_sql = "insert into oracle_tablespace(tags,host,port,service_name,tablespace_name,datafile_count,total_size,free_size," \
"used_size,max_free_size,percent_used,percent_free,used_mb,check_time)" \
"values('{tags}','{host}',{port},'{service_name}','{tablespace_name}',{datafile_count},{total_size},{free_size}," \
"{used_size},{max_free_size},{percent_used},{percent_free},{used_mb},'{check_time}')"
insert_sql = insert_data_sql.format(**locals())
mysql_exec(insert_sql)
archive_table(tags, 'oracle_tablespace')
# 临时表空间
clear_table(tags, 'oracle_temp_tablespace')
temptbsinfo_list = temp_tablespace(db_conn)
for temptbsinfo in temptbsinfo_list:
temptablespace_name, total_size, used_size, percent_used = temptbsinfo
insert_data_sql = "insert into oracle_temp_tablespace(tags,host,port,service_name,temptablespace_name,total_size,used_size," \
"percent_used,check_time)" \
"values('{tags}','{host}',{port},'{service_name}','{temptablespace_name}',{total_size}," \
"{used_size},{percent_used},'{check_time}')"
insert_sql = insert_data_sql.format(**locals())
mysql_exec(insert_sql)
archive_table(tags, 'oracle_temp_tablespace')
# undo表空间
clear_table(tags, 'oracle_undo_tablespace')
undotbsinfo_list = get_undo_tablespace(db_conn)
for undotbsinfo in undotbsinfo_list:
undotablespace_name, used_size, total_size, percent_used = undotbsinfo
insert_data_sql = "insert into oracle_undo_tablespace(tags,host,port,service_name,undotablespace_name,total_size,used_size," \
"percent_used,check_time)" \
"values('{tags}','{host}',{port},'{service_name}','{undotablespace_name}',{total_size}," \
"{used_size},{percent_used},'{check_time}')"
insert_sql = insert_data_sql.format(**locals())
mysql_exec(insert_sql)
archive_table(tags, 'oracle_undo_tablespace')
# 统计信息分析
clear_table(tags, 'oracle_table_stats')
oracletablestats_list = get_tab_stats(db_conn)
for each in oracletablestats_list:
owner, table_name, num_rows, change_pct, last_analyzed = each
insert_data_sql = "insert into oracle_table_stats(tags,host,port,service_name,owner,table_name,num_rows,change_pct,last_analyzed,check_time) " \
"values('{tags}','{host}',{port},'{service_name}','{owner}','{table_name}',{num_rows},{change_pct},'{last_analyzed}','{check_time}')"
insert_sql = insert_data_sql.format(**locals())
mysql_exec(insert_sql)
# 后台日志解析
# get_oracle_alert(tags,db_conn,oracle_params,linux_params)
db_conn.close()
else:
error_msg = "{}:数据库连接失败".format(tags)
checklog.logger.error(error_msg)
clear_table(tags, 'oracle_stat')
checklog.logger.info('{}:写入oracle_stat采集数据'.format(tags))
sql = "insert into oracle_stat(tags,host,port,service_name,status,check_time) values(%s,%s,%s,%s,%s,%s)"
value = (tags, oracle_params['host'], oracle_params['port'],
oracle_params['service_name'], 1, check_time)
mysql_exec(sql, value)
archive_table(tags, 'oracle_stat')
# report_file = os.getcwd() + '/oracle/report/' +report_name
report_file = os.getcwd() + '/templates/report/oracle/' + report_name
self.save_report(report_file, data)
insert_sql = " INSERT INTO oracle_report(tags,begin_time,end_time,report_type,file_path,status,create_time) " \
"values('{}','{}','{}','{}','{}','0','{}') ".format(
self.tags, report_begin_time, report_end_time, report_type, 'report/oracle/'+report_name,now())
mysql_exec(insert_sql, '')
if __name__ == '__main__':
tags = 'pdb1'
oracle_params = {
'host': '192.168.48.60',
'port': 1521,
'service_name': 'pdb1',
'user': '******',
'password': '******',
'service_name_cdb': 'orcl19c',
'user_cdb': 'c##dbmon',
'password_cdb': 'oracle'
}
report_type = 'addm'
begin_snap = '1958'
end_snap = '1960'
db_conn = OracleBase(oracle_params).connection()
db_conn_cdb = OracleBase(oracle_params).connection_cdb()
oracle_report = OracleReport(db_conn_cdb,tags,oracle_params)
oracle_report.get_report(report_type,1958,1960)
instance_num, begin_snap,
end_snap, suffix)
# report_file = os.getcwd() + '/oracle/report/' +report_name
report_file = os.getcwd() + '/report/' + report_name
self.save_report(report_file, data)
insert_sql = " INSERT INTO oracle_report(tags,begin_time,end_time,report_type,file_path,status,create_time) " \
"values('{}','{}','{}','{}','{}','0','{}') ".format(
self.tags, report_begin_time, report_end_time, report_type, report_file,now())
mysql_exec(insert_sql, '')
if __name__ == '__main__':
tags = 'orcl19c_pdb1'
oracle_params = {
'host': '192.168.48.60',
'port': 1521,
'service_name': 'pdb1',
'user': '******',
'password': '******',
'service_name_cdb': 'orcl19c',
'user_cdb': 'c##dbmon',
'password_cdb': 'oracle'
}
report_type = 'awr'
begin_snap = '1282'
end_snap = '1283'
db_conn = OracleBase(oracle_params).connection()
oracle_report = OracleReport(db_conn, tags, oracle_params)
oracle_report.get_report(report_type, 1282, 1283)
# encoding:utf-8
from utils.oracle_base import OracleBase
import json
if __name__ == '__main__':
oracle_params = {
'host': '192.168.48.60',
'port': 1521,
'service_name': 'pdb1',
'user': '******',
'password': '******',
'service_name_cdb': 'orcl19c',
'user_cdb': 'c##dbmon',
'password_cdb': 'oracle'
}
db_conn = OracleBase(oracle_params).connection()
OracleBase(oracle_params).call_proc('pro_top_cpu_sql', db_conn)
sql = "select ID,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12," \
"SHOW_TYPE,SHOW_TITLE,INST_INFO from snap_show_config"
res_config = OracleBase(oracle_params).query_one(sql, db_conn)
sql = "select ID,RATE,SQL_ID,SQL_EXEC_CNT,VAL1,VAL2,VAL3,VAL4,VAL4,VAL5,VAL6,VAL7,VAL8," \
"VAL9,SNAP_TYPE_ID from snap_show"
res_data = OracleBase(oracle_params).query_all(sql, db_conn)
data = {'snap_config': res_config, 'snap_data': res_data}
print(json.dumps(data))
