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:22Now 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.dbfThe 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.dbfIn 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 offlineThen 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:44Bring it online
RMAN> sql ' alter database datafile 4 online'; sql statement: alter database datafile 4 onlineFrom 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.dbfAs 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 2012Of 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.