OracleホットバックアップbatchスクリプトWindows

1.最初はネットワークに由来します.
2.環境や好みに合わせて自分で修正する.
3.実測はバックアップタスクを完了できます.
4.実環境での使用は推奨されません.
 
bak.bat:実行時にこのスクリプトを実行し、他のスクリプトは呼び出しと生成または生成後に呼び出されます.(自分で直す必要がある)
 

 1 @ECHO OFF

 2 REM ################################################################

 3 REM version 2013.10.02

 4 REM First database need to be ARCHIVELOG module.

 5 REM     1.Check the database current tablespace and generate backup script dynamic. 

 6 REM     2.The log file save as date.log and very convenience.

 7 REM     3.Compressing the backup content and named as date format,configuration by yourself is necessary.

 8 REM     4.Store the compressing file to FTP server,configuration by yourself is necessary. 

 9 REM Usage: 

10 REM     1.copy bak.bat and Hot_gen.bat to oracle database server. 

11 REM     2.Change the sid,username and passeword if you need.Contain destination address and where your log store path. 

12 REM     3.Construct schedule task and add bak.bat to system and run the time you specific. 

13 REM The principle of the script:  

14 REM          bak.bat invoke Hot_gen.bat to generate script. and using generate script to backup database.

15 rem

16 REM ###############################################################

17 ECHO. 

18 REM Backup DataBase script LogFile 

19 set "logdir=d:\ora\log"

20 ::SET logdir=d:\ora\log 

21 REM B_SCRIPT_TARGET B_BACKUP_TARGET 

22 SET B_SCRIPT_TARGET=d:\ora\scripttarget

23 SET B_BACKUP_TARGET=d:\ora\baktarget

24 if not exist %logdir% mkdir %logdir%

25 if not exist %B_SCRIPT_TARGET% mkdir %B_SCRIPT_TARGET%

26 if not exist %B_BACKUP_TARGET% mkdir %B_BACKUP_TARGET%

27 >>"%logdir%\hot_gen%date:~0,4%%date:~5,2%%date:~8,2%.log" call d:\Hot_gen.bat %B_SCRIPT_TARGET% %B_BACKUP_TARGET%

28 

29 ::call d:\Hot_gen.bat %B_SCRIPT_TARGET% %B_BACKUP_TARGET%>>"%logdir%\hot_gen%date:~0,4%%date:~5,2%%date:~8,2%.log"

30 

31 ::To avoid blank space to effect the command,write like this is recommend.

32 ::>>"%logdir%\hot_backup%date:~0,4%%date:~5,2%%date:~8,2%.log" call %B_SCRIPT_TARGET%\hot_backup.CMD  

33 

34 ::@ECHO off 

35 pause

Hot_gen.bat:bak.bat呼び出し生成はバックアップスクリプト(自己修正先、先読み)

  1 ::@ECHO off

  2 rem #####################################################################################################

  3 REM This script will create the scripts necessary for a complete hot backup of an Oracle database on NT.

  4 REM Datafiles and controlfiles are backed up in this script.

  5 REM These scripts can then be run in batch. Use the AT scheduler to schedule the backup job.

  6 REM Edit the SID, CONNECT and INIT strings used in this command file.

  7 rem #####################################################################################################

  8 rem Modify history:

  9 REM     Author: Craig MacPherson - Oracle Corporation Canada Inc. June/97

 10 REM     Edited: Stephen Morse - Oracle Corporation US, November 97

 11 REM     Edited: Hao Wang - Samsung SDS China 2005-2-28

 12 rem     Edited: Xiaoqiang Jiang -ECA 2013-10-02 Add some comment

 13 ECHO.

 14 REM HOT_GEN.CMD Usage:

 15 REM      Enter HOT_GEN SCRIPT_TARGET BACKUP_TARGET

 16 REM      where SCRIPT_TARGET is the location for the backup scripts e.g. c:\oraback\sid\COLD

 17 REM      and BACKUP_TARGET is the location for the Oracle datafile

 18 REM     backups when batch is executed

 19 

 20 

 21 REM SETlocal 10-DEC-1999

 22 REM 1) commented the above help and pause out

 23 REM 2) replaced the connects as sysdba by connect internal 25-JAN-2000 added SET ORACLE_SID= in hot_backup.cmd

 24 REM 3) Add FTP and Compress Function 2005-2-28

 25 REM example uses SID=TTV817 

 26 REM %ORACLE_HOME%=c:\oracle\ora817

 27 

 28 REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 29 REM These values cannot be derived, please SET them to reflect your environment

 30 REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 31 

 32 SET ORACLE_SID=orcl

 33 

 34 ::    The oracle home may probably: [HardDisk]:\app\Administrator\product\11.2.0\dbhome_1

 35 ::    The Oracle document:The directory path to install Oracle components (for example, /u01/app/oracle/product/11.2.0/db_n). 

 36 ::    You are prompted to enter an Oracle home in the Path field of the Specify File Locations window.

 37 

 38 ::My oracle 11g database home

 39 SET ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1

 40 

 41 REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 42 REM SET O_CONNECT="sys/change_on_install as sysdba"

 43 REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 44 SET O_SQL=connect sys/change_on_install@%ORACLE_SID% as sysdba

 45 

 46 rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 

 47 rem    The oracle initial file store path:

 48 rem My initial path is: D:\app\Administrator\product\11.2.0\dbhome_1\srvm\admin

 49 rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 50 SET O_INIT=D:\app\Administrator\product\11.2.0\dbhome_1\srvm\admin\init.ora

 51 

 52 rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 53 REM Oracle Binaries,Oracle binaries tool

 54 rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 55 SET O_PLUS=%ORACLE_HOME%\bin\sqlplus.exe /nolog

 56 SET O_COPY=%ORACLE_HOME%\bin\ocopy.exe

 57 

 58 rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 59 REM Ftp User and Password,have not test yet. 

 60 rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 61 SET F_server=192.168.1.88

 62 SET F_user=Administator

 63 SET F_passwd=ccir

 64 SET F_target=/

 65 

 66 REM Compress location

 67 SET R_PATH=d:\

 68 

 69 rem %1 is call command first parameter

 70 rem For example:a.cmd 1,b. The %1 specify the 1,the %2 specify the b and so on.

 71 

 72 SET O_BACKPATH=%2

 73 SET O_SCRIPTPATH=%1

 74 if %O_SCRIPTPATH%.==. goto help

 75 if %O_BACKPATH%.==. goto help

 76 

 77 REM ***************************************************************************

 78 REM HOT BACKUP OF THE DATABASE

 79 REM ***************************************************************************

 80 

 81 ECHO.

 82 ECHO *************************************************************************

 83 ECHO - Create hot_backup.CMD script to coordinate all backup activities for AT scheduling

 84 ECHO *************************************************************************

 85 ECHO.

 86 ECHO REM Backup the init file >%O_SCRIPTPATH%\hot_backup.CMD

 87 ECHO copy %O_INIT% %O_BACKPATH% >>%O_SCRIPTPATH%\hot_backup.CMD

 88 ECHO SET ORACLE_SID=%ORACLE_SID% >>%O_SCRIPTPATH%\hot_backup.CMD

 89 ECHO REM Run the svrmgr script to backup the datafiles >>%O_SCRIPTPATH%\hot_backup.CMD

 90 

 91 ::O_PLUS is the sqlplus.exe path

 92 ECHO %O_PLUS% @%O_SCRIPTPATH%\svrmgr1.sql >>%O_SCRIPTPATH%\hot_backup.CMD

 93 ECHO REM Run the svrmgr2.sql script to backup the controlfiles >>%O_SCRIPTPATH%\hot_backup.CMD

 94 ECHO %O_PLUS% @%O_SCRIPTPATH%\svrmgr2.sql >>%O_SCRIPTPATH%\hot_backup.CMD

 95 ECHO REM compress %O_BACKPATH% FILES >>%O_SCRIPTPATH%\hot_backup.CMD

 96 ECHO rar.exe a %R_PATH%\%date:~0,4%%date:~5,2%%date:~8,2%.rar %O_BACKPATH%\*.* >>%O_SCRIPTPATH%\hot_backup.CMD

 97 

 98 ECHO %F_user%>%O_SCRIPTPATH%\FTP_CMD.txt

 99 ECHO %F_passwd%>>%O_SCRIPTPATH%\FTP_CMD.txt

100 ECHO bin>>%O_SCRIPTPATH%\FTP_CMD.txt

101 ECHO cd %F_target%>>%O_SCRIPTPATH%\FTP_CMD.txt

102 ECHO mput %R_PATH%\%date:~0,4%%date:~5,2%%date:~8,2%.rar>>%O_SCRIPTPATH%\FTP_CMD.txt

103 ECHO BYE>>%O_SCRIPTPATH%\FTP_CMD.txt

104 

105 ECHO ftp -i -s:%O_SCRIPTPATH%\FTP_CMD.txt %F_server% >>%O_SCRIPTPATH%\hot_backup.CMD

106 ECHO rem del /S/F/Q %R_PATH%\*.* >>%O_SCRIPTPATH%\hot_backup.CMD

107 ECHO rem del /S/F/Q %O_BACKPATH%\*.* >>%O_SCRIPTPATH%\hot_backup.CMD

108 ECHO.

109 ECHO **********************************************************

110 ECHO -- Create a SQL*PLUS script for the datafile backups

111 ECHO **********************************************************

112 ECHO.

113 ECHO connect sys/change_oninstall@%ORACLE_SID% as sysdba >%O_SCRIPTPATH%\plus1.sql

114 ECHO SET heading off; >>%O_SCRIPTPATH%\plus1.sql

115 ECHO SET feedback off; >>%O_SCRIPTPATH%\plus1.sql

116 ECHO SET linesize 1000; >>%O_SCRIPTPATH%\plus1.sql

117 ECHO spool %O_SCRIPTPATH%\svrmgr1.sql; >>%O_SCRIPTPATH%\plus1.sql

118 ECHO select 'connect sys/change_oninstall@%ORACLE_SID% as sysdba' from dual;>>%O_SCRIPTPATH%\plus1.sql

119 ECHO select 'alter system switch logfile;' from dual; >>%O_SCRIPTPATH%\plus1.sql

120 ECHO select 'alter tablespace '^|^|tablespace_name^|^|' begin backup;'^|^|' >>%O_SCRIPTPATH%\plus1.sql

121 ECHO '^|^|'host start /wait %O_COPY% '^|^|file_name^|^|' %O_BACKPATH%;'^|^|' >>%O_SCRIPTPATH%\plus1.sql

122 ECHO '^|^|'alter tablespace '^|^|tablespace_name^|^|' end backup;' from dba_data_files; >>%O_SCRIPTPATH%\plus1.sql

123 ECHO select 'alter system switch logfile;' from dual; >>%O_SCRIPTPATH%\plus1.sql

124 ECHO select 'exit;' from dual; >>%O_SCRIPTPATH%\plus1.sql

125 ECHO exit; >>%O_SCRIPTPATH%\plus1.sql

126 

127 ECHO.

128 ECHO **********************************************************

129 ECHO -- Run the sql*plus script to create the svrmgr1.sql script

130 ECHO **********************************************************

131 ECHO.

132 

133 :: %O_PLUS% is the sqlplus.exe path,sqlplus.exe invoke the plus2.sql to generate svrmgr2.sql scripts

134 %O_PLUS% @%O_SCRIPTPATH%\plus1.sql

135 

136 ECHO.

137 ECHO **********************************************************

