Chapter 9: Performing Backup and Recovery

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

Approximately 1 hour

Topics

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Before you perform this tutorial, you should:

1.

Complete Chapter 2: Installing Oracle Software and Building the Database OBE

2.

Complete Chapter 3: Getting Started with Oracle Enterprise Manager OBE

3.

Complete Chapter 4: Configuring the Network Environment OBE

4.

Complete Chapter 5: Managing the Oracle Instance OBE

5.

Complete Chapter 6: Managing Database Storage Structures OBE

6.

Complete Chapter 7: Administering Users and Security OBE

7.

Complete Chapter 8: Managing Schema Objects OBE

Back to Topic List

Configuring the Flash Recovery Area and ARCHIVELOG Mode

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, increase the size for the backup and recovery areas. Execute the following commands:

sqlplus system/oracle
alter system set db_recovery_file_dest_size=8G scope=both;
exit

 

2.

At the operating system prompt, create a directory for the flash recovery area. Execute the following commands:

cd $ORACLE_BASE
mkdir flash_rec_area

 

3.

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

 

4.

Click Availability on your Oracle Database Home page.

 

5.

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.

 

6.

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: 8 GB

 

7.

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

 

8.

The Verify Flash Recovery Area Location: Specify Host Credentials window appears. Enter the host credentials to ensure appropriate access has been granted. Click Continue.

 

9.

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.

 

10.

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

 

11.

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

 

12.

The Restart Database:Activity Information page is displayed. Click Refresh to log in to Enterprise Manager Database Control and log back into Enterprise Manager once the database has been restarted.

 

Back to Topic List

Configuring Backup Settings and Policies

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 Availability on your Oracle Database Home page.

 

2.

Select Backup Settings in the Backup/Recovery Setup 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. 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. Under the Archivelog Deletion Policy, select Delete archivelogs after it has specified number of backups and accept the default of 1 backup. Click OK. You are returned to the Availability page.

 

Back to Topic List

Determining Your DBID and the Value of DB_UNIQUE_NAME

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 the Server tab. Click Initialization Parameters in the Database Configuration region.

 

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 Server page.

 

Perform the steps below to determine your DBID:

1.

Click Control Files in the Storage region of the Server page.

 

2.

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

 

3.

