def check_archivelog_info(db_args, mode, start_time, end_time): # 查询需要挖掘的归档 print("\nINFO:开始查询指定时间内的归档信息:") GET_ARCHIVELOG_SQL = SQL_LOG_MNR['GET_ARCHIVELOG_SQL'] % (start_time, end_time) archivedlog_list, _ = ora_all(db_args, GET_ARCHIVELOG_SQL, mode) if archivedlog_list == []: print("\nINFO:给定时间内,没有发现任何归档!") return 'null arch' else: print( f"\nINFO:指定时间 {start_time} -- {end_time} 存在归档,\n归档日志文件数为:{len(archivedlog_list)} 个!" ) # 查询指定的归档归档量 GET_ARCHIVELOG_SIZE_SQL = SQL_LOG_MNR['GET_ARCHIVELOG_SIZE_SQL'] % ( start_time, end_time) archivelog_size = ora_all(db_args, GET_ARCHIVELOG_SIZE_SQL, mode)[0][0][0] if archivelog_size > 20: print(f"\nINFO:需要挖掘的归档量为{archivelog_size} G,超过20G,日志量过大,建议缩小时间段!") start_time_tmp = input("请重新输入查询起始时间,格式:yyyy-mm-dd hh:MM:ss\n") end_time_tmp = input("请重新输入查询结束时间,格式:yyyy-mm-dd hh:MM:ss\n") check_archivelog_info(db_args, mode, start_time_tmp, end_time_tmp) else: print(f"\nINFO:需要挖掘的归档量为{archivelog_size}G") return archivedlog_list, archivelog_size
def check_db(db_args, mode): check_instance_sql = f"select INST_ID,INSTANCE_NAME,STATUS,VERSION from gv$instance" ins_res, ins_title = ora_all(db_args, check_instance_sql, mode) print(f"\nINFO:检查数据库信息 {db_args[0]}:") db_table = res_table(ins_res, ins_title) print(db_table) return 0
def get_init_tbs_sql(db_args, deflt_info, mode, dbf_path): tbs_list = list(set([i[1] for i in deflt_info])) temp_tbs_list = list(set([i[2] for i in deflt_info])) init_tbs_sql_list = [] init_temp_sql_list = [] dbf_dir = dbf_path for tbs_name in tbs_list: select_dbf_cnt_sql = f"SELECT count(*) from dba_data_files where tablespace_name='{tbs_name}'" datafile_cnt, _ = ora_all(db_args, select_dbf_cnt_sql, mode) create_tbs_sql = f"create tablespace {tbs_name} datafile '{dbf_dir}/{tbs_name}01.dbf' size 10m autoextend on;".replace( '//', '/') init_tbs_sql_list.append(create_tbs_sql) for dbf_id in range(2, datafile_cnt[0][0] + 1): add_db_file_sql = f"alter tablespace {tbs_name} add datafile '{dbf_dir}/{tbs_name}{dbf_id}.dbf' size 10m autoextend on;".replace( '//', '/') init_tbs_sql_list.append(add_db_file_sql) for temp_tbs_name in temp_tbs_list: create_temp_tbs_sql = f"create temporary tablespace {tbs_name} tempfile '{dbf_dir}/{temp_tbs_name}01.dbf' size 10m autoextend on;".replace( '//', '/') init_temp_sql_list.append(create_temp_tbs_sql) print("\nINFO:根据导出数据库环境生成的数据表空间初始化语句如下:\n###") print('\n'.join(init_tbs_sql_list)) print("###") print("\nINFO:根据导出数据库环境生成的默认临时表空间初始化语句如下:\n###") print('\n'.join(init_temp_sql_list)) print("###") print("\nINFO:请根据实际情况在目标环境运行初始化表空间的语句.") return init_tbs_sql_list, init_temp_sql_list
def check_supplelog_status(db_args, mode): print("\nINFO:开始检查数据库附加日志是否开启:") GET_SUPPERLOG_STATUS_SQL = SQL_LOG_MNR['GET_SUPPERLOG_STATUS_SQL'] status_res = ora_all(db_args, GET_SUPPERLOG_STATUS_SQL, mode)[0][0][0] if status_res.upper() == 'YES': print("\nINFO:数据库最小附加日志已打开!") return 'yes' elif status_res.upper() == 'NO': print("\nINFO:数据库最小附加日志未打开!") return 'no' else: print("\nWARNING:查询失败!请检查数据库是否正常!") return 'ora_err'
def check_archivelog_status(db_args, mode): print("\nINFO:开始检查数据库归档是否开启:") GET_ARCHIVELOG_STATUS_SQL = SQL_LOG_MNR['GET_ARCHIVELOG_STATUS_SQL'] status_res = ora_all(db_args, GET_ARCHIVELOG_STATUS_SQL, mode)[0][0][0] if status_res.upper() == 'ARCHIVELOG': print("\nINFO:数据库归档已经开启!") return 'arch' elif status_res.upper() == 'NOARCHIVELOG': print("\nINFO:数据库归档未开启!,无法进行日志挖掘!") return 'unarch' else: print("\nWARNING:查询失败!请检查数据库是否正常!") return 'ora_err'
def clean_dmp_job_tbs(db_args,mode,sid,os_args): sys_user = os_args[2] select_info_sql = "select 'drop table ' || owner_name || '.' || job_name || ';' from dba_datapump_jobs where state = 'NOT RUNNING'" drop_info_sqls_tmp = [sql[0] for sql in ora_all(db_args,select_info_sql,mode)[0]] drop_info_sqls = '\n'.join(drop_info_sqls_tmp) if drop_info_sqls != '': print("\nINFO:开始清理逻辑泵任务状态表.") drop_cmd = f"source ~/.bash_profile\nexport ORACLE_SID={sid}\nsqlplus -s / as sysdba<<EOF\n{drop_info_sqls}\nexit\nEOF" ssh_input_noprint(os_args,f"echo '''{drop_cmd}'''>/home/{sys_user}/drop_dmp_tbs.sh\nchmod +x /home/{sys_user}/drop_dmp_tbs.sh") drop_res = ''.join(ssh_input_noprint(os_args,f"/home/{sys_user}/drop_dmp_tbs.sh")) if 'ORA-' not in drop_res.upper(): print("\nINFO:清理逻辑泵任务状态表完成.") else: print(f"\nINFO:清理逻辑泵任务状态表失败.失败原因为:\n{drop_res}") else: drop_res = "do not clean" return drop_res
def create_dir(db_args, mode, os_args, path): dmp_dir = path check_dir_res = ''.join(ssh_input_noprint(os_args, f"ls {dmp_dir}")) if 'No such file or directory' in check_dir_res: print("\nWARRING:该路径不存在,请检查系统环境!") return 0 create_sql = f"create or replace directory mc_dump_dir as '{dmp_dir}'" grant_sql = f"grant read,write on directory mc_dump_dir to public" check_sql = "select * from dba_directories where DIRECTORY_NAME = 'MC_DUMP_DIR'" create_dir_res = ora_no_fetch(db_args, create_sql, mode) ora_no_fetch(db_args, grant_sql, mode) if 'fail' not in create_dir_res: check_res, check_title = ora_all(db_args, check_sql, mode) t = res_table(check_res, check_title) print(f"\nINFO:{db_args[0]} \n数据库目录MC_DUMP_DIR:{dmp_dir} 创建成功") print(t) return 'create dir s' else: return 'create dir f'
def get_table_info(db_args, start_time, end_time, table_name, table_owner, mc_logmnr_tb_name, mode): print( f"\nINFO:获取表{table_owner}.{table_name} 在{start_time} 至 {end_time} 的历史信息如下:" ) GET_LOGMNR_RES_SQL = SQL_LOG_MNR['GET_LOGMNR_RES_SQL'] % ( mc_logmnr_tb_name, table_name, table_owner) res_get, title = ora_all(db_args, GET_LOGMNR_RES_SQL, mode) if res_get != []: info_table = res_table(res_get, title) print(info_table) else: print( f"\nINFO:表{table_owner}.{table_name} 在{start_time} 至 {end_time} 没有查询到历史数据信息,请确认时间段!" ) print( f"\nINFO:如果需要删除本次挖掘信息,请手工执行<drop table {db_args[1]}.{mc_logmnr_tb_name};>" )
def default_info(sync_obj, db_args, mode): sync_obj = sync_obj.upper() if sync_obj == "FULL_EXPDP": select_deflt_info_sql = '''select distinct b.username,a.tablespace_name,b.TEMPORARY_TABLESPACE,b.profile from dba_tablespaces a, dba_users b,dba_segments c \ where a.tablespace_name not in ('SYSTEM','SYSAUX') and a.contents = 'PERMANENT' \ and a.tablespace_name=c.tablespace_name and b.username=c.owner \ group by b.username,a.tablespace_name,b.TEMPORARY_TABLESPACE,b.profile ''' else: schemas = "','".join( list(set([i.split('.')[0] for i in sync_obj.split(',')]))) select_deflt_info_sql = f'''select distinct b.username,a.tablespace_name,b.TEMPORARY_TABLESPACE,b.profile from dba_tablespaces a, dba_users b,dba_segments c \ where a.tablespace_name not in ('SYSTEM','SYSAUX') and a.contents = 'PERMANENT' \ and a.tablespace_name=c.tablespace_name and b.username=c.owner and username in ('{schemas}')\ group by b.username,a.tablespace_name,b.TEMPORARY_TABLESPACE,b.profile ''' deflt_info, title = ora_all(db_args, select_deflt_info_sql, mode) if deflt_info != []: info_table = res_table(deflt_info, title) print("\nINFO:导出对象用户的策略、默认表空间及默认临时表空间信息如下:") print(info_table) else: pass return deflt_info
def get_rac_state(conn_str, mode): get_rac_sql = "SELECT value FROM v$parameter where name = 'cluster_database'" rac, _ = ora_all(conn_str, get_rac_sql, mode) rac = rac[0][0] return rac
def get_sid(conn_str, mode): get_sid_sql = "select instance_name from v$instance" sid, _ = ora_all(conn_str, get_sid_sql, mode) sid = sid[0][0] return sid
def get_expdp_str(db_args,mode,sync_obj,sys_user,sys_passwd,ssh_port,degree,path): sync_obj = sync_obj.upper() obj_list = tuple(sync_obj.split(',')) ip,db_user,db_port,db_pwd,db_sid = db_args os_args = [ip,ssh_port,sys_user,sys_passwd] sid = get_sid(db_args,mode) # clean dmp jobs table drop_res = clean_dmp_job_tbs(db_args,mode,sid,os_args) if 'ORA-' in drop_res.upper(): print("INFO:请自行清理逻辑泵任务表,sql见输出信息") # schemas expdp if '.' not in sync_obj and sync_obj != 'FULL_EXPDP': check_obj_sql = f"select SEGMENT_NAME from dba_segments where owner in {obj_list} " check_obj_sql = check_obj_sql.replace(',)',')') check_obj,_ = ora_all(db_args,check_obj_sql,mode) if check_obj[0][0] == None: print(f"WARRING:用户 {sync_obj} 不存在!请检查你要导出的对象是否正确.") return 1 estimate_size = estimate_size_expdp(sync_obj,os_args,sid,db_user,db_pwd) if 'estimate size error' not in estimate_size : print(f"\nINFO:导出对象预估大小:{estimate_size}.") if 'T' in estimate_size: print("\nINFO:导出对象过大,请确认是否需要使用逻辑泵导出备份!") return 1 else: print(f"ERROR:导出预估脚本执行失败,报错为:{estimate_size}") return 1 parallel = input_parallel(os_args,degree) if parallel == 'no': return 'no' elif parallel == 1: expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" schemas={sync_obj} directory=mc_dump_dir dumpfile=\$FILE_TARGET.dmp logfile=\$FILE_LOG''' else: rac = get_rac_state(db_args,mode) if rac == 'TRUE': expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" schemas={sync_obj} directory=mc_dump_dir parallel={parallel} cluster=n dumpfile=\${{FILE_TARGET}}_%U.dmp logfile=\$FILE_LOG''' else: expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" schemas={sync_obj} directory=mc_dump_dir parallel={parallel} dumpfile=\${{FILE_TARGET}}_%U.dmp logfile=\$FILE_LOG''' # full db expdp elif sync_obj == 'FULL_EXPDP': estimate_size = estimate_size_expdp(sync_obj,os_args,sid,db_user,db_pwd) if 'estimate size error' not in estimate_size : print(f"\nINFO:导出对象预估大小:{estimate_size}.") if 'T' in estimate_size: print("\nINFO:导出对象过大,请确认是否需要使用逻辑泵导出备份!") return 1 else: print(f"ERROR:导出预估脚本执行失败,报错为:{estimate_size}") return 1 parallel = input_parallel(os_args,degree) if parallel == 'no': return 'no' elif parallel == 1: expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" directory=mc_dump_dir dumpfile=\$FILE_TARGET.dmp logfile=\$FILE_LOG full=y''' else: rac = get_rac_state(db_args,mode) if rac == 'TRUE': expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" directory=mc_dump_dir cluster=n parallel={parallel} dumpfile=\${{FILE_TARGET}}_%U.dmp logfile=\$FILE_LOG full=y''' else: expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" directory=mc_dump_dir parallel={parallel} dumpfile=\${{FILE_TARGET}}_%U.dmp logfile=\$FILE_LOG full=y''' # tables expdp else: for obj in obj_list: owner,table = obj.split('.') check_obj_sql = f"select segment_name from dba_segments where owner = '{owner}' and segment_name = '{table}'" check_obj,_ = ora_all(db_args,check_obj_sql,mode) if check_obj[0][0] == None: print(f"WARRING:表 {owner}.{table} 不存在!请检查你要导出的对象是否正确.") return 1 estimate_size = estimate_size_expdp(sync_obj,os_args,sid,db_user,db_pwd) if 'estimate size error' not in estimate_size : print(f"\nINFO:导出对象预估大小:{estimate_size}.") if 'T' in estimate_size: print("\nINFO:导出对象过大,请确认是否需要使用逻辑泵导出备份!") return 1 else: print(f"ERROR:导出预估脚本执行失败,报错为:{estimate_size}") return 1 parallel = input_parallel(os_args,degree) if parallel == 'no': return 'no' elif parallel == 1: expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" tables={sync_obj} directory=mc_dump_dir dumpfile=\$FILE_TARGET.dmp logfile=\$FILE_LOG''' else: rac = get_rac_state(db_args,mode) if rac == 'TRUE': expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" tables={sync_obj} directory=mc_dump_dir cluster=n parallel={parallel } dumpfile=\${{FILE_TARGET}}_%U.dmp logfile=\$FILE_LOG ''' else: expdp_cmd = f'''expdp \\"'\\" / as sysdba\\"'\\" tables={sync_obj} directory=mc_dump_dir parallel={parallel } dumpfile=\${{FILE_TARGET}}_%U.dmp logfile=\$FILE_LOG ''' return expdp_cmd
def check_expdp(mode, sync_obj, sys_user, sys_passwd, db_args, ssh_port, degree, path, dbf_path): sync_obj = sync_obj.upper() obj_list = tuple(sync_obj.split(',')) ip, db_user, db_port, db_pwd, db_sid = db_args os_args = [ip, ssh_port, sys_user, sys_passwd] sid = get_sid(db_args, mode) mytime = time.strftime("%Y%m%d%H%M", time.localtime()) deflt_info = default_info(sync_obj, db_args, mode) if deflt_info == []: print("\nERROR:数据库用户不存在,请检查后重新输入!") return "none user" # schemas expdp if '.' not in sync_obj and sync_obj != 'FULL_EXPDP': check_obj_sql = f"select SEGMENT_NAME from dba_segments where owner in {obj_list} " check_obj_sql = check_obj_sql.replace(',)', ')') check_obj, _ = ora_all(db_args, check_obj_sql, mode) if check_obj == []: print(f"WARRING:用户 {sync_obj} 不存在!请检查你要导出的对象是否正确.") return 1 estimate_size = estimate_size_expdp(sync_obj, os_args, sid, db_user, db_pwd) print(f"\nINFO:导出对象预估大小:{estimate_size}.") parallel = input_parallel(os_args, degree) if parallel == 'no': return 'no' elif parallel == 1: expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} schemas={sync_obj} directory=mc_dump_dir dumpfile=schemas_{mytime}.dmp logfile=schemas_{mytime}.log''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" schemas={sync_obj} directory=mc_dump_dir dumpfile=schemas_{mytime}.dmp logfile=impdp_schemas_{mytime}.log''' else: rac = get_rac_state(db_args, mode) if rac == 'TRUE': expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} schemas={sync_obj} directory=mc_dump_dir parallel={parallel} cluster=n dumpfile=schemas_{mytime}_%U.dmp logfile=schemas_{mytime}.log''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" schemas={sync_obj} directory=mc_dump_dir parallel={parallel} cluster=n dumpfile=schemas_{mytime}_%U.dmp logfile=impdp_schemas_{mytime}.log''' else: expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} schemas={sync_obj} directory=mc_dump_dir parallel={parallel} dumpfile=schemas_{mytime}_%U.dmp logfile=schemas_{mytime}.log''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" schemas={sync_obj} directory=mc_dump_dir parallel={parallel} dumpfile=schemas_{mytime}_%U.dmp logfile=impdp_schemas_{mytime}.log''' # full db expdp elif sync_obj == 'FULL_EXPDP': estimate_size = estimate_size_expdp(sync_obj, os_args, sid, db_user, db_pwd) print(f"\nINFO:导出对象预估大小:{estimate_size}.") parallel = input_parallel(os_args, degree) if parallel == 'no': return 'no' elif parallel == 1: expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} directory=mc_dump_dir dumpfile=full_{mytime}.dmp logfile=full_{mytime}.log full=y''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" directory=mc_dump_dir dumpfile=full_{mytime}.dmp logfile=impdp_full_{mytime}.log full=y''' else: rac = get_rac_state(db_args, mode) if rac == 'TRUE': expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} directory=mc_dump_dir cluster=n parallel={parallel} dumpfile=full_{mytime}_%U.dmp logfile=full_{mytime}.log full=y''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" directory=mc_dump_dir cluster=n parallel={parallel} dumpfile=full_{mytime}_%U.dmp logfile=impdp_full_{mytime}.log full=y''' else: expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} directory=mc_dump_dir parallel={parallel} dumpfile=full_{mytime}_%U.dmp logfile=full_{mytime}.log full=y''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" directory=mc_dump_dir parallel={parallel} dumpfile=full_{mytime}_%U.dmp logfile=impdp_full_{mytime}.log full=y''' # tables expdp else: for obj in obj_list: owner, table = obj.split('.') check_obj_sql = f"select segment_name from dba_segments where owner = '{owner}' and segment_name = '{table}'" check_obj, _ = ora_all(db_args, check_obj_sql, mode) if check_obj == []: print(f"WARRING:表 {owner}.{table} 不存在!请检查你要导出的对象是否正确.") return 1 estimate_size = estimate_size_expdp(sync_obj, os_args, sid, db_user, db_pwd) print(f"\nINFO:导出对象预估大小:{estimate_size}.") parallel = input_parallel(os_args, degree) if parallel == 'no': return 'no' elif parallel == 1: expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} tables={sync_obj} directory=mc_dump_dir dumpfile=tables_{mytime}.dmp logfile=tables_{mytime}.log ''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" tables={sync_obj} directory=mc_dump_dir dumpfile=tables_{mytime}.dmp logfile=impdp_tables_{mytime}.log ''' else: rac = get_rac_state(db_args, mode) if rac == 'TRUE': expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} tables={sync_obj} directory=mc_dump_dir cluster=n parallel={parallel } dumpfile=tables_{mytime}_%U.dmp logfile=tables_{mytime}.log ''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" tables={sync_obj} directory=mc_dump_dir cluster=n parallel={parallel } dumpfile=tables_{mytime}_%U.dmp logfile=impdp_tables_{mytime}.log ''' else: expdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} tables={sync_obj} directory=mc_dump_dir parallel={parallel } dumpfile=tables_{mytime}_%U.dmp logfile=tables_{mytime}.log ''' impdp_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID=<SID>\nimpdp "'" / as sysdba "'" tables={sync_obj} directory=mc_dump_dir parallel={parallel } dumpfile=tables_{mytime}_%U.dmp logfile=impdp_tables_{mytime}.log ''' print(f"\nINFO:导出命令为:\n###\n{expdp_cmd}\n###") return expdp_cmd, impdp_cmd, deflt_info