Oracle8 Backup and Recovery Guide Release 8.0 A58396-01 |
Library | Product | Contents | Index

# 11
Performing Operating System Backups

This chapter explains how to back up the data in an Oracle database, and includes the following topics:

- Performing Backups
- Recovering from an Inconsistent Backup
- Using the Export and Import Utilities for Supplemental Database Protection

See Also: This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager Administrator's Guide.

## Performing Backups

This section describes the various aspects of taking database backups, and includes the following topics:

- Listing Database Files Before Backup
- Performing Whole Database Backups
- Performing Tablespace, Datafile, Control File and Archivelog Backups
- Performing Control File Backups

### Listing Database Files Before Backup

Before taking a whole database (or tablespace, datafile, control file or archivlog) backup, identify the files to be backed up. Obtain a list of datafiles by querying the V$DATAFILE view:

         SELECT name FROM v$datafile;

Then obtain a list of online redo log files for a database using the query below:

         SELECT member FROM v$logfile;

These queries list the datafiles and online redo log files of a database, respectively, according to the information in the current control file of the database.

Finally, obtain the names of the current control files of the database by issuing the following statement within Enterprise Manager:

         SHOW PARAMETER control_files;

Whenever you take a control file backup (using the ALTER DATABASE command with the BACKUP CONTROLFILE TO 'filename' option), save a list of all datafiles and online redo log files with the control file backup. To obtain this list use the ALTER DATABASE command with the BACKUP CONTROLFILE TO TRACE option. By saving the control file backup with the output of the TO TRACE invocation, the database's physical structure at the time of the control file backup is clearly documented.

### Performing Whole Database Backups

Take a whole database backup of all files that constitute a database after the database is shut down to system-wide use in normal priority. A whole database backup taken while the database is open, after an instance crash or shutdown abort is inconsistent. In such cases, the backup is not a consistent whole database backup because the files are inconsistent with respect to a current point-in-time.

Whole database backups do not require the database to be operated in a specific archiving mode. A whole database backup can be taken if a database is operating in either ARCHIVELOG or NOARCHIVELOG mode. If the database is in NOARCHIVELOG mode, then the backup must be consistent (meaning the database is shut down cleanly before the backup).

The set of backup files that result from a consistent whole database backup are consistent because all files correspond to the same point in time. If a database restore is necessary, these files can completely restore the database to an exact point in time. After restoring the backup files, you may perform additional recovery steps to recover the database to a more current time if the database is operated in ARCHIVELOG mode. Also, you can take inconsistent whole database backups if your database is in ARCHIVELOG mode.

warning: A backup control file created during a whole database backup should only be used with the other files taken in that backup, to restore the backup. It should not be used for complete or incomplete database recovery. Unless you are taking a consistent whole database backup, you should back up your control file using the ALTER DATABASE command with the BACKUP CONTROLFILE option.

See also: For more information about backing up control files, see "Performing Control File Backups".

#### Preparing to Take a Consistent Whole Database Backup

To guarantee that a database's datafiles are consistent, always shut down the database with normal or immediate priority before making a whole database backup. Never perform a whole database backup after an instance failure or after the database is shut down with abort priority (that is, using a SHUTDOWN ABORT statement) unless your database is in ARCHIVELOG mode. In this case, the datafiles are probably not consistent with respect to a specific point-in-time.

##### To Prepare for a Whole Database Backup

1. Shut down the database with normal or immediate priority.

   To make a whole database backup, all database files must be closed by shutting down the database. Do not make a whole database backup when the instance is aborted or stopped because of a failure. Reopen the database and shut it down cleanly first.

2. Back up all files that constitute the database.

   Use operating system commands or a backup utility to make backups of all datafiles, and a single control file of the database. Also back up the parameter files associated with the database.

   You can perform OS backups:

   - within Enterprise Manager, using the HOST command
   - outside Enterprise Manager, with the operating system commands or a backup utility
