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.dbfThe 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 failureLet'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:detail; RMAN> advice failure ; RMAN> repair failure preview;
[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 missingAs 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.hmYou 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:12After 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:19The tablespace is now recovered we can try to open our database. for your users.
RMAN> alter database open; database openedAn 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.