0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

OBE Home > 10gR2 Single > Availability

Performing Backups and Recovering Your Database

Purpose

This tutorial introduces Oracle Database 10g features that enable you to more easily manage backup and recovery operations.

Time to Complete

Approximately 2 hours.

Topics

This tutorial covers the following:

 Overview
 Prerequisites
 Performing Backups
 Performing Recovery of a Data File
 Flashback of the Database
 Summary

Viewing Screenshots

 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.

Overview

New Features to Support Backup and Recovery Operations

This tutorial discusses new features that have been provided to streamline backup and recovery tasks. It utilizes the Flash Recovery Area, a feature that unifies related recovery files within a specific area and simplifies database administrator tasks. This tutorial also introduces block change tracking. In previous releases of the Oracle database when you backed up the database using incremental backups, RMAN had to examine the entire data file to determine which blocks had changed. In Oracle Database 10g, you can create a block change tracking file that records the blocks modified since the last backup. RMAN uses the tracking file to determine which blocks to include in the incremental backup. The change tracking file enables RMAN to make the incremental backup time proportional to the amount of content modified since the last backup instead of being proportional to the size of the data files being backed up.

In Oracle Database 10g, flashback functionality has been extended. The Flashback Database feature provides a way for you to quickly revert your entire Oracle database to the state it was in at a past point in time. Flashback Database is discussed in detail in this tutorial. In addition to flashback operations at the database level, it is also possible to flash back an entire table. You can also quickly recover a table that has been inadvertently dropped. The existing flashback query capabilities have also been enhanced. Refer to the Recovering from Human Error tutorial for information on these additional flashback capabilities.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

Download and unzip the rman.zip file into your working directory (c:\wkdir).

Back to Topic List

Performing Backups

In this section, you will perform the following tasks:

 Configuring the Flash Recovery Area
 Enabling Block Change Tracking
 Testing Disk Backup
 Backing up the Database
 Reviewing Backup Reports
 Examining the Flash Recovery Area Usage
 Scheduling RMAN Script Executions

Back to Topic List

Configuring the Flash Recovery Area

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.

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.

Follow the steps below to verify that your database is in ARCHIVELOG mode and configure the flash recovery area.

1.

Log in to Enterprise Manager Database Console by opening your browser and entering the following URL:

                               
http://localhost:1158/em
                            

Enter sys/oracle as SYSDBA and click Login.

Move your mouse over this icon to see the image

 

2.

The Oracle Database Home page allows you to view the current state of the database by displaying a series of metrics that portray the overall health of the database. The Oracle Database Home page provides a launch point for the database status and administration and configuration of the database environment. It contains four pages via subtabs with each page displaying subsections. Click on the Maintenance tab.

Move your mouse over this icon to see the image

 

3.

Under Backup/Recovery Settings, click Recovery Settings.

Move your mouse over this icon to see the image

 

4.

You need to put the database in archivelog mode. In the Media Recovery section, select the ARCHIVELOG Mode check box. Then scroll down to the Flash Recovery Area section.

Move your mouse over this icon to see the image

 

5.

Scroll down to the Flash Recovery Area region of the page and click Enable Flashback Database. Change the Flashback Retention Time to 6 hours, then click Ap ply .

Move your mouse over this icon to see the image

 

6.

Click Yes to restart the database.

Move your mouse over this icon to see the image

 

7.

Enter your Cluster Credentials as oracle/oracle and Database Credentials as sys/oracle as SYSDBA. Check the box to Save as Preferred Credentials and then click OK.

Move your mouse over this icon to see the image

 

8.

Click Yes to confirm.

Move your mouse over this icon to see the image

 

9.

Your database is being restarted. Wait about 10 minutes and click Refresh.

Move your mouse over this icon to see the image

 

10.

Once you click refresh, the DB Control Home Page appears and you are automatically logged in as the SYS user.

Move your mouse over this icon to see the image

 

Back to Topic

Enabling Block Change Tracking

When you enable change tracking, Oracle automatically tracks which data file blocks have changed in change tracking files. When you execute BACKUP INCREMENTAL, RMAN uses the change tracking file to more quickly identify the blocks changed since the previous incremental backup. As a result, RMAN creates incremental backups much faster than in releases prior to Oracle Database 10g.

