Oracle学习笔记[1] - 自动逻辑备份并删除过期备份

公司上了后台是oracle的应用软件，主任要做自动备份，可惜RMAN还用的不熟，只好是用逻辑备份。要求每天自动备份一次，只保留7天的纪录，过期的备份要自动删除。公司服务器是win2003的操作系统。我写了个脚本用windows的任务计划来执行一共2个文件，一个bat文件用任务计划来执行，一个sql文件执行exp命令，由bat文件调用，在执行备份操作的同时可以写log。

sql文件

rem   exp oracle by xiangcns
column today new_val dt
select to_char(sysdate,'YYYY-MM-DD') today from dual;
host echo .>>exp_vprm_loop.log
host echo -&dt->>exp_vprm_loop.log                            rem 将日期写入log
host echo .[exp_vprm_&dt]>>exp_vprm_loop.log
host echo .exp begin %time:~0,8%.>>exp_vprm_loop.log          rem 将时间写入log
host exp userid=admin/admin file=exp_vprm_&dt..dmp log=exp_vprm_&dt..log full=y
host echo .exp end %time:~0,8%.>>exp_vprm_loop.log
exit

 

bat文件

@rem =========exp oracle============
echo off
set oracle_sid=vprm
sqlplus admin/admin @exp_data.sql

@rem =======set date format=========
@echo off
rem GET YESTERDAY DATE
set dt=%date:~0,10%
set today=%date:~0,10%
rem date format is "YYYY-MM-DD"
rem set /P dt="Input Date: "
set dy=%dt:~0,4%
set dm=%dt:~5,2%
set dd=%dt:~8,2%

@rem ===========show date===========
@rem echo %dt%

@rem ===========condition===========            
if %dm%%dd%==0107 goto L01
if %dm%%dd%==0207 goto L02   rem The previous month has 31 day
if %dm%%dd%==0307 goto L07   rem The previous month is February
if %dm%%dd%==0407 goto L02
if %dm%%dd%==0507 goto L04   rem The previous month has 30 day
if %dm%%dd%==0607 goto L02
if %dm%%dd%==0707 goto L04
if %dm%%dd%==0807 goto L02
if %dm%%dd%==0907 goto L02
if %dm%%dd%==1007 goto L05
if %dm%%dd%==1107 goto L03
if %dm%%dd%==1207 goto L06

if %dd%==08 goto L10
if %dd%==09 goto L10
if %dd%==10 goto L12
if %dd%==11 goto L12
if %dd%==12 goto L12
if %dd%==13 goto L12
if %dd%==14 goto L12
if %dd%==15 goto L12
if %dd%==16 goto L11

set /A dd=dd-7           rem subtract 7
set dt=%dy%-%dm%-%dd%
goto CHECK

@rem ============day================
:L10
set /A dd=%dd:~1,1%-7    rem subtract 7
set dt=%dy%-%dm%-0%dd%
goto CHECK
:L11
set dt=%dy%-%dm%-09
goto CHECK
:L12
rem set /A dd=%dd:~0,1%-7    rem subtract 7
set /A dd=%dd%-7             rem subtract 7
set dt=%dy%-%dm%-0%dd%
goto CHECK

@rem =============month==============
:L02
set /A dm=%dm:~1,1%-1
set dt=%dy%-0%dm%-31
goto CHECK
:L04
set /A dm=dm-1
set dt=%dy%-0%dm%-30
goto CHECK

@rem =============month==============
:L05
set dt=%dy%-09-30
goto CHECK
:L03
set dt=%dy%-10-31
goto CHECK
:L06
set dt=%dy%-11-30
goto CHECK
:L01
set /A dy=dy-1
set dt=%dy%-12-31
goto CHECK

@rem============run month=============
:L07
set /A "dd=dy%%4"
if not %dd%==0 goto L08
set /A "dd=dy%%100"
if not %dd%==0 goto L09
set /A "dd=dy%%400"
if %dd%==0 goto L09
:L08
set dt=%dy%-02-28
goto CHECK
:L09
set dt=%dy%-02-29
goto CHECK

@rem=============check=================
:CHECK
if not exist exp_vprm_%today%.dmp goto E01
echo .dmp create successed.>>exp_vprm_loop.log
goto C01

:C01
if not exist exp_vprm_%today%.log goto E02
echo .log create successed.>>exp_vprm_loop.log
goto EXECUTE

:E01
echo .error:dmp do not create successful.>>exp_vprm_loop.log
goto C01

:E02
echo .error:log do not create successful.>>exp_vprm_loop.log
goto EXECUTE

@rem==========del overdue==============
:EXECUTE
@rem echo %dt%
if not exist exp_vprm_%dt%.dmp goto EXE01
del D:/vprm_bk/exp_vprm_%dt%.dmp
echo .exp_vprm_%dt%.dmp has been deleted at %today% %time:~0,8%.>>D:/vprm_bk/exp_vprm_loop.log
goto EXE02

:EXE01
echo .can not find exp_vprm_%dt%.dmp，deletion is break off at %today% %time:~0,8%.>>D:/vprm_bk/exp_vprm_loop.log
goto EXE02

:EXE02
if not exist exp_vprm_%dt%.log goto EXE03
del D:/vprm_bk/exp_vprm_%dt%.log
echo .exp_vprm_%dt%.log has been deleted at %today% %time:~0,8%.>>D:/vprm_bk/exp_vprm_loop.log
goto END

:EXE03
echo .can not find exp_vprm_%dt%.log，deletion is break off at %today% %time:~0,8%.>>D:/vprm_bk/exp_vprm_loop.log
goto END

@rem==============end==================
:END
echo .all execute finished at %today% %time:~0,8%.>>D:/vprm_bk/exp_vprm_loop.log

 

bat文件的主要操作是

（1）连接到数据库

（2）计算今天的日期和7天前的日期，其实代码这么长主要的篇幅就是算日期和写log日志，log有一个好处，可以检查有没有错误的删除记录，包括哪天创建备份文件成功或者失败，代码的check段和del段主要就是检查并记录log。计算日期是因为备份文件就是以项目名加日期的方式进行的。

（3）调用sql文件执行exp

这样的备份方式对于业务主要集中在8小时的工作时间而不是24小时运转的中小企业是完全够用了，而且恢复也很方便，drop掉、create再imp就OK了，这个代码其实性能还是很简单的，虽说实现了全部期望的功能，但是不得不承认，写成这样其实还是很土鳖的，哈哈。