AristaDBA’s Oracle Blog….

Its always a requirement when we are doing recovery that it must get complete as soon as possible. Also, the time taken to do the restore should be as minimum as possible. A pretty tough thing to do if you are managing a large db of few hundred gigabytes or terabytes. Fortunately, as the requirements do keep coming, Oracle database also keeps on bringing new features to cater those requirements. This post is about one such feature which surely can help immensely when there is a requirement to get things done ASAP!

We know that the backups can be done in two ways, either by full or by incremental. The full backup is a must and is needed even for doing an incremental backup. The time taken for the full backup surely would be high and to bring it down, its suggested to do the incremental backup. There is a "fast incremental backup" also possible using the block change tracking file but that’s not the topic of this post today. For Change Tracking file and how it works, I would suggest to read the excellent paper from Alex which goes in real deep about it. So coming back to the backups, we know this as well that only the backup sets are elgible get backed up incrementally. The image copies are not elgible for being a part of the incremental backup and must be backed up full all the time. Actually, there is still chance to do the backup of image copies in the incremental manner but that’s only "level 0" which is actually nothing but the full backup only. So if we are expecting a lot of changes happening to our db and we have chosen to take the backup of the database via image copies, its a must to create new copies to capture the new values entered in the db.This is not a very good thing to do if you have many many datafiles which are also very large in size. Also, in the case of the recovery, it would be a tedious thing to first restore and then recover those copies! The best way would be that if somehow we could update the already backed up image copies with the current data and also can use them for recovery without restoring them! Wouldn’t that be faster? If your answer is yes, its possible from 10g onwards using the "recoverable image copies" via incremental backups.
The concept of "recoverable image copies" and incremental backup is that the image copy which you would be creating, would be constantly updated by oracle when the next time an incremental backup call for it would be issued! For doing this, the incremental backups that you would be creating , would be applied to the image copies, thus making them "refreshed" with the current work done within db. Since the resultant image copies would be having all the data available in them, they can be used right away for doing the recovery and on top of it, without even being required to be restored. We can "switch" to these "updated copies" and thus can start using them right away! Think about a situation that you have your datafiles stored on Disk 1 and it has got bad sectors now. There are updated image copies available for you on Disk2. With this mechanism, you can simply switch over to those copies and once you are done with the troubleshooting of your original drive, you can switch back to it! And in this whole process, the only thing that would be moved will be the file location pointers within the control file which would now start pointing towards the image copies! Isn’t that a "fast recovery" ?

Enough of talking, let’s see it in action. I am using a 11201 db on OEL5 here with the name called Sample! Its having couple of tablespaces which did come at the time of the creation of this db. I shall add to it one new tablespace which would be our candidate for testing. Here is the information about the db first,

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:03:54 2010 

Copyright (c) 1982, 2009, Oracle.  All rights reserved. 

Connected to: 

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 

With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> select name from V$database; 

NAME 

--------- 

SAMPLE 

SQL> 

SQL> 

SQL> select name from V$datafile; 

NAME 

-------------------------------------------------------------------------------- 

/u01/app/oracle/oradata/sample/system01.dbf 

/u01/app/oracle/oradata/sample/sysaux01.dbf 

/u01/app/oracle/oradata/sample/undotbs01.dbf 

/u01/app/oracle/oradata/sample/users01.dbf

I shall now create a new tablespace and also a table within that tablespace which would contain some initial data. This table would go into the backup with this initial data and then we shall add more data to it which would be updated to the previously done backup using the incremenal methodology!Also, we shall take the backup as well.

SQL> create tablespace testtbs
  2  datafile 

3  '/u01/app/oracle/oradata/sample/testtbs.dbf' size 100m; 

Tablespace created. 

SQL> create table emp tablespace testtbs as select * from scott.emp; 

Table created. 

Now, using RMAN, we shall initiate the backup via image copies. Take a closer look at the command, 



RMAN> configure device type disk parallelism 4; 

using target database control file instead of recovery catalog
  new RMAN configuration parameters: 

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET; 

new RMAN configuration parameters are successfully stored 

RMAN> backup incremental level 1 for recover of copy tag 'testtbs_incr' database; 

Starting backup at 29-JUL-10
  allocated channel: ORA_DISK_1 

channel ORA_DISK_1: SID=20 device type=DISK 

allocated channel: ORA_DISK_2 

channel ORA_DISK_2: SID=142 device type=DISK 

allocated channel: ORA_DISK_3 

channel ORA_DISK_3: SID=21 device type=DISK 

allocated channel: ORA_DISK_4 

channel ORA_DISK_4: SID=143 device type=DISK 

no parent backup or copy of datafile 1 found 

no parent backup or copy of datafile 2 found 

no parent backup or copy of datafile 5 found 

no parent backup or copy of datafile 3 found 

no parent backup or copy of datafile 4 found 

channel ORA_DISK_1: starting datafile copy 

input datafile file number=00001 name=/u01/app/oracle/oradata/sample/system01.dbf 

channel ORA_DISK_2: starting datafile copy 

input datafile file number=00002 name=/u01/app/oracle/oradata/sample/sysaux01.dbf 

channel ORA_DISK_3: starting datafile copy 

input datafile file number=00005 name=/u01/app/oracle/oradata/sample/testtbs.dbf 

channel ORA_DISK_4: starting datafile copy 

input datafile file number=00003 name=/u01/app/oracle/oradata/sample/undotbs01.dbf 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_undotbs1_653b1sl7_.dbf tag=TESTTBS_INCR RECID=1 STAMP=725645515 

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:43 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_sysaux_653b1sk5_.dbf tag=TESTTBS_INCR RECID=2 STAMP=725645539 

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:43 

channel ORA_DISK_2: starting datafile copy 

input datafile file number=00004 name=/u01/app/oracle/oradata/sample/users01.dbf 

including current control file in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_ncnn1_TESTTBS_INCR_653b34w8_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

including current SPFILE in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_users_653b36by_.dbf tag=TESTTBS_INCR RECID=3 STAMP=725645544 

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:05 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnsn1_TESTTBS_INCR_653b369y_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:04 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf tag=TESTTBS_INCR RECID=4 STAMP=725645551 

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:06 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_system_653b3bqy_.dbf tag=TESTTBS_INCR RECID=5 STAMP=725645591 

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36 

Finished backup at 29-JUL-10

There are couple of interesting things here. First, we are taking a backup via image copy format. The second thing, there is an option added here which says "recover of copy". Yes, I did say backup and that’s what we did too but since we are talking about incrementally updated copies, this command contains the trick. Before opening the cards, the last interesting bit is mentioning of the incremental level 1 right away when we haven’t taken the level 0 backup yet! Again, this is how this image copy mechanism works!

What is happening is that we have asked the RMAN to take our database via image copy format with a supplied tag to it from us. But we have also mentioned that this backup, if is already done , should be just updating the previously done backup. So if we would had done a backup before, the recover command would had kicked and would had "updated" our previously dont image copy backup but if there is no level 0 backup is there to start with, Oracle would first take a level 0 backup only. This is also visible from the command output as available which does say that there was no parent backup found. So if there was no level 0 backup to start with, Oracle would take this level of backup on day 1. On the next day, the backup command would create  level 1 backup whose base would be the previous day’s level 0 backup. At this moment, for both day 1 and 2, only backup command is the one which would be working. Now, we do have a level 0 and a level 1 backup available with us. On day 3,the backup command would again create an incremental level 1 backup ranging the changes done from day 2 to day 3. Also, the Recover command would kick in now and would update the day 2 image copies with the incremental backup and the same process would keep on going on the subsequent days. For the identification of the datafiles which need to be updated like this, the use of the TAG is a must as this is the way through which the backup and recover commands would work with each other. So at any point, you have  updated image copies, incremental backups and the archived files to do the recovery! You would use the image copy, apply the incremental backup over it and wuold use the archive log files to update them with the latest work done in the db.

So, we have a backup that we have taken now as level 0 backup. Let’s add some data to our table EMP.

SQL> select count(*) from emp;
  COUNT(*) 

---------- 

14 



SQL> insert into emp select * from emp; 

14 rows created. 

SQL> / 

28 rows created. 

