Making User-Managed Backups of Offline Tablespaces and Datafiles

We can take all or individual tablespace backup while tablespace is OFFLINE. and all other tablespace is remain available and open for systemwide use.


Procedure

Identify datafiles associate with tablespace

SQL> select tablespace_name,file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

SQL> alter tablespace users offline normal;

Tablespace altered.

SQL> --copy datafiles to backup location
SQL> alter tablespace users online;

Tablespace altered.

SQL> alter system archive log current;

System altered.


Keep in mind

1. We can't take system tablespace or any active undo segment tablespace OFFLINE.
2. 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.
3. If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.