def db_growth(self):
query = "set head off \n \
set feedback off \n \
column TOTAL_UTILIZADO_SIZE format 999999999999999 \n \
SELECT TRUNC(SUM((A.BYTES-B.BYTES))) TOTAL_UTILIZADO_SIZE \n \
FROM \n \
( SELECT x.TABLESPACE_NAME, SUM(x.BYTES) BYTES \n \
FROM DBA_DATA_FILES x,DBA_TABLESPACES y \n \
WHERE y.CONTENTS <>'UNDO' \n \
AND x.TABLESPACE_NAME=y.TABLESPACE_NAME \n \
GROUP BY x.TABLESPACE_NAME) A, \n \
( SELECT TABLESPACE_NAME, SUM(BYTES) BYTES \n \
FROM DBA_FREE_SPACE \n \
GROUP BY TABLESPACE_NAME) B \n \
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;"
if self.user.lower() == 'sys':
result = Utils.run_sqlplus(self.password, self.user, self.sid, query, True, True)
else:
result = Utils.run_sqlplus(self.password, self.user, self.sid, query, True, False)
if 'ORA-' in result:
print 'Erro desconhecido ao executar a query:' + result
exit(3)
try:
self.growth_gather = int(result.strip(' '))
self.append_gather()
except:
print 'Impossivel tratar o valor da coleta'
exit(3)
def main(sid, user, pwd, warning, sum, schemas):
#args = parse_args()
query = get_my_query(schemas,sum)
result = ''
if user.lower() == 'sys':
result = Utils.run_sqlplus(pwd, user, sid, query, True, True)
else:
result = Utils.run_sqlplus(pwd, user, sid, query, True, False)
perf_data = ''
total = 0
if 'ORA-' in result:
print 'Erro desconhecido ao executar a query:'+result
exit(3)
# Replace 4/3/2 whitespaces devido ao resultado do sqlplus,
# split '' serve para criar a minha lista com cada coluna em um elemnto
#strip para tirar os whites antes e dps.
r = result.strip().replace(" "," ").replace(" "," ").replace(" "," ").split(' ')
if sum:
total = int(r[0])
else:
it = iter(r)
for count, schema in zip(it,it):
perf_data += schema + '=' + count + ' '
total += int(count)
perf_data += 'TOTAL='+str(total)+';'+warning
if total > int(warning):
print 'WARNING - Sobrecarga no banco, Sessoes:'+str(total)+' | ' +perf_data
exit(1)
else:
print 'Total de Sessoes:' + str(total) + '| ' +perf_data
exit(0)
def single_int_query(user, password, sid, query):
"""
Executa qualquer query que retorne 1 valor
inteiro
:param user: Usuario do oracle
:param password: Senha do oracle
:param sid: tnsnames de conexao
:param query: Diskgroup dos archives
:return:
"""
global r
if user.lower() == 'sys':
result = Utils.run_sqlplus(password, user, sid, query, True, True)
else:
result = Utils.run_sqlplus(password, user, sid, query, True, False)
if 'ORA-' in result:
print 'Erro desconhecido ao executar a query:' + result
exit(3)
try:
r_aux = result.strip(' ').replace(',', '.')
if '.' in r_aux:
r = int(round(float(r_aux)))
else:
r = int(r_aux)
except:
print 'Impossivel tratar o resultado da query: %s' % result
exit(3)
return r
def asm_space(user, password, sid, diskgroup):
"""
Calcula o tempo de disco
caso seja um diskgroup ASM
:param user: Usuario do oracle
:param password: Senha do oracle
:param sid: tnsnames de conexao
:param diskgroup: Diskgroup dos archives
:return: Espaco disponivel em bytes
"""
global diskspace
query = "set head off \n \
set feedback off \n \
col free_bytes format 999999999999999 \n \
SELECT free_mb*1024*1024 as free_bytes \n \
FROM v$asm_diskgroup \n \
where name = '%s' \n \
/" % diskgroup
if user.lower() == 'sys':
result = Utils.run_sqlplus(password, user, sid, query, True, True)
else:
result = Utils.run_sqlplus(password, user, sid, query, True, False)
if 'ORA-' in result:
print 'Erro desconhecido ao executar a query:' + result
exit(3)
try:
diskspace = int(result.strip(' '))
except:
print 'Impossivel tratar o valor de espaco ASM. Verifique o nome do diskgroup'
exit(3)
return diskspace
def open_gather_json(self):
"""
Abre o JSON das coletas radicais
clean_time
:return:
"""
gather_list = Utils.read_json(Utils.fullpath('dbgrowth_data.json'))
self.gather_list = gather_list
def clear_old_errors(self):
"""
Remove do JSON todos os erros que ja passaram do tempo
clean_time
:return:
"""
if Utils.file_exists_not_empty(Utils.fullpath('alertlog_errors.json')):
error_list = Utils.read_json(Utils.fullpath('alertlog_errors.json'))
error_list = [error for error in error_list if not self.error_is_cleared(error['date'])]
self.error_list = error_list
else:
self.error_list = []
self.write_error_json()
def clear_log_position(self):
with open(Utils.fullpath('check_alertlog.tmp'), 'w') as f:
try:
f.write("0")
except:
print "UNKNOWN - Erro ao escrever check_alertlog.tmp. Verifique as permissoes."
exit(3)
def read_partial_file(self):
if Utils.file_exists(Utils.fullpath('check_alertlog.tmp')):
with open(Utils.fullpath('check_alertlog.tmp'), 'r') as f:
try:
f.seek(0)
file_content = f.readline()
self.last_position = int(file_content)
except ValueError:
print "UNKNOWN - Impossivel ler logfile."
exit(3)
else:
with open(Utils.fullpath('check_alertlog.tmp'), 'a') as f:
try:
f.write('0')
self.last_position = 0
except:
print "UNKNOWN - Erro ao escrever check_alertlog.tmp. Verifique as permissoes."
exit(3)
def write_gather_json(self):
"""
Escreve no disco o novo json de erros
:return:
"""
with open(Utils.fullpath('dbgrowth_data.json'), 'w') as f:
try:
json.dump(self.gather_list, f)
except:
print "UNKNOWN - Impossivel gravar JSON."
exit(3)
def write_error_json(self):
"""
Escreve no disco o novo json de erros
:return:
"""
with open(Utils.fullpath('alertlog_errors.json'), 'w') as f:
try:
json.dump(self.error_list, f)
except:
print "UNKNOWN - Impossivel gravar JSON."
exit(3)
def main(sid, user, pwd, warning):
#sid, user, pwd, warning
#parse_argsv2(sys.argv[1:])
result = ''
query = "set head off \n \
set feedback off \n \
SET SERVEROUTPUT ON \n \
DECLARE \n \
begindate date; \n \
enddate date; \n \
beginval number;\n \
endval number;\n \
begin \n \
select sysdate, sum(value) \n \
into begindate, beginval \n \
from v$sysstat \n \
where name in ('user commits','user_rollbacks'); \n \
dbms_lock.sleep(5); \n \
select sysdate, sum(value) \n \
into enddate, endval \n \
from v$sysstat \n \
where name in ('user commits','user_rollbacks'); \n \
dbms_output.put_line( (endval-beginval) / ((enddate-begindate) * 86400)); \n \
end; \n \
/"
if user.lower() == 'sys':
result = Utils.run_sqlplus(pwd, user, sid, query, True, True)
else:
result = Utils.run_sqlplus(pwd, user, sid, query, True, False)
perf_data = ''
total = 0
if 'ORA-' in result:
print 'Erro desconhecido ao executar a query:'+result
exit(3)
else:
perf_data = 'TPS=' + result
if float(result.replace(',','.')) > int(warning):
print 'WARNING - Quantidade de transações por segundo acima no normal. TPS:'+result+' | ' +perf_data
exit(1)
else:
print 'OK - Transações por Segundo:' + result + ' | ' + perf_data
def update_log_position(self, lines_read):
"""
:param lines_read: Numero de linhas lidas
"""
self.last_position = int(self.last_position) + int(lines_read)
with open(Utils.fullpath('check_alertlog.tmp'), 'w') as f:
try:
f.write(str(self.last_position))
except:
print "UNKNOWN - Erro ao escrever check_alertlog.tmp. Verifique as permissoes."
exit(3)
def read_config(self, path):
"""
Realiza a leitura do JSON e adiciona a variavel config.
:param path: Local do json de config.
:return: None
"""
if Utils.file_exists(path):
with open(path) as opf:
try:
self.cfg = json.load(opf)
except ValueError:
print "UNKNOWN - Impossivel ler arquivo de configuracao."
exit(3)
else:
print "UNKNOWN - Impossivel encontrar o arquivo de configuracao."
def read_log(self):
"""
Realiza a leitura das novas linhas do log
Procura pelos erros e faz a contagem de linhas
para atualizar a ultima posicao lida do log.
Caso ja tenha lido tudo faz sua saida padrao.
:return:
"""
if Utils.file_exists(self.logfile):
with open(self.logfile) as f:
for _ in xrange(int(self.last_position)):
next(f)
for line in f:
self.find_errors(line)
self.lines_counted += 1
else:
self.clear_log_position()
print "UNKNOWN - Logfile nao encontrado"
exit(3)
if self.lines_counted > 0:
self.update_log_position(self.lines_counted)
else:
self.exit_status()
def build_issues(self):
json = Utils.read_json(self.config)
self.warning = json['warning']
self.critical = json['critical']
self.ignore = json['ignore']
def __init__(self, db_name, module):
self.read_config(Utils.fullpath("monitoramento.json"))
self.config_database(db_name, module)