MOVING TABLESPACE/DATAFILES ACROSS DISK GROUP IN ASM (ONLINE & OFFLINE)

move a datafile from a file system to ASM(this includes system tablespace, data tablespace and datafiles etc)

1. MOVING SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 8 12:00:24 2011

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1236111360 bytes

Fixed Size                  2225952 bytes

Variable Size             620759264 bytes

Database Buffers          587202560 bytes

Redo Buffers               25923584 bytes

Database mounted.

$rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Sep 8 12:01:52 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: Bhuvan1 (DBID=787350752, not open)

RMAN>  backup as copy tablespace system format '+SYSTEM';

Starting backup at 08-SEP-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA1/bhuvan/datafile/system.261.756901437

output file name=+SYSTEM/bhuvan/datafile/system.259.761314013 tag=TAG20110908T120653 RECID=16 STAMP=761314015

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=+DATA1/bhuvan/datafile/system.262.756901455

output file name=+SYSTEM/bhuvan/datafile/system.260.761314017 tag=TAG20110908T120653 RECID=17 STAMP=761314018

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

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=+SYSTEM/bhuvan/controlfile/backup.261.761314021 tag=TAG20110908T120653 RECID=18 STAMP=761314022

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 08-SEP-11

channel ORA_DISK_1: finished piece 1 at 08-SEP-11

piece handle=+SYSTEM/bhuvan/backupset/2011_09_08/nnsnf0_tag20110908t120653_0.262.761314023 tag=TAG20110908T120653 comment=NONE

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

Finished backup at 08-SEP-11

RMAN> switch tablespace system to copy;

datafile 1 switched to datafile copy "+SYSTEM/bhuvan/datafile/system.259.761314013"

datafile 7 switched to datafile copy "+SYSTEM/bhuvan/datafile/system.260.761314017"

$sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 8 12:14:08 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

bhuvan       MOUNTED

SQL> alter database open;

Database altered.

SQL>

2. MOVING SYSTEM DATAFILE FROM ONE DISK GROUP TO ANOTHER DISK GROUP

STEP#1: Start the database in the mount stage

STEP#2: connect to the rman and copy the datafile

RMAN> copy datafile 3 to '+SYSTEM';

Starting backup at 08-SEP-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=388 instance=BE1_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA1/bhuvan/datafile/psapundo.259.756901419

output file name=+SYSTEM/bhuvan/datafile/undo.263.761315651 tag=TAG20110908T123410 RECID=21 STAMP=761315652

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

Finished backup at 08-SEP-11

STEP#3: switch the datafile to the new disk group

RMAN> switch datafile 3 to copy;

datafile 3 switched to datafile copy "+SYSTEM/bhuvan/datafile/undo.263.761315651"

STEP#4: open the database

SQL> alter database open;

3. MOVING NON-SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP(WITH OFFLINE OPITION)

STEP#1: Offline the tablespace

RMAN>  sql ‘alter tablespace <TBS_NAME> offline’;

STEP#2: connect to the rman and copy the tablespace

RMAN> backup as copy tablespace <TBS_NAME> format ‘<+NEW_DISK’;

STEP#3: Switch the tablespace to the new location

RMAN> switch tablespace <TBS_NAME> to copy;

STEP#4: Online the tablespace

RMAN> sql ‘alter tablespace <TBS_NAME>  online’;

4. MOVING NON-SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP(WITH ONLINE OPITION)

STEP#1: connect to the rman and copy the tablespace

RMAN> backup as copy tablespace <TBS_NAME> format ‘<+NEW_DISK’;

STEP#2: Offline the tablespace

RMAN> sql ‘alter tablespace <TBS_NAME> offline’;

STEP#3: Switch the tablespace to the new location

RMAN> switch tablespace <TBS_NAME> to copy;

STEP#4: Recover the tablespace

RMAN> recover tablespace <TBS_NAME>;

STEP#4: Online the tablespace

RMAN> sql ‘alter tablespace <TBS_NAME> online’;

5. MOVING NON-SYSTEM DATAFILE FROM ONE DISK GROUP TO ANOTHER DISK GROUP(ONLINE & OFFLINE)

STEP#1: Offline the datafile

RMAN> sql ‘alter datafile <datafile_number or name> offline’;

STEP#2: connect to the rman and copy the tablespace

RMAN> COPY DATAFILE <datafile_number or name> to ‘<+NEW_DISK’>;

STEP#3: Switch the tablespace to the new location

RMAN> switch datafile <datafile_number or name> to copy;

STEP#4: Recover the datafile

RMAN> recover datafile <datafile_number or name>;

STEP#5: Online the tablespace

RMAN> ‘alter datafile <datafile_number or name> online’;

Happy Learning!!!!!