The database must be in archive log mode to perform a hot backup. Since
the database is in use during the backup, the backup may contain copies of
tablespaces that are hours off from each other. Furthermore, the backup may
contain datafiles that were modified during the backup copying process.
When these mismatched datafiles are restored, the database will be so out
of sync that it would be useless. However, the archived redo logs can
be applied and will bring the database up to date.
To begin a backup of a tablespace the DBA should issue the command ALTER
TABLESPACE NAME BEGIN BACKUP. From here, buffers are flushed, a
checkpoint is performed and the tablespace is flagged as being in hot
backup mode. Normally, when a datafile is updated, only a small amount
of information is written to the redo log files. However, during a hot
backup a simple change to the datafile will cause an entire oracle block
from the data file to be written to the redo log file. This extra
information will allow Oracle to roll the datafile forward when recover is
performed. When the command ALTER TABLESPACE NAME END BACKUP is issued
normal logging is resumed. The backup status of tablespaces can be seen
from the view V$BACKUP.
The DBA_DATA_FILES table can be used to find the tablespace names and on
which datafile they reside. Once a list of these are made a process can
be formulated to perform a backup.
SVRMGR> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
Using ALTER TABLESPACE BEGIN/END BACKUP, the following algorithm can be used to backup these datafiles. Note that a tablespace can sometimes span multiple datafiles.
- SVRMGR: ALTER TABLESPACE USERS BEGIN BACKUP
- backup: /oracle/oradata/MYDB/users01.dbf
- SVRMGR: ALTER TABLESPACE USERS END BACKUP
- SVRMGR: ALTER TABLESPACE TOOLS BEGIN BACKUP
- backup: /oracle/oradata/MYDB/tools01.dbf
- backup: /oracle/oradata/MYDB/tools02.dbf
- SVRMGR: ALTER TABLESPACE TOOLS END BACKUP
Let's assume we have a script called begin_backup which will alter
a tablespace into backup mode and a script end_backup which will alter a
tablespace out of backup mode. Let's also assume there is a script adsm
which will backup a datafile via ADSM/dsmc commands. Here is a korn shell
script that demonstrates how to backup a database.
- SVRMGR: ALTER TABLESPACE TEMP BEGIN BACKUP
- backup: /oracle/oradata/MYDB/temp01.dbf
- SVRMGR: ALTER TABLESPACE TEMP END BACKUP
# Archive the parameter file
# Archive each tablespace / datafile
. . .
. . .
. . .
# Flush the log files so that the latest changes will be written to the archive log files.
alter system switch logfile;
alter system switch logfile;
# Copy the on-line redo log files. These files should have no important
# information, however, they are needed to restore the structure of the
# Copy the archived logfiles from the log_archive_dest parameter
# Create a backup control file. This is not an actual copy of the
# control file, but a special "backup control file".
alter database backup controlfile to '/backup/control/file/directory/controlfile.backup';
# Lastly, just because I am paranoid, I make a backup of the in-use
# control files. I just have a feeling that they might someday be
The status of the hot backup can be found by querying the V$BACKUP table.
To show the tablespace names and the datafiles, the query can be joined
with information from DBA_DATA_FILES and V$DATAFILE. ACTIVE means the
tablespace is in backup mode. NOT ACTIVE implies the table is in its
SVRMGR> select b.status, f.TABLESPACE_NAME, d.NAME
2> from v$backup b, dba_data_files f, v$datafile d,
3> where b.file# = d.file#
4> and d.NAME = f.FILE_NAME;
STATUS TABLESPACE_NAME NAME
------------ ------------------ ----------------------------------
NOT ACTIVE SYSTEM /t01/oradata/MYDB/system.01.dbf
NOT ACTIVE RBS /t04/oradata/MYDB/rbs01.dbf
NOT ACTIVE TEMP /t03/oradata/MYDB/temp01.dbf
NOT ACTIVE TOOLS /t03/oradata/MYDB/users01.dbf
ACTIVE USERS /t04/oradata/MYDB/users01.dbf
5 rows selected.