Windows Server系统定时任务备份ORACLE数据库
Windows Server系统定时任务备份ORACLE数据库
一、编辑备份脚本
RMAN备份数据库
1、在备份脚本目录下，创建bat文件db_rman.bat
set ORACLE_SID=orcl1
D:\app\admin\product\11.2.0\dbhome_1\bin
rman target / cmdfile=D:/dbback/db_scripts/db_rman.txt log=D:/dbback/db_scripts/db_%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%.log
FORFILES /P D:/dbback/db_scripts /M *.log /D -30 /C "cmd /c del @path"
exit
2、创建rman备份脚本db_rman.txt文件
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:/dbback/db_rman/%F.ctl';
backup tag 'dbfull' format 'D:/dbback/db_rman/full%u_%s_%p' database;
sql 'alter system archive log current';
backup archivelog all format 'D:/dbback/db_rman/arc_%T_%U.arg';
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate-14';
delete noprompt obsolete;
delete noprompt expired backup;
}
list backup summary;
exit;
EXPDP备份数据库
在备份脚本目录下编辑定时任务db_expdp.bat
@echo off
REM ###########################################################
REM # Windows Server下Oracle数据库自动备份批处理脚本
REM # 使用expdb命令导出需要先在数据库中创建备份文件存贮目录
REM ###########################################################
REM 取当前系统时间,可能因操作系统不同而取值不一样
set CURDATE=%date:~0,4%%date:~5,2%%date:~8,2%
set CURMON=%date:~0,4%%date:~5,2%
set CURTIME=%time:~0,2%
REM 小时数如果小于10，则在前面补0
if "%CURTIME%"==" 0" set CURTIME=00
if "%CURTIME%"==" 1" set CURTIME=01
if "%CURTIME%"==" 2" set CURTIME=02
if "%CURTIME%"==" 3" set CURTIME=03
if "%CURTIME%"==" 4" set CURTIME=04
if "%CURTIME%"==" 5" set CURTIME=05
if "%CURTIME%"==" 6" set CURTIME=06
if "%CURTIME%"==" 7" set CURTIME=07
if "%CURTIME%"==" 8" set CURTIME=08
if "%CURTIME%"==" 9" set CURTIME=09
set CURTIME=%CURTIME%%time:~3,2%%time:~6,2%
REM 设置所有者、用户名和密码
set OWNER=orcl
set USER=dba
set PASSWORD=manager
REM 创建备份用目录，目录结构为F:\expdpbak
set FILENAME=%OWNER%_%CURDATE%_%CURTIME%.dmp
set EXPLOG=%OWNER%_%CURDATE%_%CURTIME%.log
expdp %USER%/%PASSWORD%@%OWNER% DIRECTORY=DUMP_DIR DUMPFILE=%FILENAME% LOGFILE=%EXPLOG%
FORFILES /P F:\expdpbak /M *.dmp /D -30 /C "cmd /c del @path"
FORFILES /P F:\expdpbak /M *.log /D -30 /C "cmd /c del @path"
exit
EXP/IMP备份数据库
set filename=d:/db_backup/db_expdp/scott_%date:~0,4%%date:~5,2%%date:~8,2%
exp scott/tiger file=%filename%.dmp log=%filename%.log
FORFILES /P D:\db_backup\db_expdp /M *.dmp /D -15 /C "cmd /c del @path"
FORFILES /P D:\db_backup\db_expdp /M *.log /D -15 /C "cmd /c del @path"
exit
二、编辑定时任务（RMAN备份数据库）
创建任务，输入定时任务名称
新建触发器，设置每天23:30:00开始任务
新建操作，选定备份定时任务脚本.bat
条件选项，默认
设置选项，设定任务运行时间超过以下时间，即12小时，停止任务
windows下expdp脚本
#FORFILES /P E:\bak /M *.dmp /D -2 /C "cmd /c del @path"
#删除E:\bak目录下.dmp结尾的3天前的文件
set ORACLE_SID=orcl
set dumpfile=dmp%date:~0,4%%date:~5,2%%date:~8,2%%%U.dmp
set logfile=dmp%date:~0,4%%date:~5,2%%date:~8,2%.log
set syncfile=dmp%date:~0,4%%date:~5,2%%date:~8,2%
set insert_head=insert into BACK_USER.sync_file_list (db_ip,INCREMENTAL_LEVEL,file_name,pwd_name,handle,back_completion_time,sync_status) values('10.86.240.10','0',
e:
cd E:\Oracle\app\Administrator\product\11.2.0\dbhome_1\BIN
expdp \"/ as sysdba\" directory=DIRECTORY_NAME dumpfile=%dumpfile% logfile=%logfile% EXCLUDE=STATISTICS parallel=8 compression=all schemas=HIS,CIS filesize=2G
FORFILES /P F:\expdpbak /M *.dmp /D -6 /C "cmd /c del @path"
FORFILES /P F:\expdpbak /M *.log /D -6 /C "cmd /c del @path"
exit
