Winding-Up RMAN Implementation for Production Database

While I was taking up the RMAN Backup performance issue along with support, I also posted the query on OTN Forums (http://forums.oracle.com/forums/message.jspa?messageID=3320320#3320320).

I carried out the tablespace RMAN backup on the Database Server and then copied it to Blade5 SAN location. When I did a normal copy it took only 2 minutes as opposed to 21 minutes.

This was what one contributor had to say on OTN Forum:
"The rman backup process is not only a copy process but also a check on the integrety of the written backup piece. Might be there are a lot of context switches involved in this process which can, due to latency on the network result in bad performance.(The connection to the bad performing storage is over IP as I understand it)Regards,Tycho"

Well, in any case, there definately was no solution coming out from either sides (Oracle Support and IT Admins) to resolve the performance issue and pronouce RMAN implementation successful. Assuring that <\\testdb\orcl> location would be used as a staging location to backup the RMAN files to DDS tapes, I went ahead and scheduled the RMAN full database backups.

So now, I have a Recovery Catalog Instance through which I am taking daily RMAN Backups of Production Instance, Development Instance and Test Instance. And once all the 3 backups are completed, I am taking RMAN No catalog /target Backup of Recovery Catalog Database on shared SAN folder. It takes 25 minutes to generate 700 MB RMAN Backup files of the Recovery Catalog Database.

Here is the source of windows batch file that carries out the scheduled RMAN backups:

REM#####################################################################
REM # Name:   testdb_prod_rman_hot_backup_dsk.bat
REM # Description: ORCL
REM # Generic   Hot backup script that accepts the SID
REM # Parameters:  Instance Name
REM##################################################################
REM # Check if SID was Passed
if (%1)==() goto USAGE

REM # assign ORACLE_SID to passed SID
set ORACLE_SID=%1

REM # assign a date we can use as part of the logfile
set DTE=%date:~-4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%

REM # Take the spaces out of the date string so we can use in our file name
set DTE=%DTE: =%

REM # set the script locationREM # Set location of where you will put script
set SCRIPTLOC=C:\oracle\BACKUP\Scriptscd %SCRIPTLOC%

REM # Set the Temporary command file that we will write our rman script to
set CMDFILE=%SCRIPTLOC%\%ORACLE_SID%_rman_hot_backup_dsk.cmd

REM # Remove the temporary file if it exists
del /q %CMDFILE%

REM # Set the logfile directory
REM # Change based on where you want the backup logs to go
set LOGPATH=C:\oracle\BACKUP\logs
set LOGFILE=%ORACLE_SID%_rman_hot_backup_dsk_%DTE%.log
set LOG=%LOGPATH%\%LOGFILE%

REM # Set the path of the backup location
REM # Change based on disk path for you disk based backups (ORCL)
set BACKLOC=\\testdb\%ORACLE_SID%

REM # Set to your ORACLE_HOME location
set ORACLE_HOME=C:\oracle\product\9.2.0\db_1

# Set the rest of the Oracle Environment# based on our ORACLE_HOME
set LIBPATH=%ORACLE_HOME%/lib
set LD_LIBRARY_PATH=%ORACLE_HOME%/lib
set TNS_ADMIN=%ORACLE_HOME%/network/admin

REM Set our PATH with the ORACLE_HOME so that we have a good
REM clean environment to work with
set PATH=%ORACLE_HOME%/bin;%ORACLE_HOME%/OPatch;%PATH%

REM # Write Temporary command file for RMAN backup
echo # >> %CMDFILE%
echo # display current configurations and list of backups >> %CMDFILE%
echo SHOW ALL ; >> %CMDFILE%
echo # >> %CMDFILE%
echo LIST BACKUP SUMMARY ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # lets start clean for our hot backup by doing full crosschecks >> %CMDFILE%
echo change archivelog all crosscheck ; >> %CMDFILE%
echo crosscheck backup of database; >> %CMDFILE%
echo crosscheck backup of controlfile; >> %CMDFILE%
echo # >> %CMDFILE%
echo delete noprompt expired archivelog all ; >> %CMDFILE%
echo delete noprompt expired backup ; >> %CMDFILE%
echo delete noprompt obsolete ; >> %CMDFILE%
echo # >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\testdb\orcl\%%d_%%U_%%T' MAXPIECESIZE 2 G ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # Archive current log file >> %CMDFILE%
echo SQL 'alter system archive log current' ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # Backup our database >> %CMDFILE%
echo BACKUP DATABASE tag FULL_%DTE% ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # Backup our archive log files that have not been backed up  >> %CMDFILE% 
echo BACKUP ARCHIVELOG ALL DELETE INPUT tag ARCH_%DTE% ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # put controlfile to trace >> %CMDFILE%
echo sql 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE' ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # report and delete unrecoverable and obsolete >> %CMDFILE%
echo REPORT UNRECOVERABLE ; >> %CMDFILE%
echo REPORT OBSOLETE ORPHAN ; >> %CMDFILE%
echo # >> %CMDFILE%
echo DELETE NOPROMPT OBSOLETE ; >> %CMDFILE%
echo DELETE NOPROMPT EXPIRED BACKUP ; >> %CMDFILE%
echo # >> %CMDFILE%
echo CROSSCHECK BACKUPSET ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # list backup details and summary >> %CMDFILE%
echo LIST BACKUP ; >> %CMDFILE%
echo LIST BACKUP SUMMARY ; >> %CMDFILE%

REM # Run the RMAN backup without using the recovery catalog
%ORACLE_HOME%\bin\rman target=sys/@ catalog rman/@ cmdfile=%CMDFILE% msglog=%LOG%

REM # Remove our temporary command file
del /q %CMDFILE%

REM # Goto end of script as we are done
goto END

:USAGE
echo "Must Pass ORACLE_SID as parameters."
goto END

:END

In addition to the daily full database backup, and as per Anwar's suggestion/comment to my 1st RMAN Implementation post on this blog, I scheduled the backup of archive logs at an interval of 3 hours during working hours.

The scheduled jobs are running perfectly fine, and I was able to perform Cloning to set up Test Instance using production RMAN backup. I will be posting more on how to Clone/Duplicate a Database using RMAN Duplicate Command.