In this article, we are going to have look on How to switch back to the original location of the previously corrupted/damaged/lost/canceled 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 see the datafile of USERS tablespace is currently located in the flash recovery area. And there is no USERS datafile on the location where other datafiles are.
[oracle@localhost orcl]$ pwd /home/oracle/app/oracle/oradata/orcl [oracle@localhost orcl]$ ll total 2511684 -rw-rw---- 1 oracle oracle 7348224 Jul 20 05:50 APEX_1930613455248703.dbf -rw-rw-r-- 1 oracle oracle 0 Jul 19 07:53 backup -rw-rw---- 1 oracle oracle 9748480 Jul 20 06:18 control01.ctl -rw-rw---- 1 oracle oracle 85991424 Jul 20 05:50 example01.dbf -rw-rw---- 1 oracle oracle 52429312 Jul 20 05:50 redo01.log -rw-rw---- 1 oracle oracle 52429312 Jul 20 06:18 redo02.log -rw-rw---- 1 oracle oracle 52429312 Jul 20 05:50 redo03.log -rw-rw---- 1 oracle oracle 1158684672 Jul 20 06:17 sysaux01.dbf -rw-rw---- 1 oracle oracle 871374848 Jul 20 06:15 system01.dbf -rw-rw---- 1 oracle oracle 20979712 Jul 20 05:51 temp01.dbf -rw-rw---- 1 oracle oracle 41951232 Jul 20 06:18 undotbs01.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 19 07:48 users01_damaged.dbf
First thing to do is to copy your datafile using the format clause, specifying where you want to create your datafile copy.
RMAN> backup as copy datafile 4 format='/home/oracle/app/oracle/oradata/orcl/users01.dbf'; Starting backup at 20-07-2012 06:20:35 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf tag=TAG20120720T062036 RECID=8 STAMP=789114078 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 Finished backup at 20-07-2012 06:21:22 Starting Control File and SPFILE Autobackup at 20-07-2012 06:21:23 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_20/o1_mf_s_789114083_80lptnhd_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 20-07-2012 06:21:26
Now you have to switch to your new datafile location, putting that datafile offline.
RMAN> sql 'alter database datafile 4 offline'; sql statement: alter database datafile 4 offline
Switch to your current datafile copy. RMAN knows exactly where it’s located.
RMAN> switch datafile 4 to copy; datafile 4 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/users01.dbf"
Recover your datafile because many transactions could be occurred between your “backup as copy datafile” command and putting your datafile offline.
RMAN> recover datafile 4; Starting recover at 20-07-2012 06:30:44 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 20-07-2012 06:30:49
Let your datafile be available to all the users again.
RMAN> sql 'alter database datafile 4 online'; sql statement: alter database datafile 4 online
Let’s see what the report schema command says now:
RMAN> report schema; 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/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
As you can see now in the default location of all datafiles of my database there’s also the new USERS datafile.
[oracle@localhost orcl]$ ll total 2742324 -rw-rw---- 1 oracle oracle 7348224 Jul 20 05:50 APEX_1930613455248703.dbf -rw-rw-r-- 1 oracle oracle 0 Jul 19 07:53 backup -rw-rw---- 1 oracle oracle 9748480 Jul 20 06:34 control01.ctl -rw-rw---- 1 oracle oracle 85991424 Jul 20 05:50 example01.dbf -rw-rw---- 1 oracle oracle 52429312 Jul 20 05:50 redo01.log -rw-rw---- 1 oracle oracle 52429312 Jul 20 06:33 redo02.log -rw-rw---- 1 oracle oracle 52429312 Jul 20 05:50 redo03.log -rw-rw---- 1 oracle oracle 1158684672 Jul 20 06:32 sysaux01.dbf -rw-rw---- 1 oracle oracle 871374848 Jul 20 06:32 system01.dbf -rw-rw---- 1 oracle oracle 20979712 Jul 20 05:51 temp01.dbf -rw-rw---- 1 oracle oracle 41951232 Jul 20 06:33 undotbs01.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 19 07:48 users01_damaged.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 20 06:32 users01.dbf
Now it’s time to create a new image copy in the flash recovery area of the USERS datafile. Let’s see the current situation:
RMAN> list copy of datafile 4; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 9 4 A 20-07-2012 06:30:29 13599396 20-07-2012 06:20:37 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf Tag: TAG20120719T074531
Issue the familiar “backup as copy” command to create a new image copy.
RMAN> backup as copy datafile 4; Starting backup at 20-07-2012 06:35:32 using channel ORA_DISK_1 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_80lqo53d_.dbf tag=TAG20120720T063532 RECID=10 STAMP=789114947 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16 Finished backup at 20-07-2012 06:35:48 Starting Control File and SPFILE Autobackup at 20-07-2012 06:35:48 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_20/o1_mf_s_789114949_80lqooxw_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 20-07-2012 06:35:51
Your “list copy” command shows now another available image copy of your USERS datafile.
RMAN> list copy of datafile 4; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - ------------------- ---------- ------------------- 10 4 A 20-07-2012 06:35:47 13600245 20-07-2012 06:35:33 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80lqo53d_.dbf Tag: TAG20120720T063532 9 4 A 20-07-2012 06:30:29 13599396 20-07-2012 06:20:37 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf Tag: TAG20120719T074531