SQL> / 

56 rows created. 

SQL> / 

112 rows created. 

SQL> / 

224 rows created. 

SQL> / 

448 rows created. 

SQL> / 

896 rows created. 

SQL> / 

1792 rows created. 

SQL> / 

3584 rows created. 

SQL> / 

7168 rows created. 

SQL> / 

14336 rows created. 

SQL> / 

28672 rows created. 

SQL> / 

57344 rows created. 

SQL> commit; 

Commit complete. 

SQL> select name from V$datafile
    2  ; 

NAME
  -------------------------------------------------------------------------------- 

/u01/app/oracle/oradata/sample/system01.dbf 

/u01/app/oracle/oradata/sample/sysaux01.dbf 

/u01/app/oracle/oradata/sample/undotbs01.dbf 

/u01/app/oracle/oradata/sample/users01.dbf 

/u01/app/oracle/oradata/sample/testtbs.dbf 

SQL> select count(*) from emp; 

  COUNT(*)
  ---------- 

    114688 

SQL> 

So we have enough rows inserted into the table to make sure that in the next backup, there would be surely some new data that would be added to the backup files. Let’s some check some more things about the available tablespace and also about the backups that we have created.

Since we are wiling to update our image copy with the incremental backup, we can verify that its indeed update by the comparisons of the checkpoint scn of it before and after the recovery. If we check the copy of the tablespace TESTTBS that we have created, it would show the current checkpoint scn of it,

RMAN> list copy of tablespace testtbs; 

List of Datafile Copies
  ======================= 

Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 

4       5    A 29-JUL-10       804275     29-JUL-10              Name: /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

        Tag: TESTTBS_INCR

So the number is "804275". We shall use it to see whether we have got the image copies updated or not. Since we have added a lot from our intial backup, we shall kick in the backup again. This time, as we do have a level 0 backup already with us, the backup done would be a level 1 backup.

RMAN> backup incremental level 1 for recover of copy tag 'testtbs_incr' database; 

Starting backup at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

channel ORA_DISK_1: starting incremental level 1 datafile backup set 

channel ORA_DISK_1: specifying datafile(s) in backup set 

input datafile file number=00001 name=/u01/app/oracle/oradata/sample/system01.dbf 

channel ORA_DISK_1: starting piece 1 at 29-JUL-10 

channel ORA_DISK_2: starting incremental level 1 datafile backup set 

channel ORA_DISK_2: specifying datafile(s) in backup set 

input datafile file number=00002 name=/u01/app/oracle/oradata/sample/sysaux01.dbf 

input datafile file number=00004 name=/u01/app/oracle/oradata/sample/users01.dbf 

channel ORA_DISK_2: starting piece 1 at 29-JUL-10 

channel ORA_DISK_3: starting incremental level 1 datafile backup set 

channel ORA_DISK_3: specifying datafile(s) in backup set 

input datafile file number=00005 name=/u01/app/oracle/oradata/sample/testtbs.dbf 

input datafile file number=00003 name=/u01/app/oracle/oradata/sample/undotbs01.dbf 

channel ORA_DISK_3: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

including current control file in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_ncnn1_TESTTBS_INCR_653bl7yd_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:08 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

including current SPFILE in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnsn1_TESTTBS_INCR_653blj2q_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:04 

channel ORA_DISK_2: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bl43m_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:27 

channel ORA_DISK_1: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bl456_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:39 

channel ORA_DISK_3: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:49 

Finished backup at 29-JUL-10

So the backup is done. This backup would be used to update the previously created image copies. As this is the current backup, there would be a difference in the checkpoint scn of it. Let’ssee the current backup of our tablespace and compare the checkpoint scn that we have,

RMAN> list backup of tablespace testtbs; 

List of Backup Sets
  =================== 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  ------- ---- -- ---------- ----------- ------------ --------------- 

7       Incr 1  6.05M      DISK        00:00:42     29-JUL-10              BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TESTTBS_INCR 

        Piece Name: /u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp 

  List of Datafiles in backup set 7 

  File LV Type Ckp SCN    Ckp Time  Name 

  ---- -- ---- ---------- --------- ---- 

  5    1  Incr 804762     29-JUL-10 /u01/app/oracle/oradata/sample/testtbs.dbf

