Pavan DBA's Blog

@Echo Off

:————————————————————————–

: Program: RMan_bkup.cmd

: Purpose: Backup the oracle database via RMan and dsmc

:

:————————————————————————–

 

:————————————————————————–

: Set Variables,,, Establish the environment.

:

:————————————————————————–

:————————————————————————–

: If a backup is already active,, then exit

:————————————————————————–

 

If Defined RMAN_BACKUP Exit

 

Set RMAN_BACKUP=ACTIVE

Setlocal

CLS

SET SID=%ORACLE_SID%

Set PGM_Name=RMan_bkup

TITLE RMan Backup for %SID%-%COMPUTERNAME% Please Don’t Close or Exit,,,

 

:————————————————————————–

: Read Parmfile,,, Set variables

: Parmfile should be located in same directory as RMan_bkup script

:————————————————————————–

SET PARMFILE=..\config\%PGM_Name%_Parm.txt

 

For /F “Delims== Tokens=2” %%i In (‘FIND “BKUP_DIR” %PARMFILE%’) Do SET BKUP_DIR=%%i

For /F “Delims== Tokens=2” %%i In (‘FIND “LOGDIR” %PARMFILE%’) Do SET LOGDIR=%%i

For /F “Delims== Tokens=2” %%i In (‘FIND “DIST” %PARMFILE%’) Do SET DIST=%%i

For /F “Delims== Tokens=2” %%i In (‘FIND “DSMDIR” %PARMFILE%’) Do SET DSMDIR=%%i

For /F “Delims== Tokens=2” %%i In (‘FIND “RMANOPT” %PARMFILE%’) Do SET RMANOPT=%%i

For /F “Delims== Tokens=2” %%i In (‘FIND “DFNAME” %PARMFILE%’) Do SET DFNAME=%%i

 

SET CNTL_SCRIPT=%BKUP_DIR%\RMAN_CNTLRESTORE.SCR

SET MKSAPDATA=%BKUP_DIR%\MKSAPDATA.CMD

SET TMPSCRIPT=%TEMP%\RMan_%RANDOM%.txt

 

For /F “Tokens=2*” %%i In (‘ECHO %DATE%’) Do SET DT=%%i

SET DT=%DT:/=-%

SET TM=%TIME::=-%

SET TM=%TM: =%

SET DESC=%SID%-%COMPUTERNAME% Backup: %DT% %TM%

SET LOG=%LOGDIR%\Rman_%SID%_%DT%_%TM%_Bkup.log

SET REPORT=%BKUP_DIR%\%SID%_SchemaReport_%DT%_%TM%.txt

SET LOG=%LOG: =%

SET THISDIR=%CD%

SET ORAINIT=%ORACLE_HOME%\database\SPFILE%SID%.ora

If NOT Exist %ORAINIT% Set ORAINIT=%ORACLE_HOME%\database\init%SID%.ora

 

:————————————————————————–

: Call various functions to:

: Save Control file trace backup

: Obtain Schema Report

: Build SAPData directory script

: Cleanup Old Backups

:————————————————————————–

Call :Trace_Backup

Call :Schema_Report

Call :Build_Script

 

:————————————————————————–

: Process Temp file directory Creation

:————————————————————————–

Call :Logit Creating Temp Data file Directory entries . . .

Set Dummy=%TEMP%\Dummy_Out.txt

Set Tempsql=%TEMP%\Temp.sql

 

Echo SET LINESIZE 2000; > %Tempsql%

ECHO SET HEADING OFF; >> %Tempsql%

ECHO SELECT FILE_NAME FROM DBA_TEMP_FILES; >> %Tempsql%

Echo EXIT; >> %Tempsql%

 

SQLPLUS -SILENT “/as sysdba” @%Tempsql% > %Dummy%

 

For /F %%a In (%Dummy%) Do (

Call :Process_Tempfile %%a

)

Del /F/Q %Dummy% %Tempsql%

 

Call :RMan_Cleanup

 

:————————————————————————–

: Create Script,,, Backup the database

:————————————————————————–

Call :Logit RMan_Backup Database Plus Archivelog

Call :Logit Backup Description: %DESC% %LOG%

Set I=

ECHO RUN > %TMPSCRIPT%

