1. 主程序
# Oracle重要信息备份,假设schema名称为USERNAME,要备份的信息为USERNAME,USERNAME2(部分)
# 填写任务和作者名字
_taskname = "Oracle_backup"
_owner = "JOGARY"
from pandas import DataFrame
# from sqlalchemy import create_engine
from cx_Oracle import connect
from datetime import datetime
from os import path,mkdir
starttime = datetime.now()
# 定义一个数据表读取函数
def load_data(sql):
db=connect('username','password','10.10.10.11:1521/orcl')
cr=db.cursor()
cr.execute(sql)
rs = cr.fetchall()
# 获取lob字段
for i in range(len(rs)):
rs[i] = list(rs[i])
try:
for j in range(len(rs[i])):
rs[i][j] = rs[i][j].read()
except:
continue
cols = [k[0] for k in cr.description]
zz = DataFrame(rs, columns=cols)
cr.close()
db.close()
return zz
try:
file_count = 0
comment = ''
datetimekey = datetime.now().strftime('%Y%m%d')
paths = 'E:/1.数据备份/'+datetimekey
# paths = './'+datetimekey
if(path.exists(paths)==False):
mkdir(paths)
comment += '{}文件夹已创建;'.format(datetimekey)
# os.path.exists(path)
# 获取所有建表语句
# print('开始备份数据:')
all_tabs = load_data("select 'select dbms_metadata.get_ddl(''TABLE'','''||table_name||''') sql_text from dual' TEXT from user_tables")
text = ' union all '.join(all_tabs['TEXT'])
data = load_data(text)
with open (paths+'/all_create_tables.sql','w') as f:
f.write('\n\n'.join(data.iloc[:,0]))
comment += '建表语句备份成功;'
# print('所有建表语句备份成功。')
file_count += 1
# 获取所有建视图语句
all_views = load_data("select 'select dbms_metadata.get_ddl(''VIEW'','''||view_name||''') sql_text from dual' TEXT from user_views")
text = ' union all '.join(all_views['TEXT'])
data = load_data(text)
with open (paths+'/all_create_views.sql','w') as f:
f.write('\n\n'.join(data.iloc[:,0]))
comment += '建视图语句备份成功;'
# print('所有建视图语句备份成功。')
file_count += 1
# 获取所存储过程和函数
data = load_data("SELECT * FROM ALL_SOURCE WHERE OWNER IN ('USERNAME','USERNAME2')")
try:data.to_csv(paths+'/all_sources.csv',encoding='gbk')
except:data.to_csv(paths+'/all_sources.csv')
# print('所有存储过程和函数备份成功。')
comment += '存储过程和函数备份成功;'
file_count += 1
# 获取所有建存储过程和函数语句
all_sources = data[(data['LINE']==1) & (data['OWNER']=='USERNAME')].copy()
all_sources['TEXT'] = "select dbms_metadata.get_ddl('"+all_sources['TYPE']+"','"+all_sources['NAME']+"') SQL_TEXT FROM DUAL"
text = ' union all '.join(all_sources['TEXT'])
data = load_data(text)
with open (paths+'/all_create_sources.sql','w',encoding='utf-8') as f:
f.write('\n\n'.join(data.iloc[:,0]))
comment += '建存储过程和函数语句备份成功;'
# print('所有建存储过程和函数语句备份成功。')
file_count += 1
# 获取所有表和视图列
data = load_data("SELECT * FROM all_tab_cols WHERE OWNER IN ('USERNAME','USERNAME2')")
try:data.to_csv(paths+'/all_tables.csv',encoding='gbk')
except:data.to_csv(paths+'/all_tables.csv')
# print('所有表和视图列备份成功。')
comment += '表和视图列备份成功;'
file_count += 1
# 获取所有索引列表
data = load_data("SELECT * FROM ALL_INDEXES WHERE OWNER IN ('USERNAME','USERNAME2')")
try:data.to_csv(paths+'/all_indexes.csv',encoding='gbk')
except:data.to_csv(paths+'/all_indexes.csv')
# print('所有索引备份成功。')
comment += '索引备份成功;'
file_count += 1
# 获取所有建索引语句
all_indexes = data[data['OWNER']=='USERNAME'].copy()
all_indexes['TEXT'] = "select dbms_metadata.get_ddl('INDEX','"+all_indexes['INDEX_NAME']+"') SQL_TEXT FROM DUAL"
text = ' union all '.join(all_indexes['TEXT'])
data = load_data(text)
with open (paths+'/all_create_indexes.sql','w') as f:
f.write('\n\n'.join(data.iloc[:,0]))
comment += '建索引语句备份成功;'
# print('所有建索引语句备份成功。')
file_count += 1
# 获取所有索引列
data = load_data("SELECT * FROM ALL_IND_COLUMNS WHERE INDEX_OWNER IN ('USERNAME','USERNAME2')")
try:data.to_csv(paths+'/all_index_columns.csv',encoding='gbk')
except:data.to_csv(paths+'/all_index_columns.csv')
comment += '索引列备份成功;'
# print('所有索引列备份成功。')
file_count += 1
# 获取所有comments
data = load_data("""SELECT OWNER,TABLE_NAME,TABLE_TYPE,NULL COLUMN_NAME,COMMENTS,ORIGIN_CON_ID FROM ALL_TAB_COMMENTS WHERE OWNER IN ('USERNAME','USERNAME2')
UNION ALL
SELECT OWNER,TABLE_NAME,'COLUMN' TABLE_TYPE,COLUMN_NAME,COMMENTS,ORIGIN_CON_ID FROM ALL_COL_COMMENTS WHERE OWNER IN ('USERNAME','USERNAME2')
""")
try:data.to_csv(paths+'/all_comments.csv',encoding='gbk')
except:data.to_csv(paths+'/all_comments.csv')
comment += 'comments备份成功;'
# print('所有comments备份成功。')
file_count += 1
# 获取是所有jobs
data = load_data("""select TO_NUMBER(SUBSTR(JOB_NAME,11,10)) JOB_ID,
JOB_CREATOR,OWNER,JOB_ACTION,
TO_CHAR(START_DATE,'YYYY-MM-DD HH24:MI:SS') START_DATE,REPEAT_INTERVAL,ENABLED,STATE,RUN_COUNT,FAILURE_COUNT,
TO_CHAR(LAST_START_DATE,'YYYY-MM-DD HH24:MI:SS') LAST_START_DATE,LAST_RUN_DURATION,
TO_CHAR(NEXT_RUN_DATE,'YYYY-MM-DD HH24:MI:SS') NEXT_RUN_DATE
from dba_scheduler_jobs t
where JOB_NAME like 'DBMS_JOB%'
ORDER BY JOB_ID
""")
try:data.to_csv(paths+'/all_jobs.csv',encoding='gbk')
except:data.to_csv(paths+'/all_jobs.csv')
# print('所有jobs备份成功。')
comment += 'jobs备份成功;'
file_count += 1
comment = "备份成功,已备份{}个文件:".format(file_count) + comment
# print('备份完成。')
except Exception as ex:
comment = "备份失败,错误类型:{};已备份{}个文件:".format(ex,file_count) + comment
#---------------------以下内容通常无需改动----------------------
#当前文件路径
from sys import argv
import socket
s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
s.connect(("8.8.8.8", 80))
file_path = "{}->{}".format(s.getsockname()[0],argv[0])
#写入日志表
db = connect('username','password','10.10.10.11:1521/orcl')
cr = db.cursor()
endtime = datetime.now()
cr.execute('''INSERT INTO ETL_LOG_PYTHON(TASK_NAME,START_TIME,END_TIME,TASK_TIME,OWNER,COMMENTS,TASK_PATH)
VALUES(:1,:1,:1,:1,:1,:1,:1)
''',[_taskname,starttime,endtime,(endtime-starttime).seconds,_owner,comment,file_path])
db.commit()
cr.close()
db.close()
2. 自动化配置
程序测试OK后,要实现自动化,还需要一些配置:
1. 需将程序打包成exe文件,方法参考:Python打包exe文件(如何打包成较小文件)_jogarys的博客-CSDN博客
2. Windows系统中配置自动任务(最好找个服务器配置,否则个人电脑关闭后就不能运行了),路径如下:
控制面板→系统和安全→管理工具→计划任务
点击右侧创建基本任务,选择1中打包好的exe文件,设置好运行频率即可
3. 结果测试
运行成功后,运行日志如下:
TASK_NAME | START_TIME | END_TIME | TASK_TIME | OWNER | COMMENTS | INTERFACE_TIME | TASK_PATH |
---|---|---|---|---|---|---|---|
Oracle_backup | 2023-06-18 10:45:59 | 2023-06-18 10:47:02 | 62 | JOGARY | 备份成功,已备份10个文件:20230618文件夹已创建;建表语句备份成功;建视图语句备份成功;存储过程和函数备份成功;建存储过程和函数语句备份成功;表和视图列备份成功;索引备份成功;建索引语句备份成功;索引列备份成功;comments备份成功;jobs备份成功; | 2023-06-18 10:46:41 | 10.10.10.11->E:\backup\Oracle_backup.exe |
备份好的文件如下: