Moving any File between ASM Diskgroups 1


Introduction

In our production environment we have created a data ASM diskgroup with inappropriate allocation unit (_asm_ausize) and stripe size (_asm_stripesize) and put everything into this diskgroup. Later on a new diskgroup with corrected parameters was created and a need to move anything in the old group to the new one has emerged. Following content has to be moved from old diskgroup to new one:

  • 154 data files including files of SYSTEM and SYSAUX tablespace
  • 17 temporary files
  • 1 change tracking file
  • 1 control file copy
  • 1 spfile

In a series of post, I will be explaining the different ways of doing the same jobs and writing about the possible pitfalls.

Moving Datafiles

Online Datafile Move

Steps

  1. Copy datafile of the tablespace to the new diskgroup
  2. Bring tablespace to offline mode
  3. Switch datafile to its new location
  4. Recover the tablespace
  5. Bring tablespace to online mode

Example

RMAN> run{
2>
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> copy datafile
169 to ‘+DGRP’;
4> release channel disk1;
5> }

allocated channel: disk1
channel disk1: sid=450 devtype=DISK

Starting backup at 12-JUN-08 21:10:19
channel disk1: starting datafile copy
input datafile fno=00169 name=+DGROUP1/dds/datafile/cmp_default.463.645917347
output filename=+DGRP/dds/datafile/cmp_default.1093.657234623 tag=TAG20080612T211021 recid=7533 stamp=657234623
channel disk1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JUN-08 21:10:25

Starting Control File and SPFILE Autobackup at 12-JUN-08 21:10:25
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657234626.7000.657234627 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:10:30

released channel: disk1

RMAN> sql alter tablespace cmp_default offline‘;

sql statement: alter tablespace cmp_default offline

RMAN> switch datafile 169 to copy;

datafile 169 switched to datafile copy “+DGRP/dds/datafile/cmp_default.1093.657234623”

RMAN> recover tablespace cmp_default;

Starting recover at 12-JUN-08 21:13:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK

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

Finished recover at 12-JUN-08 21:13:26

RMAN> sql alter tablespace cmp_default online‘;

sql statement: alter tablespace cmp_default online

RMAN>

Online Tablespace Move(Multiple Datafiles)

Steps

  1. Copy tablespace (allfiles) to the new diskgroup
  2. Bring tablespace to offline mode
  3. Switch tablespace (all datafiles) to its new location
  4. Recover the tablespace
  5. Bring tablespace to online mode

Example

RMAN> run{
2>      ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3>      ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
4>      ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;
5>      ALLOCATE CHANNEL disk4 DEVICE TYPE DISK;
6>      backup as copy tablespace USERS format ‘+DGRP’;
7>      release channel disk1;
8>      release channel disk2;
9>      release channel disk3;
10>    release channel disk4;
11> }

allocated channel: disk1
channel disk1: sid=450 devtype=DISK

allocated channel: disk2
channel disk2: sid=383 devtype=DISK

allocated channel: disk3
channel disk3: sid=232 devtype=DISK

allocated channel: disk4
channel disk4: sid=68 devtype=DISK

Starting backup at 12-JUN-08 21:38:03
channel disk1: starting datafile copy
input datafile fno=00112 name=+DGROUP1/dds/datafile/users.322.644072045
channel disk2: starting datafile copy
input datafile fno=00113 name=+DGROUP1/dds/datafile/users.321.644072157
channel disk3: starting datafile copy
input datafile fno=00114 name=+DGROUP1/dds/datafile/users.320.644072323
channel disk4: starting datafile copy
input datafile fno=00004 name=+DGROUP1/dds/datafile/users.261.643638281
output filename=+DGRP/dds/datafile/users.1097.657236289 tag=TAG20080612T213804 recid=7535 stamp=657236396
channel disk4: datafile copy complete, elapsed time: 00:01:55
output filename=+DGRP/dds/datafile/users.1094.657236285 tag=TAG20080612T213804 recid=7536 stamp=657236494
channel disk2: datafile copy complete, elapsed time: 00:03:31
output filename=+DGRP/dds/datafile/users.1095.657236285 tag=TAG20080612T213804 recid=7538 stamp=657236497
channel disk1: datafile copy complete, elapsed time: 00:03:34
output filename=+DGRP/dds/datafile/users.1096.657236289 tag=TAG20080612T213804 recid=7537 stamp=657236496
channel disk3: datafile copy complete, elapsed time: 00:03:34
Finished backup at 12-JUN-08 21:41:38

