Chapter 9: Performing Backup and Recovery

Purpose

This chapter introduces you to Oracle Database backup and recovery operations as performed through Enterprise Manager.

Topics

This chapter discusses the following:

Configuring the Flash Recovery Area and ARCHIVELOG mode
Configuring Backup Settings and Policies
Determining Your DBID and the value of DB_UNIQUE_NAME
Performing a Whole Database Backup
Backing Up Your Database Using the Oracle-Suggested Backup Strategy
Restoring and Recovering Your Whole Database
Performing Flashback Table
Performing Flashback Drop
Managing Backups

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.

Configuring the Flash Recovery Area and ARCHIVELOG Mode

Back to Topic List

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

Move your mouse over this icon to see the image

 

2.

Log in to Enterprise Manager Database Console by opening your browser and specifying the SYS username and password as SYSDBA. Click Login.

Move your mouse over this icon to see the image

 

3.

Click Maintenance on your Oracle Database Home page.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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>
Flash Recovery Area Size: <required size for your flash recovery area>

Move your mouse over this icon to see the image

 

6.

Scroll up to the top of the window. Check ARCHIVELOG Mode to configure ARCHIVELOG mode for your database. Click Apply.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

8.

The Restart Database:Specify Host and Target Database Credentials page is displayed. Enter your host credentials and database credentials. Click OK.

Move your mouse over this icon to see the image

 

9.

The Restart Database:Confirmation page is displayed. Click Yes to shut down the instance and restart it.

Move your mouse over this icon to see the image

 

10.

The Restart Database:Activity Information page is displayed. Click Refresh to log in to Enterprise Manager Database Control.

Move your mouse over this icon to see the image

 

 

Configuring Backup Settings and Policies

Back to Topic List

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.

Move your mouse over this icon to see the image

 

2.

Select Backup Settings in the Backup/Recovery Settings section.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

 

Determining Your DBID and the Value of DB_UNIQUE_NAME

Back to Topic List

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.

Move your mouse over this icon to see the image

 

2.

The Current property page appears. Enter db_unique_name in the Name field and click Go.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

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.

Move your mouse over this icon to see the image

 

2.

The Control Files property page appears. Select the Advanced property page.

Move your mouse over this icon to see the image

 

3.

The Database ID field contains the DBID value.

Move your mouse over this icon to see the image

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.

 

 

Back to Topic List

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.

Move your mouse over this icon to see the image

 

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 .

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

4.

The Schedule Backup: Settings page appears. Select Disk or Tape as appropriate. Click Next.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

6.

The Schedule Backup: Review page appears. Click Submit Job.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

Back to Topic List

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.

Move your mouse over this icon to see the image

 

2.

The Schedule Backup page appears. Click Schedule Oracle-Suggested Backup.

Move your mouse over this icon to see the image

 

3.

The Schedule Oracle-Suggested Backup: Destination page appears. Select Disk for your backup destination. Click Next.

Move your mouse over this icon to see the image

 

4.

The Schedule Backup: Setup page appears. Review the information and click Next.

Move your mouse over this icon to see the image

 

5.

The Schedule Backup: Schedule page appears. Review the information and adjust the start date and time as appropriate. Click Next.

Move your mouse over this icon to see the image

 

6.

The Schedule Backup: Review page appears. Review the information and click Submit Job.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

 

Back to Topic List

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

3.

The Confirmation page is displayed indicating that the instance will be shut down and restarted. Click Yes to continue.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

6.

The Perform Whole Database Recovery: Point-in-time page appears. Select Recover to the current time. Click Next.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

8.

The Perform Whole Database Recovery: Review page appears. Review the information and click Submit.

Move your mouse over this icon to see the image

 

9.

The Processing: Perform Whole Database Recovery page is displayed.

Move your mouse over this icon to see the image

 

10.

You receive the "Operation succeeded" message. Click Open Database to open the database.

Move your mouse over this icon to see the image

 

11.

You receive confirmation that the database is open. Click OK.

Move your mouse over this icon to see the image

 

12.

Enter the login information. Click Login. You are returned to the Maintenance page.

Move your mouse over this icon to see the image

 

 

Back to Topic List

Complete the following tasks to perform a flashback table operation:

Enable Row Movement
Simulate User Error
Perform Flashback Table

Enable Row Movement

Back to List

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.

Move your mouse over this icon to see the image

 

2.

The Administration property page appears. Scroll down to the Schema section. Select Tables in the Database Objects section.

Move your mouse over this icon to see the image

 

3.

The Tables page appears. Enter HR in the Schema field and REGIONS in the Object Name field. Click Go.

Move your mouse over this icon to see the image

 

4.

The REGIONS table is displayed in the Results section. Click Edit.

Move your mouse over this icon to see the image

 

5.

The Edit Table page appears. Click the Options tab.

Move your mouse over this icon to see the image

 

6.

