In this section, we will look at the different forms of backup and recovery that are possible on the Oracle database and their business implications.

Backup

There are a number of types of backup that can be performed on an Oracle database.

Logical Backup

A logical backup is an export to an operating system file of the database or objects in the database, such as schema object definitions, and the contents of the objects, such as tables and indexes. It provides a snapshot of the schema and its data at the time of the export, and reimporting can be used to restore that snapshot.

However, using export and import is not advised as the sole basis of a warehouse backup strategy on any but the smallest of data warehouses. For example, exporting the whole database would be prohibitively time consuming for a large data warehouse and would use a lot of disk space. In addition, recovery by reimport could also take an inordinate amount of time. Furthermore, it is not possible to easily export just the changed data for either the fact tables or the dimensions. This could result in the scripts to control a backup, which must be selective on the data being exported, being complex and difficult to write.

However, a logical backup can still play a very useful role. It can still be necessary or convenient to take an export snapshot of part of our data warehouse schema. For example, the dimension tables, which tend to be significantly smaller than the fact tables, could be exported more conveniently and may provide a supplement to the other backup mechanisms. If we know that a dimension table has not changed appreciably, it may be markedly quicker and easier to reimport the table from the logical backup and rerun the processes to reapply any changes to the data.

During a logical backup, we do not want changes occurring to the data that may result in a transactionally inconsistent backup. If Data Pump export is used, as discussed in Chapter 5, the use of the flashback feature ensures that a transactionally consistent view of the data is obtained.

Physical Backup

A physical backup involves backing up the actual files that form the database and are necessary for its recovery (i.e., the datafiles, control files, and redo log files).

There are two modes of physical backup that can be performed:

• Cold, or off-line, backup, which is performed when the database has been shut down in a consistent state or is not operating in archive log mode. The database must be shut down consistently (i.e. using shutdown modes immediate or normal); the database files and control file are backed up and then the database is restarted. During a cold backup, the database is not accessible to the users and this can be a major limiting factor.

• Hot, or on-line, backup, which is performed while the database is in archive log mode, open to read and write activity, and accessible to the users.

Furthermore, there are two other considerations when performing a physical backup concerning how copies of the datafiles are made.

• Full backup, which backs up all used blocks in the database data files

• Incremental backups, which backs up only those blocks in the database that have changed since the last full or incremental backup

Incremental Backups

We mentioned earlier that a type of backup that is very useful for a data warehouse is one that does not have to back up all of the datafiles. The incremental backup allows us to back up only the parts of the database datafiles that have changed.

Oracle incremental backups have the concept of backup levels, of which there are two: level 0 and level 1. A level 0 incremental backup acts as the base for subsequent incremental backups and is similar to a full backup in that it copies all blocks that contain data. (The only difference between a level 0 incremental backup and a full backup is that a full backup cannot act as the base of an incremental backup strategy.)

The level 1 backup has two types:

• Differential, which copies all blocks changed since the last level 0 or level 1 backup

• Cumulative, which copies all blocks changed since the last level 0 backup

Cumulative level 1 incremental backups, therefore, contain all of the previous level 1 backups back to the last level 0 incremental backup. They are better for recovery purposes, because only the one incremental backup needs to be applied.

Figure 12.1 shows how each of the differential incremental level 1 backups only backs up the changes occurring since the preceding level 1 or level 0 backup.

Figure 12.1. Differential Incremental Backups

A level 0 incremental backup (i.e., a full backup of all used blocks to use as a baseline) is taken on Saturday, and then each day at close of business up to Friday, a level 1 differential backup is taken. This backs up all of the blocks that have changed since the preceding day’s level 1 backup. Therefore, after Friday’s backup, all of the changed blocks are held on six incremental backup files. On the following Saturday, a new full level 0 backup is taken and the cycle repeats.

Now, let’s compare this with using a cumulative incremental backup strategy. A level 1 cumulative backup taken on the same schedule will incorporate all of the changes back to the preceding level 0 backup. To clarify how this is different from the differential level 0 backup, refer to Figure 12.2.

Figure 12.2. Cumulative Incremental Backups

A cumulative backup on Sunday backs up the changes since the preceding level 0 incremental backup on Saturday. The cumulative backup on Monday backs up the changes on Monday and the changes on Sunday. On each day, the cumulative incremental backup will back up all changes since the last level 0. Therefore, after Friday’s backup, all of the changed blocks are held on just one incremental backup file. On Saturday, a new level 0 incremental backup is taken and the cycle repeats.

