备份策略:星期一星期四 0级备份
其它1级备份
压缩备份片
功能:
备份资料库以及控制文件,归档日志。
删除备份成功能的归档日志。
删除过期的备份文件。
显示目前TableSpace的使用百分比
复制备份文件到另外的存储上
将备份结果以及 TableSpace的使用百分比发送给特定人员。
系统:
Windows 2008R2 Oralce 11.2.0.4
1.windows 中建立job.
DailyBakup.bat 内容。 --增量备份
if NOT EXIST S:\Oracle GOTO END
E:
cd \backup_script
REM rundelOld.exe
call E:\backup_script\OCS_ENV.CMD
call E:\backup_script\OCS_tabs_size.CMD
for /f "tokens=1-2 delims=週, " %%e in ('date /t') do set bkweek=%%g
set Level=1
echo %bkweek%
if %bkweek%==日 set Level=0
if %Level%==0 call E:\backup_script\deloldbackup0.cmd
if %Level%==1 rundelOld.exe
@ping 127.0.0.1 -n 3 -w 1000 > nul
if %Level%==0 rman target / nocatalog cmdfile rmanbackup.cmd msglog %HOT_LOG%
if %Level%==1 rman target / nocatalog cmdfile rmanbackup1.cmd msglog %HOT_LOG%
REM rman target / nocatalog cmdfile rmanbackup.cmd msglog %HOT_LOG%
@ping 127.0.0.1 -n 10 -w 1000 > nul
copy E:\log\OCS_HOTBK_%bktime%.LOG E:\OCS\log\HOTBK.log
type E:\log\tablespace.log >> E:\OCS\log\HOTBK.log
E:\backup_script\sendmail
call E:\backup_script\xcopyToTape.CMD
call E:\backup_script\xcopytoDB1.CMD
@ping 127.0.0.1 -n 10 -w 1000 > nul
:END
DailyBakup0.bat 内容 --0级备份
if NOT EXIST S:\Oracle GOTO END
E:
cd \backup_script
call E:\backup_script\OCS_ENV.CMD
call E:\backup_script\OCS_tabs_size.CMD
REM for /f "tokens=1-2 delims=週, " %%e in ('date /t') do set bkweek=%%g
set Level=0
echo %bkweek% >> %HOT_LOG%
REM if %bkweek%==日 set Level=0
if %Level%==0 call E:\OCS\backup_script\deloldbackup0.cmd
if %Level%==1 rundelOld.exe
@ping 127.0.0.1 -n 3 -w 1000 > nul
if %Level%==0 rman target / nocatalog cmdfile rmanbackup.cmd msglog %HOT_LOG%
if %Level%==1 rman target / nocatalog cmdfile rmanbackup1.cmd msglog %HOT_LOG%
@ping 127.0.0.1 -n 10 -w 1000 > nul
copy E:\log\OCS_HOTBK_%bktime%.LOG E:\OCS\log\HOTBK.log
type E:\log\tablespace.log >> E:\OCS\log\HOTBK.log
E:\backup_script\sendmail
call E:\backup_script\xcopyToTape.CMD
call E:\backup_script\xcopytoDB1.CMD
@ping 127.0.0.1 -n 10 -w 1000 > nul
:END
OCS_ENV.CMD 主要设置一些路径等参数。
for /f "tokens=1-4 delims=/, " %%e in ('date /t') do set bktime=%%e%%f%%g
set O_BIN=E:\oracle\product\11.2.0\db_1\BIN
set O_EXP=%O_BIN%\exp.exe
set O_PLUS=%O_BIN%\sqlplus.exe
set O_COPY=%O_BIN%\ocopy.exe
set ORACLE_SID=SFCDB
set ORACLE_TNS=SAJET
set PATH_CMD=E:\backup_script
set PATH_HOT=E:\BACKUP\oradata
set PATH_ARCH=E:\BACKUP\archive
set PATH_LOG=E:\log
set PATH_ARCHIVE=E:\archive
set PATH_PFILE=E:\oracle\product\11.2.0\db_1\database
set PATH_NETFILE=E:\oracle\product\11.2.0\db_1\NETWORK\ADMIN
set COLD_LOG=%PATH_LOG%\OCS_COLDBK_%bktime%.LOG
set ARCH_LOG=%PATH_LOG%\OCS_ARCHBK_%bktime%.LOG
set HOT_LOG=%PATH_LOG%\OCS_HOTBK_%bktime%.LOG
set EXP_LOG=%PATH_LOG%\OCS_EXPBK_%bktime%.LOG
set BKARC_CMD=%PATH_CMD%\OCS_ARCHg_exec.BAT
OCS_tabs_size.CMD --显示TBS的目前状态
REM script
Call OCS_ENV.CMD
echo.
echo **********************************************************
ECHO -- Run the sql*plus script
echo **********************************************************
echo.
%O_PLUS% /nolog @%PATH_CMD%\OCS_tabs_size.sql
echo.
echo.
endlocal
OCS_tabs_size.sql --显示TBS的目前状态
connect /@SFCDB as sysdba
set feedback off;
set linesize 200 trims on;
spool E:\Log\tablespace.log;
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN PERCENT_USED FORMAT A10
select a.TABLESPACE_NAME,
round(a.BYTES/1024/1024,0) bytes_used,
round(b.BYTES/1024/1024,0) bytes_free,
round(b.largest/1024/1024,0) largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)||'%' percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
exit;
rmanbackup.cmd --备份
run {
allocate channel disk1 type DISK;
allocate channel disk2 type DISK;
allocate channel disk3 type DISK;
allocate channel disk4 type DISK;
allocate channel disk5 type DISK;
allocate channel disk6 type DISK;
allocate channel disk7 type DISK;
allocate channel disk8 type DISK;
allocate channel disk9 type DISK;
allocate channel disk10 type DISK;
# set limit channel disk1 kbytes 20480000;
crosscheck backup;
delete noprompt obsolete;
crosscheck archivelog all;
# backup control file
backup
format = 'H:\ocs\backup\oradata\L0_ctrl_%d_%T_%s'
(current controlfile channel disk1);
# backup oracle database
backup incremental level 0
filesperset 2
format 'H:\backup\oradata\L0_df_%d_%T_%s'
AS COMPRESSED BACKUPSET database;
# (database channel disk1);
# backup all archive logs and delete
backup
filesperset 5
format 'H:\BACKUP\Archive\L0_ar_%d_%T_%s'
(archivelog all delete input );
# (archivelog FROM time 'SYSDATE-1' delete input channel disk1);
# backup control file
backup
format = 'H:\backup\oradata\L0_ctrl_%d_%T_%s'
(current controlfile channel disk1);
release channel disk1;
release channel disk2;
release channel disk3;
release channel disk4;
release channel disk5;
release channel disk6;
release channel disk7;
release channel disk8;
release channel disk9;
release channel disk10;
}
如果需要sendmail的部份请回复,待下次放上。