1598 lines
61 KiB
Python
Executable File
1598 lines
61 KiB
Python
Executable File
#!/u01/app/python/current_version/bin/python3
|
|
|
|
|
|
from libs.ogg_libs import *
|
|
|
|
import os
|
|
import cx_Oracle
|
|
import ldap
|
|
import subprocess
|
|
import cherrypy
|
|
from jinja2 import Environment, FileSystemLoader
|
|
from cherrypy.lib import auth_basic
|
|
import requests
|
|
import json
|
|
import pytz
|
|
import dateutil.parser
|
|
import datetime
|
|
from email.utils import COMMASPACE, formatdate
|
|
from requests.packages.urllib3.exceptions import InsecureRequestWarning
|
|
from libs.ogg_libs import *
|
|
import time
|
|
from email.message import EmailMessage
|
|
import smtplib
|
|
|
|
|
|
DEBUG_MODE = False
|
|
TEMPLATE_DIR = OGG_SERVICE_DIR + "/www"
|
|
LDAP = "frru3dc4635.france.intra.corp"
|
|
OEMDB_EZ_STRING = "FRPIVSQL2418/OEMPRD"
|
|
ORA_USERNAME = "system"
|
|
ORA_PASSWORD = "plusdacces"
|
|
CONTROLMDB_EZ_STRING = "dmp01-scan/EMPRDEXA"
|
|
DRIVE_DATABASES = {"FR":"dmp01-scan/DRF1PRDEXA", "IT":"dmp01-scan/DRI1PRDEXA", "UK":"dmp01-scan/DRU1PRDEXA", "SP":"dmp01-scan/DRS1PRDEXA"}
|
|
DRIVE_DATABASES_DESCRIPTION = {"FR":"Drive FRANCE database", "IT":"Drive ITALY database", "UK":"Drive UK database", "SP":"Drive SPAIN database"}
|
|
ACTIVE_SESS_HIST_HOURS = 4
|
|
LOCK_HIST_HOURS = 4
|
|
DATGUARD_ACCEPTED_LAG_SECONDS = 0
|
|
DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES = 15
|
|
|
|
|
|
env=Environment(loader=FileSystemLoader(TEMPLATE_DIR), trim_blocks=True)
|
|
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
|
|
get_hostname()
|
|
logger = start_logging(OGG_SERVICE_DIR+"/log/www_actions.log")
|
|
|
|
def ad_validate(username, domain, password):
|
|
# Delegate password check to Active Directory
|
|
RC = False
|
|
l = ldap.initialize("ldap://" + LDAP)
|
|
try:
|
|
l.protocol_version = ldap.VERSION3
|
|
l.set_option(ldap.OPT_REFERRALS, 0)
|
|
l.simple_bind_s(username + domain, password)
|
|
RC = True
|
|
logger.info("User " + username + " logged in")
|
|
except ldap.LDAPError as e:
|
|
# Reject if wrong password
|
|
RC = False
|
|
# Accept otherwise
|
|
return RC
|
|
|
|
def load_user_dict():
|
|
with open(OGG_SERVICE_DIR+"/etc/www_users.conf", 'r') as f:
|
|
user_dict = json.load(f)
|
|
return user_dict
|
|
|
|
|
|
def get_www_visible_oggsync():
|
|
all_sync_array = []
|
|
directory = OGG_SERVICE_DIR + "/sync.d/"
|
|
for name in sorted(os.listdir(directory)):
|
|
if os.path.isdir(os.path.join(directory, name)):
|
|
try:
|
|
(extract, replicat) = name.split("_")
|
|
|
|
with open(OGG_SERVICE_DIR+"/etc/status.info") as f:
|
|
arr_status = json.load(f)
|
|
|
|
for status in arr_status:
|
|
if arr_status[extract] == None:
|
|
arr_status[extract] = "UNKNOW"
|
|
if arr_status[replicat] == None:
|
|
arr_status[replicat] = "UNKNOW"
|
|
|
|
ogg_sync = OGG_Sync(extract, replicat)
|
|
ogg_sync.create_temporary_sync_directory()
|
|
ogg_sync.fast_parse_prm_headers()
|
|
env_info_filename = os.path.join(directory, name, "env.info")
|
|
env_info_contents = open(env_info_filename, "r", encoding = "utf-8", errors = "replace").readlines()
|
|
specific_params = {}
|
|
for line in env_info_contents:
|
|
line = line.rstrip().lstrip()
|
|
if line !="":
|
|
param, value = line.split("=", 1)
|
|
param = param.rstrip().lstrip()
|
|
value = value.rstrip().lstrip()
|
|
specific_params[param] = value
|
|
|
|
if specific_params["www_visible"] == "yes":
|
|
sync = (extract, replicat, specific_params["type_environement"], specific_params["www_title"], ogg_sync.database_source, ogg_sync.database_target, arr_status[extract], arr_status[replicat])
|
|
all_sync_array.append(sync)
|
|
|
|
except:
|
|
pass
|
|
return all_sync_array
|
|
|
|
def get_target_instances(target_database):
|
|
db_name = target_database[:-3]
|
|
db_name_like_pattern = "PRD%" + db_name + "%"
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql = "select TARGET_NAME from sysman.MGMT$TARGET where target_name like '" + db_name_like_pattern + "'"
|
|
cursor.execute(sql)
|
|
target_instances = []
|
|
for row in cursor:
|
|
target_instances.append(row[0])
|
|
|
|
cursor.close()
|
|
db.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
return target_instances
|
|
|
|
|
|
# OUR CONTROLLER
|
|
class Root(object):
|
|
user_dict = load_user_dict()
|
|
menu = {}
|
|
# CherryPy published function
|
|
|
|
@cherrypy.expose()
|
|
def login_page(self):
|
|
templateVars = {}
|
|
template = env.get_template('login_page.tpl')
|
|
templateVars = {
|
|
"myvar1" : "myvar1"
|
|
}
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def select_sync(self):
|
|
self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])
|
|
|
|
www_visible_oggsync_array = get_www_visible_oggsync()
|
|
|
|
status_time = time.ctime(os.path.getmtime(OGG_SERVICE_DIR+"/etc/status.info"))
|
|
templateVars = {}
|
|
template = env.get_template('select_sync.tpl')
|
|
templateVars = {
|
|
"www_visible_oggsync_array" : www_visible_oggsync_array,
|
|
"status_time" : status_time
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def oemtargetlist(self, *args, **kwargs):
|
|
self.restrict_url_to_logged()
|
|
# GET values
|
|
try:
|
|
hostlike = kwargs["hostlike"]
|
|
except KeyError as e:
|
|
hostlike=""
|
|
|
|
try:
|
|
instancelike = kwargs["instancelike"]
|
|
except KeyError as e:
|
|
instancelike=""
|
|
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
WITH data_tab AS (
|
|
select T.target_name, P1.property_value as DBAPPLICATION
|
|
from sysman.mgmt$target T,
|
|
sysman.mgmt$target_properties P1
|
|
where P1.TARGET_GUID(+) = T.TARGET_GUID
|
|
and P1.property_name(+) = 'orcl_gtp_cost_center'
|
|
"""
|
|
if hostlike !="":
|
|
sql = sql + "and (T.target_name like '%" + hostlike + "%') "
|
|
|
|
if instancelike !="":
|
|
sql = sql + "and (T.target_name like '%" + instancelike + "%') "
|
|
|
|
sql = sql + """
|
|
and (T.target_type IN ('oracle_database'))
|
|
)
|
|
SELECT
|
|
regexp_substr (target_name, '[^_]+', 1, 1) DBENV,
|
|
regexp_substr (target_name, '[^_]+', 1, 2) DBHOST,
|
|
regexp_substr (target_name, '[^_]+', 1, 3) DBINST,
|
|
DBAPPLICATION
|
|
FROM data_tab
|
|
order by 2, 3, 4, 1
|
|
"""
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
|
|
if hostlike !="":
|
|
templateVars = {}
|
|
template = env.get_template('oemtargetlist_hostlike.tpl')
|
|
templateVars = {
|
|
"selected_host": hostlike,
|
|
"items" : items
|
|
}
|
|
templateVars.update(Root.menu)
|
|
|
|
if instancelike !="":
|
|
templateVars = {}
|
|
template = env.get_template('oemtargetlist_instancelike.tpl')
|
|
templateVars = {
|
|
"instancelike": instancelike,
|
|
"items" : items
|
|
}
|
|
templateVars.update(Root.menu)
|
|
|
|
output = template.render(templateVars)
|
|
cursor.close()
|
|
|
|
except cx_Oracle.DatabaseError as err:
|
|
output = str(err)
|
|
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def oemhosts(self, *args, **kwargs):
|
|
self.restrict_url_to_logged()
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
WITH data_tab AS (
|
|
select T.target_name, P1.property_value as DBAPPLICATION
|
|
from sysman.mgmt$target T,
|
|
sysman.mgmt$target_properties P1
|
|
where P1.TARGET_GUID(+) = T.TARGET_GUID
|
|
and P1.property_name(+) = 'orcl_gtp_cost_center'
|
|
and (T.target_type IN ('oracle_database'))
|
|
)
|
|
SELECT distinct
|
|
regexp_substr (target_name, '[^_]+', 1, 2) DBHOST
|
|
FROM data_tab
|
|
order by 1 asc
|
|
"""
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
cursor.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
output = str(err)
|
|
return output
|
|
|
|
exaprodhosts=[]
|
|
exapnonrodhosts=[]
|
|
otherhosts=[]
|
|
|
|
for item in items:
|
|
dbhost = str(item[0])
|
|
|
|
if dbhost.startswith("dmp"):
|
|
exaprodhosts.append(dbhost)
|
|
elif dbhost.startswith("dmt"):
|
|
exapnonrodhosts.append(dbhost)
|
|
else:
|
|
otherhosts.append(dbhost)
|
|
|
|
templateVars = {}
|
|
template = env.get_template('oemhosts.tpl')
|
|
templateVars = {
|
|
"exaprodhosts" : exaprodhosts,
|
|
"exapnonrodhosts" : exapnonrodhosts,
|
|
"otherhosts" : otherhosts,
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def drivesessions(self, *args, **kwargs):
|
|
country = kwargs["country"]
|
|
accepted_group = "HELPDESK_" + country
|
|
self.restrict_url_to_groups(["SUPERUSER", accepted_group])
|
|
try:
|
|
db = cx_Oracle.connect(dsn=DRIVE_DATABASES[country])
|
|
cursor = db.cursor()
|
|
html_newline = "%20%0A"
|
|
sql = """
|
|
SELECT DISTINCT
|
|
S1.SID||'-'||S1.SERIAL#||'-@'||S1.INST_ID,
|
|
S1.USERNAME ||' - '||'<A HREF="mailto:'||a.email_address||'?subject=%s: Application blocking session(s)&body=Hello '|| a.full_name || ',%sPlease be advised that your session is currently blocking other users in %s on ' || S1.MODULE || ' screen with your session '|| a.user_name ||'.%sCan you please disconnect or complete the current actions underway to avoid blocking of other users.%sShould you require more information on this incident please feel free to contact your local help desk.%sKind regards">'||a.full_name||'</A>',
|
|
S1.MODULE,
|
|
S2.USERNAME ||' - '||'<A HREF=mailto:'||b.email_address||'>'||b.full_name||'</A>',
|
|
S2.MODULE,
|
|
S1.SQL_ID,
|
|
trunc(L1.ctime/60)
|
|
FROM GV$LOCK L1,
|
|
GV$SESSION S1,
|
|
GV$LOCK L2,
|
|
GV$SESSION S2,
|
|
drive.drive_users a,
|
|
drive.drive_users b
|
|
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
|
|
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
|
|
AND L1.BLOCK > 0 AND L2.REQUEST > 0
|
|
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2
|
|
AND S1.username=a.user_name(+)
|
|
AND S2.username=b.user_name(+)
|
|
AND trunc(L1.ctime/60)>0
|
|
AND S1.username not in ('SYS','SYSTEM','OGG_USER','DRIVE','WSHANDLER')
|
|
""" % ("Drive " + country, html_newline + html_newline, "Drive " + country, html_newline, html_newline, html_newline)
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
cursor.close()
|
|
db.close()
|
|
title = DRIVE_DATABASES_DESCRIPTION[country]
|
|
templateVars = {}
|
|
template = env.get_template('drivesessions.tpl')
|
|
templateVars = {
|
|
"title" : title,
|
|
"items" : items
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
except Exception as err:
|
|
output = str(DRIVE_DATABASES_DESCRIPTION[country])
|
|
pass
|
|
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def drivekillsessions(self, *args, **kwargs):
|
|
country = kwargs["country"]
|
|
oracle_sessions_string = kwargs["sessions"]
|
|
accepted_group = "HELPDESK_" + country
|
|
self.restrict_url_to_groups(["SUPERUSER", accepted_group])
|
|
oracle_sessions = oracle_sessions_string.split(",")
|
|
try:
|
|
db = cx_Oracle.connect(dsn=DRIVE_DATABASES[country])
|
|
cursor = db.cursor()
|
|
for oracle_session in oracle_sessions:
|
|
(oracle_sid, oracle_serial, oracle_instance) = oracle_session.split("-")
|
|
# before KILL session, grab informations about SQL_ID, user email etc.
|
|
sql = "select username, sql_id, event, seconds_in_wait from gv$session where sid=" + oracle_sid + " and serial#=" + oracle_serial
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
username, sql_id, event, seconds_in_wait = row
|
|
|
|
sql = "select full_name, email_address from drive.drive_users where user_name='" + username + "'"
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
full_name, email_address = row
|
|
|
|
sql = "alter system disconnect session '" + oracle_sid + "," + oracle_serial + "," + oracle_instance + "' immediate"
|
|
logger.info("HELPDESK " + cherrypy.session.get("ad_uid") + " killed on " + DRIVE_DATABASES[country] + " the user " + username + " having SID=" + str(oracle_sid) + ", SERIAL#=" + str(oracle_serial) + ", SQL_ID=" + str(sql_id) + ", EVENT=" + event + ", SECONDS_IN_WAIT=" + str(seconds_in_wait))
|
|
cursor.execute(sql)
|
|
|
|
cursor.close()
|
|
db.close()
|
|
# Send email
|
|
email_body = """
|
|
Hello %s,\n
|
|
Your DRIVE application session has been killed because it was blocking other sessions.\n
|
|
For more details, you can contact me by email: %s \n
|
|
----- \n
|
|
Kind Regards,\n
|
|
%s
|
|
""" % (full_name, cherrypy.session.get("ad_uid") + "@arval.com", cherrypy.session.get("ad_uid") + "@arval.com")
|
|
msg = EmailMessage()
|
|
msg.set_content(email_body)
|
|
msg['Subject'] = "DRIVE " + country + " -- session killed"
|
|
if email_address == "" or email_address==None:
|
|
email_address = str(username) + "@arval.com"
|
|
print (email_address)
|
|
msg['From'] = cherrypy.session.get("ad_uid") + "@arval.com"
|
|
msg['To'] = email_address
|
|
# Send the message via our own SMTP server.
|
|
s = smtplib.SMTP('localhost')
|
|
s.send_message(msg)
|
|
s.quit()
|
|
|
|
except cx_Oracle.DatabaseError as err:
|
|
print (err)
|
|
|
|
# Display information windows
|
|
templateVars = {}
|
|
template = env.get_template('box_wait.tpl')
|
|
box_type = "box-primary"
|
|
box_title = "Please wait..."
|
|
redirect_url = "../drivesessions?country=" + country
|
|
redirect_timeout = "3000"
|
|
box_message1 = "Starting session KILL..."
|
|
box_message2 = "This page will be <b>automaticly redirected</b> after JOB submission."
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"redirect_url" : redirect_url,
|
|
"redirect_timeout" : redirect_timeout,
|
|
"box_message1" : box_message1,
|
|
"box_message2" : box_message2
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def show_logfile(self, *args, **kwargs):
|
|
self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])
|
|
|
|
if cherrypy.session.get('ad_uid') != None:
|
|
logfile = kwargs["logfile"]
|
|
logfile_contents = open(OGG_SERVICE_DIR + "/log/www_executions/" + logfile, "r", encoding = "utf-8", errors = "replace").readlines()
|
|
templateVars = {}
|
|
template = env.get_template('view_log.tpl')
|
|
templateVars = {
|
|
"logfile" : logfile,
|
|
"logfile_contents" : "".join(logfile_contents)
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
else:
|
|
raise cherrypy.HTTPRedirect("/login_page")
|
|
return output
|
|
|
|
def build_menu(self):
|
|
# Disable all by default
|
|
Root.menu["show_ogg_menu"] = "NO"
|
|
Root.menu["show_drive_it_sessions_menu"] = "NO"
|
|
Root.menu["show_drive_fr_sessions_menu"] = "NO"
|
|
Root.menu["show_drive_sp_sessions_menu"] = "NO"
|
|
Root.menu["show_drive_uk_sessions_menu"] = "NO"
|
|
# Enable by GROUP
|
|
if self.user_is_in_groups(["SUPERUSER","OGGOPER"]):
|
|
Root.menu["show_ogg_menu"] = "YES"
|
|
if self.user_is_in_groups(["SUPERUSER","HELPDESK_IT"]):
|
|
Root.menu["show_drive_it_sessions_menu"] = "YES"
|
|
if self.user_is_in_groups(["SUPERUSER","HELPDESK_FR"]):
|
|
Root.menu["show_drive_fr_sessions_menu"] = "YES"
|
|
if self.user_is_in_groups(["SUPERUSER","HELPDESK_SP"]):
|
|
Root.menu["show_drive_sp_sessions_menu"] = "YES"
|
|
if self.user_is_in_groups(["SUPERUSER","HELPDESK_UK"]):
|
|
Root.menu["show_drive_uk_sessions_menu"] = "YES"
|
|
|
|
return
|
|
|
|
@cherrypy.expose()
|
|
def logout(self):
|
|
cherrypy.session.pop('ad_uid', None)
|
|
raise cherrypy.HTTPRedirect("/login_page")
|
|
return
|
|
|
|
@cherrypy.expose()
|
|
def login(self, *args, **kwargs):
|
|
ad_uid = kwargs["ad_uid"]
|
|
ad_domain = kwargs["ad_domain"]
|
|
ad_password = kwargs["ad_password"]
|
|
if not ad_validate(ad_uid, ad_domain, ad_password):
|
|
raise cherrypy.HTTPRedirect("/login_page")
|
|
else:
|
|
cherrypy.session["ad_uid"] = ad_uid.lower()
|
|
Root.user_dict = load_user_dict()
|
|
self.build_menu()
|
|
raise cherrypy.HTTPRedirect("/index")
|
|
return
|
|
|
|
@cherrypy.expose()
|
|
def index(self):
|
|
self.restrict_url_to_logged()
|
|
|
|
templateVars = {}
|
|
template = env.get_template('start_page.tpl')
|
|
templateVars = {}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
def restrict_url_to_logged(self):
|
|
# Check if user is logged in
|
|
if cherrypy.session.get("ad_uid") == None:
|
|
# Not logged in, redirect to login page
|
|
raise cherrypy.HTTPRedirect("/login_page")
|
|
return
|
|
|
|
def user_is_in_groups(self, groups):
|
|
is_in_groups = False
|
|
try:
|
|
user_groups = Root.user_dict[cherrypy.session.get("ad_uid")]
|
|
except KeyError as e:
|
|
user_groups = "GUEST"
|
|
for group in groups:
|
|
if group in user_groups:
|
|
is_in_groups = True
|
|
break
|
|
return is_in_groups
|
|
|
|
|
|
def restrict_url_to_groups(self, authorized_groups):
|
|
self.restrict_url_to_logged()
|
|
authorized = False
|
|
|
|
if not self.user_is_in_groups(authorized_groups):
|
|
raise cherrypy.HTTPRedirect("/unathorized")
|
|
return
|
|
|
|
@cherrypy.expose()
|
|
def dash_start(self, *args, **kwargs):
|
|
# self.restrict_url_to_logged()
|
|
|
|
# Set to production cluster by default
|
|
try:
|
|
cluster = kwargs["cluster"]
|
|
except:
|
|
cluster = "dmp"
|
|
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
|
|
# Database status counters and graph
|
|
sql = """
|
|
with exadata_db as (
|
|
SELECT
|
|
mgmt$target.host_name
|
|
, mgmt$target.target_name
|
|
, mgmt$target.target_type
|
|
FROM sysman.mgmt$target
|
|
where mgmt$target.host_name like '%s'
|
|
and mgmt$target.target_type = 'rac_database'
|
|
)
|
|
select count(*), A.AVAILABILITY_STATUS
|
|
FROM sysman.mgmt$availability_current A, sysman.MGMT$TARGET B , exadata_db C
|
|
WHERE B.target_name= C.target_name
|
|
AND A.TARGET_GUID=B.TARGET_GUID
|
|
group by AVAILABILITY_STATUS
|
|
""" % (cluster + "%")
|
|
|
|
cursor.execute(sql)
|
|
|
|
# All that sequence for guarantee series color :)
|
|
series_dict={}
|
|
status_dict={}
|
|
for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
|
|
series_dict[status_name] = "{name: '" + status_name + "', data: [0]},"
|
|
status_dict[status_name] = 0
|
|
|
|
for row in cursor:
|
|
(target_count, availability_status) = row
|
|
for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
|
|
if availability_status == status_name:
|
|
series_dict[status_name] = "{ name: '" + availability_status + "', data: [" + str(target_count) + "]},"
|
|
status_dict[status_name] = target_count
|
|
|
|
series = ""
|
|
for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
|
|
series = series + series_dict[status_name]
|
|
|
|
|
|
|
|
# Dataguard status counters
|
|
sql = """
|
|
SELECT count(*)
|
|
FROM sysman.mgmt$metric_current
|
|
WHERE metric_column='dg_status'
|
|
AND key_value !=target_name
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
for row in cursor:
|
|
dataguard_total_number = row[0]
|
|
|
|
sql = """
|
|
with
|
|
drp_errors as (
|
|
SELECT DISTINCT entity_name,
|
|
value,
|
|
ROUND((sysdate-collection_time)*1440) Min_delta_collect,
|
|
CASE
|
|
WHEN ROUND((sysdate-collection_time)*1440)>=%s
|
|
THEN 'collect_error'
|
|
ELSE
|
|
CASE
|
|
WHEN value>%s
|
|
THEN 'unsync'
|
|
END
|
|
END drp_status
|
|
FROM sysman.GC_METRIC_VALUES_LATEST
|
|
WHERE metric_group_name ='dataguard_sperf_112'
|
|
AND metric_column_name IN ('dg_lag','dg_pdl')
|
|
AND (value >%s
|
|
OR collection_time <=sysdate-%s/1440)
|
|
ORDER BY value DESC,
|
|
Min_delta_collect DESC
|
|
)
|
|
select count(1), drp_status from drp_errors group by drp_status order by drp_status asc
|
|
""" % (DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES, DATGUARD_ACCEPTED_LAG_SECONDS, DATGUARD_ACCEPTED_LAG_SECONDS, DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES)
|
|
|
|
cursor.execute(sql)
|
|
dataguard_status_dict = {"sync":0, "unsync":0, "collect_error":0}
|
|
for row in cursor:
|
|
(count, dataguard_status) = row
|
|
dataguard_status_dict[dataguard_status] = count
|
|
|
|
dataguard_status_dict['sync'] = dataguard_total_number
|
|
|
|
cursor.close()
|
|
db.close()
|
|
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
# Build target status graph
|
|
templateVars = {}
|
|
template = env.get_template('target_status_column.graph')
|
|
|
|
templateVars = {
|
|
"chart_name": "target_status_column_graph",
|
|
"series": series,
|
|
}
|
|
|
|
target_status_column_graph = template.render(templateVars)
|
|
|
|
# Build target status table
|
|
templateVars = {}
|
|
template = env.get_template('target_status.table')
|
|
|
|
templateVars = {
|
|
"target_up": status_dict["Target Up"],
|
|
"target_down": status_dict["Target Down"],
|
|
"blackout": status_dict["Blackout"],
|
|
"metric_error": status_dict["Metric Error"],
|
|
"pending_unknow": status_dict["Pending/Unknown"],
|
|
}
|
|
|
|
target_status_table = template.render(templateVars)
|
|
|
|
# Build dataguard status table
|
|
template = env.get_template('dataguard_status.table')
|
|
templateVars = {
|
|
"collect_error": dataguard_status_dict['collect_error'],
|
|
"unsync": dataguard_status_dict['unsync'],
|
|
"sync": dataguard_status_dict['sync'],
|
|
}
|
|
|
|
dataguard_status_table = template.render(templateVars)
|
|
|
|
|
|
# Drive boxes
|
|
drive_box_content_dict={"FR":"", "UK":"", "SP":"", "IT":""}
|
|
for country_key in drive_box_content_dict:
|
|
drive_db_name = DRIVE_DATABASES[country_key].split("/")[1]
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
|
|
# User session count metric
|
|
sql = """
|
|
select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_USER_SESSION_COUNT'
|
|
and target_name='%s'
|
|
""" % (drive_db_name)
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
user_sessions = row[0]
|
|
|
|
|
|
# Active session count metric
|
|
sql = """
|
|
select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_ACTIVE_SESSION_COUNT'
|
|
and target_name='%s'
|
|
""" % (drive_db_name)
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
active_sessions = row[0]
|
|
|
|
|
|
sql = """
|
|
select value from sysman.mgmt$metric_current where metric_name ='ME$LOCK_COUNT'
|
|
and target_name='%s'
|
|
""" % (drive_db_name)
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
locked_sessions = row[0]
|
|
|
|
templateVars = {}
|
|
template = env.get_template('drive_mini.table')
|
|
templateVars = {
|
|
"user_sessions": user_sessions,
|
|
"active_sessions": active_sessions,
|
|
"locked_sessions": locked_sessions,
|
|
}
|
|
drive_box_content_dict[country_key] = template.render(templateVars)
|
|
except:
|
|
raise
|
|
|
|
|
|
# Build dashboard
|
|
templateVars = {}
|
|
template = env.get_template('dash_start.tpl')
|
|
if cluster == "dmp":
|
|
title = "EXADATA PRODUCTION"
|
|
elif cluster == "dmt":
|
|
title = "EXADATA NON-PRODUCTION"
|
|
|
|
templateVars = {
|
|
"title": title,
|
|
"cluster": cluster,
|
|
"target_status_table": target_status_table,
|
|
"dataguard_status_table": dataguard_status_table,
|
|
"target_status_column_graph": target_status_column_graph,
|
|
"drive_fr": drive_box_content_dict["FR"],
|
|
"drive_uk": drive_box_content_dict["UK"],
|
|
"drive_sp": drive_box_content_dict["SP"],
|
|
"drive_it": drive_box_content_dict["IT"],
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def dash_start2(self, *args, **kwargs):
|
|
self.restrict_url_to_logged()
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
|
|
# Database status counters
|
|
sql = """
|
|
with exadata_db as (
|
|
SELECT
|
|
mgmt$target.host_name
|
|
, mgmt$target.target_name
|
|
, mgmt$target.target_type
|
|
FROM sysman.mgmt$target
|
|
where mgmt$target.host_name like 'dmp01%'
|
|
and mgmt$target.target_type = 'rac_database'
|
|
)
|
|
select count(*), A.AVAILABILITY_STATUS
|
|
FROM sysman.mgmt$availability_current A, sysman.MGMT$TARGET B , exadata_db C
|
|
WHERE B.target_name= C.target_name
|
|
AND A.TARGET_GUID=B.TARGET_GUID
|
|
group by AVAILABILITY_STATUS
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
status_dict={}
|
|
|
|
for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
|
|
status_dict[status_name] = 0
|
|
|
|
for row in cursor:
|
|
(target_count, availability_status) = row
|
|
status_dict[availability_status] = target_count
|
|
|
|
sql = """
|
|
select metric_column, key_value, value from sysman.mgmt$metric_current
|
|
where
|
|
target_name='+ASM_cluster-dmp' and
|
|
target_type='osm_cluster' and
|
|
metric_name='DiskGroup_Usage' and key_value!='DBFS'
|
|
and metric_column in ('total_mb','usable_file_mb')
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
usable = {}
|
|
total = {}
|
|
used = {}
|
|
|
|
for row in cursor:
|
|
(metric_column, key_value, value) = row
|
|
if metric_column == "usable_file_mb":
|
|
usable[key_value] = round (float(value)/1024/1024,2)
|
|
else:
|
|
total[key_value] = round (float(value)/1024/1024,2)
|
|
|
|
for key in total.keys():
|
|
used[key] = round(total[key] - usable[key], 2)
|
|
|
|
dg_data_series = "[%s,%s]" % (used["DATA"], usable["DATA"])
|
|
dg_data_labels = "['Used','Free']"
|
|
|
|
dg_reco_series = "[%s,%s]" % (used["RECO"], usable["RECO"])
|
|
dg_reco_labels = "['Used','Free']"
|
|
|
|
|
|
cursor.close()
|
|
db.close()
|
|
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
|
|
# Build target status table
|
|
templateVars = {}
|
|
template = env.get_template('target_status.table')
|
|
|
|
templateVars = {
|
|
"target_up": status_dict["Target Up"],
|
|
"target_down": status_dict["Target Down"],
|
|
"blackout": status_dict["Blackout"],
|
|
"metric_issue": status_dict["Metric Error"] + status_dict["Pending/Unknown"],
|
|
}
|
|
target_status_table = template.render(templateVars)
|
|
|
|
|
|
# Build Storage graph
|
|
templateVars = {}
|
|
template = env.get_template('asm_dg.graph')
|
|
templateVars = {
|
|
"chart_name": "ASM_DG_DATA",
|
|
"chart_title": "",
|
|
"series": dg_data_series,
|
|
"labels": dg_data_labels,
|
|
}
|
|
asm_data_graph = template.render(templateVars)
|
|
|
|
templateVars = {}
|
|
template = env.get_template('asm_dg.graph')
|
|
templateVars = {
|
|
"chart_name": "ASM_DG_RECO",
|
|
"chart_title": "",
|
|
"series": dg_reco_series,
|
|
"labels": dg_reco_labels,
|
|
}
|
|
asm_reco_graph = template.render(templateVars)
|
|
|
|
# Drive boxes
|
|
drive_box_content_dict={"FR":"", "UK":"", "SP":"", "IT":""}
|
|
for country_key in drive_box_content_dict:
|
|
drive_db_name = DRIVE_DATABASES[country_key].split("/")[1]
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
|
|
# User session count metric
|
|
sql = """
|
|
select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_USER_SESSION_COUNT'
|
|
and target_name='%s'
|
|
""" % (drive_db_name)
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
user_sessions = row[0]
|
|
|
|
|
|
# Active session count metric
|
|
sql = """
|
|
select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_ACTIVE_SESSION_COUNT'
|
|
and target_name='%s'
|
|
""" % (drive_db_name)
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
active_sessions = row[0]
|
|
|
|
|
|
sql = """
|
|
select value from sysman.mgmt$metric_current where metric_name ='ME$LOCK_COUNT'
|
|
and target_name='%s'
|
|
""" % (drive_db_name)
|
|
cursor.execute(sql)
|
|
for row in cursor:
|
|
locked_sessions = row[0]
|
|
|
|
|
|
templateVars = {}
|
|
template = env.get_template('drive_mini.table')
|
|
templateVars = {
|
|
"user_sessions": user_sessions,
|
|
"active_sessions": active_sessions,
|
|
"locked_sessions": locked_sessions,
|
|
"workload": self.db_workload_mini_graph2(drive_db_name),
|
|
}
|
|
drive_box_content_dict[country_key] = template.render(templateVars)
|
|
except:
|
|
raise
|
|
|
|
# Build dashboard
|
|
templateVars = {}
|
|
template = env.get_template('dash_start2.tpl')
|
|
|
|
templateVars = {
|
|
"title": "EXADATA PRODUCTION",
|
|
"target_status_table": target_status_table,
|
|
"asm_data_graph": asm_data_graph,
|
|
"asm_reco_graph": asm_reco_graph,
|
|
"drive_fr": drive_box_content_dict["FR"],
|
|
"drive_uk": drive_box_content_dict["UK"],
|
|
"drive_sp": drive_box_content_dict["SP"],
|
|
"drive_it": drive_box_content_dict["IT"],
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
|
|
return output
|
|
|
|
|
|
@cherrypy.expose()
|
|
def dash_target_status_detail(self, *args, **kwargs):
|
|
self.restrict_url_to_logged()
|
|
# Set to production cluster by default
|
|
try:
|
|
cluster = kwargs["cluster"]
|
|
except:
|
|
cluster = "dmp"
|
|
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql1 = """
|
|
with exadata_db as (
|
|
SELECT
|
|
mgmt$target.host_name
|
|
, mgmt$target.target_name
|
|
, mgmt$target.target_type
|
|
FROM sysman.mgmt$target
|
|
where mgmt$target.host_name like
|
|
"""
|
|
sql2 = "'" + cluster + '%' + "'"
|
|
sql3 = """
|
|
and mgmt$target.target_type = 'rac_database'
|
|
)
|
|
select C.target_name, E.easy_name, A.AVAILABILITY_STATUS
|
|
FROM sysman.mgmt$availability_current A, sysman.MGMT$TARGET B , exadata_db C, system.t_target_easy_name E WHERE B.target_name= C.target_name
|
|
AND B.target_name= E.target_name (+)
|
|
AND A.TARGET_GUID=B.TARGET_GUID
|
|
order by E.display_priority ASC
|
|
"""
|
|
sql = sql1 + sql2 + sql3
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
cursor.close()
|
|
db.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
|
|
|
|
templateVars = {}
|
|
template = env.get_template('target_status_details.table')
|
|
|
|
if cluster == "dmp":
|
|
title = "Exadata production database status"
|
|
elif cluster == "dmt":
|
|
title = "Exadata non-production database status"
|
|
|
|
templateVars = {
|
|
"title": title,
|
|
"query_result": items,
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
|
|
@cherrypy.expose()
|
|
def dash_dataguard_status_detail(self, *args, **kwargs):
|
|
# self.restrict_url_to_logged()
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
with
|
|
drp_errors as (
|
|
SELECT DISTINCT entity_name,
|
|
value,
|
|
ROUND((sysdate-collection_time)*1440) Min_delta_collect,
|
|
CASE
|
|
WHEN ROUND((sysdate-collection_time)*1440)>=%s
|
|
THEN 'Collect error'
|
|
ELSE
|
|
CASE
|
|
WHEN value>%s
|
|
THEN 'Unsync'
|
|
ELSE 'Sync'
|
|
END
|
|
END drp_status
|
|
FROM sysman.GC_METRIC_VALUES_LATEST
|
|
WHERE entity_name like '%s' and metric_group_name ='dataguard_sperf_112'
|
|
AND metric_column_name IN ('dg_lag','dg_pdl')
|
|
AND (value >%s
|
|
OR collection_time <=sysdate-%s/1440)
|
|
ORDER BY value DESC,
|
|
Min_delta_collect DESC
|
|
),
|
|
drp_list as (
|
|
SELECT target_name,
|
|
key_value
|
|
FROM sysman.mgmt$metric_current
|
|
WHERE
|
|
metric_column='dg_status'
|
|
AND key_value !=target_name
|
|
)
|
|
select n.easy_name, e.*, l.target_name from drp_errors e, drp_list l, system.t_target_easy_name n
|
|
where e.entity_name=l.key_value
|
|
and l.target_name = n.target_name (+)
|
|
""" % (DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES, DATGUARD_ACCEPTED_LAG_SECONDS, "%EXA",DATGUARD_ACCEPTED_LAG_SECONDS, DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES)
|
|
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
cursor.close()
|
|
db.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
|
|
templateVars = {}
|
|
template = env.get_template('dataguard_status_details.table')
|
|
|
|
|
|
templateVars = {
|
|
"title": "Unsynchronised/suspicious Dataguards",
|
|
"query_result": items,
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
|
|
def graph_simple_custom_metric(self, database, metric_name, hours_in_past, graph_title, line_color, line_description):
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
SELECT collection_timestamp, value
|
|
FROM sysman.mgmt$metric_details
|
|
WHERE metric_name ='%s'
|
|
AND target_name ='%s'
|
|
AND collection_timestamp >=sysdate-%s/24
|
|
ORDER BY collection_timestamp ASC
|
|
""" % (metric_name, database, hours_in_past)
|
|
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
cursor.close()
|
|
db.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
series_values=[]
|
|
xaxis_values=[]
|
|
for item in items:
|
|
series_values.append(str(item[1]))
|
|
xaxis_values.append('"' + item[0].strftime("%H:%M") + '"')
|
|
|
|
series = 'series: [{name: "' + line_description + '", data: [' + ",".join(series_values) + '] }],'
|
|
xaxis = 'xaxis: { categories: [' + ",".join(xaxis_values) + '] } };'
|
|
|
|
|
|
short_metric_name = metric_name.split("ME$")[1]
|
|
templateVars = {}
|
|
template = env.get_template('simple_custom_metric.graph')
|
|
templateVars = {
|
|
"chart_name": "%s_%s" % (database, short_metric_name),
|
|
"chart_title": graph_title,
|
|
"line_color": line_color,
|
|
"series": series,
|
|
"xaxis": xaxis,
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
def graph_ash_wait_class(self, database):
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, "dmp01-scan/" + database)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
select round(count(*)/60) MINUTES, DECODE(WAIT_CLASS,NULL,'CPU',WAIT_CLASS)
|
|
from gv$active_session_history
|
|
where SAMPLE_TIME between sysdate-1/24 and sysdate
|
|
group by WAIT_CLASS having count(*)>=300 FETCH FIRST 5 ROWS ONLY
|
|
"""
|
|
cursor.execute(sql)
|
|
series_values=[]
|
|
labels_values=[]
|
|
|
|
for row in cursor:
|
|
(secondes, wait_class) = row
|
|
series_values.append(str(secondes))
|
|
labels_values.append("'" + wait_class + "'")
|
|
|
|
series = "[" + ",".join(series_values) + "]"
|
|
labels = '[' + ",".join(labels_values) + "]"
|
|
|
|
cursor.close()
|
|
db.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
templateVars = {}
|
|
template = env.get_template('ash_wait_class.graph')
|
|
templateVars = {
|
|
"chart_name": "graph_ash_wait_class",
|
|
"chart_title": "Wait events profile for last hour",
|
|
"series": series,
|
|
"labels": labels,
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def dashboard_db(self, *args, **kwargs):
|
|
self.restrict_url_to_logged()
|
|
db = kwargs["db"]
|
|
try:
|
|
(instance1, instance2) = get_target_instances(db)
|
|
except ValueError as err:
|
|
# Cannot identify database instances from OEM
|
|
templateVars = {}
|
|
template = env.get_template('box_message_with_back.tpl')
|
|
box_type = "box-primary"
|
|
box_title = db
|
|
box_message = "Instances of %s database are not defined in the OEM repository" % db
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"box_message" : box_message
|
|
}
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
graph_active_sessions = self.graph_simple_custom_metric(db, "ME$GLOBAL_ACTIVE_SESSION_COUNT", ACTIVE_SESS_HIST_HOURS, "Active session history for the last %s hours" % ACTIVE_SESS_HIST_HOURS, "#f3f3f3", "Active sessions")
|
|
if db in ["DRF1PRDEXA", "DRU1PRDEXA", "DRS1PRDEXA", "DRI1PRDEXA"]:
|
|
graph_locks = self.graph_simple_custom_metric(db, "ME$LOCK_COUNT", LOCK_HIST_HOURS, "Locked session for the last %s hours" % LOCK_HIST_HOURS, "#f3f3f3", "Locked sessions")
|
|
else:
|
|
graph_locks = ""
|
|
|
|
graph_ash_wait_class = self.graph_ash_wait_class(db)
|
|
templateVars = {}
|
|
template = env.get_template('dashboard_db.tpl')
|
|
templateVars = {
|
|
"title" : db + " dashboard",
|
|
"graph_active_sessions" : graph_active_sessions,
|
|
"graph_locks" : graph_locks,
|
|
"graph_ash_wait_class" : graph_ash_wait_class,
|
|
}
|
|
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def controlm_dash(self, *args, **kwargs):
|
|
self.restrict_url_to_logged()
|
|
# Identify the table name based on current hour
|
|
now = datetime.datetime.now()
|
|
current_year = now.strftime("%y")
|
|
current_month = now.strftime("%m")
|
|
current_day = now.strftime("%d")
|
|
yesterday = str(int(current_day) - 1)
|
|
if int(current_day) < 10:
|
|
current_day = now.strftime("%d")
|
|
|
|
if int(yesterday) < 10:
|
|
yesterday = "0" + str(int(current_day) - 1)
|
|
|
|
current_hour = now.strftime("%H")
|
|
if int(current_hour) < 11:
|
|
table_name = "EMUSER.A%s001_AJOB" % (current_year + current_month + yesterday)
|
|
else:
|
|
table_name = "EMUSER.A%s001_AJOB" % (current_year + current_month + current_day)
|
|
|
|
# Assume that ration is 50 by default
|
|
try:
|
|
ratio = kwargs["ratio"]
|
|
except:
|
|
ratio = 50
|
|
|
|
# Query the table and store result in items variable
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, CONTROLMDB_EZ_STRING)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
SELECT application ,
|
|
group_name ,
|
|
job_name ,
|
|
memname ,
|
|
description ,
|
|
to_char(to_date(start_time, 'YYYYMMDDHH24MISS'),'DD/MM HH24:MI'),
|
|
SUBSTR(avg_runtime,1,2)
|
|
||'h '
|
|
||SUBSTR(avg_runtime,3,2)
|
|
||'m '
|
|
|| SUBSTR(avg_runtime,5,2)
|
|
||'s',
|
|
TO_CHAR(TRUNC(((
|
|
(SELECT sysdate FROM dual
|
|
) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/3600), '900')
|
|
|| 'h'
|
|
|| TO_CHAR(TRUNC(((
|
|
(SELECT sysdate FROM dual
|
|
) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/60) - TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/3600)*60, '00')
|
|
|| 'm'
|
|
|| TO_CHAR(TRUNC(((
|
|
(SELECT sysdate
|
|
FROM dual
|
|
) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600) - (TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/60)*60)), '00')
|
|
|| 's'
|
|
FROM %s
|
|
WHERE status='Executing'
|
|
AND (trim(TO_CHAR(TRUNC(((
|
|
(SELECT sysdate FROM dual
|
|
) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/3600), '900'))
|
|
|| trim(TO_CHAR(TRUNC(((
|
|
(SELECT sysdate FROM dual
|
|
) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/60) - TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/3600)*60, '00'))
|
|
|| trim(TO_CHAR(TRUNC(((
|
|
(SELECT sysdate FROM dual
|
|
) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600) - (TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/60)*60)), '00') )) > avg_runtime * (1+%s/100)
|
|
ORDER BY start_time ASC
|
|
""" % (table_name, ratio)
|
|
cursor.execute(sql)
|
|
items = list(cursor.fetchall())
|
|
cursor.close()
|
|
db.close()
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
templateVars = {}
|
|
template = env.get_template('controlm_jobs.table')
|
|
templateVars = {
|
|
"title" : " running jobs",
|
|
"items" : items,
|
|
"ratio" : ratio,
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
|
|
# controlm_jobs.table
|
|
return output
|
|
|
|
|
|
def db_workload_mini_graph(self, database):
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, "dmp01-scan/" + database)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
select VALUE from v$parameter where NAME in ('cpu_count','parallel_threads_per_cpu')
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
# We assume that the compute capacity on each instance is cpu_count + parallel_threads_per_cpu
|
|
compute_threads = 2 # for both instances
|
|
for row in cursor:
|
|
compute_threads = compute_threads * int(row[0])
|
|
|
|
sql = """
|
|
select count(*) from gv$session where status='ACTIVE' and type != 'BACKGROUND'
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
for row in cursor:
|
|
active_sessions = row[0]
|
|
|
|
cursor.close()
|
|
db.close()
|
|
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
in_use_percent = int(100*active_sessions/compute_threads)
|
|
if in_use_percent >= 100:
|
|
in_use_percent = 100
|
|
|
|
free_percent = 100-in_use_percent
|
|
series="[%s,%s]" % (in_use_percent, free_percent)
|
|
|
|
templateVars = {}
|
|
template = env.get_template('drive_workload_mini.graph')
|
|
templateVars = {
|
|
"chart_name": "workload_" + database,
|
|
"chart_title": str(compute_threads),
|
|
"series": series,
|
|
"labels": "labels", }
|
|
output = template.render(templateVars)
|
|
|
|
return output
|
|
|
|
def db_workload_mini_graph2(self, database):
|
|
try:
|
|
db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, "dmp01-scan/" + database)
|
|
cursor = db.cursor()
|
|
sql = """
|
|
select VALUE from v$parameter where NAME in ('cpu_count','parallel_threads_per_cpu')
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
# We assume that the compute capacity on each instance is cpu_count + parallel_threads_per_cpu
|
|
compute_threads = 2 # for both instances
|
|
for row in cursor:
|
|
compute_threads = compute_threads * int(row[0])
|
|
|
|
sql = """
|
|
select count(*) from gv$session where status='ACTIVE' and type != 'BACKGROUND'
|
|
"""
|
|
cursor.execute(sql)
|
|
|
|
for row in cursor:
|
|
active_sessions = row[0]
|
|
|
|
cursor.close()
|
|
db.close()
|
|
|
|
except cx_Oracle.DatabaseError as err:
|
|
raise
|
|
|
|
in_use_percent = int(100*active_sessions/compute_threads)
|
|
if in_use_percent >= 100:
|
|
in_use_percent = 100
|
|
|
|
series="[%s]" % in_use_percent
|
|
|
|
templateVars = {}
|
|
template = env.get_template('gauge_workload.graph')
|
|
templateVars = {
|
|
"chart_name": "workload_" + database,
|
|
"chart_title": str(compute_threads),
|
|
"series": series,
|
|
"labels": "labels", }
|
|
output = template.render(templateVars)
|
|
|
|
return output
|
|
|
|
|
|
@cherrypy.expose()
|
|
def unathorized(self, *args, **kwargs):
|
|
templateVars = {}
|
|
template = env.get_template('box_message_with_back.tpl')
|
|
box_type = "box-primary"
|
|
box_title = "Unauthorized"
|
|
box_message = "You are not authorized to use this feature."
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"box_message" : box_message
|
|
}
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
|
|
@cherrypy.expose()
|
|
def show_sync_and_actions(self, *args, **kwargs):
|
|
self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])
|
|
|
|
cherrypy.session["sync_action_has_been_executed"] = "NO"
|
|
templateVars = {}
|
|
template = env.get_template('show_sync_and_actions.tpl')
|
|
try:
|
|
(extract, replicat) = kwargs["sync"].split("_")
|
|
except KeyError as e:
|
|
raise cherrypy.HTTPRedirect("/select_sync")
|
|
|
|
cherrypy.session["extract"] = extract
|
|
cherrypy.session["replicat"] = replicat
|
|
ogg_sync = OGG_Sync(extract, replicat)
|
|
ogg_sync.parse_prm_headers()
|
|
ogg_sync.parse_prm_tables()
|
|
ogg_sync.build_extract_prm()
|
|
ogg_sync.build_replicat_prm()
|
|
ogg_sync.update_sync_status()
|
|
extract_file_contents = open(ogg_sync.extract_prm_filename, "r", encoding = "utf-8", errors = "replace").readlines()
|
|
extract_file_contents = "".join(extract_file_contents).replace("ogguser01$", "*****")
|
|
replicat_file_contents = open(ogg_sync.replicat_prm_filename, "r", encoding = "utf-8", errors = "replace").readlines()
|
|
replicat_file_contents = "".join(replicat_file_contents).replace("ogguser01$", "*****")
|
|
|
|
list_of_logfiles = []
|
|
for root, dirs, files in os.walk(OGG_SERVICE_DIR + "/log/www_executions/"):
|
|
for filename in files:
|
|
if (extract in filename) and (replicat in filename):
|
|
list_of_logfiles.append(filename)
|
|
|
|
list_of_logfiles.sort(key = str.lower, reverse = True)
|
|
|
|
html_list_of_logfiles = "<ul>"
|
|
for logfile in list_of_logfiles:
|
|
html_list_of_logfiles = html_list_of_logfiles + '<li> <i class="fa fa-fw fa-file-text-o"></i> <a href=/show_logfile?logfile=' + logfile + '>' + logfile + '</a> </li>'
|
|
html_list_of_logfiles = html_list_of_logfiles + "</ul>"
|
|
|
|
if DEBUG_MODE:
|
|
debug_info = "nothing"
|
|
# debug_info = ogg_sync.get_class_attributes_as_json()
|
|
else:
|
|
debug_info = ""
|
|
templateVars = {
|
|
"extract_replicat" : extract + "_" + replicat,
|
|
"extract_file_contents" : extract_file_contents,
|
|
"replicat_file_contents" : replicat_file_contents,
|
|
"extract" : ogg_sync.extract,
|
|
"replicat" : ogg_sync.replicat,
|
|
"extract_status" : ogg_sync.extract_status,
|
|
"replicat_status" : ogg_sync.replicat_status,
|
|
"list_of_logfiles" : html_list_of_logfiles,
|
|
"debug_info": debug_info
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def execute_sync_action(self, *args, **kwargs):
|
|
self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])
|
|
|
|
if cherrypy.session["sync_action_has_been_executed"] != "YES":
|
|
action = kwargs["action"]
|
|
extract = cherrypy.session.get("extract")
|
|
replicat = cherrypy.session.get("replicat")
|
|
if action == "stop" or action == "start" or action == "full":
|
|
cherrypy.session["action"] = action
|
|
templateVars = {}
|
|
template = env.get_template('box_confirm.tpl')
|
|
box_type = "box-primary"
|
|
box_title = "Please confirm your action"
|
|
if action != "full":
|
|
box_message = "Are you sure you want to <b>" + action.upper() + "</b> the extract <b>" + extract + "</b> and the replicat <b>" + replicat + "</b> ? "
|
|
else:
|
|
box_message = "Are you sure you want to start a <b>" + action.upper() + "</b> refresh of the OGG sync <b>" + extract + "</b> => <b>" + replicat + "</b> ? "
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"box_message" : box_message
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
if action == "incremental":
|
|
extract_delta_file = kwargs["extract_delta_file"]
|
|
replicat_delta_file = kwargs["replicat_delta_file"]
|
|
|
|
if extract_delta_file.file == None or replicat_delta_file.file == None:
|
|
templateVars = {}
|
|
template = env.get_template('box_message_with_back.tpl')
|
|
box_type = "box-primary"
|
|
box_title = "No file selected"
|
|
box_message = "Please pick both Extract/Replicat delta files before to continue."
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"box_message" : box_message
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
return output
|
|
|
|
# Create extract_delta.prm
|
|
f = open(OGG_SERVICE_DIR + "/sync.d/" + extract + "_" + replicat + "/" + extract + "_delta.prm", 'wb')
|
|
while True:
|
|
data = extract_delta_file.file.read(8192)
|
|
if not data:
|
|
break
|
|
f.write(data)
|
|
f.close()
|
|
# Create replicat_delta.prm
|
|
f = open(OGG_SERVICE_DIR + "/sync.d/" + extract + "_" + replicat + "/" + replicat + "_delta.prm", 'wb')
|
|
while True:
|
|
data = replicat_delta_file.file.read(8192)
|
|
if not data:
|
|
break
|
|
f.write(data)
|
|
|
|
cherrypy.session["action"] = action
|
|
templateVars = {}
|
|
template = env.get_template('box_confirm.tpl')
|
|
box_type = "box-primary"
|
|
box_title = "Please confirm your action"
|
|
box_message = "Are you sure you want to start a <b>" + action.upper() + "</b> refresh of the OGG sync <b>" + extract + "</b> => <b>" + replicat + "</b> ? "
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"box_message" : box_message
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
else:
|
|
output = "Already executed"
|
|
return output
|
|
|
|
@cherrypy.expose()
|
|
def start_ogg_sync_in_background(self, *args, **kwargs):
|
|
self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])
|
|
|
|
if cherrypy.session["sync_action_has_been_executed"] != "YES":
|
|
cherrypy.session["sync_action_has_been_executed"] = "YES"
|
|
action = cherrypy.session.get("action")
|
|
extract = cherrypy.session.get("extract")
|
|
replicat = cherrypy.session.get("replicat")
|
|
runid = datetime.datetime.now().strftime('%Y-%m-%d_%H_%M_%S_%f')
|
|
logfile = OGG_SERVICE_DIR + "/log/www_executions/" + extract + "_" + replicat + "_" + runid + ".log"
|
|
|
|
try:
|
|
# Start GGSCI command in background
|
|
shellcommand = 'echo "source /home/oracle/.bash_profile; /u01/app/oracle/admin/OGG_Service/ogg_sync.py -e ' + extract + ' -r ' + replicat + ' -s ' + action + '>' + logfile +'" | at now'
|
|
logger.info("User " + cherrypy.session.get("ad_uid") + " run: " + shellcommand)
|
|
cherrypy.log(shellcommand)
|
|
cmd = subprocess.run(
|
|
shellcommand,
|
|
check=True,
|
|
shell=True,
|
|
stdout=subprocess.PIPE,
|
|
)
|
|
except:
|
|
pass
|
|
|
|
# Display information windows
|
|
templateVars = {}
|
|
template = env.get_template('box_wait.tpl')
|
|
box_type = "box-primary"
|
|
box_title = "Please wait..."
|
|
redirect_url = "../select_sync"
|
|
redirect_timeout = "5000"
|
|
box_message1 = "Starting JOB: <b>" + action.upper() + "</b> the extract <b>" + extract + "</b> and the replicat <b>" + replicat + "</b>"
|
|
box_message2 = "This page will be <b>automaticly redirected</b> after JOB submission."
|
|
templateVars = {
|
|
"box_type" : box_type,
|
|
"box_title" : box_title,
|
|
"redirect_url" : redirect_url,
|
|
"redirect_timeout" : redirect_timeout,
|
|
"box_message1" : box_message1,
|
|
"box_message2" : box_message2
|
|
}
|
|
templateVars.update(Root.menu)
|
|
output = template.render(templateVars)
|
|
else:
|
|
output = "Already executed"
|
|
return output
|
|
|
|
|
|
@cherrypy.expose
|
|
def other_url(self, *args, **kwargs):
|
|
'''
|
|
The name of the method is mapped to the URL. This url is /other_url
|
|
Try calling this with /other_url/some/path
|
|
Try calling this with /other_url?foo=Foo&bar=Bar
|
|
Try calling this with POST data.
|
|
'''
|
|
return '''\
|
|
Any sub-paths on the URL are available as args: {0}
|
|
Query params _and_ POST data is available via kwargs: {1}
|
|
Headers and the HTTP method and everything else is available via
|
|
the thread-local cherrypy objects ``cherrypy.request`` and
|
|
``cherrypy.response``.
|
|
You can get and set session values and cookies as though they are
|
|
dictionaries:
|
|
cherrypy.session['key'] = 'val'
|
|
cherrypy.request.cookie['key'] = 'val'
|
|
cherrypy.session.get('key', 'defaultval')
|
|
cherrypy.request.cookie.get('key', 'defaultval')
|
|
'''.format(args, kwargs)
|
|
|
|
@cherrypy.expose
|
|
def test_url(self, *args, **kwargs):
|
|
'''
|
|
The name of the method is mapped to the URL. This url is /other_url
|
|
Try calling this with /other_url/some/path
|
|
Try calling this with /other_url?foo=Foo&bar=Bar
|
|
Try calling this with POST data.
|
|
'''
|
|
|
|
text1 = '''\
|
|
Any sub-paths on the URL are available as args: {0}
|
|
Query params _and_ POST data is available via kwargs: {1}
|
|
Headers and the HTTP method and everything else is available via
|
|
the thread-local cherrypy objects ``cherrypy.request`` and
|
|
``cherrypy.response``.
|
|
You can get and set session values and cookies as though they are
|
|
dictionaries:
|
|
cherrypy.session['key'] = 'val'
|
|
cherrypy.request.cookie['key'] = 'val'
|
|
cherrypy.session.get('key', 'defaultval')
|
|
cherrypy.request.cookie.get('key', 'defaultval')
|
|
'''.format(args, kwargs)
|
|
|
|
text2 = Root.user_dict["b11251"]
|
|
|
|
return text2
|
|
|
|
if __name__ == '__main__':
|
|
Root_conf = {
|
|
'/': {
|
|
'tools.staticdir.root': OGG_SERVICE_DIR + '/www'
|
|
},
|
|
'/AdminLTE': {
|
|
'tools.staticdir.on': True,
|
|
'tools.staticdir.dir': 'AdminLTE'
|
|
},
|
|
'global':{
|
|
'server.socket_host' : "FRPIVSQL2418",
|
|
'server.socket_port' : 9026,
|
|
'server.thread_pool' : 4,
|
|
'tools.sessions.on' : True,
|
|
'tools.sessions.timeout': 60,
|
|
'tools.encode.encoding' : "Utf-8"
|
|
}
|
|
}
|
|
# RUN
|
|
cherrypy.quickstart(Root(), '/', config=Root_conf)
|