The Geek Diary

How to Move User datafiles between ASM Diskgroups using Incrementally Updated Backups

by

So the task here is to move user datafiles (not system or sysaux datafiles) between ASM diskgroups using the incrementally updated backups. The high-level steps are:

  1. Take level 0 datafile copy as a base to the new Diskgroup. This copy has either a system-defined or user-defined tag.
  2. Periodically take level 1 differential backups created with the same tag as the level 0 datafile copy. Use BACKUP FOR RECOVER OF COPY command so that an incremental backup is created containing only blocks changed since the most recent incremental backup with the same tag.
  3. Periodically, apply the incremental backup to the level 0 datafile copy. Because the datafile copy has been updated with more recent changes, it would require less media recovery.
  4. Take the datafile offline.
  5. Use RMAN to switch the datafile to the latest image copy.
  6. Recover the datafile to make it consistent with the other files.
  7. Bring the datafile online.
  8. Verify by querying v$datafile and proceed with deleting the old file.
Note: These steps are not applicable for system or sysaux datafiles.

example

1. Query v$datafile for the location of the datafile (6 in this test case):

FILE# NAME                                                                        STATUS
---------- ---------------------------------------------------------------------- -------
    1 +ASM_DISK1/profit/datafile/system.256.766889099                             SYSTEM
    2 +ASM_DISK1/profit/datafile/undotbs1.258.766889099                           ONLINE
    3 +ASM_DISK1/profit/datafile/sysaux.257.766889099                             ONLINE
    6 +ASM_DISK1/profit/datafile/test.275.767269709                               ONLINE

2. Making Incrementally Updated Backups:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATAFILECOPY FORMAT '+ASM_DISK2' datafile 6;

Starting backup at 25-06-2012 18:32:02
using channel ORA_DISK_1
no parent backup or copy of datafile 6 found        => This is a level 0 backup as there is no parent backup.
channel ORA_DISK_1: starting datafile copy                
input datafile fno=00006 name=+ASM_DISK1/profit/datafile/test.275.767269709
output filename=+ASM_DISK2/profit/datafile/test.272.786911523 tag=INCR_UPDATE recid=25 stamp=786911531
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 25-06-2012 18:32:18
RMAN> list copy of datafile 6;

List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
25      6    A 25-06-2012 18:32:11 1356009    25-06-2012 18:32:03 +ASM_DISK2/profit/datafile/test.272.786911523
SQL> alter system switch logfile;

3. Second time when the command is fired it creates incremental level 1 backups.

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATAFILECOPY FORMAT '+ASM_DISK2' datafile 6;

Starting backup at 25-06-2012 18:35:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=+ASM_DISK1/profit/datafile/test.275.767269709
channel ORA_DISK_1: starting piece 1 at 25-06-2012 18:35:20
channel ORA_DISK_1: finished piece 1 at 25-06-2012 18:35:27
piece handle=+ASM_DISK2/profit/backupset/2012_06_25/nnndn1_tag20120625t183519_0.271.786911721 tag=TAG20120625T183519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 25-06-2012 18:35:27

4. Apply the incremental backup to the level 0 data file copy

RMAN> RECOVER COPY OF DATAfile 6  WITH TAG 'incr_update';

Starting recover at 25-06-2012 18:37:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00006 name=+ASM_DISK2/profit/datafile/test.272.786911523
channel ORA_DISK_1: reading from backup piece +ASM_DISK2/profit/backupset/2012_06_25/nnndn1_tag20120625t183519_0.271.786911721
channel ORA_DISK_1: restored backup piece 1
piece handle=+ASM_DISK2/profit/backupset/2012_06_25/nnndn1_tag20120625t183519_0.271.786911721 tag=TAG20120625T183519
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 25-06-2012 18:37:14
RMAN>  list copy of datafile 6;

List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
26      6    A 25-06-2012 18:37:13 1356106    25-06-2012 18:35:20 +ASM_DISK2/profit/datafile/test.272.786911523

4. Take the datafile offline.

SQL> alter database datafile 6 offline;

5. Switch the datafile to the latest image copy and recover the datafile. 

RMAN> switch datafile 6 to copy;
RMAN> recover datafile 6;

6. Bring the datafile online.

SQL> alter database datafile 6 online;
SQL> select file#, name,status from v$datafile;

FILE# NAME                                                                   STATUS
----- ---------------------------------------------------------------------- -------
    1 +ASM_DISK1/profit/datafile/system.256.766889099                        SYSTEM
    2 +ASM_DISK1/profit/datafile/undotbs1.258.766889099                      ONLINE
    3 +ASM_DISK1/profit/datafile/sysaux.257.766889099                        ONLINE
    6 +ASM_DISK2/profit/datafile/test.272.786911523                          ONLINE

You May Also Like