Block Change Tracking

In order to track which blocks have changed for an incremental backup, Oracle tracks the system change number (SCN). Each block in an Oracle database contains the system change number (SCN) of the most recent change to a block. When performing an incremental backup, Oracle must read each block in a file and compare the block’s SCN with the SCN of the preceding backup. If the SCN is greater, then the block has changed and is backed up. This method does rely on reading every block to get its SCN number and hence can be slow. However, one of the new features in Oracle Database 10g is block change tracking, which uses a special tracking file to record the location of all changed blocks in the database. The backup process can then use this file when performing an incremental backup rather than reading all of the datafiles. The file enables the backup to more quickly identify the location of the changed blocks and back them up resulting in a much more efficient incremental backup.

Hint

Block change tracking records changed blocks even if no redo log is generated. It should, therefore, be used after performing NOLOGGING operations—for example, during data load operations, for which you may still want to backup incrementally.

Another particularly important new feature in Oracle Database 10g is the ability to merge the incremental backup into the full backup copy of the database, so that the full backup files then contain the up-to-date backup. Full backups are a lengthy process, because they are backing up all blocks that have ever been used; incremental backups usually take a significantly shorter amount of time, because they are backing up just the changed blocks. Effectively, this new feature means that we can obtain a full backup in about the same time that is taken to perform an incremental backup of our database.

Considering the size of a typical data warehouse, copying the entire datafile every time that a backup is required can be a significant overhead in terms of time, processing requirements, and space requirements of the backup media. To only have to back up the datafiles for the partitions that have changed or, even better, the blocks in those files that have changed, can result in considerable savings.

Traditionally, backup was done to a slow medium such as tape. However, with the advent of low-cost disk storage devices, it is much easier and faster to do backups to disk. Unlike with a tape backup, restoring data from a disk backup can be a very quick operation. However, the process of managing the disk space for backups and determining which files to keep and discard can be an onerous task for a DBA. Oracle Database 10g has a new feature called the Flash Recovery Area, which can greatly simplify the management of all recovery related files. By setting up the Flash Recovery Area and some simple policies, RMAN can now automatically manage all of the backup and recovery files with no DBA intervention.

The Flash Recovery Area is a unified storage area that contains all the recovery related files for the Oracle database, including redo logs, RMAN backups, and data and control file backups. Creating a Flash Recovery Area simplifies the process of managing recovery related files, such as naming backup files, determining which ones to keep and removing files when they are no longer necessary. In addition, the Flash Recovery Area is used to contain a new type of file, called the flashback logs, which are used by the new Oracle Database 10g flashback feature discussed in more detail in Chapter 17. The Flash Recovery Area uses the Oracle Managed Files (OMF) feature, discussed in Chapter 3, to manage the files.

At this point it is worth reiterating a golden rule of backup, which is particularly relevant when we are backing up to disk. What is being backed up (i.e., the database data, control, and redo log files) must reside on separate disks to the backup destination—that is the Flash Recovery Area. This prevents any form of media failure causing the loss of both the source database files and the backup files. Furthermore, also backing up to tape, which is then held off site, enables recovery from any disaster that affects the whole data center, such as fire or flood.

The first step toward defining our backup environment is to create a backup configuration. The backup configuration is a set of defaults used for backup operations. It determines where the backup is to be stored and what the backup medium is to be (tape or disk). It enables customizations to be defined and saved for reuse.

In Enterprise Manager, on the Maintenance screen in the Backup/Recovery section, there are a number of options for performing and managing your backup and recovery. Start by selecting Configure Backup Settings, where you can specify default values for your backup operations, as shown in Figure 12.3.

Figure 12.3. Configure Backup Settings

This screen contains three sections for defining the defaults for your backup, and they are used when you create the backup job if you don’t specifically override them. The three groups of settings that can be defined are:

On the Device screen, shown in Figure 12.3, there are check boxes in the disk and tape sections for an important option in Oracle Database 10g: to instruct RMAN to compress the backup files as they are archived to the destination area. This feature assists in the management of large volumes and enables better space utilization and more backup files to be stored.

Another new and important option in Oracle Database 10g is the option to compress the backup set as it is being written to disk or tape. With writing to the disk-based Flash Recovery Area, being able to compress the backup set can have significant benefits in saving and managing the space.

