Making User-Managed Backups-17.3, Making User-Managed Backups of Offline Tablespaces and Datafiles

17.3. Making User-Managed Backups of Offline Tablespaces and Datafiles
Pay attention to the following guiding principles when backing up offline tablespaces:
(1) tablespaces that cannot be offline system tablespaces or active rollback segments
(2) Assume that a table is in table space x and its index is in table space y. Y is offline and x is online. An error is reported when you execute a dml statement.

Backup offline tablespace:
(1) query the data files identified by dba_data_files in the tablespace before backing up the tablespace.
(2) try to use the normal option for offline tablespace because it ensures that the tablespace does not need to be restored when it is set to online.
(3) Back up offline data files
(4) set the tablespace to online
NOTE: If temporary or immediate is used first, the tablespace can be set to online only after the tablespace is restored.
(5) Archiving Unarchived redo logs
SQL> alter system archive log current;

Simulation 1. Take the table space testtbs01 offline and back up offline data files.
(1)
SQL> col file_name for a40
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name = 'testtbs01 ';

TABLESPACE_NAME FILE_NAME
----------------------------------------------------------------------
TESTTBS01/oracle/oradata/boss/testtbs01_01.dbf
TESTTBS01/oracle/oradata/boss/testtbs01_02.dbf

(2)
SQL> alter tablespace testtbs01 offline normal;

(3)
$ Cp-rf/oracle/oradata/boss/testtbs01_01.dbf/oradata/bossbak/20140604/testtbs01_01 _ 'date "+ % y _ % m _ % d" '. dbf
$ Cp-rf/oracle/oradata/boss/testtbs01_02.dbf/oradata/bossbak/20140604/testtbs01_02 _ 'date "+ % y _ % m _ % d" '. dbf

(4)
SQL> alter tablespace testtbs01 online;

(5)
SQL> select group #, members, sequence #, archived, status, first_change # from v $ log;

GROUP # members sequence # arc status FIRST_CHANGE #
--------------------------------------------------------------
1 1 17 yes inactive 575472
2 1 18 yes inactive 575475
3 1 19 no current 575477
SQL> alter system archive log current;

SQL> select group #, members, sequence #, archived, status, first_change # from v $ log;

GROUP # members sequence # arc status FIRST_CHANGE #
--------------------------------------------------------------
1 1 20 yes active 581638
2 1 21 no current 581693
3, 1, 19, YES, ACTIVE 575477

SQL> alter system checkpoint; # the checkpoint triggers the dbwrn write process and writes dirty data to the data file. At this time, the active log is immediately inactive.

SQL> select group #, members, sequence #, archived, status, first_change # from v $ log;

GROUP # members sequence # arc status FIRST_CHANGE #
--------------------------------------------------------------
1 1 20 yes inactive 581638
2 1 21 no current 581693
3 1 19 yes inactive 575477