In the
previous post I used to reduce the restore time simply pointing to an image copy of my datafile. If you need to restore and recover your database
as soon as possible that way is a great solution you can use. Now it's time to swith back your datafile to the original location. We don't want to use a location
for all our datafiles and the flash recovery area just for one datafile...
Let's look first at your current report of database schema.
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
That's all.