Oracle7 Server 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
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
1. Shut down the database with normal or immediate priority.
To make a full backup, all database files must be closed by shutting down the database. Do not make a full backup when the instance is aborted or stopped because of a failure. Reopen the database and shut it down cleanly before making a full backup.
2. Back up all files that constitute the database.
Use operating system commands or a backup utility to make backups of all datafiles, online redo log files, and a single control file of the database. If you are multiplexing the online redo log, back up all members of each group, because it is not guaranteed that any one member of a group is complete. Also back up the parameter files associated with the database.
Operating system backups can be performed:
- within Server Manager, using the HOST command
- outside Server Manager, with the operating system commands or a backup utility
After you have finished backing up all datafiles, online redo log files, and a single control file of the database, you can restart the database.
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
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:
- online tablespace and datafile backups
- offline tablespace and datafile 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
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.
To prepare the datafiles of an online tablespace for backup, use either the Start Online Backup menu item of Server 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.
3. Back up the online datafiles.
At this point, you can back up the online datafiles of the online tablespace from within Server Manager, using the HOST command, by exiting Server Manager and entering the operating system commands, or starting the Backup utility
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.
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:
- 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.
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
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
.)
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.
ALTER TABLESPACE users OFFLINE NORMAL;
After a tablespace is taken offline with normal priority, all datafiles of the tablespace are closed.
3. Back up the offline datafiles.
At this point, you can back up the datafiles of the offline tablespace from within Server Manager using the HOST command, by exiting Server Manager and entering the operating system commands, or starting the Backup utility.
4. Bring the tablespace online. (Optional)
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 online and offline tablespaces, see
.
For more information about making operating system backups of files, see your operating system-specific Oracle documentation.
For more information about tablespace recovery, see
.
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.