python 定期备份Oracle 数据库

最近备份测试Oracle数据库，而且是多个账号备份，选择的是使用expdp备份。

 

创建实例备份目录 

备份前需要在数据库创建备份目录

SQL>CREATE OR REPLACE DIRECTORY backup AS '/u01/app/oracle/admin/test/dpdump/backup';

SQL>SELECT * FROM dba_directories;
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS			       test_DB_BACKUP
/u01/app/oracle/admin/test/dpdump/backup

cd /u01/app/oracle/admin/test/dpdump/
mkdir backup
chow oracle:oinstall backup

 

设置定时任务

crontab下默认是不会读取环境变量的，因此我们需要在cronta添加环境变量文件. ~/.bash_profile;

35  11 * * * . ~/.bash_profile; python /home/oracle/script/backup.py >/home/oracle/script/log

 

脚本代码

#!/usr/bin/env python
#coding=utf-8
import os

def OrclBackup():
    if os.system(command) == 0:
        print '备份成功'
        now = time.strftime('%Y-%m-%d %H:%M:%S')
        if os.system(uzip) == 0:
            print '压缩成功'
        else:
            print '压缩失败'
    else:
        print '备份失败'


if __name__ == "__main__":
    global command, uzip
    #账号
    users = ['user1', 'user2', 'user3']
    #密码
    passwd = 'TEST'
    sid = 'TEST'
    backupfloder = 'backup'
    date = time.strftime('%Y-%m-%d')
    for user in users:
        dumpfile = user + '_' + date + '.dump'
        logfile = user + '_' + date + '.log'
        path = '/u01/app/oracle/admin/' + sid + '/dpdump/' + backupfloder + '/'
        command = 'expdp ' + user + '/' + passwd + '@' + sid + ' directory=' + backupfloder + ' dumpfile=' + dumpfile + ' logfile=' + logfile
        uzip = '/bin/gzip ' + path + dumpfile
        OrclBackup()