oracle备份脚本windows server

杨树DBA
于 2024-07-31 15:32:59 发布
阅读量401
点赞数 4
文章标签： oracle windows 数据库
本文链接：https://blog.csdn.net/qq_24918471/article/details/140824395
版权
功能：全量备份，增量备份，MD5计算，备份文件拷贝，备份结果监测
@echo off
setlocal
REM ################# Setting Environment Variables ############################################################
set BK_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
for /f "tokens=2 delims=:" %%i in ('ipconfig ^| findstr /i "ipv4"') do set "HOST=%%i"  
set "HOST=%HOST: =%"
set BAKPATH=D:\BACKUP\%HOST%\%ORACLE_SID%
set DATADIR=%BAKPATH%\%BK_DATE%
set LOGDIR=%BAKPATH%\logs
set STATUS=0
set DB_TYPE=Oracle
set MD5_FILE=%DATADIR%\%BK_DATE%.md5
set REMOTE_DIR=\\192.168.0.72\backup\%HOST%\%BK_DATE%\

REM ################ Create directories if  don't exist #####################################################
if not exist "%BAKPATH%" mkdir "%BAKPATH%"
if not exist "%DATADIR%" mkdir "%DATADIR%"
if not exist "%LOGDIR%" mkdir "%LOGDIR%"


REM ################################ MAIN  Function  ############################################################
:MAIN_JOB
for /f "tokens=2 delims==" %%a in ('wmic path win32_localtime get dayofweek /value ^| find "="') do set DAY=%%a
call :rman_delete_old
if "%DAY%"=="6" (
  echo Today is Saturday
  call :rman_full_backup 
) else (
  echo Today is not Saturday
  call :rman_incr_backup
)

REM Make Md5 
call:calculate_md5
if %errorlevel% neq 0 set /a STATUS+=1
REM Copy Files
call:copy_files
if %errorlevel% neq 0 set /a STATUS+=1

if %STATUS% gtr 0 (
  echo STATUS is greater than 0
  set STATUS=fail
) else (
  echo STATUS is not greater than 0
  set STATUS=OK
)
call :sed_backup_status
goto :eof


REM Run the main job
call :MAIN_JOB

echo Done!
pause

REM  ##End Main Function #################################################################################



REM Calculate MD5 checksums
:calculate_md5
setlocal EnableDelayedExpansion
if exist %MD5_FILE% del %MD5_FILE%
for /r "%DATADIR%" %%f in (*) do (
	REM certutil -hashfile "%CURPATH%\*" MD5 | sort /+24 > "%MD5_FILE%"
    for /f "delims=" %%a in ('certutil -hashfile  "%%f"  MD5 ^| find /v "hash"') do (
        set hash=%%a
        echo !hash! >> %MD5_FILE%
    )
)
endlocal
goto :eof



:copy_files
net use \\192.168.0.72\backup /user:samba samba0814
if not exist %REMOTE_DIR% mkdir %REMOTE_DIR%
copy %DATADIR%\* %REMOTE_DIR%
goto :eof


:sed_backup_status
REM Insert into mangerdb 
set sql_statement=insert into t_backup_details (host,db_type,STATUS,datadir,logdir,md5file,system_name) select '%HOST%',DB_TYPE,'%STATUS%','%DATADIR%','%LOGDIR%','%MD5_FILE%',APPLICATION  from T_DATABASE_CMDB where host='%HOST%';
echo %sql_statement% | sqlplus -s manager/manager@192.168.10.165:1521/manager
goto :eof


:rman_delete_old_file
echo "make rman delete Script"
set LOG=%BK_DATE%.log
(
echo crosscheck archivelog all;
echo crosscheck backupset;
echo delete noprompt expired backup;
echo delete noprompt expired archivelog all;
echo delete noprompt backup completed before 'sysdate-8';
echo delete noprompt force archivelog all completed before 'sysdate-1';
echo list backup summary;
) > rman_delold.rman
if %errorlevel% neq 0 set /a STATUS+=1
for /f "delims=" %%a in ('findstr /c:"ORA-" "%LOGDIR%\%BK_DATE%.log"') do set /a STATUS+=1
goto :eof



