This chapter introduces you to Oracle Database backup and recovery operations as performed through Enterprise Manager.
This chapter discusses the following:
Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.
The flash recovery area is a unified storage location for
all recovery related files and activities in an Oracle database. All files that
are needed to completely recover a database from a media failure are part of
the flash recovery area. The recovery related files that can be created in the
flash recovery area include: archived redo log files, control files, backups
created by Recovery Manager (RMAN), flashback logs, and the change tracking
file. Oracle recommends the change tracking file be created on the same disks
as the database files. In fact, if you set up OMF, and you issue the command
to enable change tracking, the file is automatically created in the directory
specified for the database files.
By allocating a storage location and unifying related recovery files within a specific area, the Oracle database server relieves the database administrator from having to manage the disk files created by these components.
When setting up a flash recovery area, you choose a directory, file system or Automatic Storage Management disk group to hold the files, and set a disk quota for the maximum space to be used for all files stored in the flash recovery area. You must choose a location large enough to accommodate the required disk quota. When the disk space limit is approached, the Oracle server can delete nonessential files to make room for new files, subject to the limitations of the RMAN retention policy.
The flash recovery area should be on a separate disk from the working area, where active database files such as datafiles, control files, online redo logs, and change tracking files used in incremental backups are stored. Keeping the flash recovery area on the same disk as the working area exposes you to loss of both your live database files and backups in the event of a disk failure.
You can configure the flash recovery area and ARCHIVELOG mode when you create the database. If you did not perform these tasks when you created the database, follow the steps below to configure the flash recovery area and ARCHIVELOG mode:
1. |
At the operating system prompt, create a directory for the flash recovery area. Execute the following commands: cd $ORACLE_BASE mkdir flash_rec_area
|
2. |
Log in to Enterprise Manager Database Console by opening your browser and specifying the SYS username and password as SYSDBA. Click Login.
|
3. |
Click Maintenance on your Oracle Database Home page.
|
4. |
Select Recovery Settings in the Backup/Recovery Settings section. You can use the Recovery Settings page to configure
the settings for the recovery utility, including the Flash Recovery Area.
|
5. |
The Recovery Settings page appears. Scroll to the Flash Recovery section. If the flash recovery area is not enabled for your database, configure the flash recovery area by entering the following values in the appropriate fields: Flash Recovery Area Location: <directory you created
in step 1>
|
6. |
Scroll up to the top of the window. Check ARCHIVELOG Mode to configure ARCHIVELOG mode for your database. Click Apply.
|
7. |
A message confirming your changes is displayed. To complete the configuration of ARCHIVELOG mode, the database instance must be shut down. Click Yes to shut down the instance and restart it.
|
8. |
The Restart Database:Specify Host and Target Database Credentials page is displayed. Enter your host credentials and database credentials. Click OK.
|
9. |
The Restart Database:Confirmation page is displayed. Click Yes to shut down the instance and restart it.
|
10. |
The Restart Database:Activity Information page is displayed. Click Refresh to log in to Enterprise Manager Database Control.
|
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 recovery area. You can also configure features to improve backup performance.
1. |
Click Maintenance on your Oracle Database Home page.
|
2. |
Select Backup Settings in the Backup/Recovery Settings section.
|
3. |
Scroll to the Host Credentials region on the Backup Settings page. Enter the operating system username and password. Scroll up to the Disk Settings section.
|
4. |
Accept the value of 1 in the Parallelism field in the Disk Settings section on the Device page. The Disk Backup Location field is set to null so that the flash recovery area will be used for backups. Select Backup Set for Disk Backup Type. Click Test Disk Backup.
|
5. |
A message is displayed indicating the disk settings backup test was successful. Now you will configure backup policy settings. Click Policy to access the Policy page.
|
6. |
Select Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change. Select Optimize the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up. Select Enable block change tracking for faster incremental backups. Enter a file name for the Block Change Tracking file. Then scroll down to the Retention Policy section.
|
7. |
Select Retain backups that are necessary for a recovery to any time within the specified number of days (point-in-time recovery) and accept the default of 31 days. Click OK. You are returned to the Maintenance page.
|
If you lose your database control file or SPFILE, Enterprise Manager can restore them from backup, as long as you can provide the DB_UNIQUE_NAME and DBID for your database.
Perform the steps below to determine the value of DB_UNIQUE_NAME:
1. |
Click All Initialization Parameters in the Database Configuration region on the Administration page.
|
2. |
The Current property page appears. Enter db_unique_name in the Name field and click Go.
|
3. |
The resulting page displays a row with db_unique_name in the Name column, and the db_unique_name value for your database in the Value column. Record this value so that you will have it should you need it in the future for a recovery operation. Click the Database Instance link to return to the Administration page.
|
Perform the steps below to determine your DBID:
1. |
Click Control Files in the Storage region
of the Administration page.
|
2. |
The Control Files property page appears. Select the Advanced property page.
|
3. |
The Database ID field contains the DBID value. Record this value so that you will have it should you need it in the future for a recovery operation. Click the Database Instance link to return to the Administration page.
|
You can backing up the entire contents of your database by performing a whole database 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. The database can be recovered completely with this set of files.
1. |
Click the Maintenance tab. Select Schedule Backup in the Backup/Recovery region.
|
2. |
The Schedule Backup page appears. In the Customized Backup section, select Whole Database. Enter the username and password in the Host Credentials section if required. Click Schedule Customized Backup .
|
3. |
The Schedule Customized Backup: Options page appears. Select Full Backup in the Backup Type section. Select Online Backup in the Backup Mode section. Select Also back up all archived logs on disk in the Advanced section. Click Next.
|
4. |
The Schedule Backup: Settings page appears. Select Disk or Tape as appropriate. Click Next.
|
5. |
The Schedule Backup: Schedule page appears. Accept the default Job Name. Select Immediately to execute the job immediately or enter a time to execute at a later time. Click Next.
|
6. |
The Schedule Backup: Review page appears. Click Submit Job.
|
7. |
The Backup Submit Successful message is displayed. You can click View Job to access the job status page or click OK to complete the operation and return to the Maintenance page.
|
The Oracle-suggested backup strategy is based on creating an image copy of your database which is rolled forward using RMAN incremental backups. Oracle Enterprise Manager schedules RMAN backups jobs. Follow the steps below to set up your backup schedule:
1. |
Select Schedule Backup in the Backup/Recovery region.
|
2. |
The Schedule Backup page appears. Click Schedule Oracle-Suggested Backup.
|
3. | The Schedule Oracle-Suggested Backup: Destination page appears. Select Disk for your backup destination. Click Next.
|
4. |
The Schedule Backup: Setup page appears. Review the information and click Next.
|
5. |
The Schedule Backup: Schedule page appears. Review the information and adjust the start date and time as appropriate. Click Next.
|
6. |
The Schedule Backup: Review page appears. Review the information and click Submit Job.
|
7. |
The Status page appears with a message indicating the job has been successfully submitted. You can click View Job to access the job status page or click OK to complete the operation and return to the Maintenance page.
|
In this section you will recover your database by using Enterprise Manager.
1. |
Select Perform Recovery in the Backup/Recovery region of the Maintenance page.
|
2. |
The Perform Recovery page appears. Select Recover to the current time or a previous point-in-time. Enter your operating system username and password in the Host Credentials section. Click Perform Whole Database Recovery.
|
3. |
The Confirmation page is displayed indicating that the instance will be shut down and restarted. Click Yes to continue.
|
4. |
The Recovery Wizard page is displayed indicating that the instance will be shut down and restarted. Click Refresh to continue using the Recovery Wizard.
|
5. |
You are returned to the Maintenance page. Click Perform Recovery. Enter the host credentials. Click Perform Whole Database Recovery again to invoke the Recovery wizard.
|
6. |
The Perform Whole Database Recovery: Point-in-time page appears. Select Recover to the current time. Click Next.
|
7. |
The Perform Whole Database Recovery: Rename page appears. Select No. Restore the files to the default location if that is your choice or select Yes. Restore the files to a new, common location and enter the location. Click Next.
|
8. |
The Perform Whole Database Recovery: Review page appears. Review the information and click Submit.
|
9. |
The Processing: Perform Whole Database Recovery page is displayed.
|
10. |
You receive the "Operation succeeded" message. Click Open Database to open the database.
|
11. |
You receive confirmation that the database is open. Click OK.
|
12. |
Enter the login information. Click Login. You are returned to the Maintenance page.
|
Complete the following tasks to perform a flashback table operation:
Enable Row Movement | ||
Simulate User Error | ||
Perform Flashback Table |
You must enable row movement on a table in order to perform a flashback table operation on the table. In this section you enable row movement on the HR.EMPLOYEES table.
1. |
Click the Administration tab.
|
2. |
The Administration property page appears. Scroll down to the Schema section. Select Tables in the Database Objects section.
|
3. |
The Tables page appears. Enter HR in the Schema field and REGIONS in the Object Name field. Click Go.
|
4. |
The REGIONS table is displayed in the Results section. Click Edit.
|
5. |
The Edit Table page appears. Click the Options tab.
|
6. |
Select Yes from the Enable Row Movement list. Click Apply.
|
7. |
You receive a message indicating your table has been successfully modified. Click the Tables breadcrumb.
|
In this section you simulate user error by changing data in the REGIONS table. Perform the following:
1. |
View data in the REGIONS table by opening a terminal window and executing the following commands: sqlplus hr/hr col region_name format a30 select * from regions;
|
2. |
Simulate user error by executing the following SQL command to change the value in the region_name column in all rows: update regions set region_name = 'ORACLE'; commit;
|
3. |
View your change by executing the following command again: select * from regions; In the Performing Flashback Table section, you will flashback the table to the point in time before you updated the table.
|
In this section you will flashback the HR.REGIONS table.
1. |
Verify that REGIONS is still selected in the list of tables on the Tables page in Enterprise Manager. Select Flashback Table from the Actions drop-down menu. Click Go.
|
2. |
The Perform Object Level Recovery: Point-in-time page appears. Select Flashback to a timestamp and enter a date and time of a few minutes ago. Click Next.
|
3. |
The Perform Object Level Recovery: Flashback Tables page appears. Review the information on the page and click Next.
|
4. |
The Perform Object Level Recovery: Review page appears. Review the information and click Submit.
|
5. |
You receive a confirmation that the table has been flashed back. Click OK.
|
6. |
Return to your SQL*Plus session and execute the following command to view the results of the flashback table operation: select * from regions;
|
In this section you will use the flashback drop feature to retrieve a table that has been dropped. For the purposes of this exercise, you will create a new table, drop the table, and then recover it using flashback drop.
Follow the steps below to create a new table and drop it:
1. |
Access the Tables property page. Enter HR in the Schema Name field and REGIONS or a part of the name in the Object Name field and click Go.
|
2. |
Select Create Like from the Actions list. Click Go.
|
3. |
The Create Table page appears. Enter REG_HIST in the Name field. Deselect Not Null for the REGION_ID column. Click Constraints.
|
4. |
The Constraints page appears. Delete the constraints on the table by selecting each and clicking delete. They are not needed for this exercise.
|
5. |
Click OK to create the REG_HIST table.
|
6. |
You receive a message indicating the table has been created. Enter REG_HIST in the Object Name field and click Go.
|
7. |
The Tables page is displayed with the REG_HIST table in the Results section. Click Delete with Options to delete the REG_HIST table.
|
8. |
Select Delete the table definition, all its data, and dependent objects (DROP). Click Yes to confirm the deletion of the table.
|
9. |
A message is displayed indicating the table has been deleted. Click Go to attempt to retrieve the table.
|
10. |
No object found is displayed in the results section.
|
To recover the table you just deleted, you will need to perform a flashback drop. Perform the following:
1. |
On the Tables page, click Recycle Bin.
|
2. |
Enter HR in the Schema Name field and click Go.
|
3. |
Select REG_HIST and click Flashback Drop.
|
4. |
The Perform Object Level Recovery: Rename page appears. Click
Next.
|
5. |
The Perform Recovery: Review page appears. Review the information and click Submit.
|
6. |
A confirmation message is displayed. Click OK.
|
7. |
The table is no long in the recycle bin. Click the Tables breadcrumb.
|
8. |
The REG_HIST table is now included in the tables list.
|
Managing backups consists of two tasks: managing the backups themselves as they exist on disk or tape, and managing the record of backups kept in the RMAN repository. In this section, you will perform backup maintenance and update the RMAN repository. You perform the following tasks in this section:
Using the Manage Current Backups Page | ||
Crosschecking Backups | ||
Deleting Expired Backups | ||
Deleting Obsolete Backups | ||
Marking Backups as UNAVAILABLE | ||
Cataloging Backups |
You can use the Manage Current Backups page to view the backups that are recorded in the RMAN repository. From this page, you can perform the backup maintenance operations described throughout this section.
1. |
Access the Maintenance property page. Click Manage Current Backups in the Backup/Recovery section.
|
2. |
The Manage Current Backups page appears. The Backup Sets property page displays the backup sets that are recorded in the RMAN repository. Click the links in the Tag column to view detailed information about the contents of a backup set.
|
3. |
The Contents property page is displayed. Click Manage Current Backups to return to the Manage Current Backups property page.
|
4. |
Click Image Copies to view the Image Copies page.
|
5. |
The Image Copies page appears displaying the image copies that are recorded in the RMAN repository. Click the Database Instance link to return to the Maintenance page.
|
When you crosscheck a backup, RMAN verifies that the information recorded in the repository is consistent with the physical backup status. If it is not, the repository is updated to reflect the correct status. You can crosscheck all of your backup files as follows:
1. |
Click Manage Current Backups in the Backup/Recovery region Maintenance property page.
|
2. |
The Manage Current Backups page is displayed. Click Crosscheck All at the top of the page to crosscheck all files in the RMAN repository.
|
3. |
The Crosscheck All: Specify Job Parameters page is displayed. You can accept the defaults for the Job Name, Job Description, Start time, and Repeat specifications or enter your own values. Click Submit Job to submit the crosscheck job.
|
4. |
A Job submission succeeded message is displayed on the Manage Current Backups page. You can click View Job to view the status of the job.
|
5. |
In the Summary region you can view the status of the job. Click the Database tab to return to the Home page.
|
You can delete all backups marked EXPIRED in the RMAN repository by performing the following steps:
1. |
Access the Maintenance property page. Click Manage Current Backups in the Backup/Recovery region.
|
2. |
The Manage Current Backups page is displayed. Click Delete All Expired at the top of the page to remove from the RMAN repository those backups which are marked EXPIRED.
|
3. |
The Delete All Expired: Specify Job Parameters page is displayed. You can accept the defaults for the Job Name, Job Description, Start time, and Repeat specifications or enter your own values. Select Perform the operation 'Crosscheck All' before 'Delete All Expired'. If you have not performed a crosscheck operation. Click Submit Job to submit the job.
|
4. |
A Job Submission Succeeded message is displayed on the Manage Current Backups page. You can click View Job to view the status of the job.
|
5. |
In the Summary region you can view the status of the job. Click the Database tab to return to the Home page.
|
You can delete all obsolete backups by performing the following steps:
1. |
Access the Maintenance property page. Click Manage Current Backups in the Backup/Recovery region.
|
2. |
The Manage Current Backups page is displayed. Click Delete All Obsolete at the top of the page to remove from the RMAN repository those backups which are obsolete. Note: You can do this from the Backup Sets or Image Copies page.
|
3. |
The Delete All Obsolete: Specify Job Parameters page is displayed. You can accept the defaults for the Job Name, Job Description, Start time, and Repeat specifications or enter your own values. Click Submit Job to submit the job.
|
4. |
A Job Submission Succeeded message is displayed on the Manage Current Backups page. You can click View Job to view the status of the job.
|
5. |
In the Summary region you can view the status of the job.
|
6. |
You can return to the Image Copies and/or Backup Sets property pages to verify that the obsolete backups have been deleted.
|
You can mark backups in the repository as UNAVAILABLE by performing the following steps:
1. |
Access the Maintenance property page. Click Manage Current Backups in the Backup/Recovery region.
|
2. |
The Manage Current Backups page is displayed. Select the backup that you wish to mark as UNAVAILABLE and click Change to Unavailable.
|
3. |
A Confirmation page is displayed. Click Yes to continue the operation.
|
4. |
The Request in process page is displayed.
|
You can catalog backups taken with operating system commands so that RMAN can use them in a recovery operation. In this example, you will back up the datafile belonging to the EXAMPLE tablespace using operating system commands. You will then catalog the backup file in the RMAN repository using Enterprise Manager.
1. |
Invoke SQL*Plus and log on as a user with SYSDBA privileges. Place the EXAMPLE tablespace in online backup mode by issuing the following command: ALTER TABLESPACE example BEGIN BACKUP;
|
2. |
Return to the operating system prompt and make a copy of the datafile that belongs to the EXAMPLE tablespace. In this example, the copy is being made into a directory called backup. You can use a directory of your choice.
|
3. |
Take the EXAMPLE tablespace out of online backup mode by issuing the following command: ALTER TABLESPACE example END BACKUP;
|
4. |
Now you are ready to catalog the backup in the RMAN repository by using Enterprise Manager Database Control. Click Manage Current Backups in the Backup/Recovery region Maintenance property page.
|
5. |
Select Catalog Additional Files at the top of the Manage Current Backups page.
|
6. |
Select Catalog files in the specified disk location into the Recovery Manager repository and enter the location and name of the backup file (without the file extension). Click OK.
|
7. |
The Request in process page is displayed.
|
8. |
A confirmation message is displayed indicating that the file has been cataloged. Click Image Copies to access the Image Copies page and view your cataloged file.
|
9. |
Your newly cataloged file is listed in the Results section. Click Database to return to the Database Home page.
|
Move your mouse over this icon to hide all screenshot