def ora_clean_log(os_args, retention_day): ssh_ftp(os_args, '/tmp/ora_clean.sh', 'ora_clean_log\del_log.sh', 'put') ssh_input_noprint(os_args, 'chmod 775 /tmp/ora_clean.sh') run_res = ssh_trans_time(os_args, f'/tmp/ora_clean.sh {retention_day} ') ssh_input_noprint(os_args, 'rm -f /tmp/ora_clean.sh') return run_res
def ora_tbs_check(os_args, sid): ssh_ftp(os_args, '/tmp/ora_tbs_check.sh', 'ora_tbs_check\\add_tbs_check_and_advice.sh', 'put') ssh_input_noprint(os_args, 'chmod 775 /tmp/ora_tbs_check.sh') print("\nINFO:开始执行脚本:") run_res = ssh_trans_time(os_args, f'/tmp/ora_tbs_check.sh {sid} ') ssh_input_noprint(os_args, 'rm -f /tmp/ora_tbs_check.sh') return run_res
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 estimate_size_expdp(sync_obj,os_args,sid,db_user,db_pwd): sys_user = os_args[2] print("\nINFO:现在开始预估导出文件大小,可能会花费些许时间.") if '.' not in sync_obj and sync_obj != 'FULL_EXPDP': estimate_size_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp \\"'\\" / as sysdba\\"'\\" schemas={sync_obj} ESTIMATE_ONLY=y''' elif sync_obj == 'FULL_EXPDP': estimate_size_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp \\"'\\" / as sysdba\\"'\\" full=y ESTIMATE_ONLY=y''' else: estimate_size_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp \\"'\\" / as sysdba\\"'\\" tables={sync_obj} ESTIMATE_ONLY=y''' ssh_input_noprint(os_args,f"echo '''{estimate_size_cmd}'''>/home/{sys_user}/estimate_size.sh\nchmod +x /home/{sys_user}/estimate_size.sh") estimate_size_res = ssh_input_noprint(os_args,f'/home/{sys_user}/estimate_size.sh') estimate_size_tmp = [i for i in estimate_size_res if 'Total estimation using BLOCKS method:' in i] if estimate_size_tmp != []: estimate_size = estimate_size_tmp[0].split(':')[-1].replace('\n','') else: estimate_size = '\nestimate size error \n'+''.join(estimate_size_res) return estimate_size
def input_parallel(os_args,degree): if degree == 0: degree = 1 else: pass cpu_cnt = int(ssh_input_noprint(os_args,'''cat /proc/cpuinfo | grep "processor" |wc -l''')[0].replace('\n','')) p_tmp = cpu_cnt // 2 + cpu_cnt % 2 print(f"\nINFO:导出环境的逻辑CPU数为{cpu_cnt},推荐并行度应不超过{p_tmp},用户选择并行度为{degree}") if degree > p_tmp: print("WARRNING:请不要选择大于1/2倍逻辑CPU数的并行度!") return 'no' return degree
def estimate_size_expdp(sync_obj, os_args, sid, db_user, db_pwd): print("\nINFO:现在开始预估导出文件大小,可能会花费些许时间.") if '.' not in sync_obj and sync_obj != 'FULL_EXPDP': estimate_size_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} schemas={sync_obj} ESTIMATE_ONLY=y''' elif sync_obj == 'FULL_EXPDP': estimate_size_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} full=y ESTIMATE_ONLY=y''' else: estimate_size_cmd = f'''source ~/.bash_profile\nexport ORACLE_SID={sid}\nexpdp {db_user}/{db_pwd} tables={sync_obj} ESTIMATE_ONLY=y''' estimate_size_res = ssh_input_noprint(os_args, estimate_size_cmd) estimate_size_tmp = [ i for i in estimate_size_res if 'Total estimation using BLOCKS method:' in i ][0] estimate_size = estimate_size_tmp.split(':')[-1].replace('\n', '') return estimate_size
def ora_drop_tables(os_args, drop_tables_dict): print(''' ************************************************************ 本脚本用于生成用户下批量快速删除表对象的脚本 本脚本适用于单个用户下具有几万表对象的场景 ## 支持的操作系统和数据库版本配对 操作系统:LINUX/AIX/UNIX 数据库版本:Oracle 10g-19c 源数据库架构:单机 ************************************************************''') sid = drop_tables_dict['sid'] script_dir = drop_tables_dict['script_dir'] drop_user = drop_tables_dict['drop_user'] drop_user_passwd = drop_tables_dict['drop_user_passwd'] oracle_home = drop_tables_dict['oracle_home'] gap = drop_tables_dict['gap'] script_path = f'{script_dir}/ora_drop_tables.sh'.replace('//', '/') ssh_ftp(os_args, script_path, 'ora_drop_tables\get_drop_table.sh', 'put') ssh_input_noprint(os_args, f'chmod 775 {script_path}') run_res = ssh_input_noprint( os_args, f'{script_path} {sid} {script_dir} {drop_user} {drop_user_passwd} {oracle_home} {gap}' ) print(''.join(run_res)) print( f'\nINFO:生成truncate 用户{drop_user} 下表脚本路径为:\n{script_dir}/parall_cmd_truncate.sh' ) print( f'\nINFO:生成drop 用户{drop_user} 下表脚本路径为:\n{script_dir}/parall_cmd_drop.sh\n' ) ssh_input_noprint(os_args, f'rm -f {script_path}') return run_res
def crontab_config(crontab_date_str,os_args,sid): sys_user,sys_passwd = os_args[2:4] args_list = crontab_date_str.split(' ') if len(args_list) != 5: print ("ERROR:crontab 时间配置参数填写错误,请确保用一下格式填写:\n分钟 小时 日 月 周,以一个空格隔开,详情参考crontab设置写法") return "str error" else: mi,hour,day,month,week = args_list if month != '*': period = 'month' elif week != '*': period = 'week' else: period = 'day' crontab_str = f"# The backup for instance which sid is {sid},and period is {period.upper()}\n{crontab_date_str} /home/{sys_user}/expdp_{period}_{sid}.sh>/tmp/expdp_{period}_{sid}.out" ssh_input_noprint(os_args,f"crontab -l>/home/{sys_user}/mc_crontab") ssh_input_noprint(os_args,f"crontab -l>/home/{sys_user}/mc_crontab_bak") ssh_input_noprint(os_args,f"echo '''{crontab_str}''' >>/home/{sys_user}/mc_crontab") ssh_input_noprint(os_args,f"crontab /home/{sys_user}/mc_crontab") return "crontab setup complete",period
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 expdp(db_args,sync_obj,sys_user,sys_passwd,mode,ssh_port,degree,path,dbf_path,target_os_args,tag_dmp_path): ip =db_args[0] os_args = [ip,ssh_port,sys_user,sys_passwd] check_db(db_args,mode) create_dir_res = create_dir(db_args,mode,os_args,path) if create_dir_res == 'create dir s': expdp_cmd,impdp_cmd,deflt_info = check_expdp(mode,sync_obj,sys_user,sys_passwd,db_args,ssh_port,degree,path,dbf_path) if expdp_cmd != 1 and expdp_cmd != 'no' and expdp_cmd != 'none user': print(f"\nINFO:expdp命令运行中,请关注本地ora_ops.log或数据库环境下的导出日志") expdp_res = ssh_input_noprint(os_args,expdp_cmd) print(''.join(expdp_res)) if 'completed' in ''.join(expdp_res): dmp_file_name = path+'/'+expdp_cmd.split('dumpfile=')[-1].split(' ')[0] print(f"\nINFO:expdp命令执行完成,导出文件路径为:{dmp_file_name}。") print("\nINFO:开始传输导出文件到目标库") tag_dmp_file_path = tag_dmp_path+'/'+expdp_cmd.split('dumpfile=')[-1].split(' ')[0] ssh_scp(os_args,target_os_args,dmp_file_name,tag_dmp_file_path) print(f"\nINFO:导出文件传输完成,目标环境路径为{tag_dmp_file_path}") get_init_tbs_sql(db_args,deflt_info,mode,dbf_path) profile_list = list(set([i[3] for i in deflt_info])) check_profile(db_args,mode,profile_list) tag_create_sql = f"create or replace directory mc_dump_dir as '{tag_dmp_path}';\ngrant read,write on directory mc_dump_dir to public;" print(f"\nINFO:目标库导入路径目录创建语句为:\n{tag_create_sql}") print(f"\nINFO:目标库导入命令为:\n###\n{impdp_cmd}\n###") return "expdp s" else: print("\nWARNING:expdp命令执行失败。") return "expdp f" else: return 1 else: print("\nERRO:数据库目录创建失败,详情请看日志。") return create_dir_res
def ora_service_set(os_args): print('''\nINFO:Oracle自启动关闭使用范围: 1.Oracle数据库单机环境 2.Oracle数据库版本11g以上 3.主机上存在多个版本的数据库和监听 4.Linux 6-7 ''') print("\nINFO:开始配置脚本:") ssh_ftp(os_args, '/tmp/ora_dbservice_set.sh', 'ora_service_set\DbServiceSet.sh', 'put') ssh_input_noprint(os_args, 'chmod 775 /tmp/ora_dbservice_set.sh') print('\nINFO: 获取数据库环境服务信息:') run_res = ssh_input_noprint(os_args, f'/tmp/ora_dbservice_set.sh') print(''.join(run_res)) ora_home = [res for res in run_res if 'ORACLE_HOME is:' in res][0].split(' ')[-1] db_services = input("请根据已知信息填写要添加的数据库服务,若不添加,请回车:\n") listener_services = input("请根据已知信息填写要添加的监听服务,若有多个,请用逗号隔开,若不添加,请回车:\n") if db_services != '' and listener_services != '': run_cmd = f"/tmp/ora_dbservice_set.sh -d {db_services} -o {ora_home} [-l {listener_services}]" elif db_services != '' and listener_services == '': run_cmd = f"/tmp/ora_dbservice_set.sh -d {db_services} -o {ora_home}" elif db_services == '' and listener_services != '': run_cmd = f"/tmp/ora_dbservice_set.sh -o {ora_home} [-l {listener_services}]" else: print("\nWARNING:未知错误!") return "err" print("\nINFO:开始执行数据库服务添加自启动:") ssh_trans_time(os_args, run_cmd) print("\nINFO:检查通过当前脚本设置的自启动资源信息:") check_res = ssh_input_noprint(os_args, '/tmp/ora_dbservice_set.sh -m l') print(''.join(check_res)) ssh_input_noprint(os_args, 'rm -f /tmp/ora_dbservice_set.sh') print( '''\nINFO: 配置完成后,需要通过service DbService stop或者systemctl stop DbService 停止资源一次,进行验证 支持管理命令如下 Linux7以下:`service DbService start|stop|restart` Linux7以上:`systemctl start|stop|status DbService`''') return check_res
def expdp_cron(db_args, sync_obj, sys_user, sys_passwd, mode, ssh_port, degree, path, ftp_ip, ftp_dir, ftp_user, ftp_passwd, crontab_date_str, retention_day): ip, db_user, db_port, db_pwd, sid = db_args os_args = [ip, ssh_port, sys_user, sys_passwd] check_db(db_args, mode) backup_dir = path create_dir_res = create_dir(db_args, mode, os_args, path) if create_dir_res == 'create dir s': expdp_str = get_expdp_str(db_args, mode, sync_obj, sys_user, sys_passwd, ssh_port, degree, path) crontab_res, period = crontab_config(crontab_date_str, os_args, sid) if crontab_res == 'crontab setup complete': if expdp_str != 1 and expdp_str != 'no': expdp_cron_str = f''' source ~/.bash_profile export ORACLE_SID={sid} BACKUP_HOME={backup_dir} DAY=\` date +%Y%m%d_%H_%M_%S \` FILE_TARGET={sid}_{period}_\$DAY FILE_LOG={sid}_{period}_\$DAY.log FTP_IP={ftp_ip} FTP_BACKUP_HOME={ftp_dir} FTP_USER={ftp_user} FTP_PASSWD={ftp_passwd} export FILE_TARGET export FILE_LOG echo "Begin backup database by expdp at Time:"\`date\` {expdp_str} echo "Export mission over at Time:"\`date\` echo "Delete {retention_day} days ago Export File" find \$BACKUP_HOME -name "{sid}_{period}_*.dmp" -mtime +{retention_day} -exec rm {{}} \; find \$BACKUP_HOME -name "{sid}_{period}_*.log" -mtime +{retention_day} -exec rm {{}} \; echo "ALL WORKS COMPLETE! GOOD LUCK!" echo ---end---; ftp -n<<! open \$FTP_IP user \$FTP_USER \$FTP_PASSWD lcd \$BACKUP_HOME cd \$FTP_BACKUP_HOME prompt binary mput *.dmp cd \$FTP_BACKUP_HOME mput *.log close bye ! ''' ssh_input_noprint( os_args, f'''echo "{expdp_cron_str}">/home/{sys_user}/expdp_{period}_{sid}.sh\nchmod +x /home/{sys_user}/expdp_{period}_{sid}.sh''' ) print( f"\nINFO:逻辑备份脚本路径为:/home/{sys_user}/expdp_{period}_{sid}.sh" ) crontab_str_after = ''.join( ssh_input_noprint(os_args, "crontab -l")) print(f"\nINFO:当前crontab定时任务内容为:\n{crontab_str_after}") else: print("\nERROR:备份语句生成失败.") ssh_input_noprint(os_args, f"crontab /home/{sys_user}/mc_crontab_bak") return expdp_str else: print("\nERROR:crontab 设置失败.") return crontab_res else: print("\nERRO:数据库目录创建失败,详情请看日志。") return create_dir_res