RMAN Reporting: using LIST, REPORT and SQL

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 is not clear-cut. In general, I use the LIST command to view information about existing backups and the REPORT command to determine which files need to be backed or to display information on obsolete or expired backups.

I use SQL queries for specialized reports (not available via 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, usually one of the first tasks I undertake is connecting to the target database and running the LIST BACKUP command. This command allows you to view backup sets, backup pieces, and the files included in the backup:

RMAN> list backup;

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

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

RMAN> list backup summary;

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

RMAN> list copy;

To list all files that have been backed up, and the associated backup set, issue the following command:

RMAN> list backup by file;

These commands display archive redo logs on disk:

RMAN> list archivelog all;

RMAN> list copy of archivelog all;

And, this command lists the backups of the archive redo logs (and which archive redo logs are contained in which backup pieces):

RMAN> list backup of archivelog all;

There are a great many ways in which you can run the LIST command (and, likewise, the REPORT command, covered in the next section). The prior methods are the ones you’ll run most of the time. See the Oracle Database Backup and Recovery Reference Guide, available from the Technology Network area of the Oracle web site (http://otn.oracle.com), for a complete list of options.

Using REPORT

The RMAN REPORT command is useful for reporting on a variety of details. You can quickly view all the data files associated with a database, as follows:

RMAN> report schema;

The REPORT command provides detailed information about backups marked obsolete via the RMAN retention policy; for example,

RMAN> report obsolete;

You can report on data files that need to be backed up, as defined by the retention policy, like this:

RMAN> report need backup;

There are several ways to report on data files that need to be backed up. Here are some other examples:

RMAN> report need backup redundancy 2;

RMAN> report need backup redundancy 2 datafile 2;

The REPORT command may also be used for data files that have never been backed up or that may contain data created from a NOLOGGING operation. For example, say you have direct-path loaded data into a table, and the data file in which the table resides has not been backed up. The following command will detect these conditions:

RMAN> report unrecoverable;

Using SQL

There are a number of data dictionary views available for querying about backup information. Table 5-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).

Sometimes, DBAs new to RMAN have a hard time grasping the concept of backups, backup sets, backup pieces, and data files and how they 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 data files that are backed up within the backup pieces:

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:

S Key COMPLETIO BP Name                                  Datafile

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

    11 28-SEP-14 /u01/O12C/rman/O12C_0cpjkl6h_1_1.bk      /u01/dbfile/O12C/inv_mgmt_data01.dbf

                                                          /u01/dbfile/O12C/reg_index01.dbf

                                                          /u01/dbfile/O12C/tools01.dbf

                                                          /u01/dbfile/O12C/undotbs01.dbf

                                                          /u01/dbfile/O12C/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;

Here is some sample output:

SESSION_RECID COMPRESSION_RATIO TIME_TAKEN_DISPLAY      HOURS END_TIME

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

            3        2.03556203 00:00:18                  .00 28-sep-14 11:03

            6        7.81358269 00:00:25                  .01 28-sep-14 11:06

           14        10.7638918 00:00:19                  .01 28-sep-14 11:07

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 data files 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 data files that have not been backed up recently?

The following shell script checks for these conditions. 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 and also change the e-mail address of where messages are sent. When running the script, you’ll need to pass in two variables: the Oracle SID and the threshold number of past days that you want to check for the last time the backups ran or for when a data file was backed up.

#!/bin/bash

#

if [ $# -ne 2 ]; then

  echo "Usage: $0 SID threshold"

  exit 1

fi

# source oracle OS variables

export ORACLE_SID=O12C

export ORACLE_HOME=/orahome/app/oracle/product/12.1.0.1/db_1

export PATH=$PATH:$ORACLE_HOME/bin

crit_var=$(sqlplus -s <<EOF

/ as sysdba

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)

#

if [ $crit_var -ne 0 ]; then

  echo "rman backups not running on $1" | mailx -s "rman problem" This email address is being protected from spambots. You need JavaScript enabled to view it.

else

  echo "rman backups ran ok"

fi

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

crit_var2=$(sqlplus -s <<EOF

/ as sysdba

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)

#

if [ $crit_var2 -ne 0 ]; then

  echo "datafile not backed up on $1" | mailx -s "backup problem" This email address is being protected from spambots. You need JavaScript enabled to view it.

else

  echo "datafiles are backed up..."

fi

#

exit 0

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

$ rman_chk.bsh O12c 2

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

Вас заинтересует / Intresting for you:

RMAN: Specifying the Backup Us... 2482 views Андрей Волков Sat, 29 Feb 2020, 10:14:03

RMAN: Using Online or Offline ... 1638 views Андрей Волков Sat, 29 Feb 2020, 10:01:33

RMAN: Checking for Corruption ... 26951 views Андрей Волков Thu, 30 Sep 2021, 11:57:27

How to use RMAN for Stop/Start... 1925 views Андрей Волков Mon, 31 Jan 2022, 17:35:05