ECHO { >> %TMPSCRIPT%

ECHO allocate channel t1 type ‘SBT_TAPE’ parms=”ENV=(TDPO_OPTFILE=%RMANOPT%)”; >> %TMPSCRIPT%

ECHO allocate channel t2 type ‘SBT_TAPE’ parms=”ENV=(TDPO_OPTFILE=%RMANOPT%)”; >> %TMPSCRIPT%

ECHO BACKUP FORMAT ‘DB-%%d_id-%%I_%%u’ DATABASE PLUS ARCHIVELOG; >> %TMPSCRIPT%

Echo SQL “ALTER SYSTEM ARCHIVE LOG CURRENT”; >> %TMPSCRIPT%

Echo BACKUP FORMAT ‘ARCH-%%d_id-%%I_%%u’ ARCHIVELOG ALL DELETE ALL INPUT; >> %TMPSCRIPT%

Echo BACKUP FORMAT ‘CNTL-%%d-id-%%I_%%u’ (CURRENT CONTROLFILE); >> %TMPSCRIPT%

ECHO } >> %TMPSCRIPT%

 

RMAN “TARGET” / @%TMPSCRIPT% >> “%LOG%”

 

SET MAXRC=%ERRORLEVEL%

 

:————————————————————————–

: Process Control file statements for Directory and Recovery steps

:————————————————————————–

Call :Logit Creating Control File recovery script and Directory entries. . .

SET Dummy=%Temp%\Dummy.txt

 

 

For /F “Delims== Tokens=2″ %%i In (‘FINDSTR /I /C:”piece handle=CNTL-” %LOG%’) Do (

For /F %%j In (‘Echo %%i’) Do (Set Media_File=%%j)

)

 

 

Findstr /I “control_files=” %ORAINIT% | Find /V “—” > %Dummy%

 

For /F “Delims== Tokens=2*” %%a In (%Dummy%) Do (

For /D %%b In (%%a) Do (

Call :Process_CntlFiles %%b

)

)

DEL /F/Q %Dummy%

 

:————————————————————————–

: If the RMAN Backup finished successfully, then back it up w/ TSM

:————————————————————————–

If %MAXRC% EQU 0 (

 

Call :TSM_Backup

 

) Else (

Call :Logit RC: %MAXRC% Received during RMAN Backup of: %SID%-%COMPUTERNAME%

Call :Alert RC: %MAXRC% Received during RMAN Backup of: %SID%-%COMPUTERNAME%

)

 

CD “%THISDIR%”

Call :Logit RMan_Backup and TSM Processing complete: %DATE% %TIME%

CD “%THISDIR%”

Goto PGM_Exit

 

:————————————————————————–

: Function to handle TSM Backup

:

:————————————————————————–

:TSM_Backup

CD %DSMDIR%

Call :Logit RMan Backup complete,,, Backing up files to TSM . . .

DSMC ARCHIVE “%BKUP_DIR%\*” -DESC=”%DESC%” -subdir=yes -deletefiles -archmc=A35 -pass=amoco -verbose >> %LOG%

Set TSMRC=%ERRORLEVEL%

If %TSMRC% GTR 0 (

 

Call :Logit TSM RC: %TSMRC% Received during TSM Backup of: %SID%-%COMPUTERNAME%

Call :Alert TSM RC: %TSMRC% Received during TSM Backup of: %SID%-%COMPUTERNAME%

 

)

 

@Goto :EOF

 

:————————————————————————–

: Function to handle Temp File Directory Creation.

:————————————————————————–

:Process_Tempfile

Set Tempvar=%*

Set Tempvar=%Tempvar:\TEMP.=$%

 

For /F “Delims=$ Tokens=1” %%i In (‘Echo %Tempvar%’) Do (

Echo MKDIR %%i >> %MKSAPDATA%

)

 

@Goto :EOF

:————————————————————————–

: Function to handle Control file processing.

:————————————————————————–

:Process_CntlFiles

Set Tempvar=%*

If NOT Defined Tempvar Goto :EOF

Set Tempvar=%Tempvar:’=%

Set Tempvar=%Tempvar:,= %

 

Echo RESTORE CONTROLFILE TO ‘%Tempvar%’ FROM ‘%Media_File%’; >> %CNTL_SCRIPT%

 

Set Tempvar=%Tempvar:CONTROL=$%

 

For /F “Delims=$ Tokens=1*” %%i In (‘Echo %Tempvar%’) Do (

 

Echo MKDIR %%iCONTROL >> %MKSAPDATA%

 

)

 