By default, the change tracking file is created as an Oracle managed file in the location specified by the DB_CREATE_FILE_DEST or DB_RECOVERY_FILE_DEST initialization parameters.

1.

Click the Maintenance tab.

Move your mouse over this icon to see the image

 

2.

Select Backup Settings in the Backup/Recovery Settings region.

Move your mouse over this icon to see the image

 

3.

Click the Policy tab.

Move your mouse over this icon to see the image

 

4.

Check Enable block change tracking for faster incremental backups. For the name of the Block Change Tracking File enter <oracle_home>\oradata\<sid>\<filename>. Scroll to the bottom of the page.

Move your mouse over this icon to see the image

 

5.

Verify the host credentials are set. Check the Save as Preferred Credential option, then click OK.

Move your mouse over this icon to see the image

 

Back to Topic

Testing Disk Backup

Before you schedule your database backup, you want to perform an Image copy. Before scheduling backups, you first need to test the disk backup. Perform the following steps:

1.

On the Maintenance page, under Backup/Recovery Settings, click Backup Settings.

Move your mouse over this icon to see the image

 

2,

Change the Disk Backup Type to Image Copy. Then, to make sure the disk backup interface is working, click Test Disk Backup.

Move your mouse over this icon to see the image

 

3.

You will see the progress window.

Move your mouse over this icon to see the image

 

4.

After one minute or less, you see that your Disk Backup Test was successful. Scroll down.

Move your mouse over this icon to see the image

 

5.

Make sure your Host Credentials are set to oracle/oracle and click OK to save the settings.

Move your mouse over this icon to see the image

 

Back to Topic

Backing Up the Database

Follow the steps below to take a whole database backup.

1.

Click Schedule Backup in the Backup/Recovery section on the Maintenance page.

Move your mouse over this icon to see the image

 

2.

To minimize the administrative tasks of performing backups, you will use the Oracle Suggested Backup method. This method schedules a daily backup job that backs up the entire database using the backup settings configured in the previous section.

Click Schedule Oracle-Suggested Backup for the Backup Strategy.

Move your mouse over this icon to see the image

 

3.

Make sure Disk is selected, then click Next.

Move your mouse over this icon to see the image

 

4.

Click Next.

Move your mouse over this icon to see the image

 

5.

Set the time to 5 minutes after the current time, then click Next. Note: you can identify the current time if you look at the DB Control Home Page.

Move your mouse over this icon to see the image

 

6.

Click Submit Job to schedule the backup.

Move your mouse over this icon to see the image

 

7.

Your backup job has been submitted. Click View Job .

Move your mouse over this icon to see the image

 

8.

Click on Reload or Refresh in your browser until the Page Refreshed time shows a time after the scheduled start time of the backup job.

 

9.

Once your job starts, you can see its progress. Click on the Backup log to see more details about this execution.

Move your mouse over this icon to see the image

 

10.

Scroll down to see the details.

Move your mouse over this icon to see the image

 

11.

After the backup has completed successfully in about 7 minutes, click the Database tab at the bottom of the page.

Move your mouse over this icon to see the image

Note: If the backup fails, increase the size of your flash recovery area and try the backup again.

 

Back to Topic

Reviewing Backup Reports

Enterprise Manager has added new functionality to Backup Management that provides a single view of all backups run against the databases in a domain. The details of the backup are available by clicking on the specific backup for the database. By providing a combined view of all database backups and their status, a DBA can quickly pinpoint when a backup job did not complete. A drill down to the log allows the DBA to fix the problem and restart the backup.

To view the results of the image copy backup, perform the following steps:

1.

Click Maintenance tab.

Move your mouse over this icon to see the image

 

2.

In the Backup/Recovery region, click Backup Reports.

Move your mouse over this icon to see the image

 

3.

A list of backup jobs currently scheduled within the database is displayed. Click on the entry for the full backup.

Move your mouse over this icon to see the image

 

4.

The report shows summary information for the backup job, such as the number of files backed up, the size of the backup, and the files contained within it. Scroll down to view the Inputs and Outputs sections.

