公司上了后臺是oracle的應(yīng)用軟件，主任要做自動備份，可惜RMAN還用的不熟，只好是用邏輯備份。要求每天自動備份一次，只保留7天的紀(jì)錄，過期的備份要自動刪除。公司服務(wù)器是win2003的操作系統(tǒng)。我寫了個腳本用windows的任務(wù)計劃來執(zhí)行一共2個文件，一個bat文件用任務(wù)計劃來執(zhí)行，一個sql文件執(zhí)行exp命令，由bat文件調(diào)用，在執(zhí)行備份操作的同時可以寫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）連接到數(shù)據(jù)庫
（2）計算今天的日期和7天前的日期，其實代碼這么長主要的篇幅就是算日期和寫log日志，log有一個好處，可以檢查有沒有錯誤的刪除記錄，包括哪天創(chuàng)建備份文件成功或者失敗，代碼的check段和del段主要就是檢查并記錄log。計算日期是因為備份文件就是以項目名加日期的方式進行的。
（3）調(diào)用sql文件執(zhí)行exp
這樣的備份方式對于業(yè)務(wù)主要集中在8小時的工作時間而不是24小時運轉(zhuǎn)的中小企業(yè)是完全夠用了，而且恢復(fù)也很方便，drop掉、create再imp就OK了，這個代碼其實性能還是很簡單的，雖說實現(xiàn)了全部期望的功能，但是不得不承認，寫成這樣其實還是很土鱉的，哈哈。