The Database ID field contains the DBID value. (Note: Your machine will have a different 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 Server page.

 

Back to Topic List

Performing a Whole Database Backup

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 Availability tab. Select Schedule Backup in the Manage 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.

 

8.

Click View Job to view the status of the backup.

 

9.

Scroll down to the bottom of the page. In this table, you can view the status of the backup and if each step completes successfully. Note that backing up the database may take some time to complete.

Before continuing with the next exercise, ensure that you have gone through the entire process until all three steps of the backup (Prebackup, Backup, and Post Backup) are successful.

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

 

Back to Topic List

Backing Up Your Database Using the Oracle-Suggested Backup Strategy

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.

Click the Availability tab. Select Schedule Backup in the Manage region.

 

2.

The Schedule Backup page appears. Scroll down to the Host Credentials section and enter the username and password if required. Scroll back up and 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 (set the date for a couple day's from the current date). 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 Availability page.

 

Back to Topic List

Restoring and Recovering Your Whole Database

In this section you will recover your database by using Enterprise Manager.

1.

Select Perform Recovery in the Manage region of the Availability page.

 

2.

The Perform Recovery page appears. Under Recovery Scope, select Whole Database. 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 Recover.

 

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 (Note: you must wait a couple of minutes for this operation to complete). Click Refresh to continue using the Recovery Wizard.

 

5.

Click Home. You are returned to the Database Instance page displaying that the database is in Mounted state. Click Perform Recovery.

 

6.

Enter the host credentials and click Continue.

 

7.

The Database Login window appears. Enter the appropriate credential information and click Login.

 

8.

The Perform Recovery page appears.Under Recovery Scope, select Whole Database and click Recover again to invoke the Recovery wizard.

 

9.

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

 

10.

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.

 

11.

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

 

12.

The Processing: Perform Whole Database Recovery page is displayed.

 

13.

You receive a confirmation that the operation succeeded. Click Open Database.

 

14.

Click OK.

 

15.

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

 

Back to Topic List

Performing Flashback Table

Complete the following tasks to perform a flashback table operation:

A Enable Row Movement
B. Simulate User Error
C. Perform Flashback Table

Enable Row Movement

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 Schema tab.

 

2.

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.

 

Back to Topic

Simulate User Error

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/oracle
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.

 

Back to Topic

Performing Flashback Table

In this section you will flashback to 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 window appears. Click Next.

 

4.

The Perform Object Level Recovery: Review page appears. Review the information on the page 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;

 

Back to Topic

Performing Flashback Drop

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. Click OK.

 

5.

You receive a message indicating the table has been created.

 

6.

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.

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

 

Back to Topic List

Managing Backups

Indexes are optional structures associated with tables and can be used to improve query performance. An index provides a quick access path to table data. Indexes can be created on one or more columns of a table. After an index is created, it is automatically maintained and used by the Oracle Database server. Changes to a table’s data or structure are automatically incorporated into all relevant indexes with complete transparency to the user.

In this section, you will perform the following tasks:

A Using the Manage Current Backups Page
B. Crosschecking Backups
C. Deleting Expired Backups
D. Deleting Obsolete Backups
E. Cataloging Backups

Using the Manage Current Backups Page

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 Availability property page. Click Manage Current Backups in the Backup/Recovery - Manage section.

 

2.

The Manage Current Backups page appears. The Backup Sets property page displays the backup sets that are recorded in the RMAN repository (note that your system may display different information). Click the links in the Tag column to view detailed information about the contents of a backup set (select one that displays "DATAFILES" in the Contents column).

 

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.

If there are any Image Copies available, they are displayed in the Manage Current Backups page. Note for this example, the backup was already restored and thus deleted.

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

 

Back to Topic

Crosschecking Backups

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 on the Availability property page.

 

2.

The Manage Current Backups page is displayed. Scroll down to the Host Credentials section and enter the username and password if required. Click Crosscheck All at the top of the page to crosscheck all files in the RMAN repository. (Note: Your system may display different information for the backup sets).

 

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. Note that this process may take a couple of minutes to complete.

 

5.

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

Click the Database tab to return to the Home page.

 

Back to Topic

Deleting Expired Backups

You can delete all backups marked EXPIRED in the RMAN repository by performing the following steps:

1.

Click Manage Current Backups in the Backup/Recovery - Manage region on the Availability property page.

 

2.

The Manage Current Backups page is displayed. Scroll down to the Host Credentials section and enter the username and password if required. 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.

 

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.

 

Back to Topic

Deleting Obsolete Backups

You can delete all obsolete backups by performing the following steps:

1.

Click Manage Current Backups in the Backup/Recovery - Manage region on the Availability property page.

 

2.

The Manage Current Backups page is displayed. Scroll down to the Host Credentials section and enter the username and password if required. 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.

 

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.

 

6.

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

Back to Topic

Cataloging Backups

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 - Manage region on the Availability property page.

 

5.

Scroll down to the Host Credentials section and enter the username and password if required. Click 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.

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.

 

8.

Your newly cataloged file is listed in the Results section. Click Database to return to the Database Home page.

 

Back to Topic

Turn Off Archiving

To modify the database so it runs in NOARCHIVELOG mode, perform the following:

1.

Click Availability on your Oracle Database Home page.

 

2.

Select Recovery Settings in the Backup/Recovery - Setup section.

 

3.

Deselect ARCHIVELOG Mode under Media Recovery. Click Apply.

 

4.

A message confirming your changes is displayed. To complete the configuration of NOARCHIVELOG mode, the database instance must be shut down. Click Yes to shut down the instance and restart it.

 

5.

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

 

6.

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

 

7.

The Restart Database:Activity Information page is displayed. Click Refresh to log in to Enterprise Manager Database Control and log back into Enterprise Manager once the database has been restarted.

 

Back to Topic List

In this tutorial, you learned how to:

Configure the flash area.
Configure backup policies.
Confirm the DBID and the DB_UNIQUE_NAME values..
Perform a whole database backup.
Restore and recover the database.
Perform a Flashback table.
Manage your backups including deleting obsolete backups or using the cataloging feature.

Back to Topic List

Place the cursor over this icon to hide all screenshots.