Also on the Device screen, you can define the degree of parallelism to be used when the backup is performed, as well as the disk location for the backup. By default the disk location is the Flash Recovery Area, but here you can override the default to use a different disk location. Similarly, you can provide default settings if you are backing up directly to tape.

In our examples in this section, the backup will go to disk, but, in a data warehouse, it will probably also have to go to tape. For example, a disaster recovery plan may mandate off-site storage on tape. To store backups on tape, RMAN requires the installation of a media management library available from another company such as Legato or Veritas. Refer to Oracle’s Backup Solutions Partners Program for the current list of certified vendors.

When backing up to tape, it is extremely important to ensure that the physical labels placed on the tapes match the contents. Too many times we have seen customer problems, where it is impossible to recreate the database from the backups because the labels on the tapes do not match the actual tape contents. Sloppy management practices such as this could mean that you lose your entire data warehouse.

Whenever you take a full backup of the system, do not discard the previous full backup set, because, if there were a problem with the current backup, you would lose your entire data warehouse. Therefore, save as many full backup sets as is possible before you recycle the tapes.

On the Configure Backup Settings screen, use the appropriate test button to test the disk or tape device’s configuration. For example, for a disk-based backup, clicking the Test Disk Backup creates a BACKUPSET directory. This is created under the named directory for your database in the Flash Recovery Area. In the BACKUPSET directory, the new directory is named by the current date (e.g., 2004_07_18). Once tested, we know our configuration is correct.

On the Policy screen, shown in Figure 12.4, you can define various additional settings for your backup, such as how long the backup sets are to be retained.

Figure 12.4. Configure Backup Settings Policy

This screen provides a good indication of changes that have been implemented with Oracle Database 10g to ease the backup management task. For example, under the Retention Policy section, there are options to define which backup sets must be kept in order to meet the backup and recovery strategy for your system; these are then automatically used when a backup is performed.

Note the Optimize check box in the Backup Policy section. If this is selected so that backups are executed using the optimization feature, then RMAN avoids creating identical copies of files that have not changed since the last backup. However, if backup files are aged out by the retention policy, then RMAN will take a new copy.

The size of your data warehouse will determine how frequently a full backup is taken. Even if your warehouse is huge, a backup should always be taken periodically.

One of the tools provided in Oracle Enterprise Manager is the backup wizard, where you can:

To start a backup, go to the Backup/Recovery section on the Maintenance screen and click on the Perform Backup link.

Predefined Backup Strategy

Here we will see how to set up a predefined backup strategy. The first question asked by the wizard, illustrated in Figure 12.5, is where is the destination of the backup is disk, tape, or both.

Figure 12.5. Oracle Suggested Backup Strategy

The next screen (not shown) summarizes the Oracle suggested backup policy. This is first to perform a full backup followed by an incremental daily backup.

Moving on to the next screen in the wizard, shown in Figure 12.6, enables the schedule for the backup job to be set. Don’t forget that the first backup performed as part of the schedule is a full backup, which could be a very lengthy operation on a warehouse-sized database.

Figure 12.6. Predefined Backup Schedule

The definition of the backup procedure is almost complete. Click on Next and the screen in Figure 12.7 is displayed shown where we can review all of the options that we have selected. To change any of the values, press the Back key to return to the appropriate screen, and make the necessary modifications. At this point we can also view the RMAN script that has been generated.

Figure 12.7. Reviewing the Backup Procedure

Clicking on the Submit Job button completes the definition of the backup and submits the job, which will be placed on the Enterprise Manager job queue until it is time for it to run. Monitoring the job can be performed by going to the EM Home pages and clicking on the Jobs link in the Related Links section, where the screen illustrated in Figure 12.8 is displayed.

Figure 12.8. Backup Job on the Console Job Queue

An alternative to the Oracle suggested backup is to create a custom backup strategy. We will use the customized option to show how to perform a level 1 incremental backup.

Again, we start at the Schedule Backup Strategy screen shown in Figure 12.9, where a backup strategy of Customized is selected.

Figure 12.9. Type of Custom Backup

The first question asked by the wizard is the type of backup that you require. If Whole Database is selected, you will then have the choice of either backing up the entire database or taking an incremental backup. As we have already discussed, an incremental backup is very likely to be of interest to a data warehouse administrator, because it allows you to back up only the data that has changed, instead of the entire data warehouse.

