2
Configuring Database Recoverability
CERTIFICATION OBJECTIVES
2.01 Configuring Database Recoverability
2.02 Configure multiple archive log file destinations to increase availability
2.03 Define, apply, and use a retention policy
2.04 Configure the Flash Recovery Area
2.05 Use the Flash Recovery Area
Oracle provides a variety of backup procedures and options that help protect an Oracle database. If they are properly implemented, these options will allow you to back up your databases and recover them easily and efficiently. As of Oracle Database 10g, you can use Recovery Manager (RMAN) to execute nearly all of your backup and recovery operations. In situations for which you cannot use RMAN for recovery, such as for a database that is not in ARCHIVELOG mode, user-managed backup and recovery is discussed in Chapter 6.
The first part of this chapter gives you a refresher on both types of backups available to you: logical and physical. A logical backup backs up individual objects, such as a table or a schema; a physical backup backs up objects with a granularity at the file system level, such as the datafiles that make up a tablespace. A physical backup of a database can occur while the database is either online or offline. You must use RMAN for all online backups except for backing up tablespaces while they are in backup mode (ALTER TABLESPACE BEGIN BACKUP).
This chapter provides a warm-up to RMAN; however, before you can use RMAN to back up or recover a database, you must perform a number of steps: First, your database must be in ARCHIVELOG mode. Then you must ensure that the retention policy and the number of archived log file destinations are appropriate for your environment.
This chapter provides a brief overview of the RMAN commands you’ll use on a regular basis. RMAN catalog maintenance, creating RMAN backups, and using RMAN backups for recovery are covered in Chapters 3, 4, and 5, respectively. After covering user-managed backup and recovery in Chapter 6, a discussion of miscellaneous RMAN features occurs in Chapter 7 and RMAN tuning methodologies are covered in Chapter 8.
To wrap up this chapter, you’ll learn how to configure and use the flash recovery area both to simplify the location of database backups and automate the disk space allocated for recovery files.
CERTIFICATION OBJECTIVE 2.01
Configuring for Database Recoverability
Oracle offers logical and physical backup capabilities. RMAN can perform both full or incremental logical and physical backups. You should understand the implications and uses of both physical and logical backups to develop the most appropriate solution for your applications.
A robust backup strategy includes both types of backups. In general, production databases rely on physical backups as their primary backup method, while logical backups serve as the secondary method. For development databases and for some small data movement processing, logical backups offer a viable solution.
Your backup and recovery plan should include, at a minimum, these tasks:
Configuration Define backup destinations, encryption, and retention policies.
Scheduling Automate backups to minimize human error and manual intervention.
Testing Perform routine backup and recovery options to ensure that your backup plan works before you experience a real disaster.
Monitoring Ensure that you minimize the resources used to perform a sufficient backup and minimize the impact to other database users.
Restoration Restore your database files in the file system (datafiles, archive logs, and so on), from a previous backup.
Recovery Perform a recovery of the database to the point of failure of the database by applying archived redo log entries to restored database files and rolling forward changes to your restored backup. Know how to recover your database.
Backup and recovery tools such as RMAN can be used in nonemergency situations as well: For example, you can take a snapshot of an entire database at one location and restore it to another location for developers or for testing.
The following sections present information on logical and physical backups, distinguishing between online and offline backups, and focusing on online backups for the rest of this chapter and in the rest of this book. Finally, you’ll get a brief overview of RMAN commands. Details of using RMAN for your backup and recovery environment are covered in Chapters 3, 4, 5, 7, and 8.
Logical Backups
A logical backup of a database involves reading a set of database rows and writing them to a file. These records are read independently of their physical location. In Oracle, the Data Pump Export utility performs this type of database backup. To recover using the file generated from a Data Pump Export, you use Data Pump Import.
Oracle’s Import and Export utilities, available prior to Oracle Database 10g, are still provided as part of the Oracle 11g installation but are no longer supported. Users of the old Export and Import utilities are encouraged to use Data Pump Export and Data Pump Import instead.
Oracle’s Data Pump Export utility queries the database, including the data dictionary, and writes the output to an XML file called an export dump file. You can export the full database, specific users or tablespaces, or specific tables. During exports, you can choose whether or not to export the data dictionary information associated with tables, such as grants, indexes, and constraints. The file written by Data Pump Export will contain the commands necessary to completely re-create all the chosen objects and data.
Once data has been exported via Data Pump Export, it can be imported via the Data Pump Import utility. Data Pump Import reads the dump file created by Data Pump Export and executes the commands found there. For example, these commands may include a CREATE TABLE command, followed by an INSERT command to load data into the table.
Data Pump Export and Import can use a network connection for a simultaneous export and import operation, avoiding the use of intermediate operating system files and reducing total export and import time.
The data that has been exported does not have to be imported into the same database, or the same schema, that was used to generate the export dump file. You can use the export dump file to create a duplicate set of the exported objects under a different schema or into a separate database.
You can import either all or part of the exported data. If you import the entire export dump file from a full export, all the database objects, including tablespaces, datafiles, and users, will be created during the import. However, it is often useful to precreate tablespaces and users in order to specify the physical distribution of objects in another database. This is one method of changing the physical structure of a database.
If you want to import part of the data from the export dump file, the tablespaces, datafiles, and users that will own and store that data should be set up prior to the import.
Physical Backups
Physical backups involve copying the files that constitute the database. These backups are also referred to as file system backups because they involve using operating system file backup commands. Oracle supports two different types of physical file backups: offline backups and online backups (also known as cold and hot backups, respectively). You can use RMAN (covered in this chapter and in Chapters 3, 4, 5, 7, and 8) to perform all physical backups. You can optionally choose to write your own scripts to perform physical backups, but doing so will prevent you from obtaining many of the benefits of the RMAN approach.
Offline Backups
Consistent offline backups occur when the database has been shut down normally (that is, not due to instance failure) using the NORMAL, IMMEDIATE, or TRANSACTIONAL option of the SHUTDOWN command. While the database is offline, the following files should be backed up:
All datafiles
All controlfiles
All archived redo log files
The init.ora file or server parameter file (SPFILE)
Text-format files such as the password file and tnsnames.ora
You should never, ever want or need to back up online redo log files. While a slight time savings results from restoring from a cold backup after a clean shutdown, the risk of losing committed transactions outweighs the convenience. Your online redo logs should be mirrored and duplexed so that you more or less eliminate the chances of losing the current online log file.
Having all these files backed up while the database is closed provides a fixed or consistent image or snapshot of the database, as it existed at the time it was closed. The full set of these files could be restored from the backups on disk or tape at a later date, and the database would be able to function simply by restarting the database. It is not valid to perform a file system backup of the database while it is open or unless an online backup of tablespace datafiles is being performed with those tablespaces set into backup mode.
A tablespace placed into backup mode switches the tablespace datafiles offline for the purposes of reading and writing data. Any changes made to the tablespace while in backup mode will be wholly written to log files. This gives you datafiles that are static, that can be copied to the file system, and can be used later for recovery and log entry restoration. When a tablespace is switched out of backup mode, all temporary changes made in log files will be applied to (recovered into) the datafile placed online.
Offline backups that occur following database aborts will also be considered inconsistent and can require more effort to use during recoveries—if the backups are usable. A database restarted after a crash needs the online redo log files for crash recovery, but since you do not back up online redo log files, data loss after restoring an inconsistent offline backup is virtually certain.
Online Backups
You can use online backups for any database that is running in ARCHIVELOG mode. In this mode, the online redo logs are archived, creating a log of all transactions within the database.
Oracle writes to the online redo log files in a cyclical fashion: After filling the first log file, it begins writing to the second, until that one fills; then it begins writing to the third, and so on. Once the last online redo log file is filled, the Log Writer (LGWR) background process begins to overwrite the contents of the first redo log file.
When Oracle is running in ARCHIVELOG mode, the ARCn (archiver) background processes makes a copy of each redo log file before overwriting it. These archived redo log files are usually written to a disk device. The files can also be written directly to a tape device, but disk space is cheap enough that the additional cost of archiving to disk is offset by the time and labor savings when a disaster recovery operation must occur.
Most production databases, particularly those that support transaction-processing applications, must be run in ARCHIVELOG mode; using RMAN requires that the database be in ARCHIVELOG mode.
You can perform file system backups of a database while that database is open, provided the database is running in ARCHIVELOG mode. An online backup involves setting each tablespace into a backup state, backing up its datafiles, and then restoring the tablespace to its normal state.
When using the RMAN utility, you do not have to manually place each tablespace into a backup state. RMAN reads the data blocks in the same manner Oracle uses for queries.
The database can be fully recovered from an online backup, and it can, via the archived redo logs, be rolled forward to any point in time before the failure. When the database is then opened, any committed transactions that were in the database at the time of the failure will have been restored, and any uncommitted transactions recorded in the redo log files and datafiles will be rolled back.
While the database is open, the following files can be backed up:
All datafiles
All archived redo log files
One control file, via the ALTER DATABASE BACKUP CONTROLFILE command
The SPFILE
Online backup procedures are very powerful for two reasons: First, they provide full point-in-time recovery. Second, they allow the database to remain open during the file system backup. Even databases that cannot be shut down due to user requirements can still have file-system backups. Keeping the database open also keeps the System Global Area (SGA) of the database instance from being cleared when the database is shut down and restarted. Keeping the SGA memory from being cleared will improve the database’s performance because it will reduce the number of physical I/Os required by the database on restart.
|
RMAN automatically backs up the control file and SPFILE whenever the entire database or the
You can use the FLASHBACK DATABASE option, introduced in Oracle Database 10 g, to roll the database back in time without relying on physical backups. To use the FLASHBACK DATABASE command, you must have a flash recovery area defined, be running in ARCHIVELOG mode, and have issued the ALTERDATABASE FLASHBACK ON command while the database was mounted but not open. Logs written to the flash recovery area are used by Oracle during the FLASHBACK DATABASE operation. The configuration and use of the flash recovery area are covered at the end of this chapter.
RMAN Command Overview
You start RMAN from the operating system command line with, as you might expect, the RMAN command. (See the section entitled “Configure the Flash RecoveryArea” later in this chapter for instructions on using Enterprise Manager to perform RMAN operations in a GUI.) In the following sections, you’ll learn how to start RMAN from the command line along with a brief overview of the command structure once you are at the RMAN< prompt. Typically, you will script your RMAN commands to avoid typing errors for repetitive operations. Most DBAs run ad-hoc RMAN commands, especially when recovering a database.
Invoking RMAN
Here is a typical and simple invocation of RMAN that connects to a remote recovery catalog (recovery catalogs, schemas in other databases that store backup and structure information for your source database, are covered in Chapter 3):
In this example, the target option is used to connect to the database using operating system authentication, and the catalog option is used to connect to a recovery catalog in a different database. RMAN recovery catalog concepts are covered in painful detail in Chapter 3.
Although 13 different RMAN command line options are available when you start RMAN, here are the most common ones:
target Identifies the connect string for the Oracle database you want to back up.
catalog Specifies a recovery catalog database for backup information.
nocatalog Uses the control file for backup information.
cmdfile Specifies an input file containing a list of RMAN commands.
log Sets the name of the log file for RMAN messages.
The cmdfile and log options make it easy to reuse a list of RMAN commands over and over and facilitate running RMAN from a batch process.
RMAN Command Types
The two basic types of RMAN commands are standalone commands and job commands. Standalone commands are executed only at the RMAN< prompt and are self-contained. Examples of standalone commands are CHANGE, CONNECT, CREATE SCRIPT, and CREATE CATALOG.
In contrast, job commands are usually grouped and run inside of a command block using the RUN command. Within a command block, the failure of any command within the block terminates execution of the block. An example of an RMAN command that can be used only as a job command is ALLOCATE CHANNEL: the channel allocation is valid only for the duration of the command block. (You would use CONFIGURE CHANNEL, a standalone command, to create a default channel.) An RMAN channel is one stream of data from the database to a device and corresponds to one database server session.
Here is an example of some commands run within a command block to back up the database, force the archiving of the current online redo log file, and remove obsolete backups:
Note that RMAN uses a default channel when you don’t explicitly allocate a channel; in this case it’s the flash recovery area.
Some commands are both standalone and job commands—in other words, you can use them at the RMAN< command prompt or within a command block. For example, you can use BACKUP DATABASE as a standalone or within a command block; when you run BACKUP DATABASE as a standalone command, RMAN automatically allocates one or more channels based on defaults specified by CONFIGURE CHANNEL and whether or not you’re using a flash recovery area.
Table 2-1 provides a list of RMAN commands you’ll use on a regular basis, along with some common options and caveats for each command. For the complete list of all RMAN commands and their syntax, see Oracle Database Backup and Recovery Reference, 11g Release 1.
TABLE 2-1 Common RMAN Commands
CERTIFICATION OBJECTIVE 2.02
Configure Multiple Archive Log File Destinations to Increase Availability
The preparation for using RMAN in your environment consists of two basic steps: change your database to ARCHIVELOG mode (if it is not already), and configure the number and types of archive log destinations to maximize recoverability and availability. In the following two sections, you’ll learn how to configure ARCHIVELOG mode and determine the optimal set of archive log destinations.
Configuring ARCHIVELOG Mode
Consistent offline backups can be performed only while the database is shut down. However, you can perform physical file backups of a database while the database is open, provided the database is running in ARCHIVELOG mode and the backup is performed correctly. These backups are referred to as online backups.
Oracle writes to the online redo log files in a cyclical fashion: After filling the first log file, it begins writing to the second, until that one fills, and it then writes to the third, and so on. Once the last online redo log file is filled, the LGWR background process begins to overwrite the contents of the first redo log file.
When Oracle is run in ARCHIVELOG mode, the ARCn background process makes a copy of each redo log file after the LGWR process finishes writing to it. These archived redo log files are usually written to a disk device. They can instead be written directly to a tape device, but doing this can be very operator intensive and will most likely slow down a busy database while waiting for the LGWR process to finish writing a redo log file to tape. Most likely, you will write your archived redo log files to disk and send the archived log files to tape or delete them once your retention policy is satisfied.
To make use of the ARCHIVELOG capability, you must first place the database in ARCHIVELOG mode. Before starting the database in ARCHIVELOG mode, make sure you are using one of the following configurations, listed from most to least recommended:
Enable archiving to the flash recovery area only; use disk mirroring on the disks containing the flash recovery area. The DB_RECOVERY_FILE_DEST parameter specifies the file system location or ASM disk group containing the flash recovery area (see "Configure the Flash RecoveryArea” later in this chapter).
Enable archiving to the flash recovery area and set at least one LOG_ARCHIVE_DEST_n parameter to another location outside of the flash recovery area. (You’ll learn how to leverage multiple archive destinations later in the chapter in the section “Leveraging Multiple Archive Destinations.")
Set at least two LOG_ARCHIVE_DEST_n parameters to archive to non-flash recovery area destinations.
The following examples assume that the best configuration, a single mirrored flash recovery area, has been selected. The following listing shows the steps needed to place a database in ARCHIVELOG mode; first, shut down the database, and then issue these commands:
To see the currently active online redo log and its sequence number, query the V$LOG dynamic view.
If you enable archiving but do not specify any archiving locations, the archived log files reside in a default, platform-dependent location; on Unix and Linux platforms the default location is $ORACLE_HOME/dbs.
Each of the archived redo log files contains the data from a single online redo log. They are numbered sequentially, in the order in which they were created. The size of the archived redo log files varies, but it does not exceed the size of the online redo log files. When an online redo file reaches its specified maximum size, the redo log file is copied to a new archive log file, and the redo log file is then recycled for reuse by new redo log entries.
If the destination directory of the archived redo log files runs out of space, the ARCn processes will stop processing the online redo log data and the database will stop until you free up space in the destination directory. Make sure that you have enough space available in the destination directory.
This situation can be resolved by adding more space to the archived redo log file destination disk or by backing up the archived redo log files and then removing them from this directory. If you are using the flash recovery area for your archived redo log files, the database issues a warning alert (via e-mail or on the Enterprise Manager home page) if the available space in the flash recovery area is less than 15 percent, and a critical alert when the available space is less than 3 percent.
Taking action at the 15 percent level, such as increasing the size or changing the location of the flash recovery area, can most likely avoid any service interruptions, assuming that no runaway processes (such as untested SQL code running in production) are consuming space in the flash recovery area.
Leveraging Multiple Archive Destinations
You can use two different sets of archive-related initialization parameters, depending on the edition of the Oracle Database Server software you are using, the number of archived log file destinations you need, and whether the archived log file destinations are only local or both local and remote.
Local-Only Destinations
If you are using only local disk locations (in other words, you’re not using a standby database as the destination for archived redo log files) and no more than two local disk locations, you can use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_ DEST parameters. Here is an example of setting these two parameters to an archive location on two different disk drives:
Note that the disk drives can be local to the server running Oracle Database 11g, or they can be on a network-based storage server hundreds of miles away.
EXERCISE 2-1
Identify the Archive Log File Destinations
In this exercise, you will identify the archived redo log file locations and determine the minimum number of required archive destinations.
1. Connect to your database with SQL*Plus and find the value of the LOG_ ARCHIVE_* parameters:
For this database, there appears to be only one archived log file destination, and it is a remote destination. Only one remote destination must succeed for archiving to be considered successful.
2. A second archived log file destination is available if a flash recovery area is defined. Query the flash recovery area–related parameters:
If you are using Oracle Database 11g Enterprise Edition, LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are deprecated in favor of the newer LOG_ARCHIVE_DEST_n parameters.
Local and Remote Destinations
You can specify up to 10 archive log file destinations, either local or remote. If specified, you must use either the LOCATION parameter for a disk destination or the SERVICE parameter to specify a remote database instance as the destination.
In this example, you have two archived log file destinations on disk, and a third is a standby instance whose service name is STNDBY_CLEVELAND:
Defining Minimal Successful Destinations
Regardless of whether you use the LOG_ARCHIVE_DEST or the LOG_ARCHIVE_DEST_n parameter, you can use the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter to specify the number of destinations to which the ARCn processes should successfully copy a redo log file to archive log files, before recycling the online redo log file for reuse. In other words, if you define several destinations, it may be acceptable from a recovery standpoint to have only two destinations available at any given time. Some destinations can temporarily be unavailable due to network issues or a failed standby server. In this case, two available destinations may be sufficient for a potential recovery scenario.
The value of the parameter LOG_ARCHIVE_MIN_SUCCEED_DEST cannot exceed the total number of enabled destinations. In addition, if you are using LOG_ARCHIVE_DEST_n with more destinations designated as MANDATORY than the number of destinations specified by LOG_ARCHIVE_MIN_SUCCEED_DEST, then the parameter LOG_ARCHIVE_MIN_SUCCEED_DEST is ignored.
In addition, if any archive log destination is designated as MANDATORY, a failure of this destination prevents the online log files from being overwritten until the failure is resolved. In this case, parameter LOG_ARCHIVE_MIN_SUCCEED_DEST is also ignored.
Finally, if you’re using LOG_ARCHIVE_DEST, Oracle assumes that it is a MANDATORY location. The behavior will be the same as if you specified a destination using LOG_ARCHIVE_DEST_n with the MANDATORY parameter.
CERTIFICATION OBJECTIVE 2.03
Define, Apply, and Use a Retention Policy
Backups can be automatically retained and managed using one of two methods: by a recovery window or by redundancy. Using a recovery window, RMAN will retain as many backups as necessary to bring the database to any point in time within the recovery window. For example, with a recovery window of seven days, RMAN will maintain enough image copies, incremental backups, and archived redo logs to ensure that the database can be restored and recovered to any point in time within the last seven days. Any backups that are not needed to support this recovery window are marked as OBSOLETE and are automatically removed by RMAN if you are using a flash recovery area and disk space is needed for new backups.
In contrast, a redundancy retention policy directs RMAN to retain the specified number of backups (copies of datafiles and control file). Any extra copies or backups beyond the number specified in the redundancy policy are marked as OBSOLETE. As with a recovery window, obsolete backups are automatically removed if disk space is needed and the flash recovery area is used. Otherwise, you can use the DELETE OBSOLETE command to manually remove backup files and update the catalog.
If the retention policy is set to NONE, no backups or copies are ever considered obsolete, and the DBA must manually remove unneeded backups from the catalog and from disk. By default, the retention policy is a single copy (with the retention policy set to 1). You can set the retention policy to 2 copies using the following RMAN command:
The following example sets the retention policy to a recovery window of 4 days:
Oracle best practices recommends using a recovery window, or a period of time in which it will be possible to uncover any problems with the database, such as an inadvertently dropped table or deleted rows in a table, and be able to perform a point-in-time recovery at just before the error occurred.
In some environments, you may want to disable the retention policy completely. This is useful in an environment where a backup system outside of RMAN stores the disk backups to tape and deletes them. As a result, RMAN does not need to decide when a backup is obsolete and therefore no retention policy is needed. As a result, the details of RMAN backups are maintained up to the time specified by the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME. Here is how you can disable the retention policy:
EXERCISE 2-2
Query and Change the Retention Policy
In this exercise, you will identify the current RMAN retention policy and change it.
Start RMAN and connect to the recovery catalog (in this example, the recovery catalog is owned by the user RMAN on the database with a system ID (SID) of RAC (if you do not have a recovery catalog configured, you can use NOCATALOG instead):
1. Show the existing retention policy:
2. Change the retention policy to a recovery window of 10 days:
CERTIFICATION OBJECTIVE 2.04
Configure the Flash Recovery Area
The flash recovery area, available since Oracle Database 10g, is a unified storage location for all recovery-related files in an Oracle database. As the price of disk space drops, the convenience, increased availability, and decreased recovery times make a completely disk-based backup solution more desirable than tape backup.
The flash recovery area can reside in a single file system directory or as an ASM disk group. In a default installation of Oracle Database 11g, you can easily configure the flash recovery area after you specify the location for the database’s datafiles. Figure 2-1 shows the windows where you specify the location of the flash recovery area along with its size. In this example, the flash recovery area will reside in the ASM disk group +RECOV with a maximum size of 8GB.
FIGURE 2-1 Database installation Recovery Configuration and Locations window
All the files that you need to recover a database from a media failure or a logical error are contained in the flash recovery area. The files that can reside in the area are divided into two categories: permanent or transient. Permanent files are actively being used by the database instance and transient files are required only when you need to recover a part of or the entire database.
The following permanent items are stored in the flash recovery area:
Control file Oracle stores one copy of the control file in the flash recovery area during an installation.
Online redo log files You can store one mirrored copy from each redo log file group in the flash recovery area.
The following transient items are stored in the flash recovery area:
Archived redo log files When you configure the flash recovery area, one set of archived redo log files is stored in the flash recovery area.
Flashback logs Flashback logs are stored in the flash recovery area when Flashback Database is enabled.
Control file automatic backups RMAN stores control file automatic backups in the flash recovery area. When RMAN backs up the first datafile, which is part of the SYSTEM tablespace, the control file is automatically included in the RMAN backup.
Datafile copies When you use the RMAN command BACKUP AS COPY, the datafile copies are stored in the flash recovery area by default.
RMAN backupsets Files created with the BACKUP AS BACKUPSET command are stored in the flash recovery area.
RMAN files By default, RMAN uses the flash recovery area as a staging area for backup and recovery of the archive log files from disk or tape.
Three initialization parameters control the default locations for new control files, online redo log files, and data files: DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, and DB_CREATE_ONLINE_LOG_DEST_n. DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles if you do not explicitly specify a destination. DB_CREATE_ONLINE_LOG_DEST_n specifies up to five locations for online redo log files; if this parameter is not specified, and you create new or additional redo log files, Oracle uses DB_CREATE_FILE_DEST as the destination. Finally, DB_RECOVERY_FILE_DEST specifies the default location for the flash recovery area. If you use DB_RECOVERY_FILE_DEST, you must also specify DB_RECOVERY_FILE_DEST_SIZE. Here is an example of the values of these parameters in a default installation of Oracle Database 11g:
Notice that none of the DB_CREATE_ONLINE_LOG_DEST_n parameters are specified. As a result, Oracle stores the online redo log files in the location specified by DB_CREATE_FILE_DEST. But there’s only one set of online redo log files. You might ask whether this is inviting disaster if a case of media failure occurs. However, if the +DATA disk group is mirrored, you essentially have two or more copies of each online redo log file.
To further optimize the use of disk space for recovery operations, a flash recovery area can be shared by more than one database.
When the flash recovery area is configured, the initialization parameter LOG_ARCHIVE_DEST_10 is automatically set to the flash recovery area location. The corresponding ARCn background processes create archived log files in the flash recovery area and any other locations defined by the LOG_ARCHIVE_DEST_n initialization parameters.
If you do not specify a flash recovery area during installation, you can use Enterprise Manager Database Control to create or configure the flash recovery area. From the home page, select the Availability tab, and the click the Recovery Settings link to open the window shown in Figure 2-2.
FIGURE 2-2 Configuring the flash recovery area using EM Recovery Settings
This Recovery Settings window not only allows you to adjust the location and size of the flash recovery area, but it also shows you the flash recovery area disk usage broken down by file type.
The recommended size of the flash recovery area is the sum of the database size, the size of incremental backups, and the size of all archived log files that have not been copied to tape or to another disk location (if they are copied at all). You can keep all of your recovery files in the flash recovery area depending on your recovery window. The minimum size of the flash recovery area should be at least large enough to hold all archived redo log files that have not been copied to tape or another disk location.
CERTIFICATION OBJECTIVE 2.05
Use the Flash Recovery Area
The initialization parameter DB_RECOVERY_FILE_DEST_SIZE can also assist in managing the size of the flash recovery area. Its primary purpose is to limit the amount of disk space used by the area on the specified disk group or file system directory. However, it can be temporarily increased once an alert is received to give the DBA additional time to allocate more disk space to the disk group or relocate the flash recovery area.
Short of receiving a warning or critical alert, you can be a bit more proactive in monitoring the size of the flash recovery area. Using the dynamic performance view V$RECOVERY_FILE_DEST, you can see the total used and reclaimable space on the destination file system. In addition, you can use the dynamic performance view V$FLASH_RECOVERY_AREA_USAGE to see a usage breakdown by file type.
EXERCISE 2-3
Query the Location, Contents, and Size of the Flash Recovery Area
In this exercise, use the dynamic performance views V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA to determine the current size of the flash recovery area. Then reduce the size of the flash recovery area to 4GB.
1. Query the view V$RECOVERY_FILE_DEST from SQL*Plus to see its location and maximum size:
2. Determine the breakdown of file usage within the flash recovery area using V$FLASH_RECOVERY_AREA_USAGE:
3. Change the size of the flash recovery area to 4GB:
Note that DB_RECOVERY_FILE_DEST_SIZE is a dynamic parameter and therefore takes effect immediately (without a database restart).
Oracle performs some automatic management of the space in the flash recovery area as well. Oracle does this by keeping track of which files are no longer needed for recovery or other flashback functions. If not enough free space exists for new files, Oracle deletes the oldest obsolete files and writes a message to the alert log. When disk space in the flash recovery area is low and insufficient space is free for new files, a message is written to the alert log, an alert is posted on the Enterprise Manager DB Control home page, and a row is added to the data dictionary view DBA_OUTSTANDING_ALERTS. If the available free space is 15 percent or less (85 percent or more used), a warning message is issued. When the available free space reaches 3 percent or less (97 percent or more used), a critical warning is issued.
The column SUGGESTED_ACTION in the data dictionary view DBA_OUTSTANDING_ALERTS provides a possible corrective action for a disk space problem; however, for space pressure in the flash recovery area, your possible corrective actions fall into one of these categories:
Add additional disk space to the flash recovery area and adjust DB_RECOVERY_FILE_DEST_SIZE.
Back up files to a tertiary tape or disk device and remove the files from the flash recovery area.
Review and delete files from the flash recovery area using the RMAN commands REPORT OBSOLETE and DELETE OBSOLETE.
Change the RMAN retention policy.
Using RMAN with the flash recovery area is covered in more detail in Chapters 4 and 5.
CERTIFICATION SUMMARY
This chapter started with an overview of the types of backups you can and should perform on a regular basis: logical and physical. Physical backups can either be online or offline backups. However, due to the business demands of 100 percent uptime, the luxury of offline backups has given way to online backups, and the Oracle RMAN tool can be used to perform online backups.
Although RMAN is covered in much greater detail later in this book, this chapter presented an overview of how to start RMAN with some basic command line options. RMAN commands fall into two broad categories: standalone and job commands, both of which you can execute at the RMAN command line or as a batch process. It also covered the types of retention policies you can configure within RMAN, depending on your availability and recovery requirements.
Next, you learned about the other prerequisites you need to fulfill before you can run your first RMAN command, such as configuring your database for ARCHIVELOG mode (if not already archived) and specifying the appropriate number and type of archive log file destinations.
Finally, you reviewed the usage and configuration of the flash recovery area, how it automates backup and recovery operations, and how you can monitor the disk space available in the flash recovery area.
TWO-MINUTE DRILL
Configuring for Database Recoverability
A robust backup strategy includes both logical and physical backups.
A logical backup of a database involves reading a set of database rows and writing them to a file.
Oracle’s Data Pump Export utility queries the database, including the data dictionary, and writes the output to an XML file called an export dump file.
Once data has been exported via Data Pump Export, it can be imported via the Data Pump Import utility.
Physical backups involve copying the files that constitute the database.
Consistent offline backups occur when the database has been shut down normally (that is, not due to instance failure) using the NORMAL, IMMEDIATE, or TRANSACTIONAL option of the SHUTDOWN command.
You can use online backups for any database that is running in ARCHIVELOG mode.
In ARCHIVELOG mode, the online redo logs are archived, creating a log of all transactions within the database.
You can perform file system backups of a database while that database is open, provided the database is running in ARCHIVELOG mode.
The database can be fully recovered from an online backup, and it can, via the archived redo logs, be rolled forward to any point in time before the failure.
The two basic types of RMAN commands are standalone commands and job commands.
The preparation for using RMAN in your environment consists of two basic steps: change your database to ARCHIVELOG mode (if it is not already), and configure the number and types of archive log destinations to maximize recoverability and availability.
When Oracle is run in ARCHIVELOG mode, the ARCn background process makes a copy of each redo log file after the LGWR process finishes writing to it.
Configure Multiple Archive Log File Destinations to Increase Availability
Changing your database to ARCHIVELOG mode increases recoverability of your database and enables the use of RMAN as a backup and recovery tool for online backups.
The initialization parameter DB_RECOVERY_FILE_DEST specifies the location of the flash recovery area, which can be on a file system or an ASM disk group.
Set at least one LOG_ARCHIVE_DEST_n parameter to a location outside the flash recovery area.
Set at least two LOG_ARCHIVE_DEST_n parameters to archive to non–flash recovery area destinations.
For one or two archived log file destinations, you can use LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST.
For more than two archived log file destinations with at least one remote destination, use LOG_ARCHIVE_DEST_n.
Use LOG_ARCHIVE_MIN_SUCCEED_DEST to guarantee that a minimal number of archived log file destinations are accessible by ARCn.
Define, Apply, and Use a Retention Policy
RMAN can retain and manage backups by using either a recovery window or by redundancy.
Using a retention policy of NONE relies on an externally managed recovery window or redundancy.
The default RMAN retention policy is 1 copy.
The initialization parameter CONTROL_FILE_RECORD_KEEP_TIME controls how long RMAN backup information is kept in the target database’s control file if a recovery catalog is not used.
Configure the Flash Recovery Area
The flash recovery area is a unified storage location for all recovery-related files in an Oracle database.
All the files that you need in order to recover a database from a media failure or a logical error are contained in the flash recovery area.
The permanent items kept in the flash recovery area are a copy of the control file and mirrored copies of the online redo log files.
The transient items kept in the flash recovery area are the archived redo log files, flashback logs, control file automatic backups, data file copies, and RMAN files used for staging a backup or recovery operation using archived log files.
The initialization parameter DB_CREATE_FILE_DEST specifies the default location for database objects that do not explicitly specify a location.
The initialization parameter DB_CREATE_ONLINE_LOG_DEST_n specifies a default destination for one set of archived redo log files.
The initialization parameter DB_RECOVERY_FILE_DEST specifies the location of the flash recovery area.
The initialization parameter DB_RECOVERY_FILE_DEST_SIZE specifies the maximum size of the flash recovery area.
When the flash recovery area is configured, the initialization parameter LOG_ARCHIVE_DEST_10 is automatically set to the flash recovery area location.
The recommended size of the flash recovery area is the sum of the database size, the size of incremental backups, and the size of all archived log files that have not been copied to tape or to another disk location.
Use the Flash Recovery Area
The initialization parameter DB_RECOVERY_FILE_DEST_SIZE can be temporarily increased once an alert is received to give the DBA additional time to allocate more disk space to the disk group or relocate the flash recovery area.
The dynamic performance view V$RECOVERY_FILE_DEST shows the total used and reclaimable space on the destination file system or flash recovery area.
Oracle performs some automatic management of the space in the flash recovery area and keeps track of which files are no longer needed for recovery or other flashback functions.
The data dictionary view DBA_OUTSTANDING_ALERTS contains a possible corrective action for space pressure in the flash recovery area when the amount of free space in the flash recovery area is 15 percent or less of the total flash recovery area size.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.
Configuring for Database Recoverability
1. Which of the following statement is not true regarding database backups?
A. A consistent offline backup occurs after a SHUTDOWN NORMAL, IMMEDIATE, or TRANSACTIONAL.
B. As of Oracle Database 11g, RMAN supports only online backups.
C. A physical database backup copies one or more files that constitute the database.
D. A logical database backup reads a set of database rows and writes them to a file.
E. A logical database backup reads a set of database rows and writes them to an ASM disk group.
F. Online backups can occur only when your database is in ARCHIVELOG mode.
2. Which of the following objects can be backed up by RMAN while the database is open? (Choose all that apply.)
A. Archived redo log files
B. Online redo log files
C. Password files
D. Tablespaces
E. Tables and indexes
F. Control files
G. Server parameter files (SPFILEs)
H. Datafiles
3. Which of the following are not RMAN standalone commands? (Choose all that apply.)
A. BACKUP DATABASE
B. ALLOCATE CHANNEL
C. CONNECT
D. CREATE CATALOG
E. CREATE SCRIPT
Configure Multiple Archive Log File Destinations to Increase Availability
4. Choose the four best commands from the following list that you would use to enable ARCHIVELOG mode, and put them in the correct order:
1. STARUP MOUNT
2. SHUTDOWN ABORT
3. ALTER DATABASE ARCHIVELOG;
4. STARTUP FORCE
5. ALTER DATABASE ENABLE ARCHIVELOG;
6. ALTER SYSTEM SWITCH LOGFILE;
7. SHUTDOWN NORMAL
8. ALTER DATABASE OPEN;
9. SHUTDOWN IMMEDIATE
A. 2, 1, 3, 8
B. 9, 3, 1, 8
C. 4, 5, 7, 6
D. 7, 1, 3, 8
E. 9, 1, 3, 8
5. Which of the following initialization parameters is not valid?
A. LOG_ARCHIVE_DEST_3 = ‘/rmtdisk/u01/app/oracle/flash’
B. LOG_ARCHIVE_DUPLEX_DEST = ‘+DATA’
C. LOG_ARCHIVE_DEST = ‘SERVICE=RMTDB99’
D. LOG_ARCHIVE_DEST = ‘/rmtdisk/u01/app/oracle/flash’
E. LOG_ARCHIVE_DEST_10 = ‘SERVICE=RMTDB99’
F. LOG_ARCHIVE_DEST_10 = ‘/rmtdisk/u01/app/oracle/flash’
6. Your SPFILE contains the following parameter values:
You are not using a flash recovery area. The disk drive containing the directory /u03/app/oracle/ arch fails. What happens to the archive processes and the database?
A. The database pauses because LOG_ARCHIVE_DEST_2 is MANDATORY.
B. The database continues to run normally with the remaining two archive locations because at least one other destination is marked as MANDATORY.
C. The database continues to run normally with the remaining two archive locations because LOG_ARCHIVE_MIN_SUCCEED_DEST is 1.
D. The database will not start unless LOG_ARCHIVE_MIN_SUCCEED_DEST is set to at least the number of MANDATORY locations.
Define, Apply, and Use a Retention Policy
7. Which of the following RMAN commands does not correctly configure a retention policy? (Choose the best answer.)
A. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 100 DAYS;
B. CONFIGURE RETENTION POLICY TO NONE;
C. CONFIGURE RETENTION POLICY TO REDUNDANCY WINDOW OF 2 DAYS;
D. CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
8. If you disable the RMAN retention policy, how long are the details of RMAN backups kept?
A. Until the flash recovery area is full
B. Up to the time specified by the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME
C. Until the database is shut down
D. Indefinitely
Configure the Flash Recovery Area
9. Which of the following items are permanent and are stored in the flash recovery area? (Choose all that apply.)
A. Control file
B. Archived redo log files
C. Online redo log files
D. Control file backup
E. RMAN backupsets
10. Which of the following items are transient and are stored in the flash recovery area? (Choose all that apply.)
A. Control file
B. Archived redo log files
C. Online redo log files
D. Control file backup
E. RMAN backupsets
11. If you specify the initialization parameter DB_RECOVERY_FILE_DEST, what other initialization parameter must be set?
A. DB_CREATE_FILE_DEST
B. DB_CREATE_ONLINE_LOG_DEST_n
C. DB_RECOVERY_FILE_DEST_SIZE
D. No other parameter needs to be set
Use the Flash Recovery Area
12. You have just received a pager alert indicating that the flash recovery area is below 3 percent free space. Which view and column can you query for a possible corrective action for this space condition? (Choose the best answer.)
A. V$FLASH_RECOVERY_AREA_USAGE, PERCENT_SPACE_RECLAIMABLE
B. DBA_OUTSTANDING_ALERT, SUGGESTED_ACTIONS
C. DBA_OUTSTANDING_ALERTS, SUGGESTED_ACTIONS
D. DBA_OUTSTANDING_ALERTS, SUGGESTED_ACTION
LAB QUESTION
Invoke RMAN at the command line; connect to the database using operating system authentication; perform a full compressed backup using a remote catalog; show a list of backups, and delete any obsolete backups.
SELF TEST ANSWERS
Configuring for Database Recoverability
1. 
B. RMAN can perform both online and offline backups.
All other statements about online and offline backups are true.
2. A, D, F, G, and H. RMAN can back up archived redo log files, then delete them from the flash recovery area. Tablespaces can be backed up individually by RMAN. Control files are backed up either explicitly during an RMAN backup or implicitly when the SYSTEM tablespace is part of a backup, or by setting RMAN control file autobackup with CONFIGURE CONTROLFILE AUTOBACKUP ON. The SPFILE, but not a static PFILE (text parameter file), can also be included in an RMAN backup. Individual datafiles can be backed up as well.
B is wrong because you should never back up online redo files, and RMAN will not back them up anyway. C is wrong because RMAN will not back up an operating system file such as a password file; you can back up this file manually. E is wrong because RMAN cannot back up individual tables and indexes. These types of objects are best backed up by a logical backup using expdp.
3. B. The ALLOCATE CHANNEL command can be used only in a command block; you can define a default channel for a standalone command using the CONFIGURE CHANNEL command.
A, C, D and E are all standalone commands; BACKUP DATABASE can be used as a standalone command or as a job command.
Configure Multiple Archive Log File Destinations to Increase Availability
4. E. The correct commands and sequence for enabling ARCHIVELOG mode are as follows:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
All other combinations are either in the wrong order or have incorrect or unnecessary steps. You cannot use SHUTDOWN ABORT, as this leaves the database in an unusable state until the database can be recovered (or restarted), thus you cannot enable ARCHIVELOG mode without extra steps. STARTUP FORCE performs a SHUTDOWN ABORT and a STARTUP, which leaves you with the database in OPEN mode, and thus this command is not necessary. ENABLE ARCHIVELOG is not a valid keyword in the ALTER DATABASE command. SHUTDOWN NORMAL is one way to shut down the database gracefully, but then you must wait for all users to disconnect from the database. ALTER SYSTEM SWITCH LOGFILE is a valid command but is not part of the process of switching a database into ARCHIVELOG mode.
5. C. If you use LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST, the locations must be a disk device (file system or ASM disk). The destination cannot be another Oracle instance.
A, B, D, E, and F are incorrect. When you use LOG_ARCHIVE_DEST_n, the destination can be a file system or a database service. By default, if you have a flash recovery area defined, LOG_ARCHIVE_DEST_10 points to the flash recovery area; however, you can override this with any valid disk location or service.
6. A. All archive destinations marked as MANDATORY must be available when ARCn attempts to archive a filled redo log.
B is wrong because all MANDATORY locations must be available when ARCn needs to archive a filled redo log file. C is wrong because all MANDATORY locations must be available and the number of available locations must be greater than or equal to the number of locations specified in LOG_ARCHIVE_MIN_SUCCEED_DEST. D is wrong because LOG_ARCHIVE_MIN_SUCCEED_DEST can be any integer and is not related to how many LOG_ARCHIVE_DEST_n parameters are set to MANDATORY; both parameters work independently to ensure a minimum number of available archive locations.
Define, Apply, and Use a Retention Policy
7. C. REDUNDANCY WINDOW OF 2 DAYS is syntactically incorrect.
A, B, and D are all valid RMAN commands. You can set the RMAN retention policy to the total number of copies of each database file, to the number of days in the past for which you can restore the database after a logical error, or you can disable the retention policy completely and manage the retention policy externally from RMAN.
8. B. When there is no retention policy, RMAN keeps the details of RMAN backups up to the time specified by the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter.
A, C, and D are incorrect. The control file or a recovery catalog still contain the information about RMAN backups, and this information is available until CONTROL_FILE_RECORD_KEEP_TIME.
Configure the Flash Recovery Area
9. A and C. A mirrored copy of the control file and one mirrored copy of each online redo log file are stored in the flash recovery area.
B, D, and E aren’t considered permanent items.
10. B, D, and E. Archived redo log files, backups of the control file, and RMAN backupsets are considered transient and are stored in the flash recovery area.
A and C aren’t considered transient items.
11. C. When you specify the location of the flash recovery area with DB_RECOVERY_FILE_DEST, you must also set the parameter DB_RECOVERY_FILE_DEST_SIZE to limit the amount of space used by the flash recovery area on the destination file system.
A is wrong because DB_CREATE_FILE_DEST specifies a default location for any database object created without an explicit location. B is wrong since DB_CREATE_ONLINE_LOG_DEST_n specifies the location for new or additional online redo log files. D is wrong since you must specify a size for the flash recovery area.
Use the Flash Recovery Area
12. D. The REASON column in DBA_OUTSTANDING_ALERTS contains a description for the alert, and the SUGGESTED_ACTION provides a recommendation for corrective action. These descriptions also appear in the alerts section of the Enterprise Manager Database Control home page.
A is wrong since the column PERCENT_SPACE_RECLAIMABLE doesn’t provide any recommendations, only an amount of disk space that can be reclaimable for objects that may be obsolete in the flash recovery area. B and C are wrong because there is no such data dictionary view as DBA_OUTSTANDING_ALERT, and there is no such column as SUGGESTED_ACTIONS in the view DBA_OUTSTANDING_ALERTS.
LAB ANSWER
