How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile switching to an image copy (database in ARCHIVELOG mode)

Tweet

Today I will recover a lost datafile using an image copy as I told in advance at the end of this previous post .

So the first thing I must have is an image copy of my datafiles: to do that I issued the following commands.

[oracle@localhost orcl]$ rman target /
RMAN> backup as copy database;

Starting backup at 19-07-2012 07:45:31
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_80j7dd8l_.dbf tag=TAG20120719T074531 RECID=2 STAMP=789032830
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:40
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_80j7hk9c_.dbf tag=TAG20120719T074531 RECID=3 STAMP=789032923
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf tag=TAG20120719T074531 RECID=4 STAMP=789032942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_80j7m0w4_.dbf tag=TAG20120719T074531 RECID=5 STAMP=789032948
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_80j7m8fq_.dbf tag=TAG20120719T074531 RECID=6 STAMP=789032955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_apex_193_80j7mhmf_.dbf tag=TAG20120719T074531 RECID=7 STAMP=789032959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-07-2012 07:49:20

Starting Control File and SPFILE Autobackup at 19-07-2012 07:49:21
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_19/o1_mf_s_789032961_80j7mkvb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-07-2012 07:49:22

Now let's simulate a lost datafile of a NONSYSTEM tablespace

[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf

The RMAN report command is no more able to identify the size of the lost datafile

[oracle@localhost orcl]$ rman target /
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    0        USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

In the alert log we can see errors like the following:

...
Thu Jul 19 08:04:52 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8591.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...

It's time to restore and recover our datafile. First put it offline

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

Then tell to use the image copy (skipping at least to copy it in the default location)

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy
"/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf"

Recover the datafile

RMAN> recover datafile 4;

Starting recover at 19-07-2012 08:06:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK

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

Finished recover at 19-07-2012 08:06:44

Bring it online

RMAN> sql ' alter database datafile 4 online';

sql statement:  alter database datafile 4 online

From the alert log you can see:

...
Thu Jul 19 08:06:18 2012
alter database datafile 4 offline
Completed: alter database datafile 4 offline
Thu Jul 19 08:06:29 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8535.trc:
ORA-19625: error identifying file
/home/oracle/app/oracle/oradata/orcl/users01.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
WARNING: switching recovery area datafile copy
/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
as database area datafile.
This datafile is no more accounted into used space. Consider decrementing
db_recovery_file_dest_size parameter value by 235929600 bytes.
Switch of datafile 4 complete to datafile copy 
checkpoint is 13595062
Thu Jul 19 08:06:43 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 4
Thu Jul 19 08:06:56 2012
alter database datafile 4 online
Completed:  alter database datafile 4 online
...

Now the RMAN report command is able again to obtain information about your datafile

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

As you can easily see, with this scenario time makes the difference compared with the previous post In this scenario it took only 38 seconds to get back online with the database.

TOTAL TIME: 38
Thu Jul 19 08:06:18 2012
Thu Jul 19 08:06:56 2012

Of course I used a simple test machine, but compared with the previous scenario (one minute and 34 seconds) to get back online a datafile of only 250MB, I spent now only 38 seconds. You should consider this approach if you want to get back your database online as soon as possible. You should test your timesavings, but it could let you save even hours... In the next scenario I will move back that datafile located in the flash recovery area to the original location. That's all.