REM  RMAN_Scripts Full Backup
:rman_full_rman_file
echo "make rman Full RMAN Script"
set TAG=%BK_DATE%_Full
set LOG=%LOGDIR%\%BK_DATE%.log
(
echo run{
echo allocate channel d1 device type disk;
echo allocate channel d2 device type disk;
echo allocate channel d3 device type disk;
echo allocate channel d4 device type disk;
echo sql 'alter system archive log current';
echo sql 'alter system checkpoint';
echo backup as compressed backupset incremental level=0 database format '%DATADIR%\%%d_Full_0_%%s_%%t' tag '%TAG%';
echo sql 'alter system archive log current';
echo sql 'alter system checkpoint';
echo backup as compressed backupset archivelog all format '%DATADIR%\%%d_Arch_%%s_%%t' not backed up 1 times tag '%TAG%';
echo backup spfile format '%DATADIR%\%%d_SP_%%s_%%t' tag '%TAG%';
echo backup current controlfile format '%DATADIR%\%%d_Con_%%s_%%t' tag '%TAG%%';
echo release channel d1;
echo release channel d2;
echo release channel d3;
echo release channel d4;
echo }
) > rman_full.rman
if %errorlevel% neq 0 set /a STATUS+=1
for /f "delims=" %%a in ('findstr /c:"ORA-" "%LOGDIR%\%BK_DATE%.log"') do set /a STATUS+=1
goto :eof


REM  RMAN_Scripts Arch Backup
:rman_incr_rman_file
echo "make rman Incr rman Script"
set TAG=%BK_DATE%_incr
set LOG=%LOGDIR%\%BK_DATE%.log
(
echo run{
echo allocate channel d1 device type disk;
echo allocate channel d2 device type disk;
echo allocate channel d3 device type disk;
echo allocate channel d4 device type disk;
echo sql 'alter system archive log current';
echo sql 'alter system checkpoint';
echo backup as compressed backupset incremental level=1 database format '%DATADIR%\%%d_incr_0_%%s_%%t' tag '%TAG%';
echo sql 'alter system archive log current';
echo sql 'alter system checkpoint';
echo backup as compressed backupset archivelog all format '%DATADIR%\%%d_Arch_%%s_%%t' not backed up 1 times tag '%TAG%';
echo backup spfile format '%DATADIR%\%%d_SP_%%s_%%t' tag '%TAG%';
echo backup current controlfile format '%DATADIR%\%%d_Con_%%s_%%t' tag '%TAG%%';
echo release channel d1;
echo release channel d2;
echo release channel d3;
echo release channel d4;
echo }
) > rman_incr.rman
if %errorlevel% neq 0 set /a STATUS+=1
for /f "delims=" %%a in ('findstr /c:"ORA-" "%LOGDIR%\%BK_DATE%.log"') do set /a STATUS+=1
goto :eof




REM #################### TEST FUNCTION ######################################
:rman_test_rman_file
echo "make rman Test rman Script"
set TAG=%BK_DATE%_test
set LOG=%LOGDIR%\%BK_DATE%.log
(
echo run{
echo allocate channel d1 device type disk;
echo sql 'alter system archive log current';
echo sql 'alter system checkpoint';
echo sql 'alter system archive log current';
echo sql 'alter system checkpoint';
echo backup spfile format '%DATADIR%\%%d_SP_%%s_%%t' tag '%TAG%';
echo backup current controlfile format '%DATADIR%\%%d_Con_%%s_%%t' tag '%TAG%%';
echo release channel d1;
echo }
) > rman_test.rman
if %errorlevel% neq 0 set /a STATUS+=1
for /f "delims=" %%a in ('findstr /c:"ORA-" "%LOGDIR%\%BK_DATE%.log"') do set /a STATUS+=1
goto :eof

REM ######################TEST FUNCTION #######################################


:rman_full_backup
call:rman_full_rman_file
rman target / nocatalog CMDFILE rman_full.rman LOG %LOGDIR%\%BK_DATE%.log
goto :eof


:rman_incr_backup
call:rman_incr_rman_file
rman target / nocatalog CMDFILE rman_incr.rman LOG %LOGDIR%\%BK_DATE%.log
goto :eof

:rman_delete_old
call:rman_delete_old_file
rman target / nocatalog CMDFILE rman_delold.rman LOG %LOGDIR%\%BK_DATE%.log
goto :eof


REM #####################################TEST###################################
:rman_test_backup
call:rman_test_rman_file
rman target / nocatalog CMDFILE rman_test.rman LOG %LOGDIR%\%BK_DATE%.log
goto :eof
REM #####################################TEST###################################