ITPub博客

首页 > 数据库 > Oracle > Oracle 自动备份脚本

Oracle 自动备份脚本

原创 Oracle 作者:diashad 时间:2015-04-16 11:29:48 0 删除 编辑

Oracle 自动备份脚本

 创建时间: 2015-4-16 8:42  修改时间: 2015-4-16 10:33 
备份策略:星期一星期四 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的部份请回复,待下次放上。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/203348/viewspace-1579549/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-08

  • 博文量
    86
  • 访问量
    442715