Oracle RMAN 11g Backup and Recovery (146 page)

438
Part III: Using RMAN Effectively

1795 A 08-SEP-09 682922 08-SEP-09

Name:

/oracle/app/oracle/flash recovery area/ROB1/controlfile

/o1 mf TAG20090908T213230 5bg8gh41 .ctl

Tag: TAG20090908T213230

The RMAN report Command

The RMAN
report
command is used to determine the current recoverable state of your database and to provide certain information on database backups. In this section, we look at reports that tell you which datafiles have not been backed up in a specified period. We also look at reports that tell you when specific tablespaces need to be backed up because of UNRECOVERABLE

operations on datafiles. Finally, we look at the use of the
report
command to report on database schemas and obsolete database backups.

Reporting on Datafiles That Have Not Been

Backed Up Recently

A question DBAs frequently ask themselves is, “When was the last time I backed up this tablespace?” RMAN provides some answers to that question with the
report need backup
command. For example, if you want to know what tablespaces have not been backed up in the last three days, you could issue the
report need backup days=3
command and find out. Here is an example of the output of just such a report:

RMAN> report need backup days 3;

Report of files whose recovery needs more than 3 days of archived logs

File Days Name

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

4 2 D:\ORACLE\ORADATA\RECOVER\TOOLS01.DBF

5 2 D:\ORACLE\ORADATA\RECOVER\USERS01.DBF

From this report, it appears that two datafiles require application of more than three days’

worth of archived redo to be able to recover them (which implies that these datafiles have not been backed up in the last three days). In this event, we might well want to back up the datafiles or their associated tablespaces.

We can also generate reports based on a given number of incrementals that would need to be applied, as shown in this example:

RMAN> report need backup incremental 3;

Report of files that need more than 3 incrementals during recovery

File Incrementals Name

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

1 4 D:\ORACLE\ORADATA\RECOVER\SYSTEM01.DBF

2 4 D:\ORACLE\ORADATA\RECOVER\RECOVER UNDOTBS 01.DBF

3 4 D:\ORACLE\ORADATA\RECOVER\INDX01.DBF

4 4 D:\ORACLE\ORADATA\RECOVER\TOOLS01.DBF

5 4 D:\ORACLE\ORADATA\RECOVER\USERS01.DBF

Chapter 17: Monitoring and Reporting on RMAN
439

In this example, several database datafiles require four incrementals to be applied. This may well indicate that we need to perform a new backup on these datafiles at a higher incremental level, or even perform a new incremental base backup.

Reporting on Backup Redundancy or Recovery Window

We can use the
report need backup redundancy
command to determine which, if any, datafiles need to be backed up to meet our established backup redundancy policy. The following is an example of the use of this report. In this case, we want a list of all datafiles that do not have at least two different backups that can be used for recovery. These may be backup set backups or datafile copies.

RMAN> report need backup redundancy 2;

Report of files with less than 2 redundant backups

File #bkps Name

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

1 1 D:\ORACLE\ORADATA\RECOVER\SYSTEM01.DBF

4 1 D:\ORACLE\ORADATA\RECOVER\TOOLS01.DBF

5 1 D:\ORACLE\ORADATA\RECOVER\USERS01.DBF

Likewise, we can establish a minimum recovery window for our backups and report on any datafiles whose backups are older than that recovery window. This is done with the
report need
backup recovery window days
command:

RMAN> report need backup recovery window of 2 days;

Report of files whose recovery needs more than 2 days of archived logs

File Days Name

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

1 4 D:\ORACLE\ORADATA\RECOVER\SYSTEM01.DBF

2 4 D:\ORACLE\ORADATA\RECOVER\RECOVER UNDOTBS 01.DBF

3 4 D:\ORACLE\ORADATA\RECOVER\INDX01.DBF

4 4 D:\ORACLE\ORADATA\RECOVER\TOOLS01.DBF

5 4 D:\ORACLE\ORADATA\RECOVER\USERS01.DBF

In this case, several of our datafiles require application of more than two days’ worth of archived redo. So, if our recovery policy says we want backups where we only need to apply one day of redo, then we need to back up these datafiles.

Reporting on Unrecoverable Operations on Datafiles

Unrecoverable operations on objects within tablespaces, and the datafiles that make up those tablespaces, lead to certain recoverability issues. For example, if a table is created using the
Unrecoverable
option and is subsequently loaded using the direct load path, then the tablespace needs to be backed up, or else the data that was loaded will not be recoverable. It is for these circumstances that the
report unrecoverable
command is used, as shown here: RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

440
Part III: Using RMAN Effectively

File Type of Backup Required Name

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

5 full or incremental D:\ORACLE\ORADATA\RECOVER\USERS01.DBF

Reporting on the Database Schema

We are using the word “schema” here to mean the physical structure of the database. The schema includes the datafile name and number, the tablespaces they are assigned to, the size of the datafiles, and whether the datafiles contain rollback segments. This can be the current schema, or you can generate a report on the database schema at some past point in time. Here is an example of the execution of the
report schema
command:

RMAN> report schema;

Report of database schema for database with db unique name ROB1

List of Permanent Datafiles

File Size(MB) Tablespace RB segs Datafile Name

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

1 700 SYSTEM YES /ora01/oracle/rob1/rob1/system01.dbf 2 600 SYSAUX NO /ora01/oracle/rob1/rob1/sysaux01.dbf 3 280 UNDOTBS1 YES /ora01/oracle/rob1/rob1/undotbs01.dbf 4 8 USERS NO /ora01/oracle/rob1/rob1/users01.dbf
Reporting on Obsolete Backups

Backups are marked with an OBSOLETE status if you are using a retention policy (which we discussed in Chapter 14). Here is an example of the execution of
report obsolete
with a retention policy set to
redundancy 1
:

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

Archive Log 54 08 SEP 09

/oracle/app/oracle/flash recovery area/ROB1/archivelog/2009 09 08

/o1 mf 1 9 5bd8qv45 .arc

Backup Set 712 08 SEP 09

Backup Piece 714 08 SEP 09

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf annnn TAG20090908T202600 5bg4kr90 .bkp

Backup Set 774 08 SEP 09

Backup Piece 776 08 SEP 09

/oracle/app/oracle/flash recovery area/ROB1/autobackup/2009 09 08

/o1 mf s 697062444 5bg4ndym .bkp

Backup Set 928 08 SEP 09

Backup Piece 930 08 SEP 09

/oracle/app/oracle/flash recovery area/ROB1/autobackup/2009 09 08

/o1 mf s 697062563 5bg4r47m .bkp

Backup Set 1062 08 SEP 09

Backup Piece 1064 08 SEP 09

/oracle/app/oracle/flash recovery area/ROB1/autobackup/2009 09 08