Move your mouse over this icon to see the image

 

5.

These sections of the report provide more detailed information about the files that were backed up by this job. You can see the file numbers, file size, backup type, number of corrupt blocks and checkpoint SCN information. Scroll down to view the next three sections of the reports.

Move your mouse over this icon to see the image

 

6.

Now you see information regarding the Backup Sets, Backup Pieces, and Image Copies. Scroll back up to the top of the page.

Move your mouse over this icon to see the image

 

7.

Click the Database Instance: orcl link.

Move your mouse over this icon to see the image

 

Back to Topic

Examining the Flash Recovery Area Usage

You can also review the Flash Recovery Area usage. Perform the following steps:

Back to Topic

Scheduling RMAN Script Executions

You can also schedule periodic executions of an RMAN script. Perform the following steps:

1.

From the Maintenance tab, under Backup/Recovery Settings, click Recovery Settings.

Move your mouse over this icon to see the image

 

2.

Scroll down to the Flash Recovery section. You will see a pie chart which contains the usage of the flash recovery area. Notice that because you just performed a backup, the Image Copy is taking up the most space. Scroll up to the top of the page.

Move your mouse over this icon to see the image

 

3.

Click the Database Instance: orcl link.

Move your mouse over this icon to see the image

 

1.

Scroll down to the bottom of the page and click Jobs under Related Links.

Move your mouse over this icon to see the image

 

2.

Select RMAN Script from the Create Job pulldown list and click Go.

Move your mouse over this icon to see the image

 

3.

For the job name, enter DAILY_ARCH_BACKUP. Enter a description for this job, such as 'Backup archived log files in the flash recovery area'. For the RMAN script, you can either choose to have a saved script executed by typing in @c:\wkdir\backup_arch.rman or you can type in the RMAN script to run, such as the one listed here:

run {
  backup archivelog all delete input format 'c:\wkdir';
}

When finished typing in the information, click Add to specify a database target.

Move your mouse over this icon to see the image

 

4.

Click the Search button.

Move your mouse over this icon to see the image

 

5.

Select orcl and click Add.

Move your mouse over this icon to see the image

 

6.

On the Create 'RMAN Script' Job page, scroll down to the Host and Database Credentials region. Select Override Preferred Credentials and enter oracle/oracle for the Host User Name and Password and confirm the password. Enter sys/oracle for the Database User Name and password and confirm the password. Change the Database Role to SYSDBA then click the Schedule tab.

Move your mouse over this icon to see the image

 

7.

Select Later for the Start period and set the time to 11:30 pm. In the Repeat region, select Interval and set the Frequency to 1 Days. Then click Submit.

Move your mouse over this icon to see the image

 

8.

The job is then created and scheduled. To check the current status of the scheduled job, click the Database tab.

Move your mouse over this icon to see the image

 

9.

On the DB Control Home page, scroll down to the Job Activity region and click the linked number to the right of Scheduled Executions.

Move your mouse over this icon to see the image

 

10.

On the Job Activity page, you can see that the DAILY_ARCH_BACKUP job is scheduled to run at a later time. Click the Database tab at the top of the page.

Move your mouse over this icon to see the image

 

Back to Topic List

Performing Recovery of a Data File

You can easily recover a data file or your entire database through Enterprise Manager.

You can use the Flashback Database feature to quickly revert an Oracle database to the state it was in at a previous time without restoring data files and performing media recovery. You can use Enterprise Manager or the FLASHBACK DATABASE command to flashback your database.

The Simplified Recovery Through Resetlogs feature is an enhancement to recovery operations so that previous incarnation backups can be used for recovery of the current database incarnation. You no longer need to take a whole database backup after a RESETLOGS operation before you open the database for production use.

You will need to perform the following tasks to recover a lost/corrupt datafile:

 Delete a Data File

Perform a Recovery

 Place the Tablespace Online

Back to Topic List

Delete a Data File

You first view some data contained in the employees table. Then you delete the file that contains the table data to simulate the loss of a data file. In order to delete a data file, you need to take the tablespace offline, then delete it. Perform the following steps:

1.

Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter hr/hr for User Name/Password and click OK.

                               
                                 Move your mouse over this icon to see the image
                              
                            

 

