My way to backup Oracle database on Windows

Good day to all. I work as a database administrator is not the first year, but always worked in large companies. During this time I worked with Linux, Solaris, HP-UX. But about a year ago I got into a small company, and oh, horror, all the servers on Windows, as such, backup was not done, or rather only export. It was necessary to make a normal physical backup, decided to write a script, as the requirements for myself highlighted a few points:

1. The script should work should work with any OS starting with win 2003 server, without requiring additional software.
2. The script should work on any Oracle starting with version 9i, while doing a full, incremental backup, and backup archivals.
3. The script should be as simple as possible, transferred from server to server, preferably it should be one file.

The first thought was to put cygwin or perl, to make Windows similar to unix, but then there will be difficulties with transferring to other servers. I decided to write a simple batch file ms-dos, although nothing more complicated than copy con my_text.txt in batch files I did not write. The implementation of paragraph 3, in general, turned out to be a nightmare, I have no embedded documents, I can not write:

sqlplus "/ as sysdba" << SQL select sysdate from dual; SQL 

I decided to write all additional scripts to temporary files, and output the program to variables. Unexpectedly, another problem got out if the variable is not set to echo% AAA% displays% AAA%, we had to assign a space to the variable. For 10g and higher, the archivogues are going to backup, because there is compression. For 9i, the archivogs are copied as is, since there is no catalog command, when recovering from multiple backups, you will have to restore the control files. With a full backup, the entire contents of the DEST directory are transferred to the OLD. And that's what I finally got:

 echo off REM version 1.4 REM  "rman_Backup_DB.cmd [t|a|d|0-1]" REM 0-1    (0 - ) REM a     REM t   ,   sqlplus,rman,   DEST REM d   ,    ,      REM -------------------  --------------------------------- REM   set DEST=\\SMB_server\share REM     set OLD=%DEST%\OLD set ORACLE_SID=ORCL set ORACLE_HOME=C:\APP\orabase\product\11.2.0\dbhome_1 set NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251 set NLS_DATE_FORMAT=YYYY.MM.DD HH24.MI.SS REM COMPRESS=  9i   ( ) REM  10   =as compressed backupset set COMPRESS=as compressed backupset REM   Rman   set CONSTRING=target / REM   sqlplus set CONSQL="/ as sysdba" REM  Rman set RMAN=%ORACLE_HOME%\bin\rman REM  SQLPLUS set SQLPL=%ORACLE_HOME%\bin\sqlplus REM   set TFILE=%TEMP%\backup_%ORACLE_SID%.tmp REM --------------------------------------------- set ERR=0 set EXEC=%0 %1 if "%EXEC%"=="%0 " goto prov if %1==t goto prov if %1==a goto backarc if %1==d goto delarc echo alter system archive log current; > %TFILE% echo select upper ('posled '),(max (SEQUENCE#)-5) from v$archived_log where DELETED='NO'; >> %TFILE% echo exit >> %TFILE% for /F "tokens=1,2 usebackq " %%1 in (`%SQLPL% %CONSQL% @%TFILE%`) do if %%1==POSLED set SEQN=%%2 IF %1==0 ( move /Y %DEST%\*.BK? %OLD% goto backdb ) IF %1==1 goto backdb :prov echo 1.Testing sqlplus connection echo select 'POSLED ','Test_SQLPLUS_OK' from dual; > %TFILE% echo exit >> %TFILE% for /F "tokens=1,2 usebackq " %%1 in (`%SQLPL% %CONSQL% @%TFILE%`) do if %%1==POSLED set SEQN=%%2 echo Test Complete echo 2.Testing write to destination dir echo 2. Test write on destination dir OK > %DEST%\test_file.txt if ERRORLEVEL 1 ( echo Write Error goto eof ) echo Test Complete echo 3.Testing rman conection echo backup format '%DEST%\test_backup.bak' spfile; > %TFILE% %RMAN% %CONSTRING% @%TFILE% echo Test Complete echo Results: echo 1. %SEQN% type %DEST%\test_file.txt del /q /f %DEST%\test_file.txt if EXIST %DEST%\test_backup.bak ( echo 3. Testing rman OK del /q /f %DEST%\test_backup.bak ) ELSE ( echo 3. Test RMAN failed ) goto eof :delarc echo crosscheck archivelog all; > %TFILE% echo delete FORCE NOPROMPT archivelog all; >> %TFILE% %RMAN% %CONSTRING% @%TFILE% echo *********************************************************** echo *** Do not forget to do FULL BACKUP OF THE DATABASE !!! *** echo *********************************************************** goto eof :backdb echo run { > %TFILE% echo sql 'alter system archive log current'; >> %TFILE% echo sql 'alter system checkpoint'; >> %TFILE% echo allocate channel d1 type disk; >> %TFILE% echo set limit channel d1 kbytes 4000000; >> %TFILE% echo backup %COMPRESS% incremental level %1 skip inaccessible filesperset 1 format '%DEST%\%%d_%%T_%%U.bkd' database include current controlfile; >> %TFILE% echo sql 'alter system checkpoint'; >> %TFILE% echo sql 'alter system archive log current'; >> %TFILE% echo backup %COMPRESS% filesperset 40 format '%DEST%\%%d_%%T_%%U.bka' archivelog from sequence %SEQN% ; >> %TFILE% echo backup format '%DEST%\%%d_%%T_%%U.bkc' current controlfile; >> %TFILE% echo backup format '%DEST%\%%d_%%T_%%U.bks' spfile; >> %TFILE% echo } >> %TFILE% %RMAN% %CONSTRING% @%TFILE% set /a ERR=%ERR%+%ERRORLEVEL% :backarc echo %COMPRESS%|findstr compressed if ERRORLEVEL 1 ( echo alter system archive log current; > %TFILE% echo set feedback off pagesize 0 heading off verify off linesize 200 trimspool off >> %TFILE% echo spool %TFILE%.bat >> %TFILE% echo select 'move /Y '^|^|name^|^|' %DEST%' from v$archived_log where DEST_ID=1 and STATUS='A'; >> %TFILE% echo spool off >> %TFILE% echo exit >> %TFILE% %SQLPL% %CONSQL% @%TFILE% call %TFILE%.bat set /a ERR=%ERR%+%ERRORLEVEL% echo crosscheck backup; > %TFILE% echo crosscheck archivelog all; >> %TFILE% echo DELETE FORCE NOPROMPT EXPIRED backup; >> %TFILE% %RMAN% %CONSTRING% @%TFILE% ) else ( echo crosscheck backup; > %TFILE% echo crosscheck archivelog all; >> %TFILE% echo DELETE FORCE NOPROMPT EXPIRED backup; >> %TFILE% echo sql 'alter system checkpoint'; >> %TFILE% echo sql 'alter system archive log current'; >> %TFILE% echo backup %COMPRESS% filesperset 40 format '%DEST%\%%d_%%T_%%U_all.bka' archivelog all delete input; >> %TFILE% echo backup format '%DEST%\%%d_%%T_%%U.bkc' current controlfile; >> %TFILE% %RMAN% %CONSTRING% @%TFILE% ) set /a ERR=%ERR%+%ERRORLEVEL% :eof exit /b %ERR% 

Thanks for attention. Link to the script