Windows下Oracle数据库定时云备份Python脚本

备份脚本网上还是比较多了,但是要想实现相对便捷、安全的云备份,还是需要开动脑筋,自己写脚本来实现。之所以记录下来,是因为在整个过程中会遇到几个“暗坑”,希望大家能够避免。

1、需要准备的软件:

python环境安装包、nnCron LITE(定时任务工具)、7-zip压缩工具、百度同步盘

2、先决条件:

python环境安装完毕,python路径需要加入环境变量,并下载安装cx-Oracle库;7-zip软件安装完毕;nnCron LITE和百度同步盘安装完毕。

3、脚本内容:

# -*- coding:gbk -*-
import os
import time
import subprocess
import shutil

# 数据库地址
db = '127.0.0.1:1521/orcl'

# 用户
user = 'user'

# 导出用户
owner = 'user'

# 密码
psw = '123456'

# 备份保存路径
save_path = 'E:/databak'

# 云盘目录地址
yun_path = 'D:/百度云'

# 压缩包密码
zip_psw = '123456'


def proc():
    """
    调用Oracle备份命令和7z加密压缩命令
    需要提前将Oracle和7zip压缩软件加入环境变量
    """
    print '开始导出Oracle备份文件......'
    time_str = time.strftime('%Y%m%d')

    os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
    if not os.path.exists(save_path):
        os.mkdir(save_path)

    file_name = save_path + '/' + time_str + '.dmp'
    log_name = save_path + '/' + time_str + '.log'
    bak_command = 'exp ' + user + '/' + psw + '@' + db + ' file=' + file_name + ' log=' + log_name + ' owner=' + owner

    subprocess.call(bak_command, shell=True)

    print '开始加密压缩Oracle备份文件......'

    zip_file_name = save_path + '/' + time_str + '.7z'
    zip_source = save_path + '/' + time_str + '.*'
    zip_command = '7z a ' + zip_file_name + ' ' + zip_source + ' -x!*.7z' + ' -p' + zip_psw

    subprocess.call(zip_command, shell=True)

    print '开始云备份文件......'
	
    os.remove(file_name)
    os.remove(log_name)
    shutil.copy(zip_file_name,yun_path + '/' + time_str + '.7z')
	
    print '数据备份工作完成!'



if __name__ == '__main__':
    proc()

脚本中有几点需要注意的:
首先是NLS_LANG的环境变量建议设置上,即使系统中设置有此环境变量。否则会造成导出时的编码问题,如下面的错误信息:

Export: Release 10.2.0.4.0 - Production on Tue Nov 3 09:20:00 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HOME_SCHOOL
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HOME_SCHOOL
About to export HOME_SCHOOL's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HOME_SCHOOL's tables via Conventional Path ...
. . exporting table                  API_APPS_INFO          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table             API_MALL_USERSCORE          1 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table      API_MALL_USERSCORE_RECORD         15 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.

其次,确保python脚本中能用7z命令(需要加入环境变量),或者用7z.exe的绝对路径进行调用。

4、设置定时任务
cron的语法比较好理解,如我需要在凌晨2点备份数据库,则在cron.tab中设置:

#CRONTAB FILE
# Classic crontab format:
# Minutes Hours Days Months WeekDays Command

#*/15 8-16 * * 1-5 checkmail.exe

0 2 * * * C:\Python27\python.exe E:\databak\backup.py

这里需要注意的是,cron中的路径一定要用绝对路径,哪怕你已经将一些命令工具加入了环境变量,也必须使用绝对路径,否则会调用失败。

5、结尾

通过以上的设置,即可实现将数据库导出、压缩、加密并自动上传到云盘的功能,一个廉价的异地云备份方案就这么简单。

转载请注明:梧桐树下 » Windows下Oracle数据库定时云备份Python脚本

与本文相关文章

发表我的评论

取消评论
表情 插代码

Hi,您需要填写昵称和邮箱!

  • 必填项
  • 必填项