Oracle RMAN 11g Backup and Recovery (40 page)

■ V$RECOVERY_FILE_DEST

■ V$FLASH_RECOVERY_AREA_USAGE

Also, columns are available in several other views that help you to manage the FRA. Let’s look at each of these views and columns in more detail.

The DBA_OUTSTANDING_ALERTS View
As files are added or removed from the FRA, records of these events are logged in the database alert log. You can check the new DBA view, DBA_OUTSTANDING_ALERTS, for information on outstanding issues with the FRA. Note that there is somewhat of a lag between the time a space-related issue occurs and when the warning appears in the DBA_OUTSTANDING_ALERTS view.

The following is an example where the FRA has run out of space and is posting an alert to the DBA_OUTSTANDING_ALERTS view. You would need to deal with this situation quickly or risk

68
Part II: Setup Principles and Practices

the database coming to a complete halt. In this case, we used the
alter system
command to increase the amount of space allocated to the FRA.

SQL> select reason from dba outstanding alerts;

REASON

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

db recovery file dest size of 524288000 bytes is 100.00% used

and has 0 remaining bytes available.

SQL> alter system set db recovery file dest size 800m;

The V$RECOVERY_FILE_DEST View
The V$RECOVERY_FILE_DEST view provides an overview of the FRA that is defined in your database. It provides the size that the FRA is configured for, the amount of space used, how much space can be reclaimed, and the number of files in the FRA. In the following example, we can see that the increase in space to the FRA to 800MB has been recorded (SPACE_LIMIT). However, we still have used too much space (SPACE_

USED), and the FRA is still full.

SQL> select * from v$recovery file dest;

NAME

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

SPACE LIMIT SPACE USED SPACE RECLAIMABLE NUMBER OF FILES

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

c:\oracle\product\10.2.0\flash recovery area

838,860,800 1,057,116,672 338,081,280 11

One nice thing about Oracle is that it manages the FRA space for us as much as it can, and if there is reclaimable space available, it will free it as required. Note that in the previous query, Oracle indicated we were out of FRA space. Did you notice the SPACE_RECLAIMABLE column, though? This column indicates that there is reclaimable space available. This is space that is taken up by archived redo logs or backup set pieces that are no longer needed by virtue of whatever retention criteria we have selected (we will discuss retention criteria and setting those criteria later in this chapter). When Oracle needs space in the FRA (say, for example, we force a log switch), it will remove any files that are reclaimable and free up space. In the next query, we can see that this has occurred. After we ran the previous query that indicated we were out of FRA space, we forced a log switch. This caused Oracle to reclaim space from the FRA for reuse, and it then was able to write out the archived redo log. We can query the V$RECOVERY_FILE_DEST

view and see that this has indeed occurred:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$recovery file dest;

NAME

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

SPACE LIMIT SPACE USED SPACE RECLAIMABLE NUMBER OF FILES

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

c:\oracle\product\10.2.0\flash recovery area

838,860,800 719,412,736 64,000 7

Chapter 3: RMAN Setup and Configuration
69

The V$FLASH_RECOVERY_AREA_USAGE View
The V$FLASH_RECOVERY_AREA_USAGE

view provides more detailed information on which types of files are occupying space in the FRA.

This view groups the file types and then provides the percentage of space that is used by each file type, the percentage of the total FRA reclaimable space that comes from that group, and the number of files in the FRA that come from that group. Here is a query of the V$FLASH_RECOVERY_AREA_

USAGE view:

SQL> SELECT * FROM V$FLASH RECOVERY AREA USAGE;

FILE TYPE PERCENT SPACE USED PERCENT SPACE RECLAIMABLE NUMBER OF FILES

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

CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG 17.14 17.09 7

BACKUPPIECE 108.88 23.22 4

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

In this example, we notice a few things:

■ We are over our defined space allocation (the PERCENT_SPACE_USED of all the rows exceeds 100 percent). This is probably because the size of the FRA was recently changed and Oracle has not yet reclaimed enough space to bring the total used below 100 percent.

■ The backup set pieces are consuming most of that space, and 23.22 percent of that space is reclaimable.

■ The archived redo logs consume only 17 percent of the space allocated to the FRA, and even if we were to remove all of the archived redo logs, we would not free up enough space to bring the FRA under the amount of space allocated to it.

Other Views with FRA Columns
The column IS_RECOVERY_DEST_FILE can be found in a number of Oracle Database V$ views such as V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_

LOG, V$DATAFILE_COPY, V$DATAFILE, and V$BACKUP_PIECE. This column is a Boolean that indicates whether the file is in the FRA.

Another column, BYTES, can be found in the V$BACKUP_PIECE and RC_BACKUP_PIECE (an RMAN recovery catalog view) views. This column indicates the size of the backup set piece in bytes.

NOTE

Manually removing fixed files from the FRA can have unexpected

consequences. Oracle does not immediately detect the removal

of these files, and thus the space is not reclaimed. If you end up

manually removing files (or lose a disk perhaps), use the RMAN

crosscheck
command along with the
delete
command to cause
Oracle to update the current control file information on the FRA.

The folks at Oracle recommend that you not manually remove files

managed by Oracle if at all possible.

70
Part II: Setup Principles and Practices

Other Flash Recovery Area Features

The
alter database add logfile
and
alter database add standby logfile
commands create an online redo log member in the FRA if the OMF-related DB_CREATE_ONLINE_LOG_DEST_
n
parameter is not set. The
alter database drop logfile
and
alter database rename file
commands also support files in the FRA. The nice thing about using these OMF-related features is that Oracle will manage the physical files for you. Thus, if you drop an online redo log group, and the physical files of that group were created by Oracle based on the setting of DB_CREATE_ONLINE_LOG_DEST_
n,
then Oracle will remove those physical files for you.

During database creation, Oracle can use the FRA to store the database control file and online redo logs. If the OMF-related parameter DB_CREATE_ONLINE_LOG_DEST_
n
is defined, then the control file and redo logs will be created in those locations, but will not be created in the FRA, even if the FRA is defined. If DB_CREATE_ONLINE_LOG_DEST_
n
is not defined, but CREATE_

FILE_DEST is defined, then the control file and online redo logs will be created in the location defined by CREATE_FILE_DEST. If DB_RECOVERY_FILE_DEST is also defined, then a copy of the control file and online redo logs will get created there as well. The result is a multiplexed online redo log. Finally, if only DB_RECOVERY_FILE_DEST is defined, then the control file will get created in that location. If none of these parameters is defined, then the control file and online redo logs will be created to a default location, which is OS specific.