|Oracle® Database 2 Day DBA
10g Release 1 (10.1)
Part Number B10742-01
This chapter introduces you to Oracle backup and recovery as performed through Enterprise Manager. The goals are to introduce the basic concepts of Oracle backup and recovery, show how to configure your database for backup and recovery using the Oracle-suggested disk-based backup strategy, and then walk you through recovery of a full database backup.
Note:The instructions in "Configuring Your Database for Basic Backup and Recovery" describe how to set up your database to take advantage of Oracle-suggested backup strategies. If you chose to configure automated backups when creating the database using the Database Configuration Assistant, you do not need to perform the steps in this section. See "Using DBCA to Create and Configure a Database" on page 2-5 for more details on creating a database already configured for automated daily backups using the Oracle-suggested backup strategy.
This chapter includes the following topics:
Backup of an Oracle database generally refers to physical backup, protecting the files that make up your database. The files protected by the backup and recovery facilities built into Enterprise Manager include datafiles, control files, server parameter files (SPFILEs), and archived redo log files. With these your database can be reconstructed. The backup mechanisms that work at the physical level protect against damage at the file level, such as the accidental deletion of a datafile or the failure of a disk drive.
Logical-level backups, such as exporting database objects like tables or tablespaces, may be a useful supplement to physical backups for some purposes but cannot protect your entire database. The focus in Oracle backup and recovery is generally on the physical backup of database files, which permit the full reconstruction of your database.
Oracle's flashback features provide a range of physical and logical data recovery tools as efficient, easy-to-use alternatives to physical and logical backups. This chapter will introduce two of the flashback features that operate at a logical level: Oracle Flashback Table, which lets you revert a table to its contents at a time in the recent past; and Oracle Flashback Drop, which lets you rescue dropped database tables. Neither requires advance preparation such as creating logical-level exports to allow for retrieval of your lost data, and both can be used while your database is available. Oracle Database Backup and Recovery Advanced User's Guide discusses the flashback features of the Oracle database at greater length.
Oracle Enterprise Manager's physical backup and recovery features are built on Oracle's Recovery Manager (RMAN) command-line client. Enterprise Manager carries out its backup and recovery tasks by composing RMAN commands and sending them to the RMAN client. Enterprise Manager makes available much of the functionality of RMAN, as well as providing wizards and automatic strategies to simplify and further automate implementing RMAN-based backup and recovery.
After you are familiar with the basics of backup and recovery through Oracle Enterprise Manager, refer to Oracle Database Backup and Recovery Basics, and Oracle Database Backup and Recovery Advanced User's Guide, for more details on the full range of Oracle's backup capabilities.
To back up your database is to make backup copies of your datafiles, control file, and archived redo logs if any. Restoring a database from backup is simply copying the physical files that make up the database from some backup medium (disk or tape) to their locations during normal database operation. Recovery of your database is the process of updating database files restored from a backup with the changes made to the database since the backup, typically using redo log files.
A backup can be consistent or inconsistent. A consistent backup is one in which there are no changes in the redo log that have not already been applied to the datafiles at the time of the backup. In an inconsistent backup, the online and archived redo logs can contain changes that have not yet been applied to the datafiles.
After being restored from a consistent backup, the database can be opened immediately. There is no need to preserve the redo logs to be able to use a consistent backup. However, in order to create a consistent backup of your database, you must carry out a normal shutdown of your database, and then back up your datafiles. This process is known as an offline backup, because the entire database is offline for the duration of the backup process.
When your datafiles have been restored from an inconsistent backup, the database cannot be opened until the restored datafiles are updated with any pending changes in the redo log. This step brings the datafiles to a consistent state, so that all datafiles reflect all changes as of a specific point in time. After the datafiles are consistent, the database can be opened. The process in which changes from the redo log are applied to the datafiles is called media recovery, and it is described in the next section. To use inconsistent backups, however, you must run your database in
ARCHIVELOG mode. This preserves your redo log files for use in media recovery.
In spite of the name, an inconsistent backup is as robust a form of backup as a consistent backup. The great advantage to taking inconsistent backups is that you do not have to take your database offline to create an inconsistent backup. Users can continue to update the database during the backup process.
If you restore the archived redo logs and the datafiles from backup, Oracle will perform the media recovery process when you try to open the database. Any database transactions in the online and archived redo logs not already reflected in the datafiles are applied to the datafiles, bringing them to a transaction-consistent state before the database is opened.
Media recovery can be either complete recovery or point-in-time recovery. In complete recovery datafiles are restored from backup, and all changes from the archived and online redo logs are applied to the datafiles. The database is returned to its state at the time of failure, and can be opened with no loss of committed changes.
In point-in-time recovery, you return your database to its contents at a target time of your choosing in the past. Starting with a set of datafile backups created prior to the target time and a complete set of archived redo log files from the time of the backup through the target time, you can have the database re-apply to the datafiles each change since the datafiles were backed up. When all changes as of the target time have been re-applied, the datafiles are returned to their contents as of the target time. Provided that you keep all archived redo logs back to the time of a given database backup, point-in-time recovery can return your whole database to its state at any time between the time of that backup and the most recent change in the archived redo logs. Point-in-time recovery is sometimes referred to as incomplete recovery, since not all changes up through the present are applied.
Media recovery requires a control file, datafiles (typically restored from backup) and all online and archived redo logs containing changes since the time the datafiles were backed up. It is typically used only in the case of database failure (caused either by a media failure, such as the loss of a file or disk, or a user error, like the deletion of the contents of a table).
Complete recovery will be the focus of the examples in this book. Incomplete recovery is an advanced technique discussed in Oracle Database Backup and Recovery Advanced User's Guide.
RMAN's record of all backup and recovery files and activities for your database is known as the RMAN repository. Every backup action you perform through RMAN or Enterprise Manager is recorded in the repository, along with the location of and other information about every backup created or altered on disk or tape. At recovery time, you can simply issue a command like
RESTORE DATABASE and Oracle uses the record in the repository to select backups on disk and tape needed to complete the recovery.
The primary store for the RMAN repository is the control file for your database. This is one more reason why protecting your control file is a vital part of your backup strategy. In some installations, a second copy of the RMAN repository is stored in a separate Oracle database called the recovery catalog. Use of a recovery catalog is optional and is beyond the scope of this book.
To simplify the management of backup and recovery related files, Oracle lets you create a flash recovery area for your database. Designate a location (typically, a directory on disk) and a maximum disk quota for the flash recovery area, and a retention policy to specify your database recoverability goals. You can then direct backup-related activities (including archiving of redo logs) to store their files there, and Oracle will automatically manage this storage, deleting files no longer needed to meet your recoverability objectives due to their age or due to their having been backed up to tape. Use of a flash recovery area simplifies backup storage management tasks and is therefore strongly recommended.
Because the flash recovery area is so useful, most examples in this chapter will assume the use of a flash recovery area.
To take maximum advantage of Oracle's features for automatic managing backup and recovery, configure your database as follows:
Use a flash recovery area, to automate storage management for most backup-related files.
Run your database in
ARCHIVELOG mode, so that you can perform online backups and have data recovery options such as complete and point-in-time media recovery.
Use the flash recovery area as an archive log destination for your database.
You must also set a number of policies governing which files are backed up, what format is used to store backups on disk, and when files become eligible for deletion from the flash recovery area. Finally, you should make a record of two pieces of information about your database: the
DBID and the
DB_UNIQUE_NAME. This information is needed in some disaster recovery situations, such as restoring a lost database control file.
To better protect your data, put the flash recovery area on a separate disk from the working set files of your database, to avoid the disk being a single point of failure for your database.
The amount of disk space to allocate for the flash recovery area depends upon the size and activity levels of your database, which determine the size of your datafiles and redo log files, and your recovery objectives, which dictate what kinds of backups you take, when you take them, and how long you must keep them.
Space management in the flash recovery area is governed by a backup retention policy. Retention policy determines when files are obsolete, meaning that they are no longer needed to meet your data recovery objectives.
Retention policies can be based on redundancy of backups, or a recovery window. Under a redundancy-based policy, the flash recovery area considers a backup of a file to be obsolete only when a specified number of more recent backups of that file are present. For example, if you specify a retention policy requiring two backups of each file be kept, and make backups nightly starting on a Monday night, then after the Wednesday night backup succeeds, the Monday night backup becomes redundant because the Tuesday and Wednesday backups are available.
Under a recovery window-based policy, you specify a time interval measured in days, and files become obsolete only when they are no longer needed for successful complete recovery or point-in-time recovery to any point within that number of days into the past. For example, if you specify a recovery window of three days, a backup of all datafiles from at least three days ago must be retained, along with a full set of archived redo logs since that backup.
Note:Depending upon your backup strategy, a recovery window-based retention policy can require the retention of large quantities of data. If you specify a retention policy requiring a three day recovery window, and you take a full database backup only on the first night of each month and no other backups, on the 29th of the month you must retain the full database backup from the first night, as well as 28 days of archived redo logs.
A redundancy-based retention policy makes it easier to predict space usage in the flash recovery area, but do not allow you to predict how far into the past you can recover your database. A recovery window-based policy offers better protection for your data, but can make storage requirements for backups harder to predict. As already noted, even a short recovery window combined with a poorly designed backup strategy can cause unexpectedly high space requirements. (A reasonable backup strategy would include more frequent datafile backups.) Oracle recommends the use of a recovery-window-based backup retention policy as part of your backup strategy.
Even after they are obsolete, files are generally not deleted from the flash recovery area until space is needed to store new files. As long as space permits, files recently moved to tape will remain on disk as well, so that they will not have to be retrieved from tape in the event of a recovery.
The automatic deletion of obsolete files and files moved to tape from the flash recovery area makes the flash recovery area a very convenient redo log archiving destination. Other archiving destinations require manual cleanup of archived redo logs no longer needed on disk for recovery.
An approach for sizing the flash recovery area is described at length in Oracle Database Backup and Recovery Basics, but as a general rule, the larger the flash recovery area, the more useful it becomes. Ideally, the flash recovery area should be large enough to hold a copy of all of your datafiles and control files, the online redo logs, and the archived redo log files needed to recover your database using the datafile backups kept under your retention policy. If your backup strategy includes incremental backups, described in "Incremental Backups of Datafiles", add enough space to the flash recovery area to accommodate these files as well. If you can move some backups to tape, you can reduce the size of the flash recovery area somewhat, although retrieving those files from tape will cause longer database restore and recovery times.
You must have the proper credentials to perform some of the configuration tasks for backup and recovery, and to schedule backup jobs and perform recovery. Two sets of credentials are required: the Oracle user you use when you log in to Enterprise Manager, and the host operating system user whose credentials you provide when performing backup and recovery tasks. The host operating system credentials are used when RMAN is started behind the scenes, to actually perform the tasks you specified or scheduled through Enterprise Manager.
If you log in to Enterprise Manager with SYSDBA privileges, any valid host operating system user who has execute permission for the RMAN command line client will suffice for scheduling and running RMAN tasks.
For those tasks where host operating system credentials are required, a Host Credentials form where they can be entered appears at the bottom of the page used to perform the task. This form always includes a checkbox labelled Save as Preferred Credential. If you check this box before performing your action, the credentials you provide will be stored persistently for the currently logged-in Oracle user, and re-used whenever you log in as that user and perform operations requiring host credentials. (If the database is down, however, as is required for some database recovery operations, you will have to enter host credentials manually.)
It is possible to configure a flash recovery area and set the database archiving mode when first creating the database. If you did not perform these tasks at database creation time, however, you can add them to an existing database with the procedure described here.
Configuring the flash recovery area and setting
ARCHIVELOG mode requires the following steps:
Under the host operating system, create a directory to hold the flash recovery area. Make sure that the permissions on this directory allow Oracle to create files here.
From the Database Home page, go to the Maintenance page. On this page, under the Backup/Recovery heading, select Configure Recovery Settings. The Configure Recovery Settings page appears.
Under the Flash Recovery Area heading, enter the path to the flash recovery area location (the path to the directory on disk you created in step 1), and your desired flash recovery area size. Make sure the Apply changes to SPFile Only box is not checked, then click Apply to make these changes take effect.
Under the Media Recovery heading, check ARCHIVELOG Mode if it was not already checked. Below the ARCHIVELOG Mode checkbox is a list of up to ten possible log archiving locations. Enter
USE_DB_RECOVERY_FILE_DEST as one of the destinations, to specify the flash recovery area as a destination. For ease of database management, the best practice is to use the flash recovery area as your only redo log archiving destination. You can, however, specify other locations on disk, filling them in starting with the first one.
Click Apply to make these changes take effect.
Note:If your database was not previously running in
Assuming you have a flash recovery area configured and are running in
ARCHIVELOG mode, you can configure a number of settings and policies that determine how backups are stored, which data is backed up, how backups perform, and how long backups are retained before being purged from the flash recovery area. There are also settings you can configure to improve backup performance. This section provides information on concepts underlying the available settings, and information on how to change them through Enterprise Manager.
The settings available on the Device property page of Configure Backup Settings affect how backups will be written to disk and to tape. For disk-based backups, you can configure the default format for storing backups, the location on disk where backups are stored, and whether backup tasks are run in parallel for improved performance.
Image copies are exact byte-for-byte copies of the files they back up. You can create an image copy by copying a file at the host operating system level. However, unlike copying files at the operating system level, creating image copy backups through Recovery Manager or Enterprise Manager causes a record of those image copies to be made in the RMAN repository. This allows RMAN to use these copies during database restore and recovery. RMAN can only use files in restore operations if they are recorded in the RMAN repository.
Backup sets are logical entities containing the results of running various backup tasks. In fact, a backup set consists of several physical files called backup pieces. Backup pieces use an Oracle-proprietary format to store the contents of one or several files from a backup job. Backup pieces cannot be meaningfully manipulated individually; they can only be accessed as part of backup sets. When datafiles are backed up into backup sets, data blocks that have not yet been used to store data are not written into the backup pieces, saving space. This is referred to as unused block compression. Unused block compression is fundamental to how backup sets are created, and cannot be disabled.
An additional binary data compression algorithm can be applied during the writing of backup sets to save more space, at some cost in performance. Using binary compression for backup sets stored on disk is recommended if conserving disk space is more important than backup speed.
Note:Use of binary compression of backup sets is supported for backup sets written to a media manager, but if the media manager supports its own binary compression, then Oracle Corporation recommends using the media manager's compression instead.
Oracle backups to disk can be saved as image copies or backup sets. Image copy backups can only be created on disk. Backups to tape and similar media management devices must be stored as backup sets.
RMAN depends upon server sessions, processes that run on the database server, to actually perform backup and restore tasks. Each server session in turn corresponds to an RMAN channel, representing one stream of data to or from a backup device. RMAN supports parallelism, the use of multiple channels and server sessions to carry out the work of one backup or recovery task. If a task can be usefully executed in parallel on your hardware, you can set the level of parallelism to use on that task. You can also set a default level of parallelism to use when working with specific devices. Proper exploitation of parallelism can greatly increase performance on backup and recovery tasks.
For more conceptual material on channels and parallelism in RMAN backups (whether through Enterprise Manager or the command line client) see Oracle Database Backup and Recovery Advanced User's Guide.
From the Database Home page, go to the Maintenance property page, and then, under the Backup/Recovery heading, choose Configure Backup Settings.
The Configure Backup Settings page contains three property pages: Device, Backup Set and Policy.
The settings you pick here are defaults which can apply to all backup jobs. When performing individual backup tasks, you can override these defaults.
The Device property page is shown first by default. Review the following fields under the Disk Settings section:
Parallelism: For now, set this value to 1. Later, when you have had time to review the information in Oracle Database Backup and Recovery Advanced User's Guide on parallelism and performance in RMAN, you may want to change this value.
Disk Backup Location: Should be blank, to direct backups to the flash recovery area.
Disk Backup Type: Make sure that Backup Set is selected. One advantage to backing up Oracle datafiles to backup sets is that RMAN uses unused block compression to save space in backing up datafiles. Only those blocks in your datafiles that have been used to store data are included in the backup set.
You can also provide the host credentials for the backup. Enter a username and password for a host operating system account that is a member of the DBA group.
After these settings are filled in, you can click Test Disk Backup to make sure the credentials and backup location are correct.
The settings on the Backup Set property page should not be altered at this time.
From the Backup Settings page, choose the Policy property page. On this page, you can set the backup policies governing control file and SPFILE backups, tablespaces to exclude from whole database backup, and the backup retention policy.
For now, check the box to Automatically back up the control file and server parameter file (SPFILE) with every backup and database structural change. The SPFIle and control file are critical to the operation of your database and RMAN, and are also relatively small compared to typical datafiles. Backing them up frequently imposes relatively little disk space overhead. Leave the Autobackup Disk Location field blank, so that the autobackups are sent to the flash recovery area.
Check the option to Optimize the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up. This will save space in the flash recovery area.
Check the Enable block change tracking for faster incremental backups box to take advantage of the block change tracking feature of Oracle, which substantially improves performance of incremental backups at a small cost of overhead during normal operations.
You can list tablespaces to exclude from backup. If, for example, you have a read-only tablespace, you do not need to back it up. For now, make sure this list is empty, so that all tablespaces are backed up.
You can choose between three forms of retention policy:
Retain All Backups (in effect, no retention policy at all, so that all backups are kept in the flash recovery area until you explicitly delete them)
Retain backups that are necessary for a recovery to any time within the specified number of days (point-in-time recovery) for a recovery window-based retention policy
Retain at least the specified number of full backups for each datafile for a redundancy-based retention policy
For now, choose the recovery window-based retention policy, with a recovery window of 31 days.
Verify that the Host Credentials section at the bottom of the page contains proper credentials. Then click OK to save the new settings.
To find out your
DB_UNIQUE_NAME, from the database home page, from the Administration page, under Instance, click All Initialization Parameters. On the Current property page, for Filter enter
DB_UNIQUE_NAME and click Go. The resulting page displays a row with
DB_UNIQUE_NAME listed in the Name column, and the
DB_UNIQUE_NAME value for your database in the Value column.
To find out your
DBID, from the database home page, select the Administration page, then under Storage, click Controlfiles, then select the Advanced property page. The Database ID field contains the value of the
Record these two pieces of information offline in case they are needed for disaster recovery.
This section discusses performing database backup using Enterprise Manager. It introduces several types of Oracle database backup, then explains how to take the different backup types, how to take advantage of Enterprise Manager's Oracle-suggested backup strategy to implement a useful basic backup regimen permitting fast recovery, and how to schedule your own backups.
Note:The Oracle-suggested strategy for disk-only backups, as described in this section, provides efficient daily backup of your entire database to disk. This provides the ability to quickly return your database to its state at any point during the preceding 24 hours. If you need more flexible backup options than this, you will find more details on the full range of backup options in Oracle Database Backup and Recovery Basics.
To understand the Oracle-suggested backup strategy and other backup types provided through Enterprise Manager, you need some conceptual background on database backups as supported by Oracle.
A full backup of a datafile is a backup which includes all used blocks of the datafile. This can be either an image copy backup, which is an exact copy of the datafile as if copied with a host operating system file copy command, or a backup into a backup set created by RMAN. Regardless of the form in which the backup is stored, the entire datafile is backed up, even if only a few blocks have changed.
Incremental backups are based around capturing only those blocks that change between backups in each datafile of your database. In a typical incremental backup strategy, a level 0 incremental backup, capturing all blocks in the datafile, is taken as a starting point. Subsequent level 1 incremental backups, typically taken at regular intervals, capture images of each block in a datafile that changed. Level 1 backups can be cumulative, in which case all blocks changed since the most recent level 0 backup are included, or differential, in which case only those blocks changed since the most recent level 0 or level 1 incremental backup are included.
Recovering changed blocks from incremental backups is used to improve performance of the media recovery process. Since an incremental level 1 backup captures the final contents of all datafile blocks changed during the period covered by the incremental, the recovery process can skip reapplying individual updates from the redo logs of that period and simply update each block with its final contents. The redo logs are only used for the period not covered by level 1 incremental backups.
The incrementally updated backups feature of Oracle lets you use one or more level 1 incremental backups with an older image copy backup of your datafiles, to roll the copy forward to the SCN at which the last level 1 incremental backup was taken. All blocks changed since the image copy was created are overwritten with their new contents as of the time of the last level 1 incremental backup. The effect is to roll the file forward in time, so that its contents are equivalent to an image copy full datafile backup taken at the time of the last incremental level 1 backup. This feature lets you implement strategies with shorter recovery times, because you need only perform recovery starting at the SCN of the last level 1 incremental applied to your datafiles.
All RMAN backups, including incremental backups, can be tagged with a label, a text string identifying that backup, either uniquely or as part of a group of backups. (For instance, if you performed a weekly full database backup on Saturday nights, you could use the tag
FULL_SATURDAY to identify all such backups.) These tags can be used for referring to specific backups in RMAN commands; for example, you could issue a command to move the latest
FULL_SATURDAY backup to tape.
Because you can use tags to refer to different groups of backups, they are useful if you want to create several different routines in your overall backup strategy which do not interfere with each other.
When you schedule a backup job and give the job a name, the job name is used to tag the backup.
Whole backups of a database are based on backing up the entire contents of the database at the time of backup. Full backups of all datafiles are created. The results may be stored as image copies or as backup sets, but in either case the complete contents of all datafiles of the database are represented in the backup, as well as the control file, archived redo log and server parameter file. With this set of files, the database can be recovered completely.
While whole database backups can be an important element in your overall backup strategy, they are also a required step in some situations, such as when you switch
ARCHIVELOG mode on or off.
Note:If you had to switch your database into
Go to the Maintenance property page and under the Backup/Recovery column, choose Schedule Backup. The Schedule Backup: Strategy page shown in Figure 9–1, "Schedule Backup: Strategy Page" appears:
Figure 9-1 Schedule Backup: Strategy Page
The first field is a Backup Strategy field, where you can choose between Oracle-suggested (the default) and Customized. For a whole database backup, choose Customized. The page refreshes itself, to offer a choice of objects to back up and a space to enter host credentials. Select Whole Database as the set of objects to back up. Enter your host operating system credentials if required, and click Continue.
On the Schedule Backup: Options page, you specify the options for this whole database backup. Under Backup Type, choose Full Backup. For Backup Mode, choose between an online and offline backup. Typically, you will want to perform online backups, to maximize database availability. (Note that, as explained in "Configuring Your Database for Basic Backup and Recovery" on page 9-4, the database must be run in
ARCHIVELOG mode to enable online backups.)
Under the Advanced section, if this is an online backup, check the box to back up all archived logs. (There is no need to back up archived logs when performing an offline backup, as the database will be in a consistent state at the time of backup and does not require media recovery if you restore from this backup.) For now, leave the option for proxy copies unchecked. Do not specify a maximum number of files for each backup set. Click Next to move on to the Backup: Settings page.
On the Schedule Backup: Settings page, select the appropriate backup destination. Oracle Corporation recommends backing up to disk, to minimize recovery time. Then, click Next to move on to the Schedule Backup: Schedule page.
On the Schedule Backup: Schedule page, enter the schedule for the backup job, or specify to perform it immediately. A Job Name is generated for you, but if you want to tag this backup, you can edit the assigned job name, which is used as the tag. You can also edit the Job Description as needed for your own reference. Then, in the Schedule fields, you specify when to start the backup, and how often to repeat it. Leave the default start time of Immediately selected to run a backup immediately, or set Later and enter a later time. For jobs to be run regularly, set the Repeat and Repeat Until parameters as appropriate. (You probably want to change the Job Name in this case, to identify these scheduled backups as part of an ongoing series, such as WEEKLY_FULL_BACKUP. When finished, click Next to move on to the Schedule Backup: Review page.
On the Schedule Backup: Review page, you have one last chance to use the Back button and change these options.You can also click Edit RMAN Script to see the RMAN commands that will be executed to perform your specified backup jobs. Click Submit Job to add the specified backup job to the schedule (or to run it immediately).
To monitor the progress of the backup job, you can click View Job.You will arrive on a page that shows a summary of the job submitted, and a Logs section where you can follow the progress of the various steps of the backup job and drill down to see the RMAN output from the running job.
When performing an offline backup, the database instance shuts down, then restarts and enters a MOUNTED state for the duration of the offline backup. The offline backup runs in the background, generating no user-visible output in the browser. The fact that the database is not open affects the pages you see from Enterprise Manager while the offline backup runs.
After you submit the backup job, the Backup Submit Successful page appears, and suggests that you wait long enough for the offline backup to complete, then click View Job to be redirected to the Job Status page.
In this situation, because you know that the database is down in order to run its offline backup, do not choose either option. Wait long enough for the offline backup to complete, then reload the browser page again. At that time you will be prompted for database login credentials, and then returned to the job status page, where you can view the final results of the offline backup job.
If you attempt to reload the status page while the database is shutting down and coming back up to a MOUNT state, you may encounter an error page in your web browser. Wait a short period and refresh the page again. This error does not indicate a problem with the offline backup. Enterprise Manager must be restarted when the database is shut down and brought to a MOUNT state. While Enterprise Manager is being restarted, you may receive this error page.
If you attempt to reload the status page while the offline backup is in progress, you may encounter a page with a message that reads: The database status is currently unavailable. It is possible that the database is in mount or nomount state. Your choices on this page are to click Startup or Perform Recovery. Do not click either button. The offline backup is still running in the background and should not be interrupted. Instead, reload this page periodically. After the offline backup completes and the database opens, reloading this page will bring you to the database login page again.
Enterprise Manager makes it easy to set up an Oracle-suggested backup strategy for backups to disk, that protects your data and provides efficient recoverability to any point in the a recovery window of your choosing. (In the simplest case, examined in this section, this window is 24 hours.) The Oracle-suggested strategy leverages Oracle's incremental backup and incrementally-updated backup features to provide faster backups than whole database backups, and faster recoverability than is possible through applying database changes from the archived log to your datafiles.
The Oracle-suggested backup strategy is based on creating an image copy of your database which is rolled forward using incrementally updated backups. Oracle Enterprise Manager schedules RMAN backups jobs for you to run during the overnight hours.
For each datafile, the strategy calls for backups to be made as follows:
At the beginning of day 1 of the strategy (the time the first scheduled job actually runs), an incremental level 0 datafile copy backup. It contains the datafile's contents at the beginning of day 1.
In a restore-and-recovery scenario, the redo logs from day 1 can be used to recover to any point during day 1.
At the beginning of day 2, an incremental level 1 backup is created, containing the blocks changed during day 1.
In a restore-and-recovery scenario, this incremental level 1 can be applied to quickly roll forward the level 0 backup to the beginning of day 2, and redo logs can be used to recover to any point during day 2.
At the beginning of each day n for days 3 and onwards, the level 1 backup from the beginning of day n-1 is applied to the level 0 backup. This brings the datafile copy to its state at the beginning of day n-1. Then, a new level 1 is created, containing the blocks changed during day n-1.
In a restore-and-recovery scenario, this incremental level 1 can be applied to the datafile rolled forward on day n-1 to the beginning of day n, and redo logs can be used to recover the database to any point during day n.
The datafile copies used in the Oracle-suggested backup strategy are tagged with the tag ORA$OEM_LEVEL_0. The level 1 incremental backups for use in this strategy are created for use with datafile copies that are so labelled. You can safely implement other backup strategies without concern for interference from the backups for the Oracle suggested strategy.
There are also Oracle-suggested strategies that use tape backups along with disk backups, but those are beyond the scope of this document.
Go to the Maintenance property page and under the Recovery column, choose Schedule Backup. The Schedule Backup: Strategy page appears.
The Backup Strategy field lets you select between Oracle-suggested (the default) and Customized backup strategies, as well as backup destinations of disk, tape or both. For now, choose Oracle-suggested as a strategy and Disk as the destination. Choose Continue. The Schedule Backup: Setup page appears describing the setup. There are, however, no settings to change on this page. Click Next to continue to the Schedule Backup: Schedule page.
On the Schedule Backup: Schedule page, you are prompted for Start Date, Time Zone and Daily Backup Time for the daily backups. Based upon your expected usage patterns, choose times for the nightly backup during which database activity is low. Choose Next to continue to the Schedule Backup: Review page.
On the Schedule Backup: Review page, the backup script RMAN will run is displayed (although you cannot edit the script directly), and you are presented with a chance to confirm or alter your settings. In the listing you can see the tag
ORA$OEM_LEVEL_0 assigned to the backup. Assuming you do not need to change the schedule assigned, click Submit Job to add the job for the Oracle-suggested strategy to your schedule.
Your database will now be backed up once daily, using incremental backups and incrementally applied backups, allowing quick recovery to any time in the preceding 24 hours.
After taking some time to understand the full range of available backup options as described in Oracle Database Backup and Recovery Basics, you may decide to schedule backup tasks beyond those used to implement the Oracle-suggested backup strategy. While the particulars of specifying the job to perform differ for each type of backup, all backups, whether performed immediately or scheduled, begin from the Schedule Backup: Strategy page, shown in "Performing and Scheduling Backups with Enterprise Manager". Here you can select any of several object types to back up: the whole database, individual tablespaces, datafiles, or archived logs. (You can also back up existing backups in one destination to some other destination, such as moving backups from disk to tape.) For any object you select to back up, there will be options governing how that backup is taken. These will, however, differ by the type of object being backed up.
Then, you arrive at the Schedule Backup: Schedule page. You can either schedule a job to run once, immediately or at a future time, or set it to run multiple times, using the options in this form.
If you schedule a regular backup task as part of a strategy of your own devising, it is recommended that you give the resulting backup job a distinctive name, such as WEEKLY_FULL_BACKUP or DAILY_SALES. All backups produced by this strategy will have this job name appended with a timestamp as their tag. The common tag will permit you to more easily refer to the backups from this strategy during recovery activities.
A backup strategy is not complete until you validate that the backups produced can actually be used to recover the database. The RMAN command line client provides the RESTORE... VALIDATE command, which lets you confirm that a particular restore operation can be performed with the backups you have available.
For example, if the RMAN command RESTORE DATABASE restores your entire database from backup, RESTORE DATABASE VALIDATE examines the available backups and redo logs to determine whether the RESTORE DATABASE operation would complete successfully given the currently available set of backups. See Oracle Database Backup and Recovery Basics for more details about validating backups.
Restoring and recovering your whole database from a backup is completely automated, thanks to the record of your existing backups kept in the RMAN repository and the intelligence built into Recovery Manager. As long as you have taken your backups through Recovery Manager (whether at the command line or through Enterprise Manager), the entire restore and media recovery operation can be performed through the Enterprise Manager.
To access the restore and recovery tasks, from the database home page, choose the Maintenance property page, then under the heading Backup/Recovery choose Perform Recovery to start the Recovery wizard.
On the Perform Recovery: Type page, in the Type field, select the type of restore operation to perform. You can choose to recover a whole database or selected tablespaces, datafiles, archived logs, or tables.
Note:In some recovery scenarios, such as a complete restore and recovery of your database, the database state will be altered by steps you take during the wizard. Changes, some of them irrevocable, are made to your database at certain steps. For example, the database may be shut down and brought to MOUNTED state, or datafiles may be overwritten with versions from backup.
Oracle Enterprise Manager will display warnings each time a significant database change will result from pressing Continue during the recovery process. Pay close attention to these warnings.
Note:The discussion which follows assumes that you are restoring and recovering your database after the loss of one or more datafiles, but still have a usable SPFILE and control file. Enterprise Manager can also be used to restore a lost SPFILE or control file. See "Recovering from a Lost SPFILE or Control File" for details.
On the Perform Recovery: Type page, set the Type to Whole Database. The Operation Type drop-down menu will update to offer you the choices appropriate to a whole database restore: restoring files only, recovery only, and both restoring and recovering your database. Select Restore and Recover. Also, provide the requested host credentials at this time if necessary and click Continue.
At this point, Oracle shuts down your database, and you arrive at a Database page where you are given the opportunity to Startup or Perform Recovery. Choose Perform Recovery.
Note:As already noted in the discussion of offline backups in "Performing a Whole Database Backup with Oracle Enterprise Manager", Enterprise Manager may respond with an error page during the period when the database is being shut down and brought to the MOUNTED state. Wait a while and refresh the page until the error no longer occurs.
After the database reaches the MOUNTED state, Enterprise Manager responds with a page warning that the state of the database is unavailable (meaning that Enterprise Manager cannot determine the state), but that the database may be in a NOMOUNT or MOUNTED state. At this time you will be offered two choices: Startup and Perform Recovery. Because you know that the database is down so that you can perform your recovery, do not choose Startup. Click Perform Recovery to resume your recovery session. You may be prompted for host and database credentials. Then you will reach the Perform Recovery: Type page again, only now the database is in a MOUNTED state (as is required for this operation) instead of being open. At this point, as you did before, select Whole Database for Object Type and Restore and Recover for Operation, and click Continue.
Next, you see the Perform Recovery: Credentials page, where you are again prompted for operating system-level and Oracle database credentials. Provide these, and click Continue to begin the recovery process.
In the first step of performing whole database recovery, Perform Recovery:Point in Time, you specify whether to recover all transactions to your database as of the present time (which is called complete recovery), or only transactions up through some point in time in the past (which is called point-in-time recovery). Point-in-time recovery is an advanced recovery technique that lets you return the database to its state before some unwanted major change. For more details about point-in-time recovery, see Oracle Database Backup and Recovery Advanced User's Guide. For this example, however, recover the database to the current point in time. Click Continue after making your selection.
In the next step, Perform Recovery: Rename, you can rename the files being restored from backup. You can specify a new path for the files, restoring them to a different directory. For this example, however, restore the files to the default location, which is its location before the restore operation. Click Next to continue.
Finally, on the Perform Recovery: Review page, you can review the options you chose, and view the RMAN script that will be run to carry out your requested restore and recovery action. Click Submit to start the recovery process.
The process for recovering a database with a lost SPFILE or control file is similar to recovery of all datafiles. If you have lost a control file, your database is definitely down. On attempting to start an Enterprise Manager session to your database, you will encounter the warning that the database state is unavailable, and you must choose between Startup and Perform Recovery. Because you do not know why your database is down, click Startup. Enterprise Manager attempts to open the database. In the process, it will detect a lost or unusable SPFILE or control file. If the startup fails, choose Perform Recovery and Enterprise Manager will prompt you to restore the lost files. If you are not using a recovery catalog, you will have to restore these files from autobackup. For this process, you will need the DBID and DB_UNIQUE_NAME you recorded in "Recording Your DBID and DB_UNIQUE_NAME". You may also need to provide Enterprise Manager the location where you directed control file autobackups, which can be the location of the flash recovery area or some other location on disk of your choosing.
Oracle Flashback Table lets you revert one or more tables back to their contents at a previous time, without affecting other objects in your database. This recovery technique lets you recover from logical data corruptions, such as erroneously inserting rows into a table or deleting data from a table. Flashback Table lets you return tables you select to their state at a past point in time, without undoing desired changes to the other objects in your database, as would be required by a point-in-time recovery of the entire database. Also, unlike point-in-time recovery, your database remains available during the operation.
For this example, we will perform Flashback Table on the EMPLOYEES table in the HR schema. Assume that an erroneous update shortly after 23 October 2004, 15:30:00 has changed the LASTNAME column for all employees to an empty string and you need to return the original LASTNAME values to the table.
Before you can perform Flashback Table, you must ensure that row movement is enabled on the table to be flashed back.
To enable row movement, or if you do not know whether row movement is enabled, follow these steps:
From the database home page, choose the Administration page. Then, under Schema, click Tables to administer tables. To find the target table for flashback table, you can enter one or both of the schema name in the Type field and the table name in the Object Name field. Then click OK to search for the table. You may need to page through the search results to find your table.
After you find your table in the schema, click the name of the table in the Table Name column of the search results. Enterprise Manager displays a page with several property pages for administering different aspects of the table. Select the Options property page.
Make sure Enable Row Movement is set to Yes, and click Apply to update the options for the table. When the page has refreshed, you can click Tables in the breadcrumb at the top of the page to return to the search results, and enable row movement on more tables by repeating these steps for each table.
From the Maintenance property page of the database home page, under the Backup/Recovery heading, select Perform Recovery, and on the Perform Recovery: Type page, select Tables for the object type. Choose the Flashback Existing Tables option and click Continue.
On the Perform Recovery: Point-in-time page, you must choose the target time for your Flashback Table operation. If you do not know the time at which the unwanted changes occurred, you can investigate the history of transactions affecting this table by choosing Evaluate row changes and transactions to decide upon a point in time. A feature called Oracle Flashback Versions Query lets you review all recent changes to the target table. However, the use of this feature is beyond the scope of this manual.
For this example, assume that the time of the corruption is known to be Oct. 3, 2004, 11:53AM. In the form offered, select Flashback to a timestamp, and enter your target time. Click Next to continue with the Flashback Table process.
Now, on the Perform Recovery: Flashback Tables page, specify the target tables for Flashback Table, by entering table names (one on each line) in the Tables to Flashback text box. You can also click Add Tables and search for more tables to add. For this example, manually enter the HR.EMPLOYEES table in the Tables to Flashback text box. Click Next to continue with the Flashback Table process.
If your table has other dependent tables, you next see the Dependency Options page, where you are asked how they should be handled. Your choices are Cascade (flashing back any dependent tables), Restrict (flashing back only the target table), or Customize (selecting which dependent tables to flashback and which to leave as they are). You can click Show Dependencies to see which tables will be affected. How you proceed at this point will depend upon your application.
HR.EMPLOYEES has dependent tables HR.JOBS and HR.DEPARTMENTS. For this example, assume that it is safe to cascade any changes, flashing back those two tables as well as the HR.EMPLOYEES table. Note that row movement must be enabled on all affected tables, not just the initial target table. Click Next to continue. The next page to appear is the Perform Recovery: Review page, where you have a chance to confirm the Flashback Table operation to be performed. The target timestamp and tables to be flashed back are displayed. Click Submit to perform the actual Flashback Table operation. When the operation is completed, a Confirmation page appears announcing the results. Click OK to return to the database home page.
Oracle Flashback Drop lets you reverse the effects of dropping a table, returning the dropped table to the database along with its dependent objects such as indexes and triggers. It works by storing dropped objects in a Recycle Bin, from which they may be retrieved until the Recycle Bin is purged, either explicitly or because space is needed for new database objects.
As with Flashback Table, Flashback Drop can be used while the rest of your database remains open, and without undoing desired changes in objects not affected by the Flashback Drop operation. It is more convenient than forms of recovery that require taking the database offline and restoring files from backup.
Note:For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, tables in the SYSTEM tablespaces cannot recovered using Flashback Drop regardless of the tablespace type.
From the Maintenance property page of the database home page, under the Backup/Recovery heading, select Perform Recovery, and on the Perform Recovery: Type page, select Tables for the object type. For the Operation Type, choose Flashback dropped tables. Then click Continue. This takes you to the Perform Recovery: Dropped Objects Selection page.
On the Perform Recovery: Dropped Objects Selection page, you can gain access to the objects in the Recycle Bin. The Search form lets you search among the dropped objects in the Recycle Bin for the objects you want to recover. Provide values for one or both of the Schema Name and Table fields, and click Go to search.
When the page refreshes, the Results section lists the objects matching your search. If you only see the Recycle Bin listed, click the arrow next to the Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects. You can also click Expand All to see all objects in the Recycle Bin, including both dropped tables and dependent objects such as indexes and triggers. For each table listed, you can click View Content in the Operation column, to see its contents.
To select one or more tables for Flashback Drop, click the checkbox next to each table. (When a table is retrieved from the Recycle Bin, all of the dependent objects for the table that are in the recycle bin are brought back as well. They cannot be retrieved separately.) When you have selected all of your objects to restore, click Next.
On the Perform Recovery: Rename page, you have the opportunity to specify new names for any dropped objects you are returning to your database. The primary reason for renaming objects being retrieved from the recycle bin is if you have created new tables with the same names as tables being retrieved. Enter new names as needed in the New Name field in the list of tables being flashed back. Then click Next to continue.
On the Perform Recovery: Review page, you are offered an impact analysis, showing the full set of objects to be flashed back, including the dependent objects, as well as the names they will have when the Flashback Drop operation is complete. If you are satisfied with the changes listed, click Submit to perform the Flashback Drop.
When the process is complete, you arrive at a confirmation page indicating the success of the operation. Click OK to return to the database home page.
Managing backups consists of two things: managing the backups themselves as they exist on disk or tape, and managing the record of backups kept in the RMAN repository. A backup recorded in the RMAN repository can be in one of three states:
Unavailable, meaning that the backup is temporarily not available for data recovery operations (because, for example, it is stored on a tape that is stored off-site or a disk that is not mounted at the moment).
Backups can also be obsolete or not. An obsolete backup is one which, based on the currently configured retention policy, is no longer needed to satisfy data recovery goals.
Backups that no longer exist (because they were deleted from disk, because a tape was lost, and so on) must be removed from the RMAN repository. Otherwise, RMAN may discover in the middle of a database recovery operation that a needed backup is not in fact available.
Datafiles or archived redo logs copied at the operating system level can be cataloged, which adds them to the RMAN repository so that they can be used in data restore and recovery operations just as if they had been created with RMAN.
Backup maintenance functions provided through Enterprise Manager includes the following:
Viewing lists of backups (backup sets and image copies) recorded in the RMAN repository
Deleting the record of expired backups from your RMAN repository;
Deleting obsolete backups from the repository and from disk.
Note that if you use a flash recovery area for your backup storage, many maintenance activities are reduced or eliminated because of the flash recovery area's automatic management of disk space and which files are needed according to the retention policy.
To access backup management functions, from the database home page, choose the Maintenance property page, and then in the Backup/Recovery category, click Manage Current Backups.
The Manage Current Backups page has two property pages you can choose: Backup Set (the initial view) and Image Copy. Each serves a similar purpose, listing the backups stored as backup sets or image copies, according to the record in the Recovery Manager repository. Figure 9–2, "Manage Current Backups Page" shows a typical view of the Backup Set page:
Figure 9-2 Manage Current Backups Page
Backup sets are identified by their tags and completion times. To view information about which files are backed up in a backup set, drill down by clicking the value in the Contents column. For information about the individual backup pieces in a backup set, click the number of pieces in the Pieces column. Individual pieces will be listed by filename.
You can filter the list of backup sets by their contents, their status, and completion time. This makes it easier, for example, to determine what backups you have available for your control file and SPFILE.
You can also crosscheck or delete individual backups, or mark individual backups as unavailable if you know that they are temporarily not accessible by RMAN. Simply click the Select checkbox next to the file, and click the appropriate action button at the top of the Results list.
The Image Copy property page presents similar functionality to the Backup Sets property page. The focus in this discussion will be on commands in the Backup Sets property page, where they are substantially similar to those for image copies.
Like the backup and restore commands, the commands to crosscheck, delete and change the status of backups are ultimately RMAN commands and are submitted as RMAN jobs. Upon selecting these tasks, you will go to a page where you schedule and submit the RMAN job for execution, just as you would schedule a backup job. In some cases executing a maintenance task, such as crosschecking files stored on tape, may take considerable time. Depending on the task, scheduling tasks for later execution may be preferable to running them immediately and waiting. Also, some tasks, such as periodic crosschecks of your backups, should be among the regularly scheduled components of your backup strategy, as a way of being certain that your records of your backups in the RMAN repository are correct. Without that record, RMAN's recovery capabilities are severely limited.
Crosschecking a backup causes RMAN to verify that the actual physical status of the backup matches the record of the backup in the RMAN repository. For example, if a backup on disk has been deleted with an operating system command and is therefore no longer available for use in restore operations, then crosschecking that file detects this condition. After the crosscheck operation, the RMAN repository correctly reflects the state of the backups on disk or tape.
Backups to disk are marked AVAILABLE if they are still present on disk in the location listed in the RMAN repository, and if they have no corruption in the file header. Backups on tape are listed as AVAILABLE if they are still found on tape (though the file headers are not checked for corruption). Backups that are missing or corrupt are marked EXPIRED.
Click Crosscheck All at the top of the page to crosscheck all files in the RMAN repository. Note that, especially in installations where tape backups are in use, crosschecking may take a long time, because it requires access to all storage media containing backups listed in the repository. Because it can take a long time, crosschecking all files is handled as a scheduled job. When you click Crosscheck All you arrive at the Crosscheck All: Specify Job Parameters page, from which you can schedule regular crosschecks or run one immediately.
You can also crosscheck individual files, by clicking the Select checkbox next to them in the Results list and clicking Crosscheck at the top of the Results list. Unlike Crosscheck All, the crosscheck of individual files is performed immediately rather than scheduled.
Deleting expired backups removes from the RMAN repository those backups which are marked EXPIRED, that is, those which RMAN found to be inaccessible during a crosscheck operation. (No attempt is made to delete the files containing the backup from disk or tape; this command updates only the RMAN repository.)
To delete expired backups, click Delete All Expired at the top of the Manage Current Backups page. Note that this will delete both expired backup sets and expired image copies from the RMAN repository, regardless of whether you are viewing the Backup Sets or Image Copies property page when you click Delete All Expired.
When you select Delete All Expired, you arrive at a page titled Delete All Expired: Specify Job Parameters. Along with the usual scheduling options for an RMAN job, you have a checkbox Perform the operation 'Crosscheck All' before 'Delete All Expired'. Checking this box will cause the operation to take longer, but by performing the crosscheck operation immediately before deleting expired backups from the repository, RMAN will have the most up-to-date information possible about which backups are expired.
To delete obsolete backups, that is, those backups no longer needed to meet your retention policy, click Delete All Obsolete at the top of the Manage Current Backups page. All obsolete backups (both backup sets and image copies) will be deleted, regardless of whether you clicked Delete All Obsolete while viewing the Backup Set or Image Copy property page on the Manage Current Backups page.
When you click Delete All Obsolete, you arrive at the Delete All Obsolete: Specify Job Parameters. You can run the deletion job immediately or schedule it as you would a backup job.
Note that if you use a flash recovery area as your sole disk-based backup destination, you will never need to delete obsolete backups from disk. The automatic space management of the flash recovery area will keep files as specified by the backup retention policy, and then only delete them when space is needed.
If you know that some individual backup is unavailable because of a temporary condition, such as a disk drive that is temporarily offline or a tape stored off-site, you can mark the backups stored there as UNAVAILABLE. RMAN will keep these backups in the RMAN repository (and not delete them when you delete expired backups) but will not try to use them in recovery operations. When the backup becomes available again you can change its state back to AVAILABLE.
To mark backups as unavailable, click the Select checkbox next to each backup in the Results list of backups, and select Change to Unavailable.
Note that you cannot mark backups stored in the flash recovery area as UNAVAILABLE.
You may have backups in your flash recovery area or in some other destination which are not in your RMAN repository but which could be used by RMAN. For example, you might have made image copies of some or all of your datafiles at the operating system level. RMAN can use such image copies once they are recorded in the repository.
To add backups to the catalog, select Catalog Additional Files at the top of the Manage Current Backups page. On the Catalog Additional Files page, you can either select Catalog all files in the recovery area into the Recovery Manager repository, or Catalog files in the specified disk location into the Recovery Manager repository. In the latter case, you must enter a path to a disk location in the Starts With field.
Note:This path is not necessarily a complete directory name. You can enter a complete filename here to catalog a single file, such as /tmp/dfcopy1.df, or, if you have a series of datafile copies /tmp/dfcopy1.df, /tmp/dfcopy2.df, and so on, you could enter /tmp/dfcopy and match them all. Likewise, if you had a series of directories /tmp/backups1, /tmp/backups2, and so on through /tmp/backups9, each of which contained files to be cataloged, you could enter /tmp/backups and all files whose full path name began with /tmp/backups would be cataloged.
Note also that this could also match directories and files you did not intend, such as /tmp/backupslist.txt or /tmp/backups/ignore. Use care in choosing your value for Starts With.
When you click OK, RMAN immediately catalogs the files you specified.
Oracle by Example (OBE) has a series on the Oracle Database 2 Day DBA book. This OBE steps you through the tasks in this chapter, and includes annotated screen shots.
To view the Backup and Recovery OBE, point your browser to the following location: