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)