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
