RMAN backup and recovery: loss of offline data files

1. tablespace description

In the previous article (RMAN backup and recovery control file loss), we explained how to control file loss and how to deal with backups in RMAN. This article explains how to deal with offline data file loss.

A tablespace is a logical structure that stores data files. Tablespaces include SYSTEM, SYSAUX, USERS, UNDOTBS1, TEMP, EXAMPLE, and table space. The system tablespace is used to store data dictionary information. It cannot be offline, read-only, or renamed. Auxiliary tablespace is used to store some tools and some common components. It is introduced at 10 GB and cannot be taken offline. It can be read-only and renamed. User tablespace is the tablespace used to store user data. It can be offline, read-only, and renamed. The rollback tablespace is used to store the transaction information of DML operations. It cannot be offline, cannot be read-only, and can be renamed. Temporary tablespace is used to store temporary data. It cannot be offline and cannot be read-only. It can be renamed. The data table space is used to store custom data. In a project, we generally create a data table space and store the data in the data table space. This is a good practice. Do not place the data in the default USERS tablespace.

Some tablespaces can be taken offline, but some cannot. This is because the database needs to write data to these tablespaces during operation, such as system tablespaces, auxiliary tablespaces, rollback tablespaces, and temporary tablespaces. The user tablespaces, test tablespaces, and data table spaces are different, as long as the tablespace is not operated, it can be taken offline.

2. backup and recovery of offline data files

To back up and restore offline data files, we first need to use RMAN to back up the entire database, and then simulate the loss of USERS tablespace. Then, we will see that the data file cannot be found. Because the USERS tablespace can be offline, we can make the USERS tablespace offline in SQL Plus. Remember to do the following before going offline. Because we have backups in RMAN, we can restore the data file numbers corresponding to the USERS tablespace in RMAN. In fact, we can perform a copy operation in the background. After the restoration is successful, we need to restore the data file number corresponding to the USERS tablespace. After the restoration is complete, we can make the USERS tablespace online in SQL Plus. After all the operations are completed, after the USERS tablespace is backed up and restored, we can access the data and test whether the recovery is successful. Note that the numbers of columns in the tablespace online, offline, restore, and restore data files must not be mistaken. To perform these operations, check the IDs of the USERS tablespace, the default value is "4". However, the number of the USERS tablespace data file cannot be "4" due to later modification. As a DBA, always be cautious!

Three Simulation

Step 1: Back up the entire database in RMAN and confirm the data correctness in SQL Plus.

RMAN> backup database;

Starting backup at 12-DEC-13
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Input datafile file number = 00001 name =/u01/Oracle/oradata/justdb/system01.dbf
Input datafile file number = 00002 name =/u01/oracle/oradata/justdb/sysaux01.dbf
Input datafile file number = 00003 name =/u01/oracle/oradata/justdb/undotbs01.dbf
Input datafile file number = 00004 name =/u01/oracle/oradata/justdb/users01.dbf
Channel ORA_DISK_1: starting piece 1 at 12-DEC-13
Channel ORA_DISK_1: finished piece 1 at 12-DEC-13
Piece handle =/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/partition _. bkp tag = TAG20131212T095816 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Channel ORA_DISK_1: starting full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Including current control file in backup set
Including current SPFILE in backup set
Channel ORA_DISK_1: starting piece 1 at 12-DEC-13
Channel ORA_DISK_1: finished piece 1 at 12-DEC-13
Piece handle =/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/partition _. bkp tag = TAG20131212T095816 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-13

[Oracle @ orcl backupset] $ ll 2013_12_12/
Total 1025556
-Rw ------- 1 oracle oinstall 9830400 Dec 12 o1_mf_ncsnf_tag201312t095816_9bl62lw2 _. bkp
-Rw ------- 1 oracle oinstall 1040334848 Dec 12 o1_mf_nnndf_tag201312t095816_9bl61rrn _. bkp

RMAN> list backup;

List of Backup Sets
==============================

BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
14 Full 992.13 m disk 00:00:19 12-DEC-13
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20131212T095816
Piece Name:/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn _. bkp
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---------------------------------
1 Full 1088247 12-DEC-13/u01/oracle/oradata/justdb/system01.dbf
2 Full 1088247 12-DEC-13/u01/oracle/oradata/justdb/sysaux01.dbf
3 Full 1088247 12-DEC-13/u01/oracle/oradata/justdb/undotbs01.dbf
4 Full 1088247 12-DEC-13/u01/oracle/oradata/justdb/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
15 Full 9.36 m disk 00:00:01 12-DEC-13
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20131212T095816
Piece Name:/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_ncsnf_TAG20131212T095816_9bl62lw2 _. bkp
SPFILE encoded ded: Modification time: 12-DEC-13
SPFILE db_unique_name: JUSTDB
Control File Included: Ckp SCN: 1088267 Ckp time: 12-DEC-13

SQL> CONN SCOTT/tiger;
Connected.
SQL> SELECT * FROM tab;

TNAME TABTYPE CLUSTERID
-----------------------------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

 

Step 2: delete USERS tablespace in SQL Plus to simulate USERS tablespace loss

SQL> HO mv/u01/oracle/oradata/justdb/users01.dbf/opt/learn/

 

Step 3: scott User Failed to view Data

SQL> CONN SCOTT/tiger;
Connected.
SQL> DESC dept;
ERROR:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oracle/oradata/justdb/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

Step 4: make the data file No. 4th offline in SQL Plus

SQL> CONN/AS SYSDBA;
Connected.
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;

Database altered.

 

Step 5: Restore the No. 4 data file in RMAN

RMAN> restore datafile 4;

Starting restore at 12-DEC-13
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 1 device type = DISK

Channel ORA_DISK_1: starting datafile backup set restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Channel ORA_DISK_1: restoring datafile 00004 to/u01/oracle/oradata/justdb/users01.dbf
Channel ORA_DISK_1: reading from backup piece/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn _. bkp
Channel ORA_DISK_1: piece handle =/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/tags _. bkp tag = TAG20131212T095816
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 12-DEC-13

 

Step 6: Restore file 4 in RMAN

RMAN> recover datafile 4;

Starting recover at 12-DEC-13
Using channel ORA_DISK_1

Starting media recovery
Media recovery complete, elapsed time: 00:00:00

Finished recover at 12-DEC-13

 

Step 7: Make the No. 4 data file online in SQL Plus

SQL> ALTER DATABASE DATAFILE 4 ONLINE;

Database altered.

 

Step 8: View data in SQL Plus

SQL> CONN SCOTT/tiger;
Connected.
SQL> DESC dept;
Name Null? Type
-----------------------------------------------------------------------------
Deptno not null number (2)
DNAME VARCHAR2 (14)
LOC VARCHAR2 (13)

SQL> SELECT * FROM dept;

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Recommended reading:

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption