ORACLE 热备份脚本

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

备份脚本使用说明:

这个脚本的主要功能是提供Oracle 8.1.7 for nt的热备份.

首先数据库需要在归档方式(ARCHIVELOG).这个脚本的最初作者

是Craig MacPherson 改进的人是Stephen Morse.但是在脚本

中对8.1.7 的老版本数据库支持的不是很好.不能够正常运行.

我将这个脚本加以改进.具有下面的功能:

1.在每次备份的时候能够自动检测现有的数据库表空间.动态生成

所需要的备份内容.

2.脚本运行过程中的log 保存成以日期为开头的.log文件.方便

检查每次备份和压缩FTP 的过程.

3.将备份内容进行压缩(rar软件需要单独安装,经过测试的是WinRAR)

并按照日期名的格式生成一个压缩文件.

4.将压缩文件ftp到目标ftp服务器上.

使用方法:

1.将runthis.bat 和hotgen.bat 复制到oracel数据库所在的

机器上.

2.根据需要更改SID以及数据库的用户名和口令.以及备份的目标地址

以及log存放的地方.

3.建立计划任务将runthis.bat添加到系统中制订天天运行的时间.

免责声明:

本人并不是专业DBA人员只是将自己工作当中也一些经验和大家分享.

本脚本并不是要代替商业备份软件.对于有能力购买商业软件的人员

或者公司还是推荐购买商业软件.同样,本人也不提供对这个脚本运行

产生的任何责任.但是假如这个脚本有任何建议和改进的话,假如您愿意

请mail告诉我.我的邮件地址是:

wanghaobj@bj1860.net

runthis.bat:

@ECHO off

REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

REM 备份脚本使用说明:

REM 这个脚本的主要功能是提供Oracle 8.1.7 for nt的热备份.

REM 首先数据库需要在归档方式(ARCHIVELOG).这个脚本的最初作者

REM 是Craig MacPherson 改进的人是Stephen Morse.但是在脚本

REM 中对8.1.7 的老版本数据库支持的不是很好.不能够正常运行.

REM 我将这个脚本加以改进.具有下面的功能:

REM 1.在每次备份的时候能够自动检测现有的数据库表空间.动态生成

REM 所需要的备份内容.

REM 2.脚本运行过程中的log 保存成以日期为开头的.log文件.方便

REM 检查每次备份和压缩ftp 的过程.

REM 3.将备份内容进行压缩(rar软件需要单独安装,经过测试的是winrar)

REM 并按照日期名的格式生成一个压缩文件.

REM 4.将压缩文件ftp到目标ftp服务器上.

REM 使用方法:

REM 1.将runthis.bat 和hotgen.bat 复制到oracel数据库所在的

REM 机器上.

REM 2.根据需要更改SID以及数据库的用户名和口令.以及备份的目标地址

REM 以及log存放的地方.

REM 3.建立计划任务将runthis.bat添加到系统中制订天天运行的时间.

REM 免责声明:

REM 本人并不是专业DBA人员只是将自己工作当中也一些经验和大家分享.

REM 本脚本并不是要代替商业备份软件.对于有能力购买商业软件的人员

REM 或者公司还是推荐购买商业软件.同样,本人也不提供对这个脚本运行

REM 产生的任何责任.但是假如这个脚本有任何建议和改进的话,假如您愿意

REM 请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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航