Release 8.0
A54640-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Backup and Recovery Guide Release 8.0 A54640-01 |
|
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 Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager User's Guide.
This section describes the various aspects of taking database backups, and includes the following topics:
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.
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" on page 10-9.
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:
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.
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:
After you have finished backing up all datafiles and a single control file of the database, you can restart the database.
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: 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.
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.
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:
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.
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.
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
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 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.
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;
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;
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.
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.
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 page 10-3.)
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.
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 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.
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.
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.
The following situations can cause a tablespace backup to fail and be incomplete:
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 recovering a database, see Chapter 11, "Recovering a Database".
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 Oracle8 Server Utilities guide.
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 10-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 |
Following are descriptions of Export types:
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.
.
A complete export was taken on Day 1, a cumulative export was taken every week, and incremental exports were taken daily.