Depending on whether or not the database is running in archive log mode will determine the types of backup options available. In Figure 12.9, more options are available because archive log mode is enabled. With archive logging disabled, it is only possible to perform an off-line (cold) backup of the whole database which will necessitate it being automatically shut down first. Enabling archive logging means that the database, selected tablespaces, datafiles, or the archived logs can be backed up in on-line mode with the database still open to the users.

The steps in a custom backup are very similar to those we have seen previously; it uses a four-step wizard, so we won’t show all screens because most of these are self-explanatory. However, in Figure 12.10 we show the Options screen for the customized approach.

Figure 12.10. Customized Backup Options for an Incremental Backup

Here we can control the type of backup that we want to perform. As we have already mentioned, a typical warehouse is going to be too large to perform a full backup following every single refresh, so normally we will want to perform incremental backups. However, to perform an incremental backup we must start with a baseline full backup position. The Backup Type field provides us with the ability to specify which backup we want to perform.

We can perform a full backup by selecting the Full Backup option and leaving the check box clear for use as the base of an incremental backup. This will just perform the full backup. However, if we select the check box as well, then a level 0 incremental backup is performed. It is the same full backup of all of the used database blocks, but, because it is the base of an incremental backup strategy, this is what we have previously defined as a level 0 incremental backup.

We have chosen the level 1 incremental backup on the basis that a prior backup operation has performed the full backup to use as a baseline. Note the check box under the Incremental Backup option; it specifies that the incremental backup is to be merged into the full backup image held in the Flash Recovery Area.

The other options we can specify on this screen are:

Here we are backing up the archived redo logs and specifying that RMAN manage the logs by deleting those that are no longer required once they have been backed up. Similarly, we want any backups that are no longer required to be deleted. These options automate and simplify the backup procedure and the associated management tasks.

The remaining screens in the custom backup wizard enable us to specify the backup destination, disk or tape (which is step 2 and not shown), and specify the backup job name and schedule, as shown in Figure 12.11. Finally, there is a review screen (also not shown).

Figure 12.11. The Custom Backup Job Name and Schedule Screen

In Figure 12.11, we can rename the backup job to something that is more meaningful on our system, and we have full control of the start, stop, and frequency of operation of the job by the Scheduler. Once we have submitted the job in step 4 then, as before, we can always access it from the Related Links section of the OEM pages and click on the Jobs link.

Another technique for backing up the database is a tablespace backup. Using this technique, you have the ability to back up a specific tablespace, which, in a data warehouse, could make for a very nice backup strategy.

This time, when we schedule a backup, as shown in Figure 12.9, we will choose Customized and select the Tablespaces radio button. The first step is to select the tablespaces that we want to back up. In Figure 12.12, we see one of the screens from the backup wizard asking us which tablespaces we want to back up. Tablespaces are added to the list using a separate screen, accessed via the Add button. In our example, we have selected the tablespace that contains the January 2005 data.

Figure 12.12. Selecting a Tablespace to Back Up

This screen is a good example of where designing the database for management should be considered during the initial construction process. If we can group our updates to the database by date, and we know that after a given point in time there will be no changes made within this tablespace, then, if we set the tablespace to be read-only and back it up, we can rest assured that we have captured the data in that tablespace. Since it will never change, we won’t have to back it up again.

Changing the status of a tablespace to read-only is very simple. You can enter this SQL command:

ALTER TABLESPACE purchases_jan2005 READ ONLY;

Alternatively, go to the Maintenance screen and select Tablespaces from the Storage area, select the tablespace, and then click on the box on the right of the screen marked Read Only, as shown in Figure 12.13. Finally, clicking on the Apply button will make this a read-only tablespace. You can change it back to a read/write tablespace at any time.

Figure 12.13. Making a Tablespace Read-Only

Of course, it makes sense to periodically take full backups, just in case there are any problems with the backup files you have taken. Taking only tablespace backups is okay, but it still makes it possible for somebody to accidentally overwrite the tape containing all of the data for a given month.

When a database is being built, designers are constantly aware of the size of the database. However, that information seems to get lost when people start thinking about backing up the database. Everyone tends to say, “Well it’s 300 gigabytes so it will take x minutes to back up.” What they forget to add is that the backup file will need y gigabytes of space.