So the checkpoint scn of the backup of the tablespace TESTTBS is 804762 which is indeed higher than our previously done image copy. Just for the sake of comparison, let’s check one more time the image copy that we created.

RMAN> list copy of tablespace testtbs; 

List of Datafile Copies
  ======================= 

Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 

4       5    A 29-JUL-10       804275     29-JUL-10              Name: /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

        Tag: TESTTBS_INCR 

Indeed its different than the backup! Its time now to recover this image copy with the current backup and archive logs,

RMAN> recover copy of tablespace testtbs with tag 'testtbs_incr'; 

Starting recover at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

channel ORA_DISK_1: starting incremental datafile backup set restore 

channel ORA_DISK_1: specifying datafile copies to recover 

recovering datafile copy file number=00005 name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp 

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp tag=TESTTBS_INCR 

channel ORA_DISK_1: restored backup piece 1 

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 

Finished recover at 29-JUL-10

Let’s compare both backup and image copy now with each other on the basis of their checkpoint scn’s,

RMAN> list backup of tablespace testtbs; 

List of Backup Sets
  =================== 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  ------- ---- -- ---------- ----------- ------------ --------------- 

7       Incr 1  6.05M      DISK        00:00:42     29-JUL-10              BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TESTTBS_INCR 

        Piece Name: /u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp 

  List of Datafiles in backup set 7 

  File LV Type Ckp SCN    Ckp Time  Name 

  ---- -- ---- ---------- --------- ---- 

  5    1  Incr 804762     29-JUL-10 /u01/app/oracle/oradata/sample/testtbs.dbf 

RMAN> list copy of tablespace testtbs; 

List of Datafile Copies
  ======================= 

Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 

6       5    A 29-JUL-10       804762     29-JUL-10              Name: /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

        Tag: TESTTBS_INCR

As expected, both have the same checkpoint SCN now and this does mean that the image copy is incrementally updated with our previous backup and is now containing the most current information. So we have an incrementally updated image copy available with us. Its time now to put this into action and simulate a datafile loss due to a faulty hardware. Due to this, the file is removed(we won’t remove it though) and also is needed to be recovered ASAP for which the updated image copy would help us. Let’s create the error first by renaming the file,

SQL> !mv /u01/app/oracle/oradata/sample/testtbs.dbf /u01/app/oracle/oradata/sample/testtbs.dbf.orig 

SQL> alter database datafile 5 offline; 

Database altered. 

SQL> alter database datafile 5 online;
  alter database datafile 5 online 

* 

ERROR at line 1: 

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file 

ORA-01110: data file 5: '/u01/app/oracle/oradata/sample/testtbs.dbf' 

So the file is not found and thus we have to recover it. Since we have the updated copy, we won’t need to restore the image copy and we can simply switch over to it. Let’s do it,

Though the file is switched to the image copy but if we would try to bring this file online ,it won’t be possible as we would need to apply the archive logs to be applied over it to make it consistent with the rest of the database. Let’s do this now,

RMAN> switch datafile 5 to copy; 

datafile 5 switched to datafile copy "/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf" 

RMAN> recover datafile 5 ; 

Starting recover at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

starting media recovery
  media recovery complete, elapsed time: 00:00:01 

Finished recover at 29-JUL-10

So the recovery is complete. Let’s bring the file online now and verify our data from the table that’s its matching with our original count or not. If the recovery is complete, it must match!

SQL> alter database datafile 5 online; 

Database altered. 

SQL> select count(*) from emp; 

  COUNT(*)
  ---------- 

    114688

Bingo!!

So using the image copy, we have very simply and quickly have recovered our lost datafile. Let’s verify this by looking at the file name and path. If its indeed switched, the path and name must reflect towards the backup location of the file,

RMAN> report schema; 

Report of database schema for database with db_unique_name SAMPLE 

List of Permanent Datafiles
  =========================== 

File Size(MB) Tablespace           RB segs Datafile Name 

---- -------- -------------------- ------- ------------------------ 

1    680      SYSTEM               ***     /u01/app/oracle/oradata/sample/system01.dbf 

