Oracle 自动备份脚本-CSDN博客

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

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

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.

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

)

(

select TABLESPACE_NAME,

sum(BYTES) BYTES ,

max(BYTES) largest

from dba_free_space

group by TABLESPACE_NAME

)

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/203348/viewspace-1579549/