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
