ORACLE 熱備份腳本
2024-08-29 13:46:07
供稿：網(wǎng)友
備份腳本使用說明:
這個(gè)腳本的主要功能是提供Oracle 8.1.7 for nt的熱備份.
首先數(shù)據(jù)庫需要在歸檔方式(ARCHIVELOG).這個(gè)腳本的最初作者
是Craig MacPherson 改進(jìn)的人是Stephen Morse.但是在腳本
中對(duì)8.1.7 的老版本數(shù)據(jù)庫支持的不是很好.不能夠正常運(yùn)行.
我將這個(gè)腳本加以改進(jìn).具有下面的功能:
1.在每次備份的時(shí)候能夠自動(dòng)檢測(cè)現(xiàn)有的數(shù)據(jù)庫表空間.動(dòng)態(tài)生成
所需要的備份內(nèi)容.
2.腳本運(yùn)行過程中的log 保存成以日期為開頭的.log文件.方便
檢查每次備份和壓縮FTP 的過程.
3.將備份內(nèi)容進(jìn)行壓縮(rar軟件需要單獨(dú)安裝,經(jīng)過測(cè)試的是WinRAR)
并按照日期名的格式生成一個(gè)壓縮文件.
4.將壓縮文件ftp到目標(biāo)ftp服務(wù)器上.
使用方法:
1.將runthis.bat 和hotgen.bat 復(fù)制到oracel數(shù)據(jù)庫所在的
機(jī)器上.
2.根據(jù)需要更改SID以及數(shù)據(jù)庫的用戶名和口令.以及備份的目標(biāo)地址
以及l(fā)og存放的地方.
3.建立計(jì)劃任務(wù)將runthis.bat添加到系統(tǒng)中制訂天天運(yùn)行的時(shí)間.
免責(zé)聲明:
本人并不是專業(yè)DBA人員只是將自己工作當(dāng)中也一些經(jīng)驗(yàn)和大家分享.
本腳本并不是要代替商業(yè)備份軟件.對(duì)于有能力購買商業(yè)軟件的人員
或者公司還是推薦購買商業(yè)軟件.同樣,本人也不提供對(duì)這個(gè)腳本運(yùn)行
產(chǎn)生的任何責(zé)任.但是假如這個(gè)腳本有任何建議和改進(jìn)的話,假如您愿意
請(qǐng)mail告訴我.我的郵件地址是:
wanghaobj@bj1860.net
runthis.bat:
@ECHO off
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM 備份腳本使用說明:
REM 這個(gè)腳本的主要功能是提供Oracle 8.1.7 for nt的熱備份.
REM 首先數(shù)據(jù)庫需要在歸檔方式(ARCHIVELOG).這個(gè)腳本的最初作者
REM 是Craig MacPherson 改進(jìn)的人是Stephen Morse.但是在腳本
REM 中對(duì)8.1.7 的老版本數(shù)據(jù)庫支持的不是很好.不能夠正常運(yùn)行.
REM 我將這個(gè)腳本加以改進(jìn).具有下面的功能:
REM 1.在每次備份的時(shí)候能夠自動(dòng)檢測(cè)現(xiàn)有的數(shù)據(jù)庫表空間.動(dòng)態(tài)生成
REM 所需要的備份內(nèi)容.
REM 2.腳本運(yùn)行過程中的log 保存成以日期為開頭的.log文件.方便
REM 檢查每次備份和壓縮ftp 的過程.
REM 3.將備份內(nèi)容進(jìn)行壓縮(rar軟件需要單獨(dú)安裝,經(jīng)過測(cè)試的是winrar)
REM 并按照日期名的格式生成一個(gè)壓縮文件.
REM 4.將壓縮文件ftp到目標(biāo)ftp服務(wù)器上.
REM 使用方法:
REM 1.將runthis.bat 和hotgen.bat 復(fù)制到oracel數(shù)據(jù)庫所在的
REM 機(jī)器上.
REM 2.根據(jù)需要更改SID以及數(shù)據(jù)庫的用戶名和口令.以及備份的目標(biāo)地址
REM 以及l(fā)og存放的地方.
REM 3.建立計(jì)劃任務(wù)將runthis.bat添加到系統(tǒng)中制訂天天運(yùn)行的時(shí)間.
REM 免責(zé)聲明:
REM 本人并不是專業(yè)DBA人員只是將自己工作當(dāng)中也一些經(jīng)驗(yàn)和大家分享.
REM 本腳本并不是要代替商業(yè)備份軟件.對(duì)于有能力購買商業(yè)軟件的人員
REM 或者公司還是推薦購買商業(yè)軟件.同樣,本人也不提供對(duì)這個(gè)腳本運(yùn)行
REM 產(chǎn)生的任何責(zé)任.但是假如這個(gè)腳本有任何建議和改進(jìn)的話,假如您愿意
REM 請(qǐng)mail告訴我.我的郵件地址是:
REM wanghaobj@bj1860.net
REM Backup DataBase Run this
REM Author: Hao Wang - www.ChinaUnix.net 2005年2月28日
REM
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO .
REM Backup DataBase script LogFile
SET logdir=c:/tmp1
REM B_SCRIPT_TARGET B_BACKUP_TARGET
SET B_SCRIPT_TARGET=d:
SET B_BACKUP_TARGET=d:
if not exist %logdir%/nul mkdir %logdir%
call d:/hot_gen.bat %B_SCRIPT_TARGET% %B_BACKUP_TARGET%>%logdir%/hot_gen%date:~4,4%%date:~9,2%%date:~12,2%.log
call %B_SCRIPT_TARGET%/hot_backup.cmd >%logdir%/hot_backup%date:~4,4%%date:~9,2%%date:~12,2%.log
hotgen.bat:
@ECHO off
REM This script will create the scripts necessary for a complete hot backup of an Oracle database
REM on NT.
REM Datafiles and controlfiles are backed up in this script.
REM These scripts can then be run in batch. Use the AT
REM scheduler to schedule the backup job.
REM
REM Edit the SID, CONNECT and INIT strings used in this command file.
REM
REM
REM Author: Craig MacPherson - Oracle Corporation Canada Inc. June/97
REM Edited: Stephen Morse - Oracle Corporation US, November 97
REM Edited: Hao Wang - Samsung SDS China 2005-2-28
ECHO.
REM ECHO HOT_GEN.CMD Usage:
REM ECHO Enter HOT_GEN SCRIPT_TARGET BACKUP_TARGET
REM ECHO where SCRIPT_TARGET
REM is the location for the backup scripts e.g. c:/oraback/sid/COLD
REM ECHO and BACKUP_TARGET is the location for the Oracle datafile
REM backups when batch is executed
REM ECHO.
REM pause
REM SETlocal
REM 10-DEC-1999
REM 1) commented the above help
REM and pause out
REM 2) replaced the connects as sysdba by connect internal
REM 25-JAN-2000
REM added SET ORACLE_SID=
REM in hot_backup.cmd
REM 3) Add FTP and ComPRess Function
REM 2005-2-28
REM example uses SID=TTV817
REM %ORACLE_HOME%=c:/oracle/ora817
REM !
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM These values cannot be derived, please SET them to reflect your environment
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SET ORACLE_SID=ORACLE8
SET ORACLE_HOME=D:/oracle/ora81
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM SET O_CONNECT="sys/change_on_install as sysdba"
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SET O_SQL=connect sys/change_oninstall@%ORACLE_SID% as sysdba
SET O_INIT=D:/oracle/admin/oracle8/pfile/initoracle8.ora
REM Oracle Binaries
SET O_PLUS=%ORACLE_HOME%/bin/sqlplus.exe /nolog
SET O_COPY=%ORACLE_HOME%/bin/ocopy.exe
REM Ftp User and Passwd
SET F_server=192.168.1.1
SET F_user=oracle
SET F_passwd=oracle
SET F_target=/
REM Compress location
SET R_PATH=d:
SET O_BACKPATH=%2
SET O_SCRIPTPATH=%1
if %O_SCRIPTPATH%.==. goto help
if %O_BACKPATH%.==. goto help
REM ***************************************************************************
REM HOT BACKUP OF THE DATABASE
REM ***************************************************************************
ECHO.
ECHO **********************************************************
ECHO - Create hot_backup.CMD script to coordinate all backup activities
ECHO - for AT scheduling
ECHO **********************************************************
ECHO.
ECHO REM Backup the init file >%O_SCRIPTPATH%/hot_backup.CMD
ECHO copy %O_INIT% %O_BACKPATH% >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO SET ORACLE_SID=%ORACLE_SID% >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO REM Run the svrmgr script to backup the datafiles >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO %O_PLUS% @%O_SCRIPTPATH%/svrmgr1.sql >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO REM Run the svrmgr2.sql script to backup the controlfiles >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO %O_PLUS% @%O_SCRIPTPATH%/svrmgr2.sql >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO REM compress %O_BACKPATH% FILES >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO rar.exe a %R_PATH%/%date:~4,4%%date:~9,2%%date:~12,2%.rar %O_BACKPATH%/*.* >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO %F_user%>%O_SCRIPTPATH%/FTP_CMD.txt
ECHO %F_passwd%>>%O_SCRIPTPATH%/FTP_CMD.txt
ECHO bin>>%O_SCRIPTPATH%/FTP_CMD.txt
ECHO cd %F_target%>>%O_SCRIPTPATH%/FTP_CMD.txt
ECHO mput %R_PATH%/%date:~4,4%%date:~9,2%%date:~12,2%.rar>>%O_SCRIPTPATH%/FTP_CMD.txt
ECHO BYE>>%O_SCRIPTPATH%/FTP_CMD.txt
ECHO ftp -i -s:%O_SCRIPTPATH%/FTP_CMD.txt %F_server% >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO del /S/F/Q %R_PATH%/*.* >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO del /S/F/Q %O_BACKPATH%/*.* >>%O_SCRIPTPATH%/hot_backup.CMD
ECHO.
ECHO **********************************************************
ECHO -- Create a SQL*PLUS script for the datafile backups
ECHO **********************************************************
ECHO.
ECHO connect sys/change_oninstall@%ORACLE_SID% as sysdba >%O_SCRIPTPATH%/plus1.sql
ECHO SET heading off; >>%O_SCRIPTPATH%/plus1.sql
ECHO SET feedback off; >>%O_SCRIPTPATH%/plus1.sql
ECHO SET linesize 1000; >>%O_SCRIPTPATH%/plus1.sql
ECHO spool %O_SCRIPTPATH%/svrmgr1.sql; >>%O_SCRIPTPATH%/plus1.sql
ECHO select 'connect sys/change_oninstall@%ORACLE_SID% as sysdba' from dual;>>%O_SCRIPTPATH%/plus1.sql
ECHO select 'alter system switch logfile;' from dual; >>%O_SCRIPTPATH%/plus1.sql
ECHO select 'alter tablespace '^^tablespace_name^^' begin backup;'^^' >>%O_SCRIPTPATH%/plus1.sql
ECHO '^^'host start /wait %O_COPY% '^^file_name^^' %O_BACKPATH%;'^^' >>%O_SCRIPTPATH%/plus1.sql
ECHO '^^'alter tablespace '^^tablespace_name^^' end backup;' from dba_data_files; >>%O_SCRIPTPATH%/plus1.sql
ECHO select 'alter system switch logfile;' from dual; >>%O_SCRIPTPATH%/plus1.sql
ECHO select 'exit;' from dual; >>%O_SCRIPTPATH%/plus1.sql
ECHO exit; >>%O_SCRIPTPATH%/plus1.sql
ECHO.
ECHO **********************************************************
ECHO -- Run the sql*plus script to create the svrmgr1.sql script
ECHO **********************************************************
ECHO.
%O_PLUS% @%O_SCRIPTPATH%/plus1.sql
ECHO.
ECHO **********************************************************
ECHO -- Create a SQL*PLUS script for the control files
ECHO **********************************************************
ECHO.
ECHO connect sys/change_oninstall@%ORACLE_SID% as sysdba >%O_SCRIPTPATH%/plus2.sql
ECHO SET heading off; >>%O_SCRIPTPATH%/plus2.sql
ECHO SET feedback off; >>%O_SCRIPTPATH%/plus2.sql
ECHO SET linesize 1000; >>%O_SCRIPTPATH%/plus2.sql
ECHO spool %O_SCRIPTPATH%/svrmgr2.sql; >>%O_SCRIPTPATH%/plus2.sql
ECHO select 'connect sys/change_oninstall@%ORACLE_SID% as sysdba' from dual;>>%O_SCRIPTPATH%/plus2.sql
ECHO select 'alter database backup controlfile to '''^^'%O_BACKPATH%/'^^substr(name,instr(name,'/',-1)+1)^^''' REUSE;' from v$controlfile; >>%O_SCRIPTPATH%/plus2.sql
ECHO select 'alter database backup controlfile to trace;' from dual; >>%O_SCRIPTPATH%/plus2.sql
ECHO select 'exit;' from dual; >>%O_SCRIPTPATH%/plus2.sql
ECHO spool off; >>%O_SCRIPTPATH%/plus2.sql
ECHO exit; >>%O_SCRIPTPATH%/plus2.sql
ECHO.
ECHO **********************************************************
ECHO -- Run the sql*plus script to create the svrmgr2.sql scripts
ECHO **********************************************************
ECHO.
%O_PLUS% @%O_SCRIPTPATH%/plus2.sql
ECHO.
ECHO **********************************************************
ECHO -- Hot Backup Complete
ECHO **********************************************************
ECHO.
goto END_OF_FILE;
REM ***************************************************************************
REM USER HELP
REM ***************************************************************************
:HELP
ECHO.
ECHO HOT_GEN.CMD Usage:
ECHO Enter HOT_GEN SCRIPT_TARGET BACKUP_TARGET
ECHO where SCRIPT_TARGET is the location for the backup
ECHO scripts e.g. c:/oraback/sid/HOT
ECHO and BACKUP_TARGET is the location for the Oracle datafile backups when batch is executed
ECHO.
goto END_OF_FILE
:HELP2
ECHO.
ECHO Error - Cannot write to %O_BACKPATH%
ECHO.
goto END_OF_FILE
REM ***************************************************************************
REM HANDLE ERRORS HERE
REM ***************************************************************************
findstr /in "error" %O_BACKPATH%/backup.log
&& findstr /in "error" %O_BACKPATH%/backup.log >%O_BACKPATH%/error.log
findstr /in "ora-" %O_BACKPATH%/backup.log
&& findstr /in "ora-" %O_BACKPATH%/backup.log >%O_BACKPATH%/error.log
findstr /in "cannot" %O_BACKPATH%/backup.log
&& findstr /in "cannot" %O_BACKPATH%/backup.log >%O_BACKPATH%/error.log
findstr /in "not logged" %O_BACKPATH%/backup.log
&& findstr /in "not logged" %O_BACKPATH%/backup.log >%O_BACKPATH%/error.log
findstr /in "failure" %O_BACKPATH%/backup.log
&& findstr /in "failure" %O_BACKPATH%/backup.log >%O_BACKPATH%/error.log
if exist %O_BACKPATH%/error.log c:/ntreskit/logevent -s E "BACKUP FAILURE!
"
pause
endlocal
:END_OF_FILE
