def _run_sql(self, query, role, sid, asm_sid=""):
"""
Runs SQL statement as specified role against specified SID.
:param query: SQL statement
:type query: str
:param role: role
:type role: str
:param sid: SID
:type sid: str
:param asm_sid: SID of ASM instance
:type asm_sid: str
"""
if asm_sid == "":
# if an SID for database instance is specified
os.putenv("ORACLE_SID", sid)
os.putenv("ORACLE_HOME", dlpxqa.get_db_home_by_sid(sid))
SQLPLUS = os.path.join(dlpxqa.get_db_home_by_sid(sid), "bin", "sqlplus")
else:
# if an SID for ASM instance is specified
# ORACLE_SID should set to ASM SID
os.putenv("ORACLE_SID", asm_sid)
cv = dlpxqa.get_crs_version()
if DEBUG:
print "_run_sql(): CRS version: ", cv
if cv != "" and cv in ORACLE_11gR2:
# for 11gR2, ASM home is GI home
os.putenv("ORACLE_HOME", dlpxqa.get_crs_home())
SQLPLUS = os.path.join(dlpxqa.get_crs_home(), "bin", "sqlplus")
elif cv != "" and not cv in ORACLE_11gR2:
# for 10gR2 and 11gR1, ASM home is Oracle home
os.putenv("ORACLE_HOME", dlpxqa.get_db_home_by_sid(sid))
SQLPLUS = os.path.join(dlpxqa.get_db_home_by_sid(sid), "bin", "sqlplus")
else:
os.putenv("ORACLE_HOME", dlpxqa.get_db_home_by_sid(sid))
SQLPLUS = os.path.join(dlpxqa.get_db_home_by_sid(sid), "bin", "sqlplus")
if DEBUG:
print "_run_sql(): SQLPLUS: ", SQLPLUS
print "_run_sql(): query: ", query
print "_run_sql(): SID: ", sid
print "_run_sql(): ASM SID: ", asm_sid
QUERY_HEADER = "set linesize 9000\nset pagesize 9999\nset newpage none\nset feedback off\nset verify off\nset echo off\nset heading off\n"
QUERY_END = "\nquit;"
# try to catch OSError and propagate it to to higher handler
try:
temp_dir = tempfile.mkdtemp()
except OSError:
raise
# add read and execute permissions for others
mode = os.stat(temp_dir).st_mode
os.chmod(temp_dir, mode | stat.S_IROTH | stat.S_IXOTH)
sql_filename = os.path.join(temp_dir, "%s.sql" % os.getpid())
sql_file = open(sql_filename, "w+b")
try:
sql_file.write(QUERY_HEADER)
sql_file.write(query)
sql_file.write(QUERY_END)
finally:
sql_file.close()
os_login = dlpxqa.get_database_os_login(sid)
os_user = re.search(r"^(\S+)\/", os_login).group(1)
if DEBUG:
print "_run_sql(): os_user: "******"' + "/ as " + role + '"' + " @" + sql_file.name
sqlplus_cmd = dlpxqa.SU + os_user + " -c '" + sqlplus_cmd + "'"
if DEBUG:
print "_run_sql(): sqlplus_cmd: ", sqlplus_cmd
output = dlpxqa.exec_cmd(sqlplus_cmd)
shutil.rmtree(temp_dir)
return output
connection_factory = dbm_connection.ConnectionFactory()
print strftime("%Y-%m-%d %H:%M:%S", localtime())
conn_dict = get_connections(connection_factory, 'DBMONITOR', dlpxqa.get_database_list(), regsrv_ip)
discovery_failure_host_dict = {}
connection_failure_host_dict = {}
for conn in conn_dict.values():
if conn.is_connected():
print (70 * '=')
host_name = conn.root.get_host_name()
sid = conn.database
ip = dlpxqa.get_database_ip(sid)
login = dlpxqa.get_database_os_login(sid)
print 'HOST : %s (%s)' % (host_name, ip)
print 'SID : %s' % sid
print 'LOGIN: %s' % login
try:
database_role = conn.root.get_database_role(sid)
except OSError, e:
print 'ERROR: OS error raised: %s' % e.strerror
continue
print 'ROLE : %s' % database_role.rstrip()
archive_dest = conn.root.get_archive_dest(sid)
print 'ARCHIVE DEST : %s' % archive_dest
type, usage = conn.root.get_archive_dest_usage(sid)