Making User-Managed Backups of Offline Tablespaces and Datafiles

Making User-Managed Backups of Offline Tablespaces and Datafiles

Note the following guidelines when backing up offline tablespaces:

•You cannot offline the SYSTEM tablespace or a tablespace with active rollback segments. The following procedure cannot be used for such tablespaces.

•Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.

To back up offline tablespaces:

Before beginning a backup of a tablespace, identify the tablespace's datafiles by querying the DBA_DATA_FILES view. For example, assume that you want to back up the users tablespace. Enter the following in SQL*Plus:

SELECT TABLESPACE_NAME, FILE_NAME
FROM SYS.DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME FILE_NAME
------------------------------- --------------------------------
USERS /oracle/oradata/trgt/users01.dbf
In this example, /oracle/oradata/trgt/users01.dbf is a fully specified filename corresponding to the datafile in the users tablespace.

Take the tablespace offline using normal priority if possible because it guarantees that you can subsequently bring the tablespace online without having to recover it. For example:

SQL> ALTER TABLESPACE users OFFLINE NORMAL;
Back up the offline datafiles. For example:

% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf
Bring the tablespace online. For example:

ALTER TABLESPACE users ONLINE;
Note:

If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:

ALTER SYSTEM ARCHIVE LOG CURRENT;
Making User-Managed Backups of Online Tablespaces and Datafiles
You can back up all or only specific datafiles of an online tablespace while the database is open. The procedure differs depending on whether the online tablespace is read/write or read-only.

Note:

You should not back up temporary tablespaces.
Making User-Managed Backups of Online Read/Write Tablespaces
You must put a read/write tablespace in backup mode to make user-managed datafile backups when the tablespace is online and the database is open. The ALTER TABLESPACE ... BEGIN BACKUP statement places a tablespace in backup mode. In backup mode, the database copies whole changed data blocks into the redo stream. After you take the tablespace out of backup mode with the ALTER TABLESPACE ... END BACKUP or ALTER DATABASE END BACKUP statement, the database advances the datafile header to the current database checkpoint.

When restoring a datafile backed up in this way, the database asks for the appropriate set of redo log files to apply if recovery be needed. The redo logs contain all changes required to recover the datafiles and make them consistent.

To back up online read/write tablespaces in an open database:

Before beginning a backup of a tablespace, identify all of the datafiles in the tablespace with the DBA_DATA_FILES data dictionary view. For example, assume that you want to back up the users tablespace. Enter the following:

SELECT TABLESPACE_NAME, FILE_NAME
FROM SYS.DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME FILE_NAME
------------------------------- --------------------
USERS /oracle/oradata/trgt/users01.dbf
USERS /oracle/oradata/trgt/users02.dbf
Mark the beginning of the online tablespace backup. For example, the following statement marks the start of an online backup for the tablespace users:

SQL> ALTER TABLESPACE users BEGIN BACKUP;
Caution:

If you do not use BEGIN BACKUP to mark the beginning of an online tablespace backup and wait for that statement to complete before starting your copies of online tablespaces, or then the datafile copies produced are not usable for subsequent recovery operations. Attempting to recover such a backup is risky and can return errors that result in inconsistent data. For example, the attempted recovery operation can issue a "fuzzy files" warning, and can lead to an inconsistent database that you cannot open.

Back up the online datafiles of the online tablespace with operating system commands. For example, UNIX users might enter:

% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf
% cp /oracle/oradata/trgt/users02.dbf /d2/users02_'date "+%m_%d_%y"'.dbf
After backing up the datafiles of the online tablespace, run the SQL statement ALTER TABLESPACE with the END BACKUP option. For example, the following statement ends the online backup of the tablespace users:

SQL> ALTER TABLESPACE users END BACKUP;
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Caution:

If you fail to take the tablespace out of backup mode, then Oracle continues to write copies of data blocks in this tablespace to the online logs, causing performance problems. Also, you will receive an ORA-01149 error if you try to shut down the database with the tablespaces still in backup mode.

Making Multiple User-Managed Backups of Online Read/Write TablespacesWhen backing up several online tablespaces, you can back them up either serially or in parallel. Use either of the following procedures depending on your needs.

Backing Up Online Tablespaces in Parallel
You can simultaneously create datafile copies of multiple tablespaces requiring backups in backup mode. Note, however, that by putting all tablespaces in online mode at once, you can generate large redo logs if there is heavy update activity on the affected tablespaces, because the redo must contain a copy of each changed data block in each changed datafile. Be sure to consider the size of the likely redo before using the procedure outlined here.

To back up online tablespaces in parallel:

Prepare all online tablespaces for backup by issuing all necessary ALTER TABLESPACE statements at once. For example, put tablespaces users, tools, and index in backup mode as follows:

SQL> ALTER TABLESPACE users BEGIN BACKUP;
SQL> ALTER TABLESPACE tools BEGIN BACKUP;
SQL> ALTER TABLESPACE indx BEGIN BACKUP;
If you are backing up all tablespaces, you might want to use this command:

SQL> ALTER DATABASE BEGIN BACKUP;
Back up all files of the online tablespaces. For example, a UNIX user might back up datafiles with the *.dbf suffix as follows:

% cp $ORACLE_HOME/oradata/trgt/*.dbf /disk2/backup/
Take the tablespaces out of backup mode as in the following example:

SQL> ALTER TABLESPACE users END BACKUP;
SQL> ALTER TABLESPACE tools END BACKUP;
SQL> ALTER TABLESPACE indx END BACKUP;
Again, it you are handling all datafiles at once you can use the ALTER DATABASE command instead of ALTER TABLESPACE:

SQL> ALTER DATABASE END BACKUP;
Archive the online redo logs so that the redo required to recover the tablespace backups will be available for later media recovery. For example, enter:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Backing Up Online Tablespaces Serially
You can place all tablespaces requiring online backups in backup mode one at a time. Oracle recommends the serial backup option because it minimizes the time between ALTER TABLESPACE ... BEGIN/END BACKUP statements. During online backups, more redo information is generated for the tablespace because whole data blocks are copied into the redo log.

To back up online tablespaces serially:

Prepare a tablespace for online backup. For example, to put tablespace users in backup mode enter the following:

SQL> ALTER TABLESPACE users BEGIN BACKUP;
In this case you probably do not want to use ALTER DATABASE BEGIN BACKUP to put all tablespaces in backup mode simultaneously, because of the unnecessary volume of redo log information generated for tablespaces in online mode.

Back up the datafiles in the tablespace. For example, enter:

% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf
Take the tablespace out of backup mode. For example, enter:

SQL> ALTER TABLESPACE users END BACKUP;
Repeat this procedure for each remaining tablespace.

Archive the unarchived redo logs so that the redo required to recover the tablespace backups is archived. For example, enter:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;