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