@Goto :EOF

 

:————————————————————————–

: Function to backup of Trace file.

:

:————————————————————————–

:Trace_Backup

Call :Logit Backing up Control File to Trace. . .

 

Set TEMPSQL=%TEMP%\Temp_Trace.sql

ECHO ALTER DATABASE BACKUP CONTROLFILE TO TRACE; > %TEMPSQL%

ECHO EXIT; >> %TEMPSQL%

 

SQLPLUS -SILENT “/as sysdba” @%TEMPSQL%

 

For /F “Delims== Tokens=2*” %%A In (‘FIND /I “user_dump_dest” %ORAINIT%’) Do SET TRACEDIR=%%A

 

SET TRACEDIR=%TRACEDIR:’=%

 

For /F %%B In (‘DIR /B /O-D %TRACEDIR%\*.trc’) Do (

Copy /Y %TRACEDIR%\%%B %BKUP_DIR%\.

Call :Logit TraceFile: %TRACEDIR%\%%B Copied to: %BKUP_DIR%

Goto Trc_Copy_Complete

)

 

:Trc_Copy_Complete

DEL /F/Q %TEMPSQL%

@Goto :EOF

 

:————————————————————————–

: Function to get RMan Schema Report

:

:————————————————————————–

:Schema_Report

Call :Logit Creating RMan Schema Report. . .

ECHO REPORT SCHEMA; > %TMPSCRIPT%

ECHO QUIT; >> %TMPSCRIPT%

 

RMAN “TARGET” / @%TMPSCRIPT% >> “%REPORT%”

RMAN “TARGET” / @%TMPSCRIPT% >> “%LOG%”

@Goto :EOF

 

:————————————————————————–

: Function to handle Cntl Filename Processing

:

:————————————————————————–

:Process_CntlFilename

Set Dummy=%*

If NOT Defined Dummy Goto :EOF

Set Dummy=%Dummy:CONTROL= %

 

For /F “Tokens=1” %%b In (‘Echo %Dummy%’) Do (

Echo MKDIR %%bCONTROL >> %MKSAPDATA%

)

 

@Goto :EOF

 

:————————————————————————–

: Function to Build Sapdata dircectory Script

:

:————————————————————————–

:Build_Script

Call :Logit Building script for SAPDATA directory creation. . .

Set Dummy=%TEMP%\Dummy.txt

ECHO @ECHO OFF > %MKSAPDATA%

ECHO :——————————————————— >> %MKSAPDATA%

ECHO : Create All directories needed for database recovery >> %MKSAPDATA%

ECHO :——————————————————— >> %MKSAPDATA%

 

FIND /I “%DFNAME%” %REPORT% > %Dummy%

 

For /F “Tokens=5” %%i In (%Dummy%) Do (

For /F “Delims=\ Tokens=1-5” %%A In (‘ECHO %%i’) Do (

ECHO MKDIR %%A\%%B\%%C\%%D\%%E >> %MKSAPDATA%

)

)

DEL /F/S/Q %Dummy%

@Goto :EOF

 

:————————————————————————–

: Function to handle the echo of messages to console and Log

:

:————————————————————————–

:Logit

Echo %* >> %LOG%

Echo, >> %LOG%

ECHO %*

ECHO,

@Goto :EOF

 

:————————————————————————–

: Function to handle Sending Email/Pages for Errors

:

:————————————————————————–

:Alert

Call :Logit Sending Email/Page for Backup errors.

Set MSG=%*

Set SUB=RMain Backup Error on: %SID%-%COMPUTERNAME%

SendMail /Msg %MSG% /Sub %SUB% /Dist %DIST% /Att %LOG%

@Goto :EOF

 

:————————————————————————–

: Function to Cleanup Expired/Obsolete RMan backups from Cntl file

:

:————————————————————————–

:RMan_Cleanup

Call :Logit Executing RMan Expired/Obsolete Backup cleanup. . .

ECHO DELETE NOPROMPT FORCE OBSOLETE; > %TMPSCRIPT%

 

RMAN “TARGET” / @%TMPSCRIPT% >> “%LOG%”

@Goto :EOF

 

 

:PGM_Exit

DEL /F/Q %TMPSCRIPT% %TEMPSQL%

EndLocal

SET RMAN_BACKUP=

:Exit_Clean

 

Note :  As always, please test this script in test/dev env before implementing in production