Oracle7 Server Administrator's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Backing Up a Database


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

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

Guidelines for Database Backups

This section describes guidelines to consider before performing database backups, and includes the following topics:

Before you create an Oracle database, you should decide how you plan to protect the database against potential disk failures, or to enable point-in-time recovery (if desired). If such planning is not considered before database creation, database recovery may not be possible if a disk failure damages the datafiles, online redo log files, or control files of a database.

See Also: See "Creating a Backup Strategy" [*] if you are not familiar with typical backup strategies for a database.

Test Backup and Recovery Strategies

Test your backup and recovery strategies in a test environment before and after you move to a production system. By doing so, you can test the thoroughness of your strategies and minimize problems before they occur in a real situation.

Performing test recoveries regularly ensures that your archiving, backup, and recovery procedures work. It also helps you stay familiar with recovery procedures, so that you are less likely to make a mistake in a crisis.

Perform Operating System Backups Frequently and Regularly

Frequent and regular full or partial database backups are essential for any recovery scheme. The frequency of backups should be based on the rate or frequency of changes to database data (such as insertions, updates, and deletions of rows in existing tables, and addition of new tables). If a database's data is changed at a high rate, database backup frequency should be proportionally high. Alternatively, if a database is mainly read-only, and updates are issued only infrequently, the database can be backed up less frequently.

Backup Appropriate Portions of the Database When Making Structural Changes

If you make any of the following structural changes, perform a backup of the appropriate portion of your database immediately before and after completing the alteration:

Backing up the appropriate portion of the database depends on the archiving mode of the database, as described below:

Back Up Often-Used Tablespaces Frequently

If a database is operated in ARCHIVELOG mode, it is acceptable to back up the datafiles of an individual tablespace or even a single datafile. This option is useful if a portion of a database is used more extensively than others, such as the SYSTEM tablespace and tablespaces that contain rollback segments. By taking more frequent backups of the extensively used datafiles of a database, you gather more recent copies of the datafiles. As a result, if a disk failure damages the extensively used datafiles, the more recent backup can restore the damaged files. Only a small number of changes to data need to be applied to roll the restored file forward to the time of the failure, or desired point-in-time recovery, thereby reducing database recovery time.

Keep Older Backups

How long you should keep an older database backup depends on the choices you want for database recovery. If you want to recover to a past point-in-time, you need a database backup taken before that point-in-time. For a database operating in NOARCHIVELOG mode, this means a full database backup. For a database operating in ARCHIVELOG mode, this means you should perform a backup of each datafile, taken individually or together, taken any time before the desired recovery point-in-time, and a backup of the associated control file that reflects the database's structure at the point-in-time of recovery.

For added protection, consider keeping two or more backups (and all archive logs that go with these backups) previous to the current backup.

Warning: After opening the database with the RESETLOGS option, existing backups cannot be used for subsequent recovery beyond the time when the logs were reset. You should therefore shutdown the database and make a full offline backup. Doing so will enable recovery of database changes subsequent to using the RESETLOGS option.

See Also: For more information on the Export utility, see the Oracle7 Server Utilities guide.

Export Database Data for Added Protection and Flexibility

Because the Oracle Export utility can selectively export specific objects, you might consider exporting portions or all of a database for supplemental protection and flexibility in a database's backup strategy. Database exports are not a substitute for operating system backups and cannot provide the same complete recovery advantages that the built-in functionality of Oracle offers.

Consider Distributed Database Backups

If a database is a node in a distributed database, consider the following guidelines:

See Also: For more information about distributed database recovery when databases are operating in NOARCHIVELOG mode, see "Coordinate Distributed Recovery" [*].

Back Up after Creating Unrecoverable Objects

If users are creating tables or indexes using the UNRECOVERABLE option, consider taking backups after the objects are created. When tables and indexes are created as UNRECOVERABLE, no redo is logged, and these objects cannot be recovered from existing backups.

See Also: For information about the UNRECOVERABLE option, see the CREATE TABLE...AS SELECT and CREATE INDEX commands in the Oracle7 Server SQL Reference.

Creating a Backup Strategy

Before you create an Oracle database, decide how you plan to protect the database against potential failures. Answer the following questions before developing your backup strategy:

Backup Strategies in NOARCHIVELOG Mode

If a database is operated in NOARCHIVELOG mode, filled groups of online redo log files are not being archived. Therefore, the only protection against a disk failure is the most recent full backup of the database.

Plan to take full backups regularly, according to the amount of work that you can afford to lose. For example, if you can afford to lose the amount of work accomplished in one week, make a full offline backup once per week. If you can afford to lose only a day's work, make a full offline backup every day. For large databases with a high amount of activity, it is usually unacceptable to lose work. Therefore, the database should be operated in ARCHIVELOG mode, and the appropriate backup strategies should be used.

Whenever you alter the physical structure of a database operating in NOARCHIVELOG mode, immediately take a full database backup. An immediate full backup protects the new structure of the database not reflected in the previous full backup.