3. Restart the database.

   After you have finished backing up all datafiles and a single control file of the database, you can restart the database.

#### Verifying Backups

DB_VERIFY is an external command-line utility that performs a physical data structure integrity check on an offline database. Use DB_VERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.

See Also: The name and location of DB_VERIFY is dependent on your operating system (for example, dbv on Sun/Sequent systems). For more information about making operating system backups of files, see your operating system-specific Oracle documentation.

For more information on DB_VERIFY, see the Oracle8 Utilities guide.

### Performing Tablespace, Datafile, Control File or Archivelog Backups

Tablespace, datafile, control file and archivelog backups should only be taken (and in some cases can only be taken) if a database is operating in ARCHIVELOG mode. Such backups cannot be used to restore a database operating in NOARCHIVELOG mode.

#### Backing Up Online Tablespaces and Datafiles

You can back up all datafiles of an individual online tablespace or specific datafiles of an online tablespace while the database is open.

When you back up an individual datafile or online tablespace, Oracle stops recording the occurrence of checkpoints in the headers of the online datafiles being backed up. Oracle stops recording checkpoints as a direct result of issuing the ALTER TABLESPACE BEGIN BACKUP statement. This means the database is in hot backup mode, and that when a datafile is restored, it has "knowledge" of the most recent datafile checkpoint that occurred before the online tablespace backup, not any that occurred during it. As a result, Oracle asks for the appropriate set of redo log files to apply should recovery be needed. After the datafile copy is completed, Oracle advances the file header to the current database checkpoint. This is done after the ALTER TABLESPACE END BACKUP statement is executed.

##### To Back Up Online Tablespaces in an Open Database

1. Identify the datafiles.

   If you are backing up a specific datafile, use the fully specified filename of the datafile.

   Before beginning a backup on an entire tablespace, identify all of the tablespace's datafiles using the DBA_DATA_FILES data dictionary view. For example, assume that the USERS tablespace is to be backed up. To identify the USERS tablespace's datafile, you can query the DBA_DATA_FILES view:

           SELECT tablespace_name, file_name
           FROM sys.dba_data_files
              WHERE tablespace_name = 'USERS';

           TABLESPACE_NAME                     FILE_NAME
           -------------------------------   -----------------
           USERS                                filename1
           USERS                                filename2

    Here, filename1 and filename2 are fully specified filenames corresponding to the datafiles of the USERS tablespace.

    Mark the beginning of the online tablespace backup.

    To prepare the datafiles of an online tablespace for backup (and put them in hot backup mode), use either the Start Online Backup menu item of Enterprise Manager, or the SQL command ALTER TABLESPACE with the BEGIN BACKUP option.

    The following statement marks the start of an online backup for the tablespace USERS:

         ALTER TABLESPACE users BEGIN BACKUP;

    warning:

    If you forget to mark the beginning of an online tablespace backup, or neglect to assure that the BEGIN BACKUP command has completed before backing up an online tablespace, the backup datafiles are not useful for subsequent recovery operations. Attempting to recover such a backup is a risky procedure, and can return errors that result in inconsistent data later. For example, the attempted recovery operation will issue a "fuzzy files" warning, and lead to an inconsistent database that will not open.

    Back up the online datafiles.

    At this point, you can back up the online datafiles of the online tablespace from within Enterprise Manager, using the HOST command, by exiting Enterprise Manager and entering the operating system commands, or starting the Backup utility

    Mark the end of the online tablespace backup.

    After backing up the datafiles of the online tablespace, indicate the end of the online backup using either the End Online Tablespace Backup dialog box of Enterprise Manager, or the SQL command ALTER TABLESPACE with the END BACKUP option.

    The following statement ends the online backup of the tablespace USERS:

           ALTER TABLESPACE users END BACKUP;

If you forget to indicate the end of an online tablespace backup, and an instance failure or SHUTDOWN ABORT occurs, Oracle assumes that media recovery (possibly requiring archived redo logs) is necessary at the next instance start up. To avoid performing media recovery in this case, use the ALTER DATABASE datafile filename END BACKUP statement.

