Pro Oracle Database 11g Administration (84 page)

RMAN> set echo on;

RMAN>

RMAN> spool log off;

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

RMAN> spool log to '/tmp/rmanout.log' append

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

$ rman target / log /tmp/rmanout.log

You can also append to the log file as shown:

$ rman target / log /tmp/rmanout.log append

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: select

sid

,recid

,output

from v$rman_output

order by recid;

Here is some sample output:

173 1108 Starting backup at 13-SEP-10

173 1109 using channel ORA_DISK_1

173 1110 using channel ORA_DISK_2

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.

503

p

CHAPTER 18 ■ RMAN BACKUPS AND REPORTING

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:

RMAN> list 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: RMAN> list backup summary;

You can also use the LIST command to report just image copy information: RMAN> list copy;

The next command reports on archive redo log backups:

RMAN> list archivelog all;

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:

RMAN> report schema;

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

504

CHAPTER 18 ■ RMAN BACKUPS AND REPORTING

RMAN> report obsolete;

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

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

RMAN> report need backup redundancy 2;

RMAN> report need backup redundancy 2 datafile 2;

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:

RMAN> report unrecoverable;

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.

505

CHAPTER 18 ■ RMAN BACKUPS AND REPORTING

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:

SET LINES 132 PAGESIZE 100

BREAK ON REPORT ON bs_key ON completion_time ON bp_name ON file_name

COL bs_key FORM 99999 HEAD "BS Key"

COL bp_name FORM a40 HEAD "BP Name"

COL file_name FORM a40 HEAD "Datafile"

--

SELECT

s.recid bs_key

,TRUNC(s.completion_time) completion_time

,p.handle bp_name

,f.name file_name

FROM v$backup_set s

,v$backup_piece p

,v$backup_datafile d

,v$datafile f

WHERE p.set_stamp = s.set_stamp

AND p.set_count = s.set_count

AND d.set_stamp = s.set_stamp

AND d.set_count = s.set_count

AND d.file# = f.file#

ORDER BY

s.recid

,p.handle

,f.name;

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

BS Key COMPLET BP Name Datafile

------ --------- ---------------------------- ---------------------------------

414 14-SEP-10 /ora01/O11R2/rman/r1j_1_1.bk /ora01/dbfile/O11R2/mvindex01.dbf

/ora01/dbfile/O11R2/sysaux01.dbf

/ora01/dbfile/O11R2/system01.dbf

/ora02/dbfile/O11R2/users01.dbf

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.

COL hours FORM 9999.99

COL time_taken_display FORM a20

SET LINESIZE 132

--

SELECT

session_recid

,compression_ratio

,time_taken_display

,(end_time - start_time) * 24 as hours

,TO_CHAR(end_time,'dd-mon-yy hh24:mi') as end_time

FROM v$rman_backup_job_details

ORDER BY end_time;

506

CHAPTER 18 ■ RMAN BACKUPS AND REPORTING

Here is some sample output:

SESSION_RECID COMPRESSION_RATIO TIME_TAKEN_DISPLAY HOURS END_TIME,

------------- ----------------- -------------------- ------ -----------------

7509 4.55050595 03:20:48 3.35 04-sep-10 19:23

7515 4.51185084 03:23:04 3.38 05-sep-10 19:25

7521 4.43947443 03:31:48 3.53 06-sep-10 19:34

7527 4.35619748 03:45:03 3.75 07-sep-10 19:47

7533 4.2773889 04:20:04 4.33 08-sep-10 20:23

7547 4.51488 03:22:21 3.37 09-sep-10 19:24

7554 4.49303627 03:28:06 3.47 10-sep-10 19:31

7561 4.51925905 03:17:00 3.28 11-sep-10 19:19

7568 4.54671383 03:14:03 3.23 12-sep-10 19:16

7575 4.52995677 03:17:07 3.29 13-sep-10 19:19

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.

#!/bin/bash

#

echo "Usage: $0 SID threshold"

exit 1

fi

# source oracle OS variables

. /var/opt/oracle/oraset $1

crit_var=$(sqlplus -s <

darl/foobar

SET HEAD OFF FEEDBACK OFF

SELECT COUNT(*) FROM

(SELECT (sysdate - MAX(end_time)) delta

FROM v\$rman_backup_job_details) a

WHERE a.delta > $2;

EOF)

#

echo "rman backups not running on $1" | mailx -s "rman problem" [email protected] else

echo "rman backups ran ok"

507

CHAPTER 18 ■ RMAN BACKUPS AND REPORTING

fi

#--------------------------------------------

crit_var2=$(sqlplus -s <

darl/foobar

SET HEAD OFF FEEDBACK OFF

SELECT COUNT(*)

FROM

(

SELECT name

FROM v\$datafile

MINUS

SELECT DISTINCT

f.name

FROM v\$backup_datafile d

,v\$datafile f

WHERE d.file# = f.file#

AND d.completion_time > sysdate - $2);

EOF)

#

echo "datafile not backed up on $1" | mailx -s "rman problem" [email protected] else

echo "datafiles are backed up..."

fi

#

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

$ rman_chk.bsh INVPRD 2

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.

508