2.

Enter the following command to see the current number of Employees in the HR Schema:

                               
                                 
SELECT count(*) FROM hr.employees;
exit
                              
                            

Move your mouse over this icon to see the image

 

3.

In order to delete a data file, you need to take the tablespace offline, then delete it. Using Enterprise Manager, logged in as a SYSDBA user, click the Administration tab.

 

4.

Select Tablespaces.

 

5.

Click the link for the EXAMPLE tablespace.

 

6.

Notice the location of the data file for this tablespace.

 

7.

Select Take Offline from the list of Actions and click Go.

 

8.

Select the Offline Mode Immediate and click OK.

 

9.

The tablespace is taken offline. You can now delete the file. Click the Database Instance: orcl link.

 

10.

Open Windows Explorer and navigate to the location of the EXAMPLE tablespace file. Right-click the EXAMPLE01.DBF and select Delete.

 

11. Click Yes to confirm.

 

12.

Switch back to Enterprise Manager. From the Administration tab, select the Tables link.

 

13.

Enter HR in the Schema field and click Go.

 

14.

Select the radio button in front of the EMPLOYEES table, select View data from the list of Actions and click Go.

 

15.

Note the I/O error. The data file contents are no longer there. Click the Database Instance: orcl link.

 

Back to Topic

Perform a Recovery

Now you are ready to perform a recovery of the data file you just deleted. Perform the following steps:

1.

Click the Maintenance tab.

 

2.

Under Backup/Recovery, select Perform Recovery.

 

3.

Note the database information stating that there is one data file that needs recovery. Click Datafiles Need Media Recovery.

 

4.

The datafile file to restore is already in the data file list. Select the Select check box in front of the datafile name. Then click Next.

 

5.

Take the default settings for restoring to the default location, and click Next.

 

6.

Review the job to be submitted, and note that you can click Edit RMAN Script to view the script that will be run. Click Submit.

 

7.

The browser will continue to poll for status on the restore request and display a processing screen.

 

8.

After about a minute, an "Operation Succeeded" message will appear, at which point you should click OK.

 

Back to Topic

Place Tablespace Online

Before you can access the data in the restored data file, you need to put the tablespace back online. Perform the following steps:

1.

On the database Administration page, click Tablespaces.

 

2.

Select the EXAMPLE tablespace and select Place Online in the list of Actions, then click Go.

 

3.

Click Yes to confirm.

 

4.

Your data file for the EXAMPLE tablespace has been placed online again. Click the Database Instance: orcl link.

 

5.

To verify you can view the data now, select the Tables link .

Move your mouse over this icon to see the image

 

6.

Enter hr for the Schema and click Go.

Move your mouse over this icon to see the image

 

7.

Once again, select the EMPLOYEES table radio button and select View Data from the Actions pulldown and click Go.

Move your mouse over this icon to see the image

 

8.

The data is now shown. Your recovery of the datafile was successful.

Move your mouse over this icon to see the image

 

Back to Topic List

Flashback of the Database

The Flashback Database feature provides a way for you to quickly revert your entire Oracle database to the state it was in at a past point in time. You can use Flashback Database to back out changes that have resulted in logical data corruption or are a result of user error. You can create a restore point before implementing database changes to assist in performing Flashback operations. Flashback Database is faster than traditional point-in-time recovery using backups and redo log files. The time to restore a database is now proportional to the number of changes that need to be backed out, not the size of the database.

Flashback Database is implemented using a new type of log file called a Flashback Database log. Flashback Database logs contain data block images for changed blocks and other information that enables the operation. The data block images are used to quickly back out changes to the database during the Flashback Database operation.

When you request a flashback of the database, the Oracle database server uses the Flashback Database logs to back out changes. In this exercise you will create a restore point, modify a database table, and then flashback to the restore point to undo the changes to the table. Perform the following steps:

 Create a Restore Point

Modify a Table

 Use the Restore Point to Flashback the Database

Back to Topic List

Create a Restore Point

1.

Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for User Name and click OK.

Move your mouse over this icon to see the image

 

2.