See Also: See the Oracle8 Reference for more information about the DBA_DATA_FILES data dictionary view.

See your operating system-specific Oracle documentation for more information about making operating system backups of files.

##### Determining Datafile Backup Status

To view the backup status of a datafile, you can use the data dictionary table V$BACKUP. This table lists all online files and gives their backup status. It is most useful when the database is open. It is also useful immediately after a crash, because it shows the backup status of the files at the time of the crash. You can use this information to determine whether you have left tablespaces in backup mode.

Note: V$BACKUP is not useful if the control file currently in use is a restored backup or a new control file created since the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$BACKUP accurately. Also, if you have restored a backup of a file, that file's STATUS in V$BACKUP reflects the backup status of the older version of the file, not the most current version. Thus, this view might contain misleading information on restored files.

For example, the following query displays the current backup status of datafiles:

         SELECT file#, status
             FROM v$backup;

         FILE#        STATUS
         ---------   ---------
         0011         INACTIVE
         0012         INACTIVE
         0013         ACTIVE
         ...

In the STATUS column, "INACTIVE" indicates that the file is not currently being backed up. "ACTIVE" indicates that the file is marked as currently being backed up.

##### Backing Up Several Online Tablespaces

If you have to back up several online tablespaces, use either of the following procedures:

- Back up the online tablespaces in parallel. For example, prepare all online tablespaces for backup:

         ALTER TABLESPACE ts1 BEGIN BACKUP;
         ALTER TABLESPACE ts2 BEGIN BACKUP;
         ALTER TABLESPACE ts3 BEGIN BACKUP;

Next, back up all files of the online tablespaces and indicate that the online backups have been completed:

         ALTER TABLESPACE ts1 END BACKUP;
         ALTER TABLESPACE ts2 END BACKUP;
         ALTER TABLESPACE ts3 END BACKUP;

- Back up the online tablespaces serially. For example, individually prepare, back up, and end the backup of each online tablespace:

         ALTER TABLESPACE ts1 BEGIN BACKUP;
         backup files
         ALTER TABLESPACE ts1 END BACKUP;
         ALTER TABLESPACE ts2 BEGIN BACKUP;
         backup files
         ALTER TABLESPACE ts2 END BACKUP;

The second option minimizes the time between ALTER TABLESPACE... BEGIN/END BACKUP commands and is recommended. During online backups, more redo information is generated for the tablespace.

#### Backing Up Offline Tablespaces and Datafiles

All or some of the datafiles of an individual tablespace can be backed up while the tablespace is offline. All other tablespaces of the database can remain open and available for system-wide use.

Note: You cannot take the SYSTEM tablespace or any tablespace with active rollback segments offline. The following procedure cannot be used for such tablespaces.

##### To take tablespaces offline and online, you must have the MANAGE TABLESPACE system privilege.

1. Identify the datafiles of the offline tablespace.

   Use the fully specified filename of the datafile.

   Before taking the tablespace offline, identify the names of its datafiles by querying the data dictionary view DBA_DATA_FILES. (See Step 1 on .)

2. Take the tablespace offline, using normal priority if possible.

   Use of normal priority, if possible, is recommended because it guarantees that the tablespace can be subsequently brought online without the requirement for tablespace recovery.

   To take a tablespace and all associated datafiles offline with normal priority, use the Take Offline menu item of Enterprise Manager, or the SQL command ALTER TABLESPACE with the OFFLINE parameter. The following statement takes a tablespace named USERS offline normally:

         ALTER TABLESPACE users OFFLINE NORMAL;

    After a tablespace is taken offline with normal priority, all datafiles of the tablespace are closed.

    Back up the offline datafiles.

    At this point, you can back up the datafiles of the offline tablespace from within Enterprise Manager using the HOST command, by exiting Enterprise Manager and entering the operating system commands, or starting the Backup utility.

    Bring the tablespace online. (Optional)

    Bring the tablespace online using either the Place Online menu item of Enterprise Manager, or the SQL command ALTER TABLESPACE with the ONLINE option. The following statement brings an offline tablespace named USERS online:

             ALTER TABLESPACE users ONLINE;

    Note:

    If you took the tablespace offline using temporary or immediate priority, the tablespace may not be brought online unless tablespace recovery is performed.

    After a tablespace is brought online, the datafiles of the tablespace are open and available for use.