Starting Control File and SPFILE Autobackup at 12-JUN-08 21:41:38
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657236498.6997.657236501 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:41:46

released channel: disk1

released channel: disk2

released channel: disk3

released channel: disk4

RMAN> sql ‘alter tablespace users offline’;

sql statement: alter tablespace users offline

RMAN> switch tablespace users to copy;

datafile 4 switched to datafile copy “+DGRP/dds/datafile/users.1097.657236289”
datafile 112 switched to datafile copy “+DGRP/dds/datafile/users.1095.657236285”
datafile 113 switched to datafile copy “+DGRP/dds/datafile/users.1094.657236285”
datafile 114 switched to datafile copy “+DGRP/dds/datafile/users.1096.657236289”

RMAN> recover tablespace users;

Starting recover at 12-JUN-08 21:44:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK

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

Finished recover at 12-JUN-08 21:44:40

RMAN> sql ‘alter tablespace users online’;

sql statement: alter tablespace users online

RMAN>

Less High Available Online Tablespace Move(Multiple Datafiles)

Steps

  1. Bring tablespace to offline mode
  2. Copy tablespace (allfiles) to the new diskgroup
  3. Switch tablespace (all datafiles) to its new location
  4. Bring tablespace to online mode

Different from the previous one this solution is the one explained in Metalink Note:390274.1

Taking datafiles offline before starting the copy operation makes recovery unnecessary. However if the tablespaces are too large (Big File tablespaces are heavily used in our datawarehouse. A single file of size 5 TB), tablespace will be unavailable till the end of copy operation.

Example

RMAN> sql ‘alter tablespace TRF_BIG_B_2006M05 offline’;

sql statement: alter tablespace TRF_BIG_B_2006M05 offline

RMAN> run{
2>      ALLOCATE CHANNEL
disk1 DEVICE TYPE DISK;
3>      backup as copy tablespace
TRF_BIG_B_2006M05 format ‘+DGRP’;
4>      release channel
disk1;
5> }

allocated channel: disk1
channel disk1: sid=450 devtype=DISK

Starting backup at 12-JUN-08 21:49:17
channel disk1: starting datafile copy
input datafile fno=00030 name=+DGROUP1/dds/datafile/trf_big_b_2006m05.388.644681639
output filename=+DGRP/dds/datafile/trf_big_b_2006m05.1098.657236959 tag=TAG20080612T214918 recid=7543 stamp=657236968
channel disk1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 12-JUN-08 21:49:33

Starting Control File and SPFILE Autobackup at 12-JUN-08 21:49:33
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657236973.6993.657236975 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:49:37

released channel: disk1

RMAN> switch tablespace TRF_BIG_B_2006M05 to copy;

datafile 30 switched to datafile copy “+DGRP/dds/datafile/trf_big_b_2006m05.1098.657236959”

RMAN> sql
‘alter tablespace TRF_BIG_B_2006M05 online’;

sql statement: alter tablespace TRF_BIG_B_2006M05 online

RMAN>

Online More than One Tablespace Move

Steps

  1. Copy all tablespaces (allfiles) to the new diskgroup
  2. Do for all tablespaces you move
    • Bring tablespace to offline mode
    • Switch tablespace (all datafiles) to its new location
    • Recover the tablespace
    • Bring tablespace to online mode

If you move more than one tablespace using this approach will decrease the manual work you do.

Example

RMAN> run{
2>      ALLOCATE CHANNEL
disk1 DEVICE TYPE DISK;
3>      ALLOCATE CHANNEL
disk2 DEVICE TYPE DISK;
4>      backup as copy tablespace
COMPRESSED_BTS,COMPRESS_BTS_02 format ‘+DGRP’;
5>      release channel
disk1;
6>      release channel
disk2;
7> }

allocated channel: disk1
channel disk1: sid=450 devtype=DISK

allocated channel: disk2
channel disk2: sid=383 devtype=DISK