Backup Strategies in ARCHIVELOG Mode

If a database is operating in ARCHIVELOG mode, filled groups of online redo log files are being archived. Therefore, the archived redo log coupled with the online redo log and datafile backups can protect the database from a disk failure, providing for complete recovery from a disk failure to the instant that the failure occurred (or, to the desired past point-in-time). Following are common backup strategies for a database operating in ARCHIVELOG mode:

Note: When you perform this initial full backup, make sure that the database is in ARCHIVELOG mode first. Otherwise, the backed up database files will contain the NOARCHIVELOG mode setting.

Warning: If the control file does not contain the name of a datafile, and you have no backup of that datafile, you cannot recover the file if it is lost. Also, do not use operating system utilities to backup the control file in ARCHIVELOG mode, unless you are performing a full, offline backup.

Read-Only Tablespaces and Backup

You can create backups of a read-only tablespace while the database is open. Immediately after making a tablespace read-only, you should back up the tablespace. Provided the tablespace remains read-only, there is no need to perform any further backups of it.

Unlike backups of writeable tablespaces, you do not need to use the BEGIN and END BACKUP commands to mark the beginning and end of the online backup of a read-only tablespace. Using these commands with reference to a read-only tablespace causes an error.

After you change a read-only tablespace to a read-write tablespace, you need to resume your normal backups of the tablespace, just as you do when you bring an offline read-write tablespace back online.

Bringing the datafiles of a read-only tablespace online does not make these files writeable, nor does it cause the file header to be updated. Thus, it is not necessary to perform a backup of these files, as is necessary when you bring a writeable datafile back online.

Performing Backups

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

Listing Database Files Before Backup

Before taking a full or partial database 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 Server 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 Full Offline Backups

Take a full offline backup of all files that constitute a database after the database is shut down to system-wide use in normal priority. A full backup taken while the database is open, after an instance crash or shutdown abort is useless. In such cases, the backup is not a full offline backup because the files are inconsistent with respect to the current point-in-time. The files that constitute the database are the datafiles, online redo log files, and control file.

Full offline backups do not require the database to be operated in a specific archiving mode. A full offline backup can be taken if a database is operating in either ARCHIVELOG or NOARCHIVELOG mode.

The set of backup files that result from a full offline backup are consistent. All files correspond to the same point in time. If database recovery is necessary, these files can completely restore the database to an exact point in time. After restoring the backup files, additional recovery steps may be possible to restore the database to a more current time if the database is operated in ARCHIVELOG mode and online redo logs are not restored.

Warning: A backup control file created during a full database backup should only be used with the other files taken in that backup, to restore the full backup. It should not be used for complete or incomplete database recovery. Unless you are taking a full 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 "Control File Backups" [*].

Preparing to Take a Full Backup

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

To Perform a Full Backup

Verifying Backups

DB_VERIFY is a command-line utility that performs a physical data structure integrity check on database files. Use DB_VERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

See Also: See page 3 - 8 for more information about database shutdown.

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 Oracle7 Server Utilities guide.

Performing Partial Backups

You can perform different types of partial backups:

Partial backups should only be taken (and in some cases can only be taken) if a database is operating in ARCHIVELOG mode. Partial backups cannot be used to restore a database operating in NOARCHIVELOG mode.

Online Tablespace and Datafile Backups

All datafiles of an individual online tablespace or specific datafiles of an online tablespace can be backed up while the tablespace and datafiles are currently online and in use for normal database operation.

To back up online tablespaces, you must have the MANAGE TABLESPACE system privilege.

To Perform an Online Backup of an Entire Tablespace or Specific Datafile

	SELECT tablespace_name, file_name
	   FROM sys.dba_data_files
	   WHERE tablespace_name = 'USERS';
	
	TABLESPACE_NAME    FILE_NAME
	---------------    ---------
	USERS              filename1
	USERS              filename2

	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.

	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.

See Also: See the Oracle7 Server 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.

To restart the database without media recovery, see "Recovering From an Incomplete Online Tablespace Backup" [*].

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:

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

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

		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.

Offline Tablespace and Datafile Backups

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.

To Back Up the Offline Datafiles of an Offline Tablespace

	ALTER TABLESPACE users OFFLINE NORMAL;

	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.

See Also: For more information about online and offline tablespaces, see page 8 - 7.

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

For more information about tablespace recovery, see page 24 - 20.

Performing Control File Backups

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

To backup 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 an Incomplete Online Tablespace Backup

The following situations can cause an incomplete tablespace 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:

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

See Also: For information on starting the database, see page 3 - 2.

For information on recovering a database, see page 24 - 7.

Using the Export and Import Utilities for Supplemental Database Protection

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

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 Oracle7 Server 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 23 - 1 lists available 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
Table 23 - 1. Export Modes

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:

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

Assume that the schedule illustrated in Figure 23 - 1 is used in exporting data from an Oracle database.

Figure 23 - 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.

To recover from a disk failure that occurs on Day 10, before the next incremental export is taken on Day 11




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index