How to recover from a loss of a nonsystem tablespace on the same location while the database is closed

Tweet

In a previous post we saw how to proceed when you lose a non-system tablespace while the database is open.
Today I'm going to describe another way to restore it while the database is not open: for some reasons your database crashed and while trying to start it up you are getting "ORA-01157: cannot identify/lock data file %s - see DBWR trace file".

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*
-rw-rw---- 1 oracle oracle 85991424 Nov 30 02:08 /home/oracle/app/oracle/oradata/orcl/example01.dbf
[oracle@localhost orcl]$ rm example01.dbf 

The database is not open and if I try to execute startup command it signals ORA-01157 error. That error means instance was not able to open the example01.dbf (data)file.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 11 03:15:10 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'

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

...
Fri Jan 11 03:15:44 2013
ALTER DATABASE OPEN
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_12904.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12961.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
...

Even if I know how to solve this kind of problem, I would like to take some of your time and use a new RMAN feature, the Data Recovery Advisor.
It can detect and show current restore and recovery problems occurring in your database, advices you about their resolution and even execute for you all the RMAN commands to fix problems.

When I remember to use Data Recovery Advisor I usually perform the following four commands:

RMAN> list failure; 
RMAN> list failure  detail;
RMAN> advice failure ;
RMAN> repair failure preview;

Let's see what Data Recovery Advisor shows us executing my previous commands. The instance is in MOUNT state and this is the output executing LIST FAILURE command:

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jan 11 05:20:45 2013

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing

As you can see one database failure exists from "11-01-2013 03:15:46" and it has a failure id as 363, it's status is still OPEN having also a HIGH priority.
We want now to have more details on that specific failure id: simply execute the following command to know example01.dbf datafile is missing and because it contains some objects they are not available.

RMAN> list failure 363 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 363
  Failure ID Priority Status    Time Detected       Summary
  ---------- -------- --------- ------------------- -------
  9865       HIGH     OPEN      11-01-2013 03:15:46 Datafile 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf' is missing
    Impact: Some objects in tablespace EXAMPLE might be unavailable

To obtain advices on how to solve your current failure run the following command.
It will show the manual or automated actions required to repair your database. The commands you can use to solve your issue automatically are contained into the repair script.

RMAN> advise failure 363;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /home/oracle/app/oracle/oradata/orcl/example01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4119770863.hm

You can now open with a text editor your repair script to look at the suggested commands, but why not use again RMAN client and the REPAIR FAILURE PREVIEW command ?
As you can see RMAN displays the repair commands without actually running them.

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4119770863.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 5;
   recover datafile 5;
   sql 'alter database datafile 5 online';

So it's time to recover our tablespace executing the restore command and the name of your lost tablespace:

RMAN> restore tablespace example;

Starting restore at 11-01-2013 06:45:05
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=32 STAMP=804375950 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_8gz9rctt_.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 11-01-2013 06:45:12

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

RMAN> recover tablespace example;

Starting recover at 11-01-2013 06:45:18
using channel ORA_DISK_1

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

Finished recover at 11-01-2013 06:45:19

The tablespace is now recovered we can try to open our database. for your users.

RMAN> alter database open;

database opened

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

...
Fri Jan 11 06:45:10 2013
Restore of datafile copy /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_8gz9rctt_.dbf complete to datafile 5 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  checkpoint is 14769061
Fri Jan 11 06:45:19 2013
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 2 Seq 38 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
  Mem# 1: /home/oracle/app/oracle/oradata/orcl/redo02b.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
 tablespace EXAMPLE
alter database open
...

That's all.