Performing Backups

• Listing Database Files Before Backup

• Performing Full Offline Backups

• Performing Partial Backups

• Performing Control File Backups

Listing Database Files Before Backup

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

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

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

3. Restart the database.

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

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

• online tablespace and datafile backups

• offline tablespace and datafile backups

Online Tablespace and Datafile Backups

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

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.

2. Mark the beginning of the online tablespace backup.

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

4. 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 Server 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.

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

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 .)

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 Server 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.

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)

Bring the tablespace online using either the Place Online menu item of Server 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 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

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

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.