In the examples shown here, we have used disks to store our backups. For a real data warehouse, however, you will be storing them on tape; so don’t forget how many tapes you will require for the backup strategy you will be implementing. It has been known for sites to actually run out of tapes! Our Easy Shopping Inc. database is small, and the datafiles occupy approximately 1.5 gigabytes on a Windows XP system. In Figure 12.14, we can see the different sizes for the full backup files.

Figure 12.14. Backup File Sizes

A full backup occupies a little less than 1GB, which is about 66 percent of the full size of the database. Therefore, you can see that you can save a significant amount of storage space by using the backup provided by Oracle Database 10g. If a backup were taken using the Operating System backup utility, which may or may not compress the file, then 1.5GB of storage would be needed, instead of 1GB.

The files resulting from an incremental backup will be even smaller than the full backup files, though the size will be dependent upon the update activity that has occurred in the database. Obviously, we can see that there is a big advantage in using the RMAN utility for backups.

It is easy to see that even for the incremental backups, the size of the files is going to be significantly less than that for the full warehouse. By taking incremental backups, you can make backups of your warehouse more frequently and not have to worry about when you are going to find the time to back up the entire warehouse. By using the new option to merge the incremental backup into the full backup files, you can significantly improve the recovery times.

One final reminder on this topic: during the testing of the data warehouse, don’t forget to test your backup procedures and obtain some estimates of possible run times for backups. Then you can discuss your requirements with the operations department so that your management tasks can be included with all of the other work that has to be done.

A recovery catalog should be stored in its own tablespace. In our example, the RMANREP tablespace is used. A new user, who will own the recovery catalog schema in the recovery catalog database, should be created. In the following example we have created a user called RMAN and granted it the appropriate privileges.

CREATE USER rman IDENTIFIED BY rmanrep
  DEFAULT TABLESPACE rmanrep TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON rmanrep;
GRANT connect, resource TO rman;
GRANT recovery_catalog_owner TO rman;

The next step is to actually create the recovery catalog, which is achieved by running the RMAN utility. As soon as the RMAN prompt appears, as illustrated, connect to the database that will hold the catalog. Since you are using the syntax CONNECT CATALOG, an error message will appear saying that the recovery catalog is not installed. Ignore this message. Then issue the CREATE CATALOG command. Once complete, you are now ready to use the catalog.

C:>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

RMAN> connect CATALOG rman/rmanrep

connected to recovery catalog database
recovery catalog is not installed

RMAN> CREATE CATALOG

recovery catalog created

Before a database can be included in the recovery catalog, it must be registered. Otherwise, you may see the message “RMAN-20001 Target database not found in recovery catalog.” Do not be alarmed by the message.

To register the EASYDW database in the catalog, go to the Backup/Recovery section on the Maintenance page and click on the Configure Recovery Catalog Settings link; you will see the screen shown in Figure 12.15.

Figure 12.15. Recovery Catalog Information

Here you must specify where the recovery catalog is located (i.e., either on a known database or by entering the database access information directly) and you must also provide the catalog repository account username and password. In our example, the account is RMAN, which can be found on the OEMREP database on server DS9. On the next screen (not shown), it is simply a case of clicking the Register Database button to register our EASYDW database in the RMAN catalog.

As mentioned previously, the recovery catalog should always be placed in a database different from the one being backed up. Also, don’t forget that the recovery catalog must also be backed up regularly because it is stored inside a database.

Oracle Enterprise Manager can be used to access the catalog to report on various backup information. The example in Figure 12.16 shows the Manage Current Backups screen accessed from the Maintenance home page, which lists the backup information stored in the catalog. A very pertinent piece of information is whether the backup is obsolete due to being replaced by a more recent backup.

Figure 12.16. List of the Backup Contents of the Catalog

From the screen in Figure 12.16, we are able to perform additional tasks to manage our backup sets by pressing the following buttons.

By clicking on the links in the Contents column, you can get more information from the catalog about the contents of what that backup set contains. For example, clicking on the datafile link for Key 2736 results in the screen shown in Figure 12.17.

Figure 12.17. Contents of a Backup Set

Although this has been a brief introduction to the recovery catalog and how it is used by OEM, hopefully you can begin to see some of the benefits of using it, especially being able to see which backups exist. Using this information, you will see later how automatic recovery is possible, although, for a data warehouse, we may prefer to do it manually.