RMAN BACKUP AS COPY

By default the BACKUP command in RMAN creates BackupSet(s) -- each of which is one or more BackupPiece(s).  A datafile may span BackupPieces but may not span a BackupSet.

However, RMAN does allow another method -- BACKUP AS COPY.  This is akin to "User Managed Backups" created with OS commands -- except that the ALTER TABLESPACE | DATABASE BEGIN BACKUP command does not have to be issued.

BACKUP AS COPY creates a byte-for-byte copy of each datafile [except, inasmuch, blocks being modified by concurrent writes to the datafile].

If an active datafile is corrupted, the DBA can choose to SWITCH TO COPY instead of having to restore the datafile copy.  Thus, a switch can be a fast operation.  Obviously, the DBA must plan carefully where he creates such copies if he intends to SWITCH anytime later  (he wouldn't keep a datafile copy on a non-protected [RAID or ASM] storage target).

Here's a simple demo :

SQL> select file_id, file_name 
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf

SQL> alter tablespace add_tbs
  2  add datafile '/oradata/orcl/add_tbs_02.dbf' size 100M;

Tablespace altered.

SQL> 
SQL> create table hemant.add_table tablespace add_tbs as select * from dba_source;

Table created.

SQL> select file_id, count(*)
  2  from dba_Extents
  3  where owner = 'HEMANT'
  4  and segment_name = 'ADD_TABLE'
  5  group by file_id
  6  order by 1
  7  /

   FILE_ID   COUNT(*)
---------- ----------
        14         48
        15         37

SQL>  

So, I have begun with two datafiles and extents spread across both datafiles.
I now take backups of the two datafiles.

RMAN> backup as copy datafile 14 format '/tmp/ADDTBS01.DBF';

Starting backup at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
output file name=/tmp/ADDTBS01.DBF tag=TAG20120603T224508 RECID=5 STAMP=785025915
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> backup as copy datafile 15 format '/tmp/add_tbs_second.dbf' ;

Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/oradata/orcl/add_tbs_02.dbf
output file name=/tmp/add_tbs_second.dbf tag=TAG20120603T224538 RECID=6 STAMP=785025939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> 
RMAN> list backup of tablespace add_tbs completed after "sysdate-1";

specification does not match any backup in the repository

RMAN> list backup of datafile 14;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    530.30M    DISK        00:01:44     23-MAR-12      
        BP Key: 18   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T231454
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5192430    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    530.70M    DISK        00:01:23     23-MAR-12      
        BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T235528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5194695    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    1.23M      DISK        00:00:00     24-MAR-12      
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001527
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5196666    24-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    487.60M    DISK        00:00:00     01-JAN-12      
        BP Key: 29   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T112516
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 4956816    01-JAN-12 /oradata/orcl/add_tbs_01.dbf

RMAN> 

Notice how a LIST BACKUP doesn't show the backups ! Why ? Because it shows only BACKUPSET Backups ! If I want to see these BACKUP AS COPY backups, I must use LIST COPY :

RMAN> list copy of datafile 14;

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

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
5       14   A 03-JUN-12       5353830    03-JUN-12      
        Name: /tmp/ADDTBS01.DBF
        Tag: TAG20120603T224508

4       14   A 24-MAR-12       5196322    24-MAR-12      
        Name: /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf


RMAN> list copy of datafile 15;

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

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
6       15   A 03-JUN-12       5353869    03-JUN-12      
        Name: /tmp/add_tbs_second.dbf
        Tag: TAG20120603T224538


RMAN> 

What happens when I lose the datafiles ?

SQL> !rm /oradata/orcl/add_tbs_0[1-2].dbf

SQL> select count(*) from hemant.add_table;

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

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from hemant.add_table;

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

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from hemant.add_table;

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

SQL> alter tablespace add_tbs offline;

Tablespace altered.

SQL> alter tablespace add_Tbs online;
alter tablespace add_Tbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/oradata/orcl/add_tbs_01.dbf'


SQL> 

As an aside : Note how the extents and datafiles seemed to be accessible until I tried to ONLINE the datafiles.
Now, I do not have regular backups. Can I use the datafile copies ?  YES.

RMAN> restore datafile 14;

Starting restore at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_DISK_1: restoring datafile 00014
input datafile copy RECID=5 STAMP=785025915 file name=/tmp/ADDTBS01.DBF
destination for restore of datafile 00014: /oradata/orcl/add_tbs_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00014
output file name=/oradata/orcl/add_tbs_01.dbf RECID=0 STAMP=0
Finished restore at 03-JUN-12

RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/tmp/add_tbs_second.dbf"

RMAN> 

I actually restored datafile 14 to the target location but only switched datafile 15 to the copy. This is clearly evidenced by :

SQL> select file_id, file_name  
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /tmp/add_tbs_second.dbf

SQL> 

I can now simply recover the tablespace.

RMAN> recover tablespace add_tbs;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

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

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs online';

sql statement: alter tablespace add_tbs online

RMAN> 

and query it now.

SQL> select /*+ FULL */ count(*) from hemant.add_table;

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

SQL> alter tablespace add_tbs offline;

Tablespace altered.

SQL> alter tablespace add_tbs online;

Tablespace altered.

SQL> select /*+ FULL */ count(*) from hemant.add_table;

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

SQL>  

I can also restore datafile 15 by using the same "AS COPY" and "SWITCH" trick.

RMAN> backup as copy datafile 15 format '/oradata/orcl/add_tbs_02.dbf' ;

Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/tmp/add_tbs_second.dbf
output file name=/oradata/orcl/add_tbs_02.dbf tag=TAG20120603T230025 RECID=7 STAMP=785026826
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs offline';

sql statement: alter tablespace add_tbs offline

RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/oradata/orcl/add_tbs_02.dbf"

RMAN> recover tablespace add_tbs;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

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

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs online';

sql statement: alter tablespace add_tbs online

RMAN> 

SQL> select file_id, file_name
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /oradata/orcl/add_tbs_02.dbf

SQL> 

Today's question :  When is BACKUP AS COPY [and, optionally, SWITCH DATAFILE TO COPY] really useful ?  Which are the scenarios you would use this facility ?

.
.
.