03 June, 2012

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 ?

.
.
.

13 comments:

Noons said...

On the question:
to effect an online move of datafiles to a different file system, for example?

Noons said...

Oh, BTW:
good post! It's rare to see anyone posting on rman nowadays, and yet it's such an essential DBA tool!

Hemant K Chitale said...

Noons,
Yes, these commands can be used to
a. Move datafile(s) to another filesystem(s)
b. Move datafiles from filesystem to ASM
c. Move datafiles from ASM to filesystem
d. As a base for incrementally updated backups

Hemant

Sid said...

Hi Hemant, good post.
Can the same be used for e.g to keep a backup copy of database on another node for reporting or test purposes?

Hemant K Chitale said...

Sid,
You'd have to NFS mount the filesystem of the other node as RMAN would backup to a filesystem which is visible on the database server.
Also, since 10g RMAN is sensitive to the NFS mount options. There are a couple of Oracle Support notes on https://support.oracle.com on this.

Hemant

Nayab said...

Hi Hemant,

This is Nayab, I am a Storage cum backup admin i just wanted to understand how rman helps backup via third party like Netbackup etc.. How the DB backup works how the archive backups work ?? Please help me

Thanks,
Nayab

Hemant K Chitale said...

Nayab,
Oracle's RMAN communicates with the Media Manager (e.g. NetBackup) using a Library file that the MM provides. Also, additional parameters have to be configured to identify the NetBackup server and present the database host as a NetBackup client.

Whether you backup the database or the archivelogs or both, the "method" between RMAN and NetBackup is the same.
Once the backups are done, it becomes NetBackup's job to maintain a catalog matching each RMAN BackupPiece to the correct tape so that when RMAN requires a restore of a BackupSet, comprising of one or more BackupPieces, NetBackup identifies the correctly labelled tapes that are required to be read.

Hemant K Chitale

Anonymous said...

Can we take Backup on Disk and Tape at same time by RMAN?

Hemant K Chitale said...

Anonymous,
afaik, you can't do simultaneous backups to disk and tape. You can make two backups on disk with two separate FORMAT commands.


Hemant K Chital

Anonymous said...

HI

Is there a way to force RMAN to write all the info into a single backup piece/backup set.

I am seeing issues with very slow RMAN restores. The restore is initiated from the database server and uses Netbackup as the MML.

Assuming a single RMAN file would restore quickly than a multiplexed option.

Thanks - V

Hemant K Chitale said...

Look at the FILESPERSET parameter in the documentation. If your database has very many datafiles, you'd still end up with multiple backupsets.

If you are using a single Tape Drive, it really doesn't make a difference how many BackupPieces and BackupSets you need to Restore -- the Restore will be a serial operation. On the other hand if you have multiple Tape Drives and your NetBackup and Tape Silo implementation are configured to write to multiple Tapes concurrently, you should be using Parallelism or Multiple Channels and BackupSets for both Backups and Restores.

With backups to disk, i'd prefer multiple Channels with multiple BackupSets.

Unknown said...

Thank You..

Unknown said...

Great Post Hemant !! Quite practical & useful too