How to recover from a loss of a non-system tablespace

Tweet

The following scenario will describe how to proceed when you lose a non-system tablespace, I mean not the SYSTEM and UNDO tablespace. In particular this example will restore the lost tablespace to its original location. When you lose a non-system tablespace you cannot access and query only objects that were created on their datafiles;
meanwhile users can continue to query and use all the others objects in the database and you can restore it while the database is open.
Moreover because your database is running in ARCHIVELOG mode any committed transactions don't need to be inserted again.
Let's simulate a loss of the EXAMPLE tablespace, in my case formed by only one datafile:

[oracle@localhost ~]$ ll /home/oracle/app/oracle/oradata/orcl/example01.dbf*
-rw-rw---- 1 oracle oracle 85991424 Jul 26 06:34 /home/oracle/app/oracle/oradata/orcl/example01.dbf
[oracle@localhost ~]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf /home/oracle/app/oracle/oradata/orcl/example01.dbf.bck

The database is still open and I query for the very first time an object located on the EXAMPLE tablespace. I receive an error stating the instance was not able to open the example01.dbf (data)file

SQL> select count(*) from ix.ORDERS_QUEUETABLE;
select count(*) from ix.ORDERS_QUEUETABLE
  *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

If you look at the alert log the same error and a trace file are generated

...
Thu Jul 26 06:49:51 2012
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_6858.trc:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Thu Jul 26 06:49:53 2012
Checker run found 1 new persistent data failures
...

As you can see the objects located on EXAMPLE tablespace are no more available. So it's time to recover our tablespace: I'd like to remind you that all the following steps are executed while the database is OPEN as you can see even from the screen log of rman console (connected to target database: ORCL (DBID=1229390655)):

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Sep 25 23:09:23 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

First thing to do is to put the lost tablespace OFFLINE.

RMAN> sql 'alter tablespace example offline immediate';

sql statement: alter tablespace example offline immediate

Second step is to issue the restore command providing the name of your lost tablespace

RMAN> restore tablespace example;

Starting restore at 26-07-2012 06:54:15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=22 STAMP=789633285 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_812kvfk3_.dbf
destination for restore of datafile 00005: /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf RECID=0 STAMP=0
Finished restore at 26-07-2012 06:54:19

After your tablespace was restored from your backup pieces it's time to execute the recover command.

RMAN> recover tablespace example;

Starting recover at 26-07-2012 06:54:27
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 26-07-2012 06:54:29

The tablespace is now recovered and you can put it again online and available for your users.

RMAN> sql 'alter tablespace example online';

sql statement: alter tablespace example online

An extract of the alert log taken during the restore and recover process...

...
Thu Jul 26 06:53:38 2012
alter tablespace example offline immediate
Completed: alter tablespace example offline immediate
Thu Jul 26 06:54:16 2012
Checker run found 2 new persistent data failures
Thu Jul 26 06:54:18 2012
Restore of datafile copy /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_812kvfk3_.dbf complete to datafile 5 /home/oracle/app/oracle/oradata/orcl/example01.dbf
checkpoint is 13657207
Thu Jul 26 06:54:28 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace EXAMPLE
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace EXAMPLE
Thu Jul 26 06:54:40 2012
alter tablespace example online
Completed: alter tablespace example online
...

That's all.