RMAN Scripting

$ rman target=/ catalog=u/p@catalog cmdfile cmd.rman
You can also use the cmdfile option with an equal sign:

$ rman target=/ catalog=u/p@catalog cmdfile=cmd.rman
You can use the SQL*Plus-like notation to call a script by placing an @ before the name.
For example:

$ rman target=/ catalog=u/p@catalog @cmd.rman

SCRIPT UNIX
1. # Beginning of Script
2. # Start of Configurable Section
3. export ORACLE_HOME=/opt/oracle/10.2/db_1
4. export ORACLE_SID=PRODB1
5. export TOOLHOME=/opt/oracle/tools
6. export BACKUP_MEDIA=DISK
7. export BACKUP_TYPE=FULL_DB_BKUP
8. export MAXPIECESIZE=16G
9. # End of Configurable Section
10. # Start of site specific parameters
11. export BACKUP_MOUNTPOINT=/oraback
12. export DBAEMAIL="dbas@proligence.com"
13. export DBAPAGER="dba.ops@proligence.com"
14. export LOG_SERVER=prolin2
15. export LOG_USER=oracle
16. export LOG_DIR=/dbalogs
17. export CATALOG_CONN=${ORACLE_SID}/${ORACLE_SID}@catalog
18. # End of site specific parameters
19. export LOC_PREFIX=$BACKUP_MOUNTPOINT/loc
20. export TMPDIR=/tmp
21. export NLS_DATE_FORMAT="MM/DD/YY HH24:MI:SS"
22. export TIMESTAMP=`date +%T-%m-%d-%Y`
23. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
24. export LIBPATH=$ORACLE_HOME/lib:/usr/lib:/lib
25. export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
26. export LOG=${TOOLHOME}/log
27. LOG=${LOG}/log/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}_${TIMESTAMP}.log
28. export TMPLOG=$TOOLHOME/log/tmplog.$$
29. echo `date` "Starting $BACKUP_TYPE Backup of $ORACLE_SID \
30. to $BACKUP_MEDIA" > $LOG
31. export LOCKFILE=$TOOLHOME/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}.lock
32. if [ -f $LOCKFILE ]; then
33. echo `date` "Script running. Exiting ..." >> $LOG
34. else
35. echo "Do NOT delete this file. Used for RMAN locking" > $LOCKFILE
36. $ORACLE_HOME/bin/rman log=$TMPLOG <37. connect target /
38. connect catalog $CATALOG_CONN
39. CONFIGURE SNAPSHOT CONTROLFILE NAME TO
40. '${ORACLE_HOME}/dbs/SNAPSHOT_${ORACLE_SID}_${TIMESTAMP}_CTL';
41. run
42. {
43. allocate channel c1 type disk
44. format '${LOC_PREFIX}1/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
45. maxpiecesize ${MAXPIECESIZE};
46. allocate channel c2 type disk
47. format '${LOC_PREFIX}2/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
48. maxpiecesize ${MAXPIECESIZE};
49. allocate channel c3 type disk
50. format '${LOC_PREFIX}3/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
51. maxpiecesize ${MAXPIECESIZE};
52. allocate channel c4 type disk
53. format '${LOC_PREFIX}4/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
54. maxpiecesize ${MAXPIECESIZE};
55. allocate channel c5 type disk
56. format '${LOC_PREFIX}5/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
57. maxpiecesize ${MAXPIECESIZE};
58. allocate channel c6 type disk
59. format '${LOC_PREFIX}6/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
60. maxpiecesize ${MAXPIECESIZE};
61. allocate channel c7 type disk
62. format '${LOC_PREFIX}7/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
63. maxpiecesize ${MAXPIECESIZE};
64. allocate channel c8 type disk
65. format '${LOC_PREFIX}8/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
66. maxpiecesize ${MAXPIECESIZE};
67. backup
68. incremental level 0
69. tag = 'LVL0_DB_BKP'
70. database
71. include current controlfile;
72. release channel c1;
73. release channel c2;
74. release channel c3;
75. release channel c4;
76. release channel c5;
77. release channel c6;
78. release channel c7;
79. release channel c8;
80. allocate channel d2 type disk format
81. '${LOC_PREFIX}8/CTLBKP_${ORACLE_SID}_${TIMESTAMP}.CTL';
82. backup current controlfile;
83. release channel d2;
84. }
85. exit
86. EOF
87. RC=$?
88. cat $TMPLOG >> $LOG
89. rm $LOCKFILE
90. echo `date` "Script lock file removed" >> $LOG
91. if [ $RC -ne "0" ]; then
92. mailx -s "RMAN $BACKUP_TYPE $ORACLE_SID $BACKUP_MEDIA Failed" \
93. $DBAEMAIL,$DBAPAGER < $LOG
94. else
95. cp $LOG ${LOC_PREFIX}1
96. mailx -s "RMAN $BACKUP_TYPE $ORACLE_SID $BACKUP_MEDIA Successful" \
97. $DBAEMAIL < $LOG
98. fi
99. scp $LOG \
100. ${LOG_USER}@${LOG_SERVER}:${LOG_DIR}/${ORACLE_SID}/.
101. rm $TMPLOG
102. fi