See Also: For more information about making operating system backups of files, see your operating system-specific Oracle documentation.

### Performing Control File Backups

You should back up the control file of a database after making a structural modification to a database operating in ARCHIVELOG mode.

To back up a database's control file, you must have the ALTER DATABASE system privilege.

You can take a backup of a database's control file using the SQL command ALTER DATABASE with the BACKUP CONTROLFILE option. The following statement backs up a database's control file:

         ALTER DATABASE BACKUP CONTROLFILE TO 'filename' REUSE;

Here, filename is a fully specified filename that indicates the name of the new control file backup.

The REUSE option allows you to have the new control file overwrite a control file that currently exists.

#### Backing Up the Control File to the Trace File

The TRACE option of the ALTER DATABASE BACKUP CONTROLFILE command helps you manage and recover your control file. TRACE prompts Oracle to write SQL commands to the database's trace file, rather than making a physical backup of the control file. These commands start up the database, re-create the control file, and recover and open the database appropriately, based on the current control file. Each command is Commented. Thus, you can copy the commands from the trace file into a script file, edit them as necessary, and use the script to recover the database if all copies of the control file are lost (or to change the size of the control file).

For example, assume the SALES database has three enabled threads, of which thread 2 is public and thread 3 is private. It also has multiplexed redo log files, and one offline and one online tablespace.

    ALTER DATABASE
              BACKUP CONTROLFILE TO TRACE NORESETLOGS;

    3-JUN-1992 17:54:47.27:
    # The following commands will create a new control file and use it
    # to open the database.
    # No data other than log history will be lost. Additional logs may
    # be required for media recovery of offline data files. Use this
    # only if the current version of all online logs are available.
    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
              MAXLOGFILES 32
              MAXLOGMEMBERS 2
              MAXDATAFILES 32
              MAXINSTANCES 16
              MAXLOGHISTORY 1600
    LOGFILE
              GROUP 1
                  '/diska/prod/sales/db/log1t1.dbf',
                  '/diskb/prod/sales/db/log1t2.dbf'
              )    SIZE 100K
            GROUP 2
                  '/diska/prod/sales/db/log2t1.dbf',
                    '/diskb/prod/sales/db/log2t2.dbf'
            ) SIZE 100K,
            GROUP 3
                  '/diska/prod/sales/db/log3t1.dbf',
                  '/diskb/prod/sales/db/log3t2.dbf'
            ) SIZE 100K
    DATAFILE
            '/diska/prod/sales/db/database1.dbf',
            '/diskb/prod/sales/db/filea.dbf'
    ;
    # Take files offline to match current control file.
    ALTER DATABASE DATAFILE '/diska/prod/sales/db/filea.dbf' OFFLINE

    # Recovery is required if any data files are restored backups,
    # or if the last shutdown was not normal or immediate.
    RECOVER DATABASE;

    # All logs need archiving and a log switch is needed.
    ALTER SYSTEM ARCHIVE LOG ALL;

    # Database can now be opened normally
    ALTER DATABASE OPEN;

    #  Files in normal offline tablespaces are now named.
    ALTER DATABASE RENAME FILE 'MISSING0002'
            TO '/diska/prod/sales/db/fileb.dbf';

Using the command without NORESETLOGS produces the same output. Using the command with RESETLOGS produces a similar script that includes commands that recover and open the database, but resets the redo logs upon startup.

## Recovering From a Failed Online Tablespace Backup

