C H A P T E R  18

RMAN Backups and Reporting

Chapter 17 provided the details on configuring RMAN and using specialized features to control the behavior of RMAN. After you consider which features you require, you're ready to create backups. RMAN can back up the following types of files:

• Datafiles
• Control files
• Archived redo log files
• Spfile
• Backup pieces

For most scenarios, you will use RMAN to back up datafiles, control files, and archive redo log files. If you have the autobackup of the control file feature enabled, then RMAN will automatically back up the control file and the spfile (if you're using one) when a BACKUP or COPY command is issued. You can also back up the backup piece files that RMAN has created.

RMAN does not back up Oracle Net files, password files, block change tracking files, or the Oracle binary files (files created when you installed Oracle). You should put in place operating system backups that include those files.

Also note that RMAN does not back up online redo log files. If you did back up the online redo log files, it would be pointless to restore them. The online redo log files contain the latest redo generated by the database. You would not want to overwrite them from a backup with old redo information. When your database is in archivelog mode, the online redo log files contain the most recently generated transactions required to perform complete recovery.

This chapter details many of the features related to running the RMAN BACKUP command. Also covered in this chapter are techniques for logging output and reporting on RMAN backup operations. This chapter begins by covering a few common practices to enhance what is displayed in the RMAN output when running commands.

Preparing to Run RMAN Backup Commands

Before I run RMAN backups, I usually set a few things so as to enhance what is shown in the output. You don't need to set these variables every time you login and run an RMAN command. However, when troubleshooting or debugging issues, it's almost always a good idea to enable the following:

• Set NLS_DATE_FORMAT operating system variable
• Set ECHO
• Show RMAN variables

The bulleted items are discussed in the following sections.

Setting NLS_DATE_FORMAT

Before running any RMAN job, I set the operating system variable NLS_DATE_FORMAT to include a time (hours, minutes, and seconds) component. For example:

Additionally, if I have a shell script that calls RMAN, I put the prior line directly in the shell script (see the shell script at the end of Chapter 17 for an example):

This ensures that when RMAN displays a date, it always includes the hours, minutes, and seconds as part of the output. By default, RMAN only includes the date component (DD-MON-YYYY) in the output. For example, when starting a backup, here is what RMAN displays:

When you set the NLS_DATE_FORMAT OS variable to include a time component, the output will look like this instead:

When troubleshooting, it's essential to have a time component so that you can determine how long a command took to run, or how long a command was running before a failure occurred. Oracle Support will almost always ask you to set this variable to include the time component before capturing output and sending it to them.

The only downside to setting the NLS_DATE_FORMAT is if you set it to a value unknown to RMAN, it can cause connectivity issues. For example, here the NLS_DATE_FORMAT is set to an invalid value:

When set to an invalid value, you get this error when logging into RMAN:

To unset the NLS_DATE_FORMAT variable, set it to a blank value, like so:

Setting ECHO Setting ECHO

Another value that I always set in any RMAN scripts is the ECHO command, seen here:

This instructs RMAN to display the command that it's running in the output, so you can see what RMAN command is running along with any relevant error or output messages associated with the command. This is especially important when you're running RMAN commands within scripts, because you're not directly typing in a command (and may not know what command was issued within the shell script). For example, without SET ECHO ON, here's what is displayed in the output for a command:

With SET ECHO ON, this output shows the actual command that was run:

From the prior output you can see which command is running, when it started, and so on.

Showing Variables

Another good practice is to run the SHOW ALL command within any script, like so:

This displays all of the RMAN configurable variables. When troubleshooting, you may not be aware of something that another DBA has configured. This gives you a snapshot of the settings as they were when the RMAN session executed.

Running Backups

Before you run an RMAN backup, make sure you read Chapter 17 for details on how to configure RMAN with settings for a production environment. For production databases, I mainly run RMAN from a shell script similar to the script shown at the end of Chapter 17. Within the shell script, I configure every aspect of RMAN that I want to use for a particular database. If you run RMAN out-of-the-box with its default settings, you will be able to back up your database. However, these settings will not be adequate for most production database applications.

Backing up the Entire Database

If you're not sure where RMAN will be backing up your database files, you need to read Chapter 17 because it describes how to configure RMAN to create the backup files in the location of your choice. Here is how I usually configure RMAN to write to specific locations on disk (note that the CONFIGURE command must be executed before you run the BACKUP command):

After a backup location is configured, I almost always use a command similar to the one shown next to back up the entire database:

This command ensures that RMAN will back up all datafiles in the database, all archive redo logs generated prior to the backup, and all archive redo logs generated during the backup. This command also ensures that you have all of the datafiles and archive redo logs that would be required to restore and recover your database.

If you have the autobackup of the control file feature enabled (see Chapter 17 for details), the last task RMAN does as part of the backup is generate a backup set that contains a backup of the control file. This control file will contain all information regarding the backup that took place and any archive redo logs that were generated during the backup.

There are many nuances to the RMAN BACKUP command. For production databases, I usually back up the database with the BACKUP INCREMENTAL LEVEL=0 DATABASE PLUS ARCHIVELOG command. That's usually sufficient. However, you will encounter many situations where you need to run a backup that uses a specific RMAN feature or you might troubleshoot an issue where you need to be aware of the other ways to invoke an RMAN backup. These aspects are discussed in the next several sections of this chapter.

Full Backup versus Incremental Level=0

The term “RMAN full backup” sometimes causes confusion. A more apt way of phrasing what a full backup is doing would be “RMAN is backing up all modified blocks within one or more datafiles.” The term “full” does not mean that all blocks are backed up or that all datafiles are backed up. It simply refers to the fact that all blocks that would be required to rebuild a datafile (in the event of a failure) are being backed up. You can take a full backup of a single datafile and the contents of that backup piece may be quite a bit smaller than the datafile itself.

The term “RMAN level 0 incremental backup” doesn't exactly describe itself very well, either. A level 0 incremental backup is backing up the exact same blocks as a full backup. In other words, the following two commands back up the exact same blocks in a database:

The only difference between the prior two commands is that an incremental level 0 backup can be used in conjunction with other incremental backups whereas a full backup can not participate in an incremental backup strategy.

Therefore, I almost always prefer to use the INCREMENTAL LEVEL=0 syntax (as opposed to a full backup). This is because it gives me the flexibility to use the level 0 incremental backup with different incremental level backups.

Backup Sets versus Image Copies

The default backup mode of RMAN instructs it to only back up blocks that have been used in a datafile; these are known as backup sets. RMAN can also make byte-for-byte copies of the datafiles; these are known as image copies. Creating a backup set is the default type of backup that RMAN creates. The next command creates a backup set backup of the database:

If you prefer, you can explicitly place the AS BACKUPSET command when creating backups:

You can instruct RMAN to create image copies by using the AS COPY command. The following creates image copies of every datafile in the database:

Since image copies are identical copies of the datafiles, they can be directly accessed by the DBA with operating system commands. For example, say you had a media failure and you didn't want to use RMAN to restore an image copy. You could use an operating system command to copy the image copy of a datafile to a location where it can be used by the database, whereas a backup set consists of binary files that only the RMAN utility can write to or read from.

I prefer to use backup sets when working with RMAN. The backup sets tend to be smaller than the datafiles and can have true binary compression applied to them. Also, I don't find it inconvenient to use RMAN as the mechanism for creating backup files that only RMAN can restore. Using RMAN with backup sets is efficient and very reliable.

Backing up Tablespaces

RMAN has the ability to back up at the entire database level (as shown in prior examples), the tablespace level, or even more granularly at the datafile level. When you back up a tablespace, RMAN backs up any datafiles associated with the tablespaces(s) that you specify. For example, the following command will back up all of the datafiles associated with the SYSTEM and SYSAUX tablespaces:

One scenario where I back up at the tablespace level is if I've recently created a new tablespace and want to take a backup of just the datafiles (associated with the newly added tablespace). Note that when troubleshooting backup and recovery issues, it's often more efficient to work with one tablespace (because it's usually much faster to back up one tablespace than the entire database).

Backing up Datafiles

You may occasionally need to back up individual datafiles. For example, when troubleshooting issues with backups, it's often helpful to attempt to successfully backup one datafile. You can specify datafiles by filename or by file number like so:

Here's an example where file numbers are specified:

Here are some other examples of backing up datafiles using various features:

Backing up the Control File

The most reliable way to back up the control file is to configure the autobackup feature:

This command ensures that the control file is automatically backed up when a BACKUP or COPY command is issued. I usually enable the autobackup of the control file feature and then never worry about explicitly issuing a separate command to back up the control file. When in this mode, the control file is always created in its own backup set and backup piece after the datafile backup pieces have been created.

If you need to manually back up the control file, you can do so like this:

The location of the backup is either a default operating system location, the FRA (if using), or a manually configured location. As shown in Chapter 17, I prefer to set the location of the control file backup piece to the same location as the datafile backups:

Backing up the Spfile

If you have enabled the autobackup of the control file feature, the spfile will be backed up automatically (along with the control file) anytime a BACKUP or COPY command is issued. If you need to manually back up the spfile, use the following command:

The location of the file that contains the backup of the spfile is dependent on what you have configured. By default, if you don't use a FRA and you haven't explicitly configured a location via a channel, then for Linux/Unix servers the backup goes to the ORACLE_HOME/dbs directory.

Backing up Archive Redo Logs

I don't usually back up the archive redo logs separately from the database backups. As mentioned earlier, I normally back up the database files and the archive redo log files by using the following command:

However, you will occasionally find yourself in a situation where you need to take a special one off backup of the archive redo logs. You can issue the following command to back up the archive redo logs files:

If you have a mount point that is nearly full and you determine that you want to back up the archive redo logs (so that they exist in a backup file) but then you want to immediately delete the archive redo log files (that were just backed up) from disk, you can use the following syntax to back up the archive redo logs and then have RMAN delete them from the storage media:

Listed next are some other ways in which you can back up the archive redo log files:

If an archive redo log has been removed from disk manually via an operating system delete command, RMAN will throw the following error when attempting to back up the non-existent archive redo log file:

In this situation, first run a CROSSCHECK command to let RMAN know which files are physically available on disk:

Backing up Fast Recovery Area

If you use a fast recovery area, one nice RMAN feature is that you can back up all of the files in that location with one command. If you're using a media manager and have a tape backup channel enabled, you can back up everything in the fast recovery area to tape like this:

You can also back up the fast recovery area to a location on disk. Use the TO DESTINATION command to accomplish this:

RMAN will automatically create directories as required beneath the directory specified by the TO DESTINATION command.

RMAN will back up full backups, incremental backups, control file autobackups, and archive redo log files. Keep in mind that flashback logs, online redo log files, and the current control file are not backed up.

Excluding Tablespaces from Backups

Suppose you have a tablespace that contains non-critical data and you don't ever want to back it up. RMAN can be configured to exclude such tablespaces from the backup. To determine if RMAN is currently configured to exclude any tablespaces, run this command:

Use the EXCLUDE command to instruct RMAN which tablespaces to not back up:

Now for any database level backups, RMAN will exclude the datafiles associated with the USERS tablespace. You can instruct RMAN to back up any excluded tablespaces with this command:

You can clear the exclude setting via the following command:

Backing up Datafiles Not Backed Up

Suppose you have just added several datafiles to your database and you want to ensure that you have a backup of them. You can issue the following command to instruct Oracle to back up datafiles that have not been backed up yet:

You can also specify a time range for files that haven't been backed up yet. Suppose you discover that your backups haven't been running for the last several days and you want to back up everything that hasn't been backed up within the last 24 hours. The following command backs up all datafiles that have not been backed up within the last day:

This command is also useful if your backups aborted for some reason, such as a power failure in the data center or your backup directory became full during backups. After you have resolved the issue that caused your backup job to fail, you can issue the previous command and RMAN will only back up the datafiles that haven't been backed up in the specified time period.

Skipping Read-Only Tablespaces

Since data in read-only tablespaces can't change while in read-only mode, you may only want to back up read-only tablespaces once and then skip them in subsequent backups. Use the SKIP READONLY command to achieve this:

Keep in mind that when you skip read-only tablespaces, you'll need to keep available a backup that contains these tablespaces. You don't want to find yourself in the situation where you haven't backed up a read-only tablespace for six months and then have a media failure.

Skipping Offline or Inaccessible Files

Sometimes datafiles become corrupt. You know that you don't need this datafile for anything so you take it offline, like so:

Now, suppose you attempt to run an RMAN backup:

The following error is thrown when RMAN encounters a datafile that it can't back up:

In this situation, you'll have to instruct RMAN to exclude the offline datafile from the backup. The SKIP OFFLINE command instructs RMAN to ignore datafiles with an offline status:

If a file has gone completely missing, use SKIP INACCESSIBLE to instruct RMAN to ignore files that are not available on disk. This might happen if the datafile was deleted using an operating system command. Here's an example of excluding inaccessible datafiles from the RMAN backup:

You can skip read-only, offline, and inaccessible datafiles with one command:

When dealing with offline and inaccessible files, you should figure out why the files are offline or inaccessible and try to resolve any issues.

Backing Up Large Files in Parallel

Normally, RMAN will only use one channel to back up a single datafile. When you enable parallelism, it allows RMAN to spawn multiple processes to back up multiple files. However, even when parallelism is enabled, RMAN will not use parallel channels simultaneously to back up one datafile, but starting with Oracle Database 11g, you can instruct RMAN to use multiple channels to back up one datafile in parallel. This is known as a multisection backup. This feature can speed up the backups of very large datafiles.

Use the SECTION SIZE parameter to make a multisection backup. The following example configures two parallel channels to back up one file:

When this code runs, RMAN will allocate two channels to back up datafile 10 in parallel.

Adding RMAN Backup Information to the Repository

Another scenario is when you need to populate the control file with information regarding RMAN backups. For example, say you had to recreate your control file, and now the control file no longer contains any information regarding RMAN. In this situation, use the CATALOG command to populate the control file with RMAN metadata. For example, if all of the RMAN backup files are kept in the /ora01/O11R2/rman directory, you can make the control file aware of these backups files in this directory like this:

This causes RMAN to look for any backup pieces, image copies, control file copies, or archive redo logs in the specified directory, and if found, populate the control file with the appropriate metadata. For this example, two backup piece files are found in the given directory:

If you enter YES, then metadata regarding the backup files will be added to the control file. In this way, the CATALOG command allows you to make the RMAN repository (control file and recovery catalog) aware of files that RMAN can work with for backup and recovery.

You can also instruct RMAN to catalog any files in the recovery area that the control file isn't currently aware of like this:

You can also catalog specific files. This example instructs RMAN to add metadata to the control file for a specific backup piece file:

Creating Incremental Backups

RMAN has three separate and distinct incremental backup features:

• Incremental level backups
• Incrementally updating backups
• Block change tracking

With incremental level backups, RMAN only backs up the blocks that have been modified since a previous backup. Incremental backups can be applied to the entire database, tablespaces, or datafiles. This is the most commonly used incremental feature with RMAN.

Incrementally updating backups is an entirely different feature from incremental level backups. These backups take image copies of the datafiles and then use incremental backups to update the image copies. This gives you an efficient way to implement and maintain image copies as part of your backup strategy. You only take the image copy backup once, and then use incremental backups to keep the image copies updated with the most recent transactions.

Block change tracking is another feature designed to speed up the performance of incremental backups. The idea here is that an operating system file is used to record which blocks have changed since the last backup. RMAN can use the block change tracking file to quickly identify which blocks need to be backed up when performing incremental backups. This feature can greatly improve the performance of incremental backups.

Taking Incremental Level Backups

RMAN implements incremental backups through levels. Starting with Oracle Database 10g, there are only two documented levels of incremental backups: level 0 and level 1. Prior versions of Oracle offer five levels, 0 through 4. These levels (0 through 4) are still available in Oracle Database 10g and 11g, but are not specified in the Oracle documentation. You must first take an incremental level 0 backup to establish a baseline, after which you can take a level 1 incremental backup.

Here's an example of taking an incremental level 0 backup:

Suppose for the next several backups you only want to back up the blocks that have changed since the last level 0 baseline backup. This line of code takes a level 1 backup:

There are two different types of incremental backups: differential and cumulative.  Which type of incremental (differential or cumulative) you use depends on your requirements. Differential backups (the default) are smaller but take more time to recover from. Cumulative backups are larger than differential backups but take less time to recover from.

A differential incremental level 1 backup instructs RMAN to back up blocks that have changed since the last level 1 or level 0 backups, whereas a cumulative incremental level 1 backup instructs RMAN to back up blocks that have changed since the last level 0 backup. Cumulative incremental backups, in effect, ignore any level 1 incremental backups.

When using incremental backups, I almost always use the default of differential. Usually I don't worry about the differences between incremental and cumulative backups. If you require cumulative backups, you must specify the key word CUMULATIVE. Here's an example of taking a cumulative level 1 backup:

Here are some other examples of taking incremental backups at more granular level than the database:

Making Incrementally Updating Backups

The basic idea behind an incrementally updating backup is to create image copies of datafiles and then use incremental backups to update the image copies. In this manner, you have image copies of your database that are kept somewhat current by applying an incremental backup to them. This can be an efficient way to combine image copy backups with incremental backups.

To understand how this backup technique works, you'll need to inspect the commands that perform an incrementally updating backup. You need two lines of RMAN code to enable this feature:

In the first line, a tag is specified (this example uses incupdate). You can use whatever you want for the tag name; the tag name lets RMAN associate the backup files being used each time the commands are run. Then, this code will do the following the first time you run the script:

• RECOVER COPY generates a message saying there's nothing for it to do.
• If no image copies exist, the BACKUP INCREMENTAL creates an image copy of the database datafiles.

You should see messages like this in the output when the RECOVER COPY command runs the first time:

In the output for the BACKUP INCREMENTAL, you should see text like this indicating that image copies are being created:

The second time you run the incrementally updating backup, it does as follows:

• RECOVER COPY still has nothing to do.
• BACKUP INCREMENTAL makes an incremental level 1 backup and assigns it the tag name specified; this backup will subsequently be used by the RECOVER COPY command.

The third time you run the incrementally updating backup, it does this:

• Now that an incremental backup has been created, the RECOVER COPY applies the incremental backup to the image copies.
• BACKUP INCREMENTAL makes an incremental level 1 backup and assigns it the tag name specified; this backup will subsequently be used by the RECOVER COPY command.

Going forward, each time that you run the two lines of code you will have a regularly repeating backup pattern. If you use image copies for backups, you might consider using an incrementally updating backup strategy because you avoid creating entire image copies each time the backup runs. The image copies are updated each time the backup runs with the incremental changes from the previous backup.

Using Block Change Tracking

Block change tracking is where a binary file is used to record changes to database datafile blocks. The idea is that incremental backup performance can be improved because RMAN can use the block change tracking file to pinpoint which blocks have changed since the last backup. This saves a great deal of time because otherwise RMAN would have to scan all blocks that have been backed up to determine if they've changed since the last backup. You can enable block change tracking by doing the following:

1. If not already enabled, set the DB_CREATE_FILE_DEST parameter to a location. For example:
   SQL> alter system set db_create_file_dest='/ora01/O11R2/bct' scope=both;
2. Enable block change tracking via the ALTER DATABASE command:
   SQL> alter database enable block change tracking;

This example creates a file with an OMF name in the directory specified by DB_CREATE_FILE_DEST. In this example, the file created is named as:

You can also enable block change tracking by directly specifying a file name, which does not require DB_CREATE_FILE_DEST to be set. For example:

You can verify the details of block change tracking by running the following query:

For space planning purposes, the size of the block change tracking file is approximately 1/30,000 the size of the total size of the blocks being tracked in the database. Therefore, the size of the block change tracking file is proportional to the size of the database and not the amount of redo generated.

To disable block change tracking, run the following command:

Checking for Corruption in Datafiles and Backups

You can use RMAN to check for corruption in datafiles, archive redo logs, and control files. You can also verify whether a backup set is restorable. The RMAN VALIDATE command is used to perform these types of integrity checks. There are three ways you can run the VALIDATE command:

• VALIDATE
• BACKUP...VALIDATE
• RESTORE...VALIDATE

Using VALIDATE

The VALIDATE command can be used alone (without BACKUP or RESTORE) to check for missing files or physical corruption in database datafiles, archive redo log files, control files, spfile, and backup set pieces. For example, this command will validate all datafiles and the control files:

You can also validate just the control file as follows:

You can validate the archive redo log files like so:

You can combine all of the prior three integrity checks into one command, as shown:

Under normal operations the VALIDATE command only checks for physical corruption. You can specify that you want to also check for logical corruption by using the CHECK LOGICAL clause like so:

You can use VALIDATE in a variety of ways. Here are a few more examples:

If RMAN detects any corrupt blocks, the V$DATABASE_BLOCK_CORRUPTION is populated. This view contains information on the file number, block number, and the number of blocks affected. You can use this information to perform a block level recovery. See Chapter 19 for details on block level recovery.

Using BACKUP...VALIDATE

The BACKUP VALIDATE command is very similar to the VALIDATE command in that it can check to see if datafiles are available and if the datafiles contain any corrupt blocks. For example:

This command doesn't actually perform any type of backup or create any files. It only scans the datafiles and checks for corruption. Like the VALIDATE command, BACKUP VALIDATE by default only checks for physical corruption. You can instruct it to also check for logical corruption as shown:

Here are some other variations of the BACKUP...VALIDATE command:

Like the VALIDATE command, BACKUP...VALIDATE will populate V$DATABASE_BLOCK_CORRUPTION if it detects any corrupt blocks. The information in this view can be used to determine which blocks can potentially be restored by block level recovery. See Chapter 19 for more details on block level recovery.

Using RESTORE...VALIDATE

The RESTORE...VALIDATE command is used to verify backup files that would be used to restore. This command validates backup sets, datafile copies, and archive redo log files:

No actual files are restored when using RESTORE...VALIDATE. This means you can run the command while the database is online and available.

Logging RMAN Output

When troubleshooting RMAN output or checking on the status of a backup job, it's essential to have a record of what RMAN ran and the status of each command. There are several methods for logging RMAN output. Some of them are built-in aspects of the Linux/Unix operating system and others are RMAN-specific features:

• Linux/Unix redirect output to file
• Linux/Unix logging commands
• RMAN LOG command
• V$RMAN_OUTPUT view

These logging features are discussed in the next sections.

Redirecting Output to a File

I run almost all RMAN backup jobs from shell scripts. The shell scripts are usually run automatically from a scheduling tool such as cron. When running RMAN commands in this fashion, I always capture the output by instructing the shell command to redirect standard output messaging and standard error messaging to a log file. This is done with the redirection > character. This example runs a shell script (rmanback.bsh) and redirects both standard output and standard error output to a log fie named rmanback.log:

Here, 1> instructs standard output to be redirected to the specified file. The 2>&1 instructs the shell script to send standard error output to the same location as standard output.

Capturing Output with Unix/Linux Logging Commands

You can instruct Unix/Linux to create a log file to capture any output that is also being displayed on your screen. This can be done in one of two ways:

• tee
• script

Capturing Output with tee

When you start RMAN, you can send the output you see on your screen to an operating system text file using the tee command:

Now you can connect to the target database and run commands. All of the output seen on your screen will be logged to the /tmp/rman.log file:

The tee party session stops writing to the log file when you exit from RMAN.

Capturing Output with the script Command

The script command is useful because it instructs the operating system to log any output that appears on the terminal to a log file. To capture all output, run the script command before connecting to RMAN:

To end a script session, type in Ctrl+D or type in exit. The /tmp/rman.log file will contain all output that was displayed on your screen. The script command is useful when you need to capture all output from one point in time to another point in time. For example, you may be running RMAN commands, exiting from RMAN, running SQL*Plus commands, and so on. The script session lasts from the point you start script to where you type in Ctrl+D.

Logging Output to a File

An easy way to capture RMAN output is to use the SPOOL LOG command to send the output to a file. This example spools a log file from within RMAN:

By default, the SPOOL LOG command will overwrite an existing file. If you want to append to the log file, use the keyword APPEND:

You can also direct output to a log file when starting RMAN on the command line, which will overwrite an existing file:

You can also append to the log file as shown:

When you use SPOOL LOG as shown in the previous examples, the output goes to a file and not to your terminal. Therefore, I hardly ever use SPOOL LOG when running RMAN interactively. It's mainly a tool for capturing output when running RMAN from scripts.

Querying for Output in the Data Dictionary

If you don't capture any RMAN output, you can still view the most recent RMAN output by querying the data dictionary. The V$RMAN_OUTPUT view contains messages recently reported by RMAN:

Here is some sample output:

The V$RMAN_OUTPUT view is an in-memory object that holds up to 32,768 rows. Information in this view is cleared out when you stop and restart your database. This view is handy when you're using the RMAN SPOOL LOG command to spool output to a file and cannot view what is happening on your terminal.

RMAN Reporting

There are several different methods for reporting on the RMAN environment:

• LIST command
• REPORT command
• Query metadata via data dictionary views

When first learning RMAN, the difference between the LIST and REPORT commands may seem confusing because the distinction between the two commands is not clear cut. In general, I use the LIST command to view information about existing backups, whereas the REPORT command is used to determine what files need to be backed or to display information on obsolete or expired backups.

I use SQL queries for specialized reports (not available via the LIST or REPORT) or for automating reports. For example, I'll generally implement an automated check via a shell script and SQL that reports whether the RMAN backups have run within the last day.

Using LIST

When investigating issues with RMAN backups, one of the first tasks I usually do is connect to the target database and run the LIST BACKUP command. This command shows backup sets, backup pieces, and the files included in the backup:

It shows all RMAN backups recorded in the repository. You may want to spool that to an output file so that you can save the output and then use an operating system editor to search through and look for specific strings in the output.

To get a summarized view of backup information, use the LIST BACKUP SUMARY command:

You can also use the LIST command to report just image copy information:

The next command reports on archive redo log backups:

There are a great number of ways in which you can run the LIST command (and likewise the REPORT command, covered in the next section). The prior methods listed are the ones you'll run most of the time. For a complete list of options available see the Oracle Database Backup and Recovery Reference guide (available on Oracle's OTN website).

Using REPORT

The RMAN REPORT command is useful for reporting on a variety of details. You can quickly view all of the datafiles associated with a database via the following:

The REPORT command provides detailed information about backups marked as obsolete via the RMAN retention policy. For example:

You can report on datafiles that need to be backed up as defined by the retention policy like so:

There are several ways to report on datafiles that need to be backed up. Here are some other varieties:

Another way to use the REPORT command is for datafiles that have never been backed up or may contain data created from a NOLOGGING operation. For example, say you have direct path loaded data into a table, and the datafile in which the table resides has not been backed up. The following command will detect these situations:

Using SQL

There are a number of data dictionary views available for querying about backup information. Table 18–1 describes RMAN-related data dictionary views. These views are available regardless of your use of a recovery catalog (the information in these views is derived from the control file).

Table 18–1. Description of RMAN Backup Data Dictionary Views

View Name	Information Regarding
V$RMAN_BACKUP_JOB_DETAILS	RMAN backup jobs.
V$BACKUP	Backup status of online datafiles.
V$BACKUP_ARCHIVELOG_DETAILS	Archive logs backed up.
V$BACKUP_CONTROLFILE_DETAILS	Control files backed up.
V$BACKUP_COPY_DETAILS	Control file and datafile copies.
V$BACKUP_DATAFILE	Control files and datafiles backups.
V$BACKUP_DATAFILE_DETAILS	Datafiles backed up in backup sets, image copies, and proxy copies.
V$BACKUP_FILES	Datafiles, control files, spfiles, and archive redo logs backed up.
V$BACKUP_PIECE	Backup piece files.
V$BACKUP_PIECE_DETAILS	Backup piece detailed information.
V$BACKUP_SET	Backup sets.
V$BACKUP_SET_DETAILS	Backup set detailed information.

Sometimes DBAs new to RMAN have a hard time grasping the concept of backups, backup sets, backup pieces, datafiles, and how those concepts relate. I find the following query useful when discussing RMAN backup components. This query will display backup sets, the backup pieces with the set, and the datafiles that are backed up within the backup piece:

The output here has been shortened to fit on the page:

Sometimes it's useful to report on the performance of RMAN backups. The following query reports on the time taken for an RMAN backup per session.

Here is some sample output:

The contents of V$RMAN_BACKUP_JOB_DETAILS are summarized by a session connection to RMAN. Therefore, the report output is more accurate if you connect to RMAN (establishing a session) and then exit out of RMAN after the backup job is complete. If you remain connected to RMAN while running multiple backup jobs, the query output reports on all backup activity while connected (for that session).

You should have an automated method of detecting whether or not RMAN backups are running and if datafiles are being backed up. One reliable method of automating such a task is to embed SQL into a shell script and then run the script on a periodic basis from a scheduling utility such as cron.

I typically run two basic types of checks regarding the RMAN backups:

• Have the RMAN backups run recently?
• Are there any datafiles that have not been backed up recently?

The following shell script checks for the conditions listed above. You'll need to modify the script and provide it with a username and password for a user that can query the data dictionary objects referenced in the script (in this script the username/password is darl/foobar). When running the script, you'll need to pass in two variables: the Oracle SID and the threshold number of days that you want to check in the past for the last time the backups ran or when a datafile was backed up.

For example, to check if backups have been running successfully within the past two days, run the script (named rman_chk.bsh) like this:

The prior script is basic but effective. You can enhance and add to it as required for your RMAN environment.

Summary

RMAN provides many flexible and feature rich options for backups. By default, RMAN only takes backups of blocks that have been modified in the database. The incremental features allow you to also backup only blocks that have been modified since the last backup. These incremental features are particularly useful in reducing the size of backups in large database environments where you only have a small percentage of data in the database that changes from one backup to the next.

You can instruct RMAN to back up every block in each datafile via an image copy. An image copy is a block-for-block identical copy of the datafile. Image copies have the advantage of being able to restore the backup files directly from the backup (without using RMAN). You can use the incrementally updated backup feature to implement an efficient hybrid of image copy backups and incremental backups.

RMAN contains built-in commands for reporting on many aspects of backups. The LIST command reports on backup activity. The REPORT command is useful for determining which files need to be backed up as dictated by the retention policy.

After you've successfully configured RMAN and created backups, you are in a position to be able to restore and recover your database in the event of a media failure. Restore and recovery topics are detailed in the next chapter.