Scripting Oracle RMAN Commands
Two ways to script RMAN, use a command file which is a text file residing in the file system and use a stored script which is stored in the RMAN catalog database and executed from the RMAN command prompt.
Command file to backup the USERS tablespace:
connect target /
connect catalog rman/secretpass@rmancat
run { allocate channel c1 type disk format ‘/orabak/%U’;
backup tablespace users;
}
RMAN> @backup_ts_users.rman
rman @backup_ts_users.rman
rman cmdfile=backup_ts_users.rman
rman target=/ catalog=
rman/secretpass@rmancat
Note that the CONNECT clauses are inside the backup_ts_users.rman command file, so there is no reason to provide the password in the command line – meaning you can eliminate the risk of accidental exposure of the password. Had we not included the password of the catalog user rman inside the command file, we would have had to call the Oracle RMAN executable like above.
To use a command file parameter-driven approach like the following where instead of actual values, the new command file includes the parameters (also known as placeholders or substitution variables) &1 and &2 which with a parameter-driven command file you define any number of parameters in this manner and pass the values at runtime.
connect target /
connect catalog rman/secretpass@rmancat
run { allocate channel c1 type disk format ‘&1/%U’;
backup tablespace &2;
}
A shell script, named backup_ts_generic.sh calls the backup_ts_generic.rman command file with the values /tmp as the backup location for parameter &1 and USERS as the tablespace name for parameter &2:
$ORACLE_HOME/bin/rman <
@backup_ts_generic.rman “/tmp” USERS
EOF
You can make this shell script even more generic so that the parameters are passed from the command line of the file system itself. For example, if you modify the second line in the backup_ts_generic.sh shell script to read:
@backup_ts_generic.rman “/tmp” $1
you will be able to call the backup_ts_generic.rman command file, provide /tmp as the backup location, and pass the tablespace name in the command line. For instance, if you want to backup the MYTS1 tablespace, you can issue the command:
backup_ts_generic.rman MYTS1
To log RMAN scripts via an automated mechanism such as cron in UNIX or Scheduler in Windows, you are not physically watching the command window, so how do you know the output of the Oracle RMAN commands? To capture the output, you can use the log parameter in the Oracle RMAN command line:
rman cmdfile=backup_ts_users.rman log=backup_ts_users.log
Stored Scripts – although command files work pretty well in most cases, they have one huge drawback – a command file should be available on the server where the Oracle RMAN backup is to be run. Otherwise, from within the command file, you have to connect from the Oracle RMAN client to the server by using a connect string:
connect target sys/oracle123@remotedb
There are several problems with this setup. First, the modified command file needs to store the password of SYS or some other user with SYSDBA privilege. In a security-conscious environment that may not be acceptable. Second, the Oracle RMAN client may not be compatible with the Oracle Database release. Finally, for performance reasons, you may very well want to run the Oracle RMAN client on the same server as the database itself. But what if you have databases on different servers? You will have to replicate a command file script to all servers. And when you modify the script you will have to make sure it is copied to all those servers again.
The solution use Oracle RMAN stored scripts, you can create scripts that are stored inside the Oracle RMAN catalog and not on the server itself. Because it is stored inside the Oracle RMAN catalog, you will need to connect to the catalog first, as shown below. To execute this script, al you have to do is call it with the execute command from the Oracle RMAN prompt:
RMAN> run { execute script backup_ts_users; }
Stored script for backing up USERS tablespace:
rman
RMAN> connect target /
RMAN> connect catalog rman/secretpass@rmancat
RMAN> create script backup_ts_users
command ‘Tablespace USERS Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace users;
}
The backup_ts_users stored script created above is available only to the target database to which it is currently connected. It is a local stored script, and you can’t execute a local script for one database in another. To execute a script in multiple databases, create a global stored script by using the keyword GLOBAL between CREATE and SCRIPT. For instance, to create a global script for the one above, replace:
create script backup_ts_users
with
create global script backup_ts_users
Once created, this global stored script can be executed in any database connected to this catalog. If you need to modify the script, there is no need to copy it to all servers or databases; it’s automatically available for execution to all databases connecting to the catalog.
If the global stored script already exists and you want to update it, replace CREATE with REPLACE:
replace global script backup_ts_users
- and include the updated script text.
Parameterization: This backup_ts_users stored script has a very specific purpose – backing up the USERS tablespace. What if you want to backup a different tablespace? Rather than creating multiple scripts, you can create a generic stored script to backup any tablespace.
A stored script merely asks for a value; it does not store the value you used in the script itself:
RMAN> create script backup_ts_any
comment ‘Any Tablespace Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace &1;
}
With the parameterized stored script created, pass the value of the parameter via a USING clause. For example, to backup the SYSTEM tablespace by using this backup_ts_any stored script, use the following Oracle RMAN command:
run { execute script
backup_ts_any using ‘SYSTEM’; }
To display the list of stored scripts:
RMAN> list script names;
RMAN> list global script names;
RMAN> print global script backup_ts_level1_any;
RMAN> delete global script backup_ts_level1_any;
RMAN> create script backup_ts_users
from file ‘backup_ts_users.rman’;
RMAN> print script backup_ts_users to file ‘backup_ts_users.rman’;