3 The Oracle Home for that database. Change for another database.
4 The SID of the database being backed up.
5 The location on the server where this script is executed.
6 The media where the backup is stored, such as tape or disk. This parameter is only
for naming the log file, not for directing the target of the backup.
7 The type of backup, such as full or incremental. This is only for naming the log file.
This parameter does not actually cause the backup to be full or otherwise.
8 The MAXPIECESIZE parameter for RMAN. This parameter in RMAN creates the
backup pieces to be limited to a certain size, which is a limitation on some
operating systems. The limit should be based on the database size as well. If your
database is fairly small and you want to remove any limit, just specify a very high
number. In this example, we have assumed a 16GB limit.
11 The backups will be made to /oraback/loc1 through /oraback/loc8.
12 The email that says where the successful notification should be sent.
13 The email that says where the failure email should be sent, usually a pager.
14 The server where the log files of each run are stored.
15 The user ID of the log server.
16 The directory where the logs are kept on the central log server.
17 The connection string for the catalog connection. Here we assume that your catalog
database connect string is catalog and you have defined a separate catalog owner
for each database, where the owner’s name is the same as the SID of the database
being backed up and the password is the same as the owner name. This is not
absolutely necessary; you can have a common owner for catalogs of all databases.
Whatever your decision is, update this parameter to reflect that.
19 The mount points where the backups will be taken have a common format, such as
/oraback/loc, where varies from 1 to 8. The format is mentioned here.
20 The directory where the temporary file log file of the script is generated. Later this
temp file and the RMAN log file are merged and sent out as the log file.
21 The date format that the time stamps in the RMAN log files are shown as.
22 The time stamp; the log files are generated in this name.
23–25 Various path variables that need to be there. Remember, this script is called from a
cron job, so the user’s profile is not executed, and no variables are set.
26 The log file name is constructed.
27 The temporary log file is created in this name. The parameter $$ indicates the PID in
the shell script. Since the PID of each process is different, a different log file will be
created each time.
31 Since we want to prevent the script from starting if it is running currently, we’re
using a lock file. At the beginning of each run, the script checks the lock file. If it is
present, it indicates the script is running now, and the current run is aborted. At the
end of the run, the script deletes the lock file.
32 We check whether the lock file exists. If it does, then the script is running, so we
abort this run.

35 If the lock file does not exist, we create one. The contents of the file do not matter,
but we put the lines “Do NOT delete this file. Used for RMAN locking” in the file, just
in case someone gets curious and opens this file. The message should be crystal clear.
36 We start the RMAN command. The << EOF clause at the end of the line indicates that
the RMAN executable should accept all the lines until the string EOF is encountered.
37 We connect to the target database.
38 We connect to the catalog.
39 When RMAN starts backing up the database, it must get an exclusive lock on the
control file. Since that creates the disruption of the database, RMAN takes a
snapshot of the control file and uses that. Here, in this line, we decide the snapshot
control file location.
43–45 We allocate the first channel, specifying the format string so that the backups go
there. We also specify MAXPIECESIZE, which determines how big each piece should
be. Note the format string:
${LOC_PREFIX}1/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman
The location of the file will be constructed as /oraback/loc1, a mount point.
46–66 We do the same for channels 2 through 8. They go to the mount points /oraback/
loc2 through /oraback/loc8.
67–71 The actual backup command comes here. You can specify any RMAN command you
want here.
72–79 The channels are explicitly released, a best practice.
80–83 We take an explicit backup of the current control file. Note that in line 69 we have
included the control file as part of the backup, but the control file gets embedded in
the backup pieces. If you have the catalog, it is simple to get the control file from the
backup pieces. But imagine the worst-case scenario where the catalog is lost and so
is the control file. It will be hard to locate the control file from the many backup
piece files. Therefore, as a good practice, we take an explicit backup of the control
file, which has a clearly identified name.
87 After we exit the RMAN command line, we capture the return code, $?.
88 We merge the RMAN log with the script log file.
89 We remove the lock file created earlier to indicate that the script has completed its
run and a new script run may be started.
87 We check the status of the RMAN execution. 0 indicates successful execution.
91 If the script fails because of any reason, the return code will not be 0. The exact
return code is immaterial; the cause of the error will be captured in the RMAN log
file. The error is notified to the DBA’s pager. The log file is sent to the pager and the
DBA’s email.
95 If the RMAN execution was successful, we copy the log file to one of the locations
where the backups are generated. The tape backup software will pick it up from that
location.
99–100 The log file is also copied to the central log server.
101 The temporary log file is removed.

CRONTAB

10 0 * * 0,1,3,5 /u02/backup/backup_database.sh

1. Issue the following Unix command:

$ crontab –e

This opens your crontab file in the vi editor. If you don’t have any entry yet in crontab,
you will see an empty file. Place whatever line you want in the file. Be sure to adhere to
the format described in Table 9-2 later in this chapter.

2. Save the file and exit. The line is now scheduled in crontab.
3. Check cron for all scheduled programs:

$ crontab –l


SCRIPT WINDOWS

1. @ECHO OFF
2. :: Beginning of Script
3. :: Start of Configurable Section
4. set ORACLE_HOME=C:\oracle\product\10.2\db_1
5. set ORACLE_SID=MOBDB10
6. set TOOLHOME=C:\TOOLS
7. set BACKUP_MEDIA=DISK
8. set BACKUP_TYPE=FULL_DB_BKUP
9. set MAXPIECESIZE=16G
10. set BACKUP_MOUNTPOINT=c:\oracle\flash
11. set DBAEMAIL="dbas@proligence.com"
12. set DBAPAGER="dba.ops@proligence.com"
13. set CATALOG_CONN=%ORACLE_SID%/%ORACLE_SID%@catalog
14. set MS=mail.proligence.com
15. ::
16. :: end of Configurable Section
17. ::
18. set BACKUP_LOC_PREFIX=%BACKUP_MOUNTPOINT%\loc
19. set TMPDIR=C:\temp
20. set NLS_DATE_FORMAT="MM/DD/YY HH24:MI:SS"
21. realdate /d /s="set curdate=" > %TOOLHOME%\tmp_dt.bat
22. realdate /t /s="set curtime=" > %TOOLHOME%\tmp_tm.bat
23. call %TOOLHOME%\tmp_dt.bat
24. call %TOOLHOME%\tmp_tm.bat
25. ::
26. ::
27. set LOG=%TOOLHOME%\%ORACLE_SID%_%BACKUP_TYPE%_%BACKUP_MEDIA% ➥_
%CURDATE%_%CURTIME%.log
28. set TMPLOG=%TOOLHOME%\tmplog.$$
29. ::
30. :: Build the Command File
31. set FORMATSTRING=%BACKUP_LOC_PREFIX%1\%ORACLE_SID%_%%u_%%p.rman
32. set CMDFILE=%TOOLHOME%\%ORACLE_SID%.rman
33. echo run { > %CMDFILE%
34. echo allocate channel c1 type disk >> %CMDFILE%
35. echo format '%FORMATSTRING%' >> %CMDFILE%
36. echo maxpiecesize %MAXPIECESIZE%; >> %CMDFILE%
37. echo backup >> %CMDFILE%
38. echo tablespace users; >> %CMDFILE%
39. echo release channel c1; >> %CMDFILE%
40. echo } >> %CMDFILE%
41. :: End of Command File Generation
42. ::
43. echo Starting the script > %LOG%
44. %ORACLE_HOME%\bin\rman target=/ catalog=%CATALOG_CONN% @%CMDFILE% ➥
msglog=%TMPLOG%
45. ::
46. :: Merge the Logfiles
47. type %TMPLOG% >> %LOG%
48. :: Check for errors
49. ::
50. echo THE OUTPUT WAS %ERRORLEVEL% >> %LOG%
51. findstr /i "error" %LOG%
52. if errorlevel 0 if not errorlevel 1 bmail -s %MS% -t %DBAPAGER% ➥
-f "Database" -m %LOG%
53. @echo on