2    450      SYSAUX               ***     /u01/app/oracle/oradata/sample/sysaux01.dbf 

3    75       UNDOTBS1             ***     /u01/app/oracle/oradata/sample/undotbs01.dbf 

4    5        USERS                ***     /u01/app/oracle/oradata/sample/users01.dbf 

5    100      TESTTBS              ***     /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

List of Temporary Files
  ======================= 

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name 

---- -------- -------------------- ----------- -------------------- 

1    29       TEMP                 32767       /u01/app/oracle/oradata/sample/temp01.dbf

So we can see that the control file is now pointing for the tablespace TESTTBS to the backup location only which just confirms our recovery using the incrementally updated image copies!

In case, you are willing to bring the file back to its original location, like in our case, to the ORADATA folder, its very simple. All what we need to do is to create a backup copy of this datafile at the required location and switch it back to that destination. Let’s do that as well.

Before doing the backup of the datafile, we shall add some more data to the datafile and will verify once the switching is over that it has come or not. So first, let’s insert some more data to the table,

SQL> insert into emp select * from emp; 

114688 rows created. 

SQL> commit; 

Commit complete. 

SQL> select count(*) from emp; 

  COUNT(*)
  ---------- 

    229376

Now, we shall take the backup of the datafile as image copy to our required destination.

RMAN> backup as copy to destination '/u01/app/oracle/oradata/sample' datafile 5; 

Starting backup at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

channel ORA_DISK_1: starting datafile copy 

input datafile file number=00005 name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

output file name=/u01/app/oracle/oradata/sample/SAMPLE/datafile/o1_mf_testtbs_653cbrlg_.dbf tag=TAG20100729T163328 RECID=7 STAMP=725646812 

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 

Finished backup at 29-JUL-10

Before we can switch, the datafile must be offline so let’s do that followed by the switching of the file and recovering it. We shall query the table after bringing the datafile online to verify that our newly inserted data has come or not.

RMAN> sql "alter database datafile 5 offline"; 

sql statement: alter database datafile 5 offline 

RMAN> switch datafile 5 to copy; 

datafile 5 switched to datafile copy "/u01/app/oracle/oradata/sample/SAMPLE/datafile/o1_mf_testtbs_653cbrlg_.dbf" 

RMAN> recover datafile 5; 

Starting recover at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

starting media recovery
  media recovery complete, elapsed time: 00:00:01 

Finished recover at 29-JUL-10 

RMAN> sql "alter database datafile 5 online"; 

sql statement: alter database datafile 5 online

So the recovery is done. Let’s check the file name and path to verify that its switched back. We shall use RMAN to do so,

RMAN> report schema; 

Report of database schema for database with db_unique_name SAMPLE 

List of Permanent Datafiles
  =========================== 

File Size(MB) Tablespace           RB segs Datafile Name 

---- -------- -------------------- ------- ------------------------ 

1    680      SYSTEM               ***     /u01/app/oracle/oradata/sample/system01.dbf 

2    450      SYSAUX               ***     /u01/app/oracle/oradata/sample/sysaux01.dbf 

3    75       UNDOTBS1             ***     /u01/app/oracle/oradata/sample/undotbs01.dbf 

4    5        USERS                ***     /u01/app/oracle/oradata/sample/users01.dbf 

5    100      TESTTBS              ***     /u01/app/oracle/oradata/sample/SAMPLE/datafile/o1_mf_testtbs_653cbrlg_.dbf 

List of Temporary Files
  ======================= 

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name 

---- -------- -------------------- ----------- -------------------- 

1    29       TEMP                 32767       /u01/app/oracle/oradata/sample/temp01.dbf 

Since we had mentioned the location to the ORADATA folder, the file has been shifted over it. Let’s check the table row count as a final verification,

SQL> select count(*) from emp; 

  COUNT(*)
  ---------- 

    229376

Perfect!!!!

Though being a very powerful and relatively easy feature, I still find most of the dba’s unaware about it or if aware too, unsure how to use it. Hope the above demo would help to understand this feature somewhat and for complete understanding, nothing can beat oracle documentation,

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#i1034163