The following situations can cause a tablespace backup to fail and be incomplete:

- You did not indicate the end of the online tablespace backup operation (using the ALTER TABLESPACE command with the END BACKUP option), and the database was subsequently shut down with the ABORT option.
- A system or instance failure, or SHUTDOWN ... ABORT interrupted the backup.

Upon detecting an incomplete online tablespace backup at startup, Oracle assumes that media recovery (possibly requiring archived redo log) is necessary for startup to proceed. You can avoid performing media recovery by using the ALTER DATABASE DATAFILE ... END BACKUP command. Remember to list all the datafiles of the tablespaces that were in the process of being backup up before the database was restarted. You can determine whether datafiles were in the process of being backed up by querying the V$BACKUP view.

warning: Do not use ALTER DATABASE DATAFILE ... END BACKUP if you have restored any of the affected files from a backup.

After you have restarted your database, you can perform the recovery in either of two ways:

- Use the STARTUP RECOVER command to start and recover the database automatically.
- Start an instance, open and mount the database, and issue the statement RECOVER DATABASE.

The first method is easier because it prompts Oracle to perform recovery only if it is needed.

See Also: For information on recovering a database, see Chapter 12, "Recovering a Database".

## Using the Export and Import Utilities for Supplemental Database Protection

This section describes the Import and Export utilities, and includes the following topics:

- Using Import
- Using Export

Export and Import are utilities that move Oracle data in and out of Oracle databases. Export writes data from an Oracle database to an operating system file in a special format. Import reads Export files and restores the corresponding information into an existing database. Although Export and Import are designed for moving Oracle data, you can also use them to supplement backups of data.

See Also: Both the Export and Import utilities are described in detail in the Oracle8 Utilities guide.

### Using Export

The Export utility allows you to backup your database while it is open and available for use. It writes a read-consistent view of the database's objects to an operating system file. System audit options are not exported.

warning: If you use Export to backup, all data must be exported in a logically consistent way so that the backup reflects a single point in time. No one should make changes to the database while the Export takes place. Ideally, you should run the database in restricted mode while you export the data, so no regular users can access the data.

Table 11-1 lists available export modes.

##### Table 11-1 Export Modes

Mode | Description
User | exports all objects owned by a user
Table | exports all or specific tables owned by a user
Full Database | exports all objects of the database

Following are descriptions of Export types:

#####

Incremental Export | Only database data that has changed since the last incremental, cumulative, or complete export is exported. An incremental export exports the object's definition and all its data. Incremental exports are typically performed more often than cumulative or complete reports.
--- | ---
| For example, if tables A, B, and C exist, and only table A's information has been modified since the last incremental export, only table A is exported.
Cumulative Exports | Only database data that has been changed since the last cumulative or complete export is exported.
| Perform this type of export on a limited basis, such as once a week, to condense the information contained in numerous incremental exports.
| For example, if tables A, B, and C exist, and only table A's and table B's information has been modified since the last cumulative export, only the changes to tables A and B are exported.
Complete Exports | All database data is exported.
| Perform this type of export on a limited basis, such as once a month, to export all data contained in a database.

### Using Import

The Import utility allows you to restore the database information held in previously created Export files. It is the complement utility to Export.

To recover a database using Export files and the Import utility:

- Re-create the database structure, including all tablespaces and users.

  Note: These re-created structures should not have objects in them.

- Import the appropriate Export files to restore the database to the most current state possible. Depending on how your Export schedule is performed, imports of varying degrees will be necessary to restore a database.
- Assume that the schedule illustrated in Figure 11-1 is used in exporting data from an Oracle database

  #### Figure 11-1 A Typical Export Schedule

A complete export was taken on Day 1, a cumulative export was taken every week, and incremental exports were taken daily.

1. Recreate the database, including all tablespaces and users.
2. Import the complete database export taken on Day 1.
3. Import the cumulative database export taken on Day 7.
4. Import the incremental database exports taken on Days 8, 9, and 10.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index