Oracle數(shù)據(jù)庫定時(shí)自動備份批處理代碼

echooffsetpweek=%date:~-1%setfn=e:\bakdb\gdosys_dqgis%Date:~0,4%%Date:~5,2%%Date:~8,2%if%pweek%equ日(expsystem/sysorclfile=%fn%.dmpowner=gdosys,dqgislog=%fn%.lograra%fn%.rar%fn%.*del%fn%.dmpdel%fn%.log)else(expsystem/sysorclfile=d:\bakweek\%date:~8,2%.dmpowner=gdosys,dqgislog=d:\bakweek\%date:~8,2%.logremexpsystem/sysorclfile=d:\bakweek\%date:~11,3%.dmpowner=gdosyslog=d:\bakweek\%date:~11,3%.log)hi.baidu./lvns/blog/item/beb171d3c2fa2cd6a8ec9a2d.html這是最近寫的一個(gè)Oracle數(shù)據(jù)庫自動備份的批處理，經(jīng)過測試正常運(yùn)行，記錄如下。包括兩部分，邏輯備份和冷備份，如有不完整之處，還往看到本文的高手指導(dǎo)！注：這些容是本人經(jīng)過一番學(xué)習(xí)和多次測試調(diào)試后的結(jié)果，如果想，請以連接的形式，合作！第一部分，邏輯備份下面是：AutoBackup_Logic.bat--邏輯備份的主文件，直接在計(jì)劃任務(wù)中調(diào)用就可以了。echooffremrem需要配置的參數(shù)說明：remBAT_HOME即本批處理所在的目錄；remDUMP_DIR:即要備份到的數(shù)據(jù)文件目錄,存在與數(shù)據(jù)字典表：dba_directories中。remsetBAT_HOME=E:\BATsetDUMP_DIR=E:\Temp\DBBackupsetBKFILE=%Date:~0,4%%Date:~5,2%%Date:~8,2%setHHMMSS=%time:~0,2%%time:~3,2%%time:~6,2%call%BAT_HOME%\LogicBackup.bat%DUMP_DIR%>%BAT_HOME%\LogicBackup_%BKFILE%%HHMMSS%.logechoCompleted!下面是：LogicBackup.batechooffremcallLogicBackup.bat%DUMP_DIR%remrem本批處理實(shí)現(xiàn)支持expdp數(shù)據(jù)泵的Oracle數(shù)據(jù)庫版本的數(shù)據(jù)庫邏輯備份，流程為:導(dǎo)出數(shù)據(jù)到remdump_dir數(shù)據(jù)庫目錄下，然后把dmp文件以rar格式放入當(dāng)天備份日期命名的目錄下便于以后按rem照日期恢復(fù)。remrem需要配置的參數(shù)說明：remBAT_HOME即本批處理所在的目錄；remBKDIR:是調(diào)用本批處理時(shí)傳進(jìn)來的參數(shù)dump_dir，即要備份到的數(shù)據(jù)文件目錄。remremAuthor:SanlavoremTime:2007-06-07remremecho備份開始...echo當(dāng)前的時(shí)間是:%DATE%%time%setBAT_HOME=E:\BATsetBKDIR=%1setBKFILE=%Date:~0，4%%Date:~5，2%%Date:~8，2%setHHMMSS=%time:~0，2%%time:~3，2%%time:~6，2%expdpsystem/sysparfile=%BAT_HOME%\ScriptsLib\parfiletest.sqlifnotexist%BKDIR%\%BKFILE%_LOGIC(md%BKDIR%\%BKFILE%_LOGIC)else(echo目錄%BKDIR%\%BKFILE%_LOGIC已經(jīng)存在)echo開始壓縮文件...rara%BKDIR%\%BKFILE%_LOGIC\%BKFILE%%HHMMSS%_LOGIC_FULL.rar%BKDIR%\*.dmpecho壓縮文件%BKFILE%%HHMMSS%_LOGIC_FULL.rar完成echo開始移動文件...move%BKDIR%\*.dmp%BKDIR%\%BKFILE%_LOGIC\move%BKDIR%\*.log%BKDIR%\%BKFILE%_LOGIC\echo移動文件完成echo開始刪除dmp文件...del/f/s/q%BKDIR%\%BKFILE%_LOGIC\*.dmpecho刪除dmp文件完成netsend%userdomain%"數(shù)據(jù)庫邏輯備份已于:％DATE%%time%完成！"echo.echo備份已于：%DATE%%time%完成！echo.rem移動批處理的日志文件到備份目錄下面xcopy%BAT_HOME%\LogicBackup_%BKFILE%*.log%BKDIR%\%BKFILE%_LOGIC\echoSuccessfullyCompleted!下面是expdp的parfile參數(shù)的文件(parfiletest.sql)容：directory=dump_dirdumpfile=DBBackup_Logic_%U.dmplogfile=DBBackup_Logic.logfilesize=80Mfull=yjob_name=EXP_DB_FULL到此，邏輯備份批處理完成，可以用命令來定時(shí)執(zhí)行，例如：c:\>at06:06/every:SE:\BAT\AutoBackup_Logic.bat這樣，邏輯備份將會在每周星期六凌晨六點(diǎn)六分執(zhí)行。第二部分，冷備份：下面是：AutoBackup.bat--主文件echooffsetBAT_HOME=E:\BATsetDUMP_DIR=E:\Temp\DBBackupcallColdBackup.batorcl%DUMP_DIR%>%BAT_HOME%\ColdBackup_%DATE:~0,10%.logpause下面是備份流程文件：ColdBackup.batechooffremE:\BAT>ColdBackup.batorclE:\Temp\DBBackupremrem==============================================rem要備份的文件列表有：參數(shù)文件、控制文件、數(shù)據(jù)文件、重做日志文件remrem參數(shù)文件有：rem%ORACLE_HOME%\database\spfile<sid>.orarem%ORACLE_HOME%\database\PWD<sid>.orarem%ORACLE_HOME%\database\pfile<sid>.oraremrem控制文件：可以通過查詢數(shù)據(jù)字典視圖v$control確認(rèn)控制文件的位置和清單remselectstatus,namefromv$controlfile;remrem數(shù)據(jù)文件：可以通過查詢數(shù)據(jù)字典視圖dba_data_files確定數(shù)據(jù)文件的位置和清單remselectstatus,file_namefromdba_data_files;remrem重做日志文件：查詢數(shù)據(jù)字典v$logfile可以確認(rèn)聯(lián)機(jī)重做日志文件的位置和清單remselectgroup#,status,memberfromv$logfile;remAuthor:SanlavoremTime:11:352007-5-31rem==============================================remrem請?jiān)谶@里配置好本批處理程序所放的路徑BAT_HOME以及ORACLE_HOME:setORACLE_HOME=D:\oracle\product\10.2.0\db_1setBAT_HOME=E:\BATsetBKFILE=%Date:~0,4%%Date:~5,2%%Date:~8,2%setHHMMSS=%time:~0,2%%time:~3,2%%time:~6,2%rem數(shù)據(jù)庫脫機(jī)call%BAT_HOME%\ShutdownDB.bat%1rem%1為數(shù)據(jù)庫vSID＞%2為備份文件路徑rem創(chuàng)建備份目錄ifexist%2\%BKFILE%_DBBackup(echo%2\%BKFILE%_DBBackup已經(jīng)存在)else(mkdir%2\%BKFILE%_DBBackupecho%2\%BKFILE%_DBBackup已經(jīng)創(chuàng)建)remcopy備份文件到備份目錄中remcallxcopyfiles.bat%1%2rembegin==============================================echo數(shù)據(jù)庫%1冷備份開始...rem備份dbs目錄下參數(shù)文件xcopy%ORACLE_HOME%\dbs\*.*%2\%BKFILE%_DBBackup\dbs/S/I/C/Y/Frem備份%ORACLE_HOME%\database目錄下參數(shù)文件xcopy%ORACLE_HOME%\database\*.*%2\%BKFILE%_DBBackup\database/S/I/C/Y/EXCLUDE:ScriptsLib\databaseExclude.lst/Frem備份數(shù)據(jù)庫控制文件、數(shù)據(jù)文件、重做日志文件rem創(chuàng)建數(shù)據(jù)庫＜SID＞命名的文件夾，存放相關(guān)文件ifnotexist%2\%BKFILE%_DBBackup\%1(md%2\%BKFILE%_DBBackup\%1)rem從filepath.txt中讀取文件路徑信息并copy到目標(biāo)路徑for/f%%iin(%BAT_HOME%\ScriptsLib\filepath.txt)do(xcopy%%i%2\%BKFILE%_DBBackup\%1/S/I/C/Y/F)echo數(shù)據(jù)庫%1冷備份完成!remend==============================================echo啟動數(shù)據(jù)庫rem上面的操作過程中以及修改了環(huán)境路徑，所以掉不到該批處理文件%BAT_HOME:~0，2%cd%BAT_HOME%call%BAT_HOME%\StartupDB.bat%1:Operation:Endremifexist*.dmp(remmove*.dmp%BKFILE%_DBBackup&gotoEnd1remechoMovedumpfilesuccessfullrem)else(remecho不存在數(shù)據(jù)庫備份文件&gotoEnd1rem)remEndrem::把日期目錄下的文件移動到當(dāng)前目錄rem::move%BKFILE%_DBBackup\*.batrem:End1remecho*remechoOperationSuccessfullyremecho*pause上文中的：databaseExclude.lst容如下：.log.exe即，排除日志文件和exe文件，其他的全部備份。上文中的：filepath.txt容是經(jīng)過數(shù)據(jù)字典查詢后得到的比如控制文件、數(shù)據(jù)文件、日志文件等的路徑列表，例如：D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTLD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTLD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTLD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBFD:\SDEDB.DBFE:\SDEDB\SDE_DATA01.DBFE:\SDEDB\SDE_INDEX01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\WJ01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOGD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOGD:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG在冷備份中涉及到數(shù)據(jù)庫的關(guān)閉和啟動，這里不在羅索了。關(guān)于冷備份的執(zhí)行，也可以模仿邏輯備份部分，在計(jì)劃任務(wù)中調(diào)用。第三部分，熱備份：remscript:hotbak.sqlremcreater:chenjipingremdate:4.4.2002remdesc:backupdatabasedatafileinarchive--connectdatabaseconnectinternal/virpure;--archivealtersystemcheckpoint;altersystemarchivelogall;altersystemswitchlogfile;--startaltertablespaceafis_remotebeginbackup;!xcopye:\orant\database\remote_data1.orae:\databak/H/R;!xcopye:\orant\database\remote_data2.orae:\databak/H/R;altertablespaceafis_remoteendbackup;altertablespaceafis_rmtafisbeginbackup;!xcopye:\orant\database\remote_data1.orae:\databak/H/R;!xcopye:\orant\database\remote_data2.orae:\databak/H/R;altertablespaceafis_remoteendbackup;altertablespaceafis_rmtafisbeginbackup;!xcopye:\orant\database\rmtafis_data1.orae:\databak/H/R;!xcopye:\orant\database\rmtafis_data2.orae:\databak/H/R;altertablespaceafis_rmtafisendbackup;!xcopye:\orant\database\rmtafis_data1.orae:\databak/H/R;!xcopye:\orant\database\rmtafis_data2.orae:\databak/H/R;altertablespaceafis_rmtafisendbackup;altertablespaceafis_lquserbeginbackup;!xcopye:\orant\database\lquser_data.orae:\databak/H/R;altertablespaceafis_lquserbeginbackup;!xcopye:\orant\database\lquser_data.orae:\databak/H/R;backup;altertablespaceafis_lquserendaltertablespaceafis_transfbeginbackup;backup;!xcopye:\orant\database\transf1_data.orae:\databak/H/R;!xcopye:\orant\database\transf2_data.orae:\databak/H/R;altertablespaceafis_transfendbackup;altertablespacesystembeginbackup;!xcopye:\orant\database\sys1afis.orae:\databak/H/R;altertablespacesystemendbackup;altertablespacerbsbeginbackup;!xcopye:\orant\database\rbs1afis.orae:\databak/H/R;altertablespacerbsendbackup;altertablespaceusrbeginbackup;!xcopye:\orant\d