Starting backup at 12-JUN-08 21:56:17
channel disk1: starting datafile copy
input datafile fno=00163 name=+DGROUP1/dds/datafile/compressed_bts.298.644699643
channel disk2: starting datafile copy
input datafile fno=00164 name=+DGROUP1/dds/datafile/compress_bts_02.297.644708473
output filename=+DGRP/dds/datafile/compress_bts_02.1100.657237379 tag=TAG20080612T215617 recid=7545 stamp=657237431
channel disk2: datafile copy complete, elapsed time: 00:00:55
output filename=+DGRP/dds/datafile/compressed_bts.1099.657237379 tag=TAG20080612T215617 recid=7546 stamp=657238158
channel disk1: datafile copy complete, elapsed time: 00:13:10
Finished backup at 12-JUN-08 22:09:28

Starting Control File and SPFILE Autobackup at 12-JUN-08 22:09:29
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657238169.6991.657238171 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 22:09:33

released channel: disk1

released channel: disk2

RMAN> sql ‘alter tablespace COMPRESSED_BTS offline’;

sql statement: alter tablespace COMPRESSED_BTS offline

RMAN> sql ‘alter tablespace COMPRESS_BTS_02 offline’;

sql statement: alter tablespace COMPRESS_BTS_02 offline

RMAN> switch tablespace COMPRESSED_BTS,COMPRESS_BTS_02 to copy;

datafile 163 switched to datafile copy “+DGRP/dds/datafile/compressed_bts.1099.657237379”
datafile 164 switched to datafile copy “+DGRP/dds/datafile/compress_bts_02.1100.657237379”

RMAN> recover tablespace COMPRESS_BTS_02,COMPRESSED_BTS;

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

Finished recover at 12-JUN-08 22:25:25

RMAN> sql ‘alter tablespace COMPRESS_BTS_02 online’;

sql statement: alter tablespace COMPRESS_BTS_02 online

RMAN> sql ‘alter tablespace COMPRESSED_BTS online’;

sql statement: alter tablespace COMPRESSED_BTS online

RMAN>

Offline Tablespace Move

Unfortunately, if you need to move SYSTEM or SYSAUX tablespaces to some other diskgroup, you are unlucky. You need to shutdown your database for a while.

Steps

  1. shutdown your database
  2. startup it in mount mode
  3. copy datafile to new diskgroup
  4. switch datafiles to new diskgroup
  5. Open your database

Sample

himalaya@oracle $ srv

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jun 13 00:07:00 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                  2402240 bytes
Variable Size            2843793472 bytes
Database Buffers         1.3237E+10 bytes
Redo Buffers               22708224 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

himalaya@oracle $ rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Fri Jun 13 00:13:39 2008

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

connected to target database: DDS (DBID=1378915377, not open)

RMAN> copy datafile 1 to ‘+DGRP’;

Starting backup at 13-JUN-08 00:51:49
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=77 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DGROUP1/dds/datafile/system.258.643638267
output filename=+DGRP/dds/datafile/system.1102.657247927 tag=TAG20080613T005205 recid=7551 stamp=657247953
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 13-JUN-08 00:52:40

Starting Control File and SPFILE Autobackup at 13-JUN-08 00:52:41
piece handle=+RGRP/dds/autobackup/2008_06_13/s_657247505.6986.657247963 comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUN-08 00:52:49

RMAN> switch datafile 1 to copy;

datafile 1 switched to datafile copy “+DGRP/dds/datafile/system.1102.657247927”

RMAN> alter database open;

database opened

RMAN>

That’s all for tonight. See you…

About kocakahin

Just a computer engineer

Posted on June 13, 2008, in Oracle. Bookmark the permalink. 4 Comments.

  1. This is really informative. I am planning to do the same thing in my production environment and followed your examples on a test RAC database. It works just the same!
    Good job!

    There’s also another method of moving data files with the DBMS_FILE_TRANSFER package – see Metalink Note Note:330103.1 One caveat with this – there’s a bug which prevents dropping the old data files after using the utility.

    jadba

  2. Good job Husnu, thanks

  1. Pingback: Moving any File between ASM Diskgroups 2 « The great grandson of Husnu Sensoy

  2. Pingback: Move or Rename Datafile in RAC + ASM Environment « Adhika's Weblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: