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:\ntreskit\logevent -s E "BACKUP FAILURE!" 203 pause 204 205 endlocal 206 :END_OF_FILE
PS:上传到FTP和压缩部分没有测试,根据实际环境取舍,压缩时给出压缩软件的详细路径(D:\..\..\*.exe)和类型(7zip、rar..........)