Select Yes from the Enable Row Movement list. Click Apply.

Move your mouse over this icon to see the image

 

7.

You receive a message indicating your table has been successfully modified. Click the Tables breadcrumb.

Move your mouse over this icon to see the image

 

Simulate User Error

Back to List

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;

Move your mouse over this icon to see the image

 

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;

Move your mouse over this icon to see the image

 

3.

View your change by executing the following command again:

select * from regions;

Move your mouse over this icon to see the image

In the Performing Flashback Table section, you will flashback the table to the point in time before you updated the table.

 

Performing Flashback Table

Back to List

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

3.

The Perform Object Level Recovery: Flashback Tables page appears. Review the information on the page and click Next.

Move your mouse over this icon to see the image

 

4.

The Perform Object Level Recovery: Review page appears. Review the information and click Submit.

Move your mouse over this icon to see the image

 

5.

You receive a confirmation that the table has been flashed back. Click OK.

Move your mouse over this icon to see the image

 

6.

Return to your SQL*Plus session and execute the following command to view the results of the flashback table operation:

select * from regions;

Move your mouse over this icon to see the image

 

Back to Topic List

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.

Move your mouse over this icon to see the image

 

2.

Select Create Like from the Actions list. Click Go.

Move your mouse over this icon to see the image

 

3.

The Create Table page appears. Enter REG_HIST in the Name field. Deselect Not Null for the REGION_ID column. Click Constraints.

Move your mouse over this icon to see the image

 

4.

The Constraints page appears. Delete the constraints on the table by selecting each and clicking delete. They are not needed for this exercise.

Move your mouse over this icon to see the image

 

5.

Click OK to create the REG_HIST table.

Move your mouse over this icon to see the image

 

6.

You receive a message indicating the table has been created. Enter REG_HIST in the Object Name field and click Go.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

8.

Select Delete the table definition, all its data, and dependent objects (DROP). Click Yes to confirm the deletion of the table.

Move your mouse over this icon to see the image

 

9.

A message is displayed indicating the table has been deleted. Click Go to attempt to retrieve the table.

Move your mouse over this icon to see the image

 

10.

No object found is displayed in the results section.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

2.

Enter HR in the Schema Name field and click Go.

Move your mouse over this icon to see the image

 

3.

Select REG_HIST and click Flashback Drop.

Move your mouse over this icon to see the image

 

4.

The Perform Object Level Recovery: Rename page appears. Click Next.

Move your mouse over this icon to see the image

 

5.

The Perform Recovery: Review page appears. Review the information and click Submit.

Move your mouse over this icon to see the image

 

6.

A confirmation message is displayed. Click OK.

Move your mouse over this icon to see the image

 

7.

The table is no long in the recycle bin. Click the Tables breadcrumb.

Move your mouse over this icon to see the image

 

8.

The REG_HIST table is now included in the tables list.

Move your mouse over this icon to see the image

 

 

Managing Backups

Back to Topic 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

 

Using the Manage Current Backups Page

Back to Topic List

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

3.

The Contents property page is displayed. Click Manage Current Backups to return to the Manage Current Backups property page.

Move your mouse over this icon to see the image

 

4.

Click Image Copies to view the Image Copies page.

Move your mouse over this icon to see the image

 

5.

The Image Copies page appears displaying the image copies that are recorded in the RMAN repository.

Move your mouse over this icon to see the image

Click the Database Instance link to return to the Maintenance page.

 

 

Crosschecking Backups

Back to Topic List

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

5.

In the Summary region you can view the status of the job.

Move your mouse over this icon to see the image

Click the Database tab to return to the Home page.

 

 

 

Deleting Expired Backups

Back to Topic List

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

5.

In the Summary region you can view the status of the job.

Move your mouse over this icon to see the image

Click the Database tab to return to the Home page.

 

 

Deleting Obsolete Backups

Back to Topic List

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

5.

In the Summary region you can view the status of the job.

Move your mouse over this icon to see the image

 

6.

You can return to the Image Copies and/or Backup Sets property pages to verify that the obsolete backups have been deleted.

 

 

Marking Backups as UNAVAILABLE

Back to Topic List

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.

Move your mouse over this icon to see the image

 

2.

The Manage Current Backups page is displayed. Select the backup that you wish to mark as UNAVAILABLE and click Change to Unavailable.

Move your mouse over this icon to see the image

 

3.

A Confirmation page is displayed. Click Yes to continue the operation.

Move your mouse over this icon to see the image

 

4.

The Request in process page is displayed.

 

 

 

 

 

Cataloging Backups

Back to Topic List

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;

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

3.

Take the EXAMPLE tablespace out of online backup mode by issuing the following command:

ALTER TABLESPACE example END BACKUP;

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

5.

Select Catalog Additional Files at the top of the Manage Current Backups page.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

7.

The Request in process page is displayed.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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 see the image

 

 

Move your mouse over this icon to hide all screenshot