This article is about recovery of corrupted/damaged/lost/canceled NONSYSTEM datafile switching to an image copy when database in ARCHIVELOG mode.

We must have an image copy of my datafiles:

The following command is use to take copy.

[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

Source

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.