138 ECHO -- Create a SQL*PLUS script for the control files

139 ECHO **********************************************************

140 ECHO.

141 ECHO connect sys/change_on_install@%ORACLE_SID% as sysdba >%O_SCRIPTPATH%\plus2.sql

142 ECHO SET heading off; >>%O_SCRIPTPATH%\plus2.sql

143 ECHO SET feedback off; >>%O_SCRIPTPATH%\plus2.sql

144 ECHO SET linesize 1000; >>%O_SCRIPTPATH%\plus2.sql

145 ECHO spool %O_SCRIPTPATH%\svrmgr2.sql; >>%O_SCRIPTPATH%\plus2.sql

146 ECHO select 'connect sys/change_oninstall@%ORACLE_SID% as sysdba' from dual;>>%O_SCRIPTPATH%\plus2.sql

147 ECHO select 'alter database backup controlfile to '''^|^|'%O_BACKPATH%\'^|^|substr(name,instr(name,'\',-1)+1)^|^|''' REUSE;' from v$controlfile; >>%O_SCRIPTPATH%\plus2.sql

148 ECHO select 'alter database backup controlfile to trace;' from dual; >>%O_SCRIPTPATH%\plus2.sql

149 ECHO select 'exit;' from dual; >>%O_SCRIPTPATH%\plus2.sql

150 ECHO spool off; >>%O_SCRIPTPATH%\plus2.sql

151 ECHO exit; >>%O_SCRIPTPATH%\plus2.sql

152 

153 ECHO.

154 ECHO **********************************************************

155 ECHO -- Run the sql*plus script to create the svrmgr2.sql scripts

156 ECHO **********************************************************

157 ECHO.

158 

159 ::sqlplus.exe invoke the plus2.sql to generate svrmgr2.sql scripts

160 %O_PLUS% @%O_SCRIPTPATH%\plus2.sql

161 

162 ECHO.

163 ECHO **********************************************************

164 ECHO -- Hot Backup Complete

165 ECHO **********************************************************

166 ECHO.

167 goto END_OF_FILE;

168 

169 REM ***************************************************************************

170 REM USER HELP

171 REM ***************************************************************************

172 :HELP

173 ECHO.

174 ECHO HOT_GEN.CMD Usage:

175 ECHO Enter HOT_GEN SCRIPT_TARGET BACKUP_TARGET

176 ECHO where SCRIPT_TARGET is the location for the backup

177 ECHO scripts e.g. c:\oraback\sid\HOT

178 ECHO and BACKUP_TARGET is the location for the Oracle datafile backups when batch is executed

179 ECHO.

180 goto END_OF_FILE

181 

182 :HELP2

183 ECHO.

184 ECHO Error - Cannot write to %O_BACKPATH%

185 ECHO.

186 goto END_OF_FILE

187 

188 

189 REM ***************************************************************************

190 REM HANDLE ERRORS HERE

191 REM ***************************************************************************

192 findstr /in "error" %O_BACKPATH%\backup.log

193 && findstr /in "error" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log

194 findstr /in "ora-" %O_BACKPATH%\backup.log

195 && findstr /in "ora-" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log

196 findstr /in "cannot" %O_BACKPATH%\backup.log

197 && findstr /in "cannot" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log

198 findstr /in "not logged" %O_BACKPATH%\backup.log

199 && findstr /in "not logged" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log

200 findstr /in "failure" %O_BACKPATH%\backup.log

201 && findstr /in "failure" %O_BACKPATH%\backup.log >%O_BACKPATH%\error.log

202 if exist %O_BACKPATH%\error.log c:
treskit\logevent -s E "BACKUP FAILURE!"

203 pause

204 

205 endlocal

206 :END_OF_FILE

PS:FTPと圧縮部分にアップロードしてテストせず、実際の環境に応じて取捨選択し、圧縮時に圧縮ソフトウェアの詳細パス(D:.........*.exe)とタイプ(7 zip、rar......)を与える.