Many DBAs have discovered how Oracle Recovery Manager (Oracle RMAN) can reliably back up, restore, and recover Oracle Database instances. Among its many features is the ability to script the commands for batch-centric, hands-off execution. This article discusses the ways to script Oracle RMAN commands in Oracle Database 11g and how to pick the right approach for your specific needs.
Why Script?
Why create a script for Oracle RMAN commands? There are two primary reasons:
1. Most Oracle RMAN activities are batch-oriented and can be automated. For instance, backing up a database is a repetitive activity and not something you would want to execute interactively.
2. Scripts provide consistency. For tasks of an ad hoc nature, such as recovering a database from a backup, automation is not strictly required. When a script is used to execute the activity, however, the action will be the same, regardless of the experience and expertise of the DBA performing the recovery.
There are two ways to script Oracle RMAN commands in Oracle Database 11g:
1. Use a command file. A command file is a text file residing in the file system.
2. Use a stored script. This script is stored in the Oracle RMAN catalog database and executed from the Oracle RMAN command prompt.
Command Files
Oracle RMAN command files are plain-text files and similar to shell scripts in UNIX or batch files in Microsoft Windows. Listing 1 shows a very simple example command file—named backup_ts_users.rman—used to back up the USERS tablespace. The file extension .rman is not necessary, but it is helpful in making the meaning of the file clear.
Code Listing 1: Command file for backing up USERS tablespace
connect target /
connect catalog rman/secretpass@rmancat
run {
allocate channel c1 type disk format ‘/orabak/%U’;
backup tablespace users;
}
You can call a command file in several ways. From the Oracle RMAN prompt, you can call the example command file as follows:
RMAN> @backup_ts_users.rman
Note that the command file is executed by the @ sign. It is important, however, to provide the full name of the command file, including the extension. (The Oracle RMAN executable does not expect or apply a default extension.)
You can also call the command file directly from the command line as
rman @backup_ts_users.rman
This approach for calling the script is highly useful in shell scripts or batch files for making backups. Also note that instead of using the @ sign to call the command file, you can use the cmdfile parameter as follows:
rman cmdfile=backup_ts_users.rman
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 that 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 this:
rman target=/ catalog=
rman/secretpass@rmancat
If this command were executed, someone on the server could easily get the password of the catalog user by checking the process. When the command file contains the connection information—including the password—for the catalog user, the sensitive information is not visible to anyone watching the process. Note that you should also set the permissions of the command file in such a way that nonadmin users will not be able to read it.
Passing parameters. The backup_ts_users.rman command file works well, but it’s too specific. It forces the output of the backup to one specific directory and backs up only one tablespace (USERS). If you want to back up to a different location or back up a different tablespace, you have to create a new script.
A better strategy is to make an Oracle RMAN command file parameter-driven. Rather than hard-coding specific values in the script, you can include parameters whose values are passed at runtime. Listing 2 shows a modified version of the backup_ts_users .rman command file, named backup_ts_generic.rman. Instead of actual values, the new command file includes the parameters (also known as placeholders or substitution variables) &1 and &2. With a parameter-driven command file, you can define any number of parameters in this manner and pass the values at runtime.
Code Listing 2: Parameter-driven command file
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 <<EOF
@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 so it reads
@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 back up the MYTS1 tablespace, you can issue
backup_ts_generic.sh MYTS1
Logging. When you run Oracle 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? The output is especially crucial when command execution results in an error and you need to examine the output. 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
Now the output of the backup_ts_generic.rman command file will be recorded in a file named backup_ts_users.log instead of appearing on the screen. You can view this file later to examine the results of the Oracle RMAN run.
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, this modified command file needs to store the password of SYS or some other user with the SYSDBA privilege. In a security-conscious environment, that may not be acceptable. Second, the Oracle RMAN client may be 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? With Oracle RMAN stored scripts, you can create scripts that are stored inside the Oracle RMAN catalog and not on the server itself. Listing 3 shows an example stored script called backup_ts_users. Because it is stored inside the Oracle RMAN catalog, you will need to connect to the catalog first, as shown in the listing. To execute this script, all you have to do is call it with the execute command from the Oracle RMAN prompt:
RMAN> run { execute script
backup_ts_users; }
Code Listing 3: Stored script for backing up USERS tablespace
C:\> rman
RMAN> connect target /
RMAN> connect catalog rman/secretpass@rmancat
RMAN> create script backup_ts_users
2> comment ‘Tablespace Users Backup’
3> {
4> allocate channel c1 type disk format ‘c:\temp\%U’;
5> backup tablespace users;
6> }
The backup_ts_users stored script created in Listing 3 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 created 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 the script shown in Listing 3 as a global stored script, 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 back up a different tablespace? Rather than creating multiple scripts, you can create a generic stored script to back up any tablespace (as you did with the command files earlier).
Listing 4 shows how to create a parameterized stored script. In place of the tablespace name, Listing 4 uses the &1 parameter, whose value is passed at runtime. When a parameter-driven stored script is created, Oracle RMAN asks for an example value for any parameter used. When &1 is included as a parameter in line 5, Oracle RMAN asks for an example value. Enter users or any other example tablespace you may want to pass. Remember, the stored script merely asks for an example value; it does not store the value you used in the script itself.
Code Listing 4: Parameter-driven stored script
RMAN> create script backup_ts_any
2> comment ‘Any Tablespace Backup’
3> {
4> allocate channel c1 type disk format ‘c:\temp\%U’;
5> backup tablespace &1;
Enter value for 1: users
users;
6> }
7>
created script backup_ts_any
With the parameterized stored script created, pass the value of the parameter via a USING clause. For example, to back up 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’; }
Administration. Oracle RMAN provides features to help administer stored scripts.
To display the list of stored scripts, use the list script names command as follows:
RMAN> list script names;
List of Stored Scripts in Recovery Catalog
Scripts of Target Database ARUPLAP1
Script Name
Description
————
backup_ts_any
Any Tablespace Backup
backup_ts_users
Tablespace Users Backup
This command displays the names of local as well as global stored scripts.
To display the global scripts only, use the following command:
RMAN> list global script names;
RMAN> print global script
backup_ts_level1_any;
If the stored script you want to print is local, omit the keyword GLOBAL in the command.
To drop a script, such as backup_ts_level1_any, use the following command:
RMAN> delete global script
backup_ts_level1_any;
What if you want to create a stored script from a script file in the file system? You can import the file into the catalog. Here is an example:
RMAN> create script backup_ts_users
from file ‘backup_ts_users.rman’;
Conversely, you can create a file from a stored script (or export a stored script to a file). Here is an example:
RMAN> print script backup_ts_users
to file ‘backup_ts_users.rman’;
Conclusion
Oracle RMAN scripts provide capabilities not only for automation but also for consistency of execution. This article presented two ways to script RMAN commands: via OS-level command files and through scripts stored in a catalog database. Both approaches enable creation of generic scripts that use parameters whose values can be passed at execution time.
to access non-Oracle systems you must use Oracle Heterogeneous Services.
Ida Bagus Enderajana
Recent Comments