Create the HR.JOBS_HIST table by executing the CREATE_JOBS_HIST script as follows:

                               
connect / as sysdba
@c:\wkdir\create_jobs_hist
SELECT count(*) FROM hr.jobs_hist;
                            

Move your mouse over this icon to see the image

 

3.

Switch to Enterprise Manager Database Control and click the Maintenance tab.

Move your mouse over this icon to see the image

 

4.

Under Backup/Recovery, click Manage Restore Points.

Move your mouse over this icon to see the image

 

5.

Click Create.

Move your mouse over this icon to see the image

 

6.

Enter the name BEFORE_TRUNC and click OK.

Move your mouse over this icon to see the image

 

7.

The restore point is created and its associated SCN value is displayed. Click the Database Instance: orcl link.

Move your mouse over this icon to see the image

 

Back to Topic

Modify a Table

1.

Switch to your SQL Plus window. Truncate the HR.JOBS_HIST table to simulate a user error using the following commands:

                               
TRUNCATE TABLE hr.jobs_hist; 
SELECT count(*) FROM hr.jobs_hist;
                            

Move your mouse over this icon to see the image

 

2.

Return to Enterprise Manager and click the Administration tab.

Move your mouse over this icon to see the image

 

3.

Select Tables.

Move your mouse over this icon to see the image

 

4.

Enter HR for the Schema and click Go.

Move your mouse over this icon to see the image

 

5.

Select the HR.JOBS_HIST table and click Edit.

Move your mouse over this icon to see the image

 

6.

In the empty row after the last column entry, enter LAST_UPDATE for the name and TIMESTAMP for the data type. Click Apply.

Move your mouse over this icon to see the image

 

7.

The change was applied successfully. Click Database Instance: orcl link.

Move your mouse over this icon to see the image

 

8.

Return to SQL*Plus and describe the table to see the new column using the following commands:

                               
DESCRIBE hr.jobs_hist;
exit
                            

Move your mouse over this icon to see the image

 

Back to Topic

Use the Restore Point to Flashback the Database

1.

Click the Maintenance tab.

Move your mouse over this icon to see the image

 

2.

Click Perform Recovery.

Move your mouse over this icon to see the image

 

3.

To put the database in recovery mode, click the Perform Whole Database Recovery button.

Move your mouse over this icon to see the image

 

4.

Click Yes to shutdown the database.

Move your mouse over this icon to see the image

 

5.

The Recovery Wizard page is displayed. Wait about 8 minutes, then click Refresh.

Move your mouse over this icon to see the image

 

6.

The database was shutdown and started in mount state.

Move your mouse over this icon to see the image

 

7.

Flashback the database to the restore point created before the HR.JOBS_HIST table was truncated. Open a SQL Plus session and enter the following commands:

                               
                                 
                                   
connect / as sysdba;
                                
                              
                              
                                 
                                   
                                                                                            
                                 
                                   
FLASHBACK DATABASE TO RESTORE POINT before_trunc;
                                
                              
                            

Move your mouse over this icon to see the image

 

8.

When the FLASHBACK command completes, open the database using the following command:

ALTER DATABASE OPEN RESETLOGS;

Move your mouse over this icon to see the image

 

9.

After a few minutes, the database is opened. Enter the following commands to check the contents and structure of the HR.JOBS_HIST table. Notice that the rows are restored, but the recently added column no longer exists.

                               
connect hr/hr
SELECT count(*) FROM hr.jobs_hist; DESCRIBE hr.jobs_hist; exit;
Move your mouse over this icon to see the image

 

10.

Switch back to Enterprise Manager and click the Database tab.

Move your mouse over this icon to see the image

 

11.

Enter sys/oracle as the User Name and Password. Select SYSDBA as the Connect As and click Login.

Move your mouse over this icon to see the image

 

12.

The database is now Open.

Move your mouse over this icon to see the image

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Configure and use the Flash Recovery Area
 Enable Block Change Tracking for faster incremental backups
 Schedule backups of your database
 Recover from a lost data file
 Flashback the database to a previous point-in-time using restore points

Back to Topic List

 Move your mouse over this icon to hide all screenshots

Left Curve
Popular Downloads
Right Curve
Untitled Document