0532-0534

Previous Table of Contents Next

Page 532

Alternative Backup Methods

Cold and hot backups are not the only options available to the DBA. Other backup methods exist, but they are often unreliable and do not permit the level of recoverability that is available from cold and hot backups. These alternative backup methods are useful as supplemental backups within a backup strategy. They are not designed to replace cold and hot backups .

Tablespace Offline Copy

Of the supplemental backup methods, the tablespace offline copy method is the only one that can feasibly be used in production. It is something of a hybrid between a cold backup and a hot backup. It enables you to do essentially a cold backup of a tablespace while the database remains online.

In the tablespace offline copy method, each individual tablespace is taken offline by an alter tablespace command issued from Oracle Server*Manager or SQL*Plus:

 alter tablespace users offline; alter tablespace users online; 

The steps in a tablespace offline copy backup are

1. Alter the tablespace offline.
2. Perform operating system copy of the database files associated with the tablespace.
3. Alter the tablespace online.
4. Repeat steps 1 through 3 until all the tablespaces have been backed up.
5. Back up the control files.
6. Back up the online redo logs.

This method permits a complete hot-style backup of the individual tablespaces, but without the additional activity within the redo logs, rollback segments, and so on. By using this method, you can take a backup of a tablespace and use archive logs to recover any transactions that occurred after the backup.

The tablespace offline copy backup has some drawbacks. Database objects on the tablespace are unavailable while it is being copied . This is in direct contrast to the hot backup, in which a tablespace and its objects remain online and accessible. Likewise, you cannot back up the SYSTEM tablespace with this method because it cannot be taken offline. You must use another backup method to do that.

Export

A popular method for supplemental database backup is the exp utility, which performs exports of data within the Oracle database. The exp utility can perform three types of data exports:

• Incremental exports: All the information that has changed since this last incremental export is exported.

Page 533

• Cumulative exports: All the data that has changed since the last cumulative export is exported. A cumulative export is a collection of incremental exports.
• Full exports: All the data within the database is exported.

There are a number of options available with the exp utility. They are described in Chapter 17, "Import and Export."

If an export is used for recovery, everything in the database that has been added, deleted, or otherwise changed since the last export is lost. For this reason, exports are used only to facilitate quick, point-in-time recoveries , such as when a static reference table is truncated. They do not provide the level of recovery that most mission-critical operations require.

Here is a partial screen listing for an export:

 % exp file=/tmp/exp.log full=y Export: Release 7.1.6.2.0 - Production on Mon Sep 11 03:29:09 1995 Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. Username: system Password: ....... Connected to: Oracle7 Server Release 7.1.6.2.0 - Production Release With the distributed option PL/SQL Release 2.1.6.2.0 - Production About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers 

To reclaim the exported data, use the Oracle imp utility, which imports the data. Both imp and exp are in the $ORACLE_HOME/bin directory. For a more detailed discussion of exports, refer to the Oracle7 Server Utilities User's Guide.

SQL*Loader Readable File

Another strategy for backups is a result of the Oracle7 direct-load path in SQL*Loader. By using SQL scripts, PL/SQL programs, or 3GL interface programs, you can create a file for each database table in which each row is in a SQL*Loader-readable format, such as comma- delimited. With this method, you can re-create and repopulate tables quickly after a failure.

The time required to administer this method neutralizes whatever benefit you might gain by it. Whenever a change is made to a database table, the change must be reflected in the appropriate program or else it is not correct. Likewise, this backup method has many of the same limitations as exp without any of its simplicity or benefits. Because of the time and physical disk storage space required, this type of backup is a heavy undertaking. It must be policed almost constantly.

Page 534

NOTE

If the structure of a database table is changed ”using an alter table command for example ”the programs that create the SQL*Loader readable files must be changed. If this is not done, proper backups of all data within the database object will not be taken.

For some sites, however, this type of backup is practical, despite the obvious constraints. For a more detailed discussion of the implementation of SQL*Loader, refer to the Oracle7 Server Utilities User's Guide.

Types of Database Failure

Most DBAs experiences a database failure at some point. It might be a minor failure in which the users never even know that they lost service, or it might be a severe loss that lasts for several days. Most failures fall somewhere in the middle.

Most failures result primarily from the loss or corruption of a physical data file. Of course, many other factors can cause database problems. Indeed, problems can occur in the memory structures (the SGA), the system hardware, or even the Oracle software that prevent the DBA from starting up the database. The following sections describe the most common types of failures.

Tablespace

If a loss or corruption takes place in any of the database files that make up a tablespace, media recovery is required. The extent of the media recovery needed depends largely on the extent of the data file loss or corruption. The three types of recovery available for this type of recovery are

• Database recovery
• Tablespace recovery
• Data file recovery

The database recovery method is generally chosen if the SYSTEM tablespace has been damaged, in which case it synchs all the data files within the database during the recovery procedure. The tablespace recovery method is used if recovery is needed for multiple tablespaces that had become damaged, such as from the loss of a disk drive. The data file recovery method is performed if only a single database file has been damaged. The commands used to implement these methods are

 recover database;     recover tablespace users;     recover datafile `/u03/oradata/norm/rbs01.dbf'; 

Previous Table of Contents Next