linux下expdp定时备份_[Oracle] linux数据泵的crontab定时备份和oracle job定时备份

1.数据泵定时备份

创建数据泵目录

CREATE DIRECTORY dumpdir AS '/home/oracle/backup';

GRANT READ,WRITE ON DIRECTORY dumpdir TO apps;

建立定时计划 每天1点执行

crontab -e

0 1 * * * /home/oracle/backup/orcl_expdp.sh

expdp导出脚本

vi /home/oracle/backup/orcl_expdp.sh

export PATH

export ORACLE_BASE=/apps/oracle_11/

export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/db_1

export ORACLE_SID=orcl

export PATH=$PATH:HOME/bin:$ORACLE_HOME/bin

BACKUP_DATE=$(date +%Y%m%d)

REMOVE_DATE=$(date +%Y%m%d -d '15 days ago')

expdp

apps/apps directory=dumpdir SCHEMAS=apps exclude=statistics

dumpfile="apps_$BACKUP_DATE".dmp  logfile="apps_$BACKUP_DATE".log

cd /home/oracle/backup/

mkdir $BACKUP_DATE

mv apps* $BACKUP_DATE/

rm -rf $REMOVE_DATE/

2.oracle job 方式定时备份

创建job任务，建议使用sys用户

begin

dbms_scheduler.create_job

(

job_name => 'expdp_job',

job_type => 'EXECUTABLE',

REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=1',

job_action => /home/oracle/backup/orcl_expdp.sh,

enabled => true,

comments => 'expdp user guoyk '

);

end;

/

手动执行一次： exec dbms_scheduler.run_job('expdp_job'); linux系统命令行中执行，plsql执行报错

exec dbms_scheduler.stop_job('expdp_job');

删除job              exec dbms_scheduler.drop_job(job_name => 'expdp_job',force => TRUE);

查看相关表     select * from dba_scheduler_jobs;

select * from dba_scheduler_job_run_details;可以查询每天执行sucess or failed.

ps:只保留15天的备份