1 This line instructs the batch program executer to stop displaying the commands in the
file; just execute them.
4 We set the Oracle Home.
5 We set the Oracle SID.
6 We set the location of this batch file.
7 We specify the type of the backup, such as disk, tape, and so on. Please note that
specifying a type here merely places the type in the name of the log file; it does not
impact the type of the backup created by this batch file. The RMAN backup commands
in the batch file determine the nature of the backup created.
8 We specify the type of backup, such as full or incremental, so that it becomes part of
the name of the log file.
9 The MAXPIECESIZE for the backup is specified here.
10 The variables that hold the location of the backup.
11–12 The email addresses where an email will be sent.
13 The catalog connection string. In this script, we have assumed that the rman repository
username is the ORACLE_SID and the password is the same as the username.
14 The mail server name. You can ask your email administrator for this. In many small and
medium organizations, this may be mail.organization.com.
21 We want to create a log file whose name should have the current date and time. The
standard Windows date command does not easily yield a usable form of the date to be
used in the log file, as is the case with the time component. Here we have used a special
program called realdate. More information about realdate is provided following the
table.
In this line, we have extracted the current date and issued the command to set a
variable curdate to hold the current date. For instance, if this program is executed on
February 1, 2007, the command realdate /d /s="set curdate=" returns set
curdate=20070201. This line is placed in the file tmp_dt.bat.
22 We again use realdate to extract the current time. For instance, if the program is
executed at 11:15:53 p.m., the command realdate /t /s="set curtime=" yields
set curtime=231553. This line places that string in the file tmp_tm.bat.
23–24 We execute the batch files we generated in the previous two lines. These set the
variables curdate and curtime.
27 We set the name of the log file.
28 We create a temporary log file to hold the output of the RMAN commands.
31 We create a variable called FORMATSTRING for the name of the backup piece.
32 We create a variable called CMDFILE to hold the name of the command file that will be
passed to RMAN.
33–40 We put all the RMAN commands to be executed later in the command file.
44 We call the RMAN to execute the command file created dynamically in lines 33–40. The
output goes to the log file named in line 28.
47 Now that we have the output of the RMAN output, we place the contents of that RMAN
log file to the main log file we have been using.
50 We place the result of the RMAN run, as captured in the variable ERRORLEVEL. If the
RMAN run was successful, this variable will be 0. The result will be in the log file.
51 If there is any error, the log file will contain that error. This line shows how to use the
findstr command to find out whether the log file contains the word error in either
uppercase or lowercase.
52 If the error was found, the errorlevel variable will be nonzero, and we want to email
the log file to the email address specified in the variable DBAPAGER. To send the email,
we have used a program called bmail, which is described next.

Script Windows

RMAN> run {
2> allocate channel c1 type disk
3> format 'c:\oracle\flash\loc1\MOBDB10_%u_%p.rman'
4> maxpiecesize 16G;
5> backup
6> tablespace users;